Do all the things like ++ or -- rants, post your own rants, comment on others' rants and build your customized dev avatarSign Up
sboesch54015dDon't really have much experience in this field, but have you tried to EXPLAIN queries?
We once had an issue where we were lazy and queried SELECT * instead of SELECT field1, field2 in our client. But it was still fast. However, IIRC, the vendor added some binary fields in a later update, which made the whole thing brutally slow, of course.
nitwhiz1018315dWell do you take it multiple times a week?
Check your indexes, as previously suggested use EXPLAIN, profile the consumer to see which queries are causing it and take a look at tools like newrelic, they have some analysis and stuff in it that can be pretty useful.
Developed an invoicing tool once and there was a missing index on the state of the invoice which was queried a lot.
Got the query time down from 10 seconds to about 20 milliseconds.
The amount of data was the cause. It worked fine for 2 years and suddenly it became incredibly slow.
zarathustra74215dThanks all. I'm pretty confident the SQL is ok, one project that takes nearly 2 minutes to load 1900 rows of time through the application, but the query in SQL completes in less than a second. But I will take another look at the indexes etc.
Unfortunately we don't have anything like New relic etc.
How are you calling into the SQL? Are you holding the result set cached? Are you pulling the entire datasets just to grab a subset after (e.g. pagination in code but not in SQL)? Are you properly closing your connections? Had a similar problem years ago and it turned out we were not caching the data and kept calling full result sets and paginating in code. This would not have been a problem if we were caching the data, but we weren't. We were querying the entire data set each time we changed pages.
Plasticnova140014dI only read the first sentence, but you keep the bus over 50 mph or it explodes. That’s how Keanu handled his speed issue anyways
zarathustra74212dThanks all, did further investigations, indexes etc were all good, but I'd decided to do some comparisons against a previous version of the program running against the old dB server.
It looks like upgrading SQL fixed some other issues with slow running queries, so now we are left with the fact that the way the program is written isn't very efficient