1

I an somewhat new to SQL and I have (hopefully) an easy question.

This query takes about 7 minutes to run on our DB, 10s of millions of rows.

SELECT TOP 100 collectView.tagName, collectView.time, collectView.value
FROM TIMELINE.dbo.collectView collectView`
WHERE 
(collectView.tagName='currentGS.volume' and
collectView.time>getdate()-1)
ORDER BY collectView.time DESC

I was hoping that the TOP 100 in select would speed it up since it only needs 100 rows but no.

Anyone have any suggestions?

ekad
  • 14,436
  • 26
  • 44
  • 46
Orin Moyer
  • 509
  • 2
  • 7
  • 13
  • we can't really help without knowing more details. Is it a view or a table? (the name points to a view), in which case it could be many things. If it's a table, does it have indexes on it? – Lamak Apr 06 '15 at 17:59
  • using `TOP 100` doesn't necessarily make it take less time.. the database still has to access all the rows while doing the `ORDER BY`, but it will take less time to actually display the results *after* the query has completed. – dub stylee Apr 06 '15 at 18:08
  • If you take away the top 100 part, how many records would you get back? – Dan Bracuk Apr 06 '15 at 18:29
  • Can you remove the "collectView.time>get date()-1" and tell us if the query is any faster? – alas Apr 06 '15 at 19:06
  • with no time in the WHERE, it returns 1,045,280 rows in the same time, sorry for the confusion – Orin Moyer Apr 06 '15 at 19:12

2 Answers2

1

This is your query:

SELECT TOP 100 collectView.tagName, collectView.time, collectView.value
FROM TIMELINE.dbo.collectView collectView
WHERE collectView.tagName = 'currentGS.volume' and
      collectView.time > getdate() - 1
ORDER BY collectView.time DESC;

The best index for this query is the covering index collectView(tagName, time, value). However, this assumes that collectView is really a table and not a view.

If it is a view, one option is to create an indexed view, described here (based on the table name, I'm assuming you are using SQL Server). Alternatively, you need to speed up the view itself. Perhaps the solution is as simple as recompiling the view to fix a broken execution plan.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

As you're doing ORDER by collectView.time limiting results will not speed up until SQL didn't finish sorting. Try to add index on that column:

CREATE INDEX time_index ON collectView (time);

From my experience i can say that in most cases where you have large table and you need to operate on most of records, adding index will boost your code from couple of minutes to couple of seconds.

Let me know if this helps

Paweł Tomkiel
  • 1,974
  • 2
  • 21
  • 39