4 messages in com.mysql.lists.bugsRE: Servere performance issue with co...
FromSent OnAttachments
Kevin Cowley05 May 2004 10:37 
Jeremy Zawodny05 May 2004 11:09 
Kevin Cowley05 May 2004 11:20 
Sinisa Milivojevic05 May 2004 11:27 
Subject:RE: Servere performance issue with compiled (pre-prepared) querie s - they're 50% slower than parsed queries
From:Kevin Cowley (kevi@alchemetrics.co.uk)
Date:05/05/2004 10:37:44 AM
List:com.mysql.lists.bugs

I've just downloaded the latest snapshot, rebuilt 4.1.2 and re-run the tests. The results are below. It looks like the Inserts are now quicker, but not by much, but the selects are considerably slower.

I'll download and rerun the tests at the end of the week unless you specifically ask for another run sooner.

Parsed Queries

Insert total average mean mean max min run records time time time records time time 0 24851 6.820585 0.000274 0.000275 2620 19829.000000 226.000000 1 24851 6.065923 0.000244 0.000231 4456 8437.000000 225.000000 2 24851 6.304409 0.000254 0.000231 3459 30257.000000 226.000000 3 24851 6.510745 0.000262 0.000231 2607 18397.000000 225.000000 4 24851 6.724278 0.000271 0.000231 3375 191237.000000 224.000000 5 24851 8.153951 0.000328 0.000275 3823 20535.000000 227.000000 6 24851 6.882307 0.000277 0.000275 4093 21495.000000 227.000000 7 24851 6.425859 0.000259 0.000231 3676 20949.000000 226.000000 8 24851 6.470958 0.000260 0.000231 3130 22156.000000 225.000000 9 24851 6.282352 0.000253 0.000232 4421 21469.000000 226.000000

Select run 0 run 1 run 2 run 3 run 4 run 5 run 6 run 7 run 8 run 9 average query rows time time time time time time time time time time time 0 61 0.074916 0.072847 0.072966 0.072618 0.072627 0.072619 0.072647 0.072607 0.072610 0.072586 0.072904 1 7 0.049443 0.049423 0.049373 0.049351 0.049371 0.049354 0.049372 0.049349 0.049357 0.049351 0.049374 2 41 0.092653 0.092617 0.092579 0.092552 0.092536 0.092584 0.092576 0.092556 0.092562 0.092537 0.092575 3 9 0.048734 0.048697 0.048834 0.048660 0.048707 0.048675 0.048661 0.048660 0.048792 0.048672 0.048709 4 16 0.048296 0.048277 0.048280 0.048287 0.048277 0.048282 0.048263 0.048263 0.048268 0.048442 0.048293 5 62 0.092250 0.092204 0.092175 0.092157 0.092143 0.092190 0.092129 0.092192 0.092129 0.093665 0.092323 6 6 0.066074 0.066101 0.066061 0.066111 0.066087 0.066170 0.066072 0.066095 0.066123 0.066497 0.066139 7 1 0.123014 0.123101 0.122950 0.122984 0.122952 0.122979 0.122906 0.122879 0.123233 0.122876 0.122987 8 8 0.047967 0.047914 0.047902 0.047905 0.049032 0.047899 0.047914 0.047874 0.047927 0.047925 0.048026 9 4 0.113309 0.113234 0.113296 0.113226 0.116240 0.113273 0.113541 0.113248 0.113206 0.113224 0.113580 10 0 0.215568 0.215465 0.215478 0.215458 0.233277 0.215481 0.215375 0.215539 0.215344 0.215625 0.217261 11 0 0.140931 0.141002 0.140924 0.140928 0.141657 0.140951 0.141049 0.140876 0.140869 0.140872 0.141006 12 1 0.250533 0.250500 0.251514 0.250477 0.250623 0.250553 0.250457 0.250825 0.250445 0.250462 0.250639 13 0 0.123744 0.123715 0.124173 0.123710 0.123893 0.123866 0.123662 0.123758 0.123657 0.123666 0.123784 14 0 0.134293 0.134303 0.134271 0.134346 0.134316 0.134277 0.134306 0.134235 0.134282 0.134286 0.134291 15 6 0.245146 0.245130 0.245171 0.245123 0.245227 0.245100 0.245139 0.245025 0.245034 0.245082 0.245118 16 0 0.074154 0.074260 0.074158 0.074192 0.074204 0.074155 0.074141 0.074125 0.074168 0.074160 0.074172 17 0 0.096158 0.096137 0.096129 0.096357 0.096171 0.096206 0.096148 0.096108 0.096173 0.096147 0.096173 18 1 0.126585 0.126651 0.126583 0.126572 0.126625 0.126568 0.126603 0.126524 0.126606 0.126578 0.126590 19 8 0.111515 0.110790 0.110945 0.110769 0.111060 0.110829 0.110755 0.110779 0.110728 0.110793 0.110896

