I'm in the process of writing a report writer for mysql which is necessary for the migration from the current database of my company's product to mysql (due to the amount of reports dependent on the current database report writer).
Anyways, I'm running into a little bit of a performance issue as the old database report writer had direct access to the database through a c library (no sql interface). On some reports there can be as many as 100,000 select statements. With mysql this is ending up with a performance penalty of about 3x the existing time that the current report writer takes. Running Intel's vtune I can see that the select statements (through mysql_query)are taking up around 90% of the run time. I was originally using the C++ library but changed it to the C api after seeing that the C++ api was giving a much larger performance penalty likely due to it duplicating the row data (vtune said about 3x penalty but it was more like a 1.5x penalty). Anyways, I'm not sure if there is any kind of change I can make to reduce this sql statement penalty and was hoping someone here could possibly help reduce it. The select statements are very simple and usually take a form like select field1, field2, field3 from table where field4 = (some value) order by field1 and sometimes like this: select field1, field2, field3 from table where field4 = (some value) LIMIT 1 Most often there will only be one result returned and only one table is ever queried at a time. I have a feeling it's the overhead with every query that's really the problem here and that there really is no fix. I also can't really combine the sql statements and save the data for later due to the unique format of the reports. But perhaps there are some optimizations I can make to help. Thanks, Gary Hertel --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
