Post reply

help make query faster please with an index

  • 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

  • September 20, 2007 at 6:19 pm

    #735097

    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.

     

  • September 21, 2007 at 6:27 am

    #735191

    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!

  • September 21, 2007 at 8:27 am

    #735259

    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?

  • September 21, 2007 at 10:36 am

    #735313

    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?

  • September 24, 2007 at 8:13 am

    #735619

    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

  • September 24, 2007 at 11:51 am

    #735739

    ok thanks

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

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