Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
boggyboy
Yak Posting Veteran
57 Posts |
 Posted - 2010-03-26 : 15:05:12
|
| I have a query that performed just fine in development, but against a production database the same query takes over a couple minutes to run. Not good for my customer relations to say the least.My query does alot of aggregating and summing values in a way similar to a pivot table. In production it will look across thousands of rows and return only about 15 rows.My question is this... What is a best practice for working around these slow running queries? Should i cache the data? Should i start over?Nick W Saban |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
boggyboy
Yak Posting Veteran
57 Posts |
Posted - 2010-03-26 : 17:13:26
|
| Thanks for the reply...the only difference between the two environments is the volume of data. there is about 100 times the volumeNick W Saban |
 |
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-03-26 : 17:33:08
|
| Is there any possibility of getting the full production data into the dev environment? The other difference you may not have taken into account is the load on the prod server. If it's running at near capacity, and you hit it with a complex query, bad things will happen. :)There are 10 types of people in the world, those that understand binary, and those that don't. |
|
|
|
boggyboy
Yak Posting Veteran
57 Posts |
Posted - 2010-03-26 : 17:54:21
|
| Already did that. I restored the prod db into dev. the server is a pretty good one...dual quad core xeonNick W Saban |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-03-26 : 18:03:53
|
quote:
Originally posted by boggyboy
Already did that. I restored the prod db into dev. the server is a pretty good one...dual quad core xeonNick W Saban
So does the dev environment now have the same performance issues as the prod environment? As to your question about caching, if you're working with data that's not going to change, then caching the results is usually a good idea, especially if the results are small, and take a long time to produce. Also, perhaps you could post the query here and some details on the tables it references. There might be a few tweaks that could be applied to it.There are 10 types of people in the world, those that understand binary, and those that don't. |
|
|
|
boggyboy
Yak Posting Veteran
57 Posts |
Posted - 2010-03-31 : 10:56:56
|
| Thanks for the reply... very much appreciated.Nick W Saban |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-01 : 03:45:06
|
" What is a best practice for working around these slow running queries? "SQL Profiler has a template for just logging "slow-running-queries" - that is useful to find the worst culprits (amongst which amy be some housekeeping ones that are always slow, so you have to ignore those of course ...)I then take the SQL (from SQL Profiler, or some other logging means) and run it (on the live server if you like) in a transaction block - so that no updates occur on the server:BEGIN TRANSACTION... test code here ...ROLLBACK saves me accidentally trying an UPDATE or an SProc without realising it does an update!That will give you an indication of the run-time.Next up check the Query Plan to see what indexes are being used (or, perhaps what indexes you expect to be being used that are NOT!!) |
|
|
|
|
|
|
|
|
Performance Issue