help make query faster please with an index

Question

help make query faster please with an index

jon pill

SSCommitted

Points: 1659
More actions
September 20, 2007 at 3:54 pm

#374317

rsRecordset.Open
"select ESDSTotal,ER2DSTotal,ESASKSIZE,ESBIDSIZE,ER2ASKSIZE,ER2BIDSIZE,ER2RATIOASK,ER2RATIOBID,ESRATIOASK,ESRATIOBID,ESNETR,ER2NETR from DEMANDSPREAD WHERE TIMESTAMP=(SELECT MAX(TIMESTAMP)FROM DEMANDSPREAD)", gcnConnect
rsRecordset3.Open "SELECT AVG(s.ESDSTotal) AS ESDSTotal FROM dbo.DemandSpread s INNER JOIN (SELECT MAX(s1.[TimeStamp]) AS MaxTimeStamp FROM dbo.DemandSpread s1) d ON s.[TimeStamp] BETWEEN DATEADD(mi, -1, d.MaxTimeStamp) AND d.MaxTimeStamp", gcnConnect
I think i need to setup and index, as this database gets larger my queries get slower
how can i set sql up to only look at data in the last lines of the data base as its all timestamped i only need rto look at the last fewlines, well 60 lines, but i search the whole data bases on this and it gets really slow

Viewing 7 posts - 1 through 6 (of 6 total)

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

michaela SSCertifiable Points: 5649 More actions · Answer 1

You would have to build this directly on your db as this is depending of your records; my suggestion is to run the query in Management Studio, then Tools->Database Tuning Advisor; And a recommendation, do not run this directly on your production db, instead create a copy.

Lowell SSC Guru Points: 323518 More actions · Answer 2

take a look at your WHERE statement:

WHERE TIMESTAMP=(SELECT MAX(TIMESTAMP)FROM DEMANDSPREAD)",

because this query is keying off of the TIMESTAMP column, it would greatly benefit from having an index on it.

that would change your execution plan from a slow TABLE SCAN to a faster INDEX SCAN or INDEX SEEK;

something like CREATE INDEX IX_DEMANDSPREAD_TIMESTAMP ON DEMANDSPREAD(TIMESTAMP) could help.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jon pill SSCommitted Points: 1659 More actions · Answer 3

so i execute that query inside sql server only once?

can i create a index without a t-sql statement?

nad how will that change my query to search?

jon pill SSCommitted Points: 1659 More actions · Answer 4

I am running a few queries from excel into sql on a timed bases i open and close each one before the next, how does an index work over a timestamp?

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

1) You only need to create an index once.

2) You can do this via a query (create index..) or using the GUI (i.e. sql server management studio)

3) All subsequent queries on that table can and will have access to the index automatically. In fact, all queries that reference that timestamp column will automatically have their query plans redone the first time they are called after the index is created.

4) Be aware that if your data is updated frequently the timestamp value changes each time and the index will become fragmented. However, since the timestamp is constantly increasing in value you should use a fill factor of 100%. You will still need to do routine maintenance to defrag the tail area of the index.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

jon pill SSCommitted Points: 1659 More actions · Answer 6

jon pill

SSCommitted

Points: 1659

September 24, 2007 at 11:51 am

#735739

ok thanks