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

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: [email protected]
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/[email protected]