Servere performance issue with compiled (pre-prepared) queries - they"re 50% slower than parsed qu

OK

I've just been completing some benchmarking comparing compiled
(pre-prepared) queries (new in 4.1.1 and above) with standard parsed
queries.

For both INSERT queries "insert into table( col1, col2,col3) values(?,?,?)"
and SELECT queries "select col1, col2,col3 where col1 between ? and ?" the
compile (pre-prepared) query is 50% slower. These time are just for the
'execute' and the queries are only prepared once.

Parsed :-

Insert
total average mean mean max min
run records time time time records time time
0 24851 7.610451 0.000306 0.000239 1008 105588.000000
231.000000

Select

run 0 average
query rows time time
0 61 0.075193 0.007519
1 7 0.049296 0.004930
2 41 0.092106 0.009211
3 9 0.048571 0.004857
4 16 0.048425 0.004843
5 62 0.092040 0.009204
6 6 0.066477 0.006648
7 1 0.280004 0.028000
8 8 0.047900 0.004790
9 4 0.113344 0.011334
10 0 0.214862 0.021486
11 0 0.140650 0.014065
12 1 0.251094 0.025109
13 0 0.123815 0.012382
14 0 0.133870 0.013387
15 6 0.306981 0.030698
16 0 0.074054 0.007405
17 0 0.095875 0.009588
18 1 0.126500 0.012650
19 8 0.109567 0.010957


Compiled :-

Insert
total average mean mean max min
run records time time time records time time
0 24851 18.224807 0.000733 0.000506 1117 303256.000000
438.000000

Select
run 0 average
query rows time time
0 0 0.086140 0.086140
1 0 0.062718 0.062718
2 0 0.109377 0.109377
3 0 0.062499 0.062499
4 0 0.062543 0.062543
5 0 0.109723 0.109723
6 0 0.085447 0.085447
7 0 0.132177 0.132177
8 0 0.062524 0.062524
9 0 0.133001 0.133001
10 0 0.526721 0.526721
11 0 0.525792 0.525792
12 0 0.524818 0.524818
13 0 0.133818 0.133818
14 0 0.525871 0.525871
15 0 0.527045 0.527045
16 0 0.085946 0.085946
17 0 0.108599 0.108599
18 0 0.526602 0.526602
19 0 0.119850 0.119850

Anyone care to shed some light on this?

Also why with select queries do you have to allocate and free the results
area every time you run the query? Surely it would be more logical to re-use
the existing area - maybe rather than freeing it you should just be able to
reset it.

Kevin Cowley
R&D

Tel: 0118 902 9099 (direct line)
Email: kevin.cowley@alchemetrics.co.uk
Web: http://www.alchemetrics.co.uk



************************************************************ **************************
ALCHEMETRICS LIMITED (ALCHEMETRICS)
Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX
Tel: +44 (0) 118 902 9000 Fax: +44 (0) 118 902 9001
This e-mail is confidential and is intended for the use of the addressee only.
If you are not the intended recipient, you are hereby notified that you must
not use, copy, disclose, otherwise disseminate or take any action based on
this e-mail or any information herein.
If you receive this transmission in error, please notify the sender
immediately by reply e-mail or by using the contact details above and then
delete this e-mail.
Please note that e-mail may be susceptible to data corruption, interception
and unauthorised amendment. Alchemetrics does not accept any liability for
any such corruption, interception, amendment or the consequences thereof.
************************************************************ **************************


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Servere performance issue with compiled (pre-prepared) queries - they"re 50% slower than parse

Kevin Cowley writes:
> OK
>
> I've just been completing some benchmarking comparing compiled
> (pre-prepared) queries (new in 4.1.1 and above) with standard parsed
> queries.
>
> For both INSERT queries "insert into table( col1, col2,col3) values(?,?,?)"
> and SELECT queries "select col1, col2,col3 where col1 between ? and ?" the
> compile (pre-prepared) query is 50% slower. These time are just for the
> 'execute' and the queries are only prepared once.
>

[skip]

> Anyone care to shed some light on this?
>
> Also why with select queries do you have to allocate and free the results
> area every time you run the query? Surely it would be more logical to re-use
> the existing area - maybe rather than freeing it you should just be able to
> reset it.
>
> Kevin Cowley
> R&D
>
> Tel: 0118 902 9099 (direct line)
> Email: kevin.cowley@alchemetrics.co.uk
> Web: http://www.alchemetrics.co.uk
>
>

Hi!

We have done some measurements with 4.1.2 and prepared statements were
a bit faster. 4.1.2 should be out within a week or two.

We would be gratefull if you could repeat your measurements with
4.1.2.

Regarding result set, that is not a bad idea. This would however
require a new set of functions. Probably most current application
would continue to use current API.

--

Sincerely,

--
For technical support contracts, go to https://order.mysql.com/?ref=msmi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB
/_/ /_/\_, /___/\___\_\___/ Full time Developer and Support Coordinator
<___/ www.mysql.com Larnaca, Cyprus



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org