| Bug #36376 | Slow results from MySQL Stored Procedure to VS2005 | ||
|---|---|---|---|
| Submitted: | 28 Apr 2008 13:12 | Modified: | 4 Aug 2008 11:34 |
| Reporter: | R Roberts | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / NET | Severity: | S2 (Serious) |
| Version: | 5.2.1 | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
[28 Apr 2008 13:12]
R Roberts
[28 Apr 2008 13:13]
R Roberts
see notes
[13 May 2008 12:04]
Sveta Smirnova
Thank you for the report. Do you experience same behavior using mysql command line client or only if call this procedure from C# program? Which version of MySQL server do you use?
[10 Jun 2008 14:33]
R Roberts
I actually call the Stored procedure directly from my C# project. The MySQL database I use is SQLYog Community Edition - MySQL GUI Versi on v6.16 Hope this helps....
[11 Jun 2008 6:39]
Tonci Grgin
Hi. This is in no way enough to repeat the problem. Please, at least paste your connect string here.
[12 Jun 2008 9:32]
Tonci Grgin
Hi and thanks for info. I was looking for "use procedure bodies=True". Now, there can be several reasons for the slowdown you're seeing: o) I__S is slow to retrieve information. This is overcome by putting "use procedure bodies=True" as it fetches procedure info from mysql.proc o) SP is always slow to execute for the first time, since it c/NET parses and caches it for repeated execution. o) SS cursors materialize in form of in-memory temporary table. This can also take some time and/or load the server. So, can you please: - test SP in mysql command line client and see if it's fast there - do research in c/NET docs and see how to speed up multiple SP execution - test same SP in C# program (not wizard!) by calling it multiple times and timing each execution Please inform me of results. A lot of optimizations are done in 5.3 branch, but I'm not sure for this particular one. However, you are free to try it.
[25 Jun 2008 14:15]
R Roberts
Hi I have done the timing execution for the following: MySQL - Ran the SP in the query window: 9703 ms 10172 ms 9031 ms 9016 ms MySQL - Ran the SP in the command client: 10.42 sec 9.64 sec 9.39 sec 10.39 sec C# - Winform - Ran the SP in coding (Not Wizard): 00:00:12.5000000 00:00:10.4531250 00:00:09.8906250 00:00:09.4218750 Hope this helps - seems somewhat slightly slow on those above results. Thanks
[18 Jul 2008 12:23]
Tonci Grgin
Hi and sorry for the delay. I was on vacation. First of all :-) on your private comment! Now, I see no slowdown. As a matter of fact, c/NET is speeding up execution as it goes: C# - Winform - Ran the SP in coding (Not Wizard): 00:00:12.5000000 00:00:10.4531250 00:00:09.8906250 00:00:09.4218750 Do we still consider this report valid or should I close it now?
[21 Jul 2008 7:34]
R Roberts
Hi Hope you had a good holiday and got the much needed relaxation. So it seems writing out the code using MySqlCommand etc to call the SP is much quicker then. Why is it slow through the calling of the SP in the XSD file? I guess my long stored procedure which I showed in pink seemed to have no effect in running it against the time? Thanks for your help.
[22 Jul 2008 8:30]
Tonci Grgin
Yes I did :-) I think we should change synopsis according to what you said: "So it seems writing out the code using MySqlCommand etc to call the SP is much quicker then. Why is it slow through the calling of the SP in the XSD file?" New one could be: Slow results from long SP stored in XSD, or something. I will consult on this as it is possible that XSD uses different parsing/caching model.
[24 Jul 2008 7:26]
Tonci Grgin
Hi. I got response from c/NET team and this is expected. Here's the answer: "SP's are not "stored" in the XSD, this is about creating a table adapter on a dataset that uses SP's. One reason why calling an SP can be slower for us is because we retrieve the body of the proc and parse out the parameters. This is mitigated a bit over the life of an app with proc caching. Also one can turn off access to procedure bodies to speed it up but he/she has to add the parameters to the command in the correct order." Can we close the report now?
[24 Jul 2008 14:04]
R Roberts
OK, I am trying to get my head round, so the SP being in the xsd, is not really called but is in format of table adapter set. So therefore, it is better in the long run to write out the MySqlCommands etc to run the actual SP to get productive results? What do you mean by this "Also one can turn off access to procedure bodies to speed it up but he/she has to add the parameters to the command in the correct order." ?? Have I got the connection string with the variables set up correctly? Thanks for your assistance.
[25 Jul 2008 6:19]
Tonci Grgin
Yes you got it right. Now, let's look into your questions from your last post: "OK, I am trying to get my head round, so the SP being in the xsd, is not really called but is in format of table adapter set. So therefore, it is better in the long run to write out the MySqlCommands etc to run the actual SP to get productive results?" Correct in this case. "What do you mean by this "Also one can turn off access to procedure bodies to speed it up but he/she has to add the parameters to the command in the correct order." ??" You can transform SP into execution-ready form in two ways; by parsing it or by providing everything yourself making the parsing obsolete. Parsing can be from I__S (slow) or from mysql.proc (much faster but requires elevated privileges). You can also add all the parameters manually in *exact* order that they appear in SP (fastest way) bypassing parsing. "Have I got the connection string with the variables set up correctly?" Yes you did. There is no *wrong* way, just different ways suited for different situations/privileges ... "Thanks for your assistance." Np, I'm learning too. Closing the report as there is no real bug here.
[25 Jul 2008 6:32]
Tonci Grgin
Related: Bug#36694.
[25 Jul 2008 11:03]
Tonci Grgin
Test case
Attachment: Bug36376.cs (text/plain), 5.06 KiB.
[25 Jul 2008 11:05]
Tonci Grgin
Attached test case for all 3 cases I mentioned, using I__S, direct exec and using mysql,proc: RESULT:3100 Time:00:00:02.8906250 RESULT:3099 Time:00:00:01.9843750 RESULT:3098 Time:00:00:01.4843750 RESULT:3097 Time:00:00:00.6406250 RESULT:3096 Time:00:00:00.4062500 RESULT:3095 Time:00:00:01.6250000 RESULT:3094 Time:00:00:00.9531250 RESULT:3093 Time:00:00:02.0156250 RESULT:3092 Time:00:00:00.9843750 Done Test I__S. RESULT:3100 Time:00:00:00 RESULT:3099 Time:00:00:00 RESULT:3098 Time:00:00:00 RESULT:3097 Time:00:00:00 RESULT:3096 Time:00:00:00 RESULT:3095 Time:00:00:00 RESULT:3094 Time:00:00:00.0156250 RESULT:3093 Time:00:00:00 RESULT:3092 Time:00:00:00 Done Test Direct. RESULT:3100 Time:00:00:00.0156250 RESULT:3099 Time:00:00:00 RESULT:3098 Time:00:00:00 RESULT:3097 Time:00:00:00 RESULT:3096 Time:00:00:00 RESULT:3095 Time:00:00:00 RESULT:3094 Time:00:00:00 RESULT:3093 Time:00:00:00 RESULT:3092 Time:00:00:00.0156250 Done Test mysql.proc. Done Test Full.
[4 Aug 2008 11:34]
R Roberts
Hi Having ran the Test App you attached and I included the full running in other bug case 38493. I do not understand this: "You can transform SP into execution-ready form in two ways; by parsing it or by providing everything yourself making the parsing obsolete. Parsing can be from I__S (slow) or from mysql.proc (much faster but requires elevated privileges). You can also add all the parameters manually in *exact* order that they appear in SP (fastest way) bypassing parsing." What do you mean by parsing or make the parsing obsolete? I agree that it is best not to use I__S. I felt it was best to do Test Direct. What do you mean by "You can also add all the parameters manually in *exact* order that they appear in SP (fastest way) bypassing parsing". An example would be help me understand it visually - a code so I can think on the same lines as you. Many thanks for your assistance though.
