Oh, and just as a sidenote all the queries have indexes that match the order by and where conditions (they're the same indexes as the original database).
-Gary Hertel ----- Original Message ----- From: "Gary Hertel" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, February 27, 2003 2:52 PM Subject: speeding up simple SELECT statements with the C api? > 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
