Hi Guys,
I have some queries that show in the console from time to time as slow queries. Now that I know they are slow, what is the best way to attack them and make them fast again?
They do have index’s, but I’m not sure what the next step would be. Does anyone have any pointers?
Thanks
Any time you have a slow query, your best bet is to start looking at the query’s execution plan. There’s a really nice Stack Overflow answer on how to do this for SQL Server. Look for things like table scans or nested loops that are taking a lot of time, then research how to restructure your query, tables and/or indexes to avoid them. (And make sure your db has enough memory to keep all the indexes in memory and still have enough room for your intermediate query results).
I’d caution against just adding indexes and hoping for the best. Indexes can definitely speed up queries by letting the DB look in the index to find the records you need instead of scanning the whole table, but they slow down inserts (and sometimes updates), and can even slow down queries by increasing disk access (you have to go to disk to get the index because you’re low on memory, then you have to go to disk to get a large number of records. It might have been faster to scan the whole table.)
Unfortunately, there won’t be a one-size-fits-all easy answer of “just do x and your db will speed up!”. But the query execution plan will be a good place to start.