Compiled (Pre-prepared)

Insert

run records time time time records time time 0 24851 6.474224 0.000261 0.000257 4597 21462.000000 207.000000 1 24851 6.523098 0.000262 0.000255 6049 23318.000000 210.000000 2 24851 6.386642 0.000257 0.000256 4449 23161.000000 207.000000 3 24851 6.519648 0.000262 0.000256 6229 19210.000000 248.000000 4 24851 7.719113 0.000311 0.000256 5180 24232.000000 206.000000 5 24851 6.161722 0.000248 0.000257 4259 19774.000000 206.000000 6 24851 5.987938 0.000241 0.000212 3898 23452.000000 206.000000 7 24851 6.327306 0.000255 0.000256 3534 24790.000000 205.000000 8 24851 6.091885 0.000245 0.000212 4112 24251.000000 207.000000 9 24851 6.382733 0.000257 0.000212 5787 191016.000000 206.000000

Select

run 0 run 1 run 2 run 3 run 4 run 5 run 6 run 7 run 8 run 9 average query rows time time time time time time time time time time time 0 0 0.437444 0.419829 0.412711 0.096162 0.085786 0.089553 0.415813 0.447224 0.412606 0.097838 0.291497 1 0 0.421069 0.420800 0.420568 0.068622 0.065719 0.086317 0.483824 0.133079 0.556397 0.098176 0.275457 2 0 0.421695 0.421387 0.422516 0.491819 0.246641 0.107930 0.560652 0.484094 0.555208 0.200201 0.391214 3 0 0.421287 0.421791 0.423138 0.090164 0.090360 0.067598 0.485454 0.134826 0.605345 0.617853 0.335782 4 0 0.420637 0.419801 0.421588 0.068673 0.065798 0.067577 0.411304 0.064665 0.552008 0.618335 0.311039 5 0 0.421718 0.420134 0.411883 0.491230 0.259388 0.107861 0.411920 0.477208 0.412143 0.201834 0.361532 6 0 0.420711 0.424738 0.412332 0.097632 0.087143 0.087326 0.412273 0.450799 0.411840 0.268889 0.307368 7 0 0.421789 0.424857 0.422870 0.515664 0.527633 0.129651 0.540812 0.705058 0.557240 0.457545 0.470312 8 0 0.421194 0.421585 0.423295 0.092068 0.105025 0.067555 0.485094 0.192330 0.558749 0.620728 0.338762 9 0 0.422031 0.419744 0.412516 0.492132 0.488680 0.129601 0.412837 0.480036 0.418996 0.220696 0.389727 10 0 0.426005 0.424792 0.451020 0.516837 0.553936 0.543235 0.575944 0.639704 0.714140 0.902019 0.574763 11 0 0.422444 0.429347 0.449292 0.572222 0.620788 0.298257 0.551548 0.754606 0.829995 0.737637 0.566614 12 0 0.424097 0.432708 0.454663 0.578973 0.617020 0.571019 0.563856 0.767397 0.843719 0.884417 0.613787 13 0 0.422312 0.423519 0.426041 0.518890 0.534378 0.232511 0.495625 0.564376 0.565397 0.441198 0.462425 14 0 0.421741 0.422967 0.412499 0.521565 0.537638 0.161451 0.412316 0.447149 0.412858 0.439183 0.418937 15 0 0.424952 0.430311 0.441126 0.556725 0.591733 0.554715 0.566439 0.661365 0.692906 0.892472 0.581274 16 0 0.422370 0.423990 0.453605 0.117189 0.138630 0.218314 0.499172 0.563484 0.570311 0.235178 0.364224 17 0 0.422323 0.419337 0.418111 0.492441 0.249981 0.129617 0.412780 0.447589 0.412502 0.199785 0.360447 18 0 0.421743 0.420457 0.427702 0.498457 0.499763 0.186529 0.412596 0.571030 0.413459 0.437185 0.428892 19 0 0.422239 0.432789 0.415143 0.561170 0.544531 0.141262 0.515882 0.450903 0.591474 0.633315 0.470871

-----Original Message----- From: Sinisa Milivojevic [mailto:sin@mysql.com] Sent: 05 May 2004 13:29 To: kevi@alchemetrics.co.uk Cc: Bu@lists.mysql.com Subject: Re: Servere performance issue with compiled (pre-prepared) queries - they're 50% slower than parsed queries

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.