Query of live data too slow

Question

Query of live data too slow

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

chugghin (8/26/2010)
but the overall query will run for a week of data (somewhere in the neighborhood of about 60000 rows) in about 25-30 seconds.

That's hellishly slow for that simple query. With some tuning (if I could see the exec plan 🙁 ) I could likely get that under a second.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

chugghin Say Hey Kid Points: 672 More actions · Answer 2

I did some more trolling around ('cuz I like to squeeze every last bit of speed from my queries that I know how to) and came across this post:

http://www.sqlservercentral.com/Forums/Topic446175-360-1.aspx

If my understanding is correct - they were basically instructed to use a 'covering' index. I did the same on the tables I could and WOW...that made a HUGE difference. I'm getting data back in 3 seconds or less.

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 3

chugghin (8/26/2010)
I did some more trolling around ('cuz I like to squeeze every last bit of speed from my queries that I know how to) and came across this post:
http://www.sqlservercentral.com/Forums/Topic446175-360-1.aspx
If my understanding is correct - they were basically instructed to use a 'covering' index. I did the same on the tables I could and WOW...that made a HUGE difference. I'm getting data back in 3 seconds or less.

Take a look at the new execution plan and note the differences from the old one. This is a great chance to learn.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

chugghin Say Hey Kid Points: 672 More actions · Answer 4

In seeing this post - it also brings a question to mind. If I'm understanding the table structure and the following post - it appears as though they have a PK and an index on the same fields. Does this cause the query to do double duty?

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 5

chugghin (8/26/2010)
In seeing this post - it also brings a question to mind. If I'm understanding the table structure and the following post - it appears as though they have a PK and an index on the same fields. Does this cause the query to do double duty?

Inserts will have to do extra work, but having the duplication just requires extra storage other than that. I wouldn't do it in most cases, at all. But in some situations it can work out well.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning