SQL Server Performance

SQL Query Tuning

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by wkm1925, May 10, 2008.

  1. wkm1925 New Member

    I've following table and data

    tblSEL
    Date_Taken | Time | Main_ID | Value
    -----------------------------------------------
    5/11/2008 12:00:00 AM | 500 | 233 | 4780
    5/11/2008 12:00:00 AM | 500 | 239 | 3122
    5/11/2008 12:00:00 AM | 515 | 233 | 5435
    5/11/2008 12:00:00 AM | 530 | 233 | 6799
    5/11/2008 12:00:00 AM | 545 | 233 | 4090
    5/11/2008 12:00:00 AM | 600 | 234 | 2312
    5/11/2008 12:00:00 AM | 615 | 233 | 7409
    ...
    ...
    ...
    tblSEL having more than 6million rows
    *Date_Taken datatype is smalldatetime

    tblLocation
    Main_ID | Location
    ---------------------
    233 | PRK
    234 | PER
    239 | SAB
    ...
    ...
    ...

    i've following query to getting the last 7month data
    SELECT
    t1.Date_Taken, t1.Time,
    t1.Main_ID, t1.WATER_ULEVEL
    FROM dbo.tblSEL t1 INNER JOIN dbo.tblLocation t2
    ON t1.Main_ID=t2.Main_ID
    WHERE t2.Location='PRK'
    AND t1.Date_Taken>=CONVERT(VARCHAR(10),DATEADD(m,-7,GETDATE()),101) AND
    t1.Date_Taken<=CONVERT(VARCHAR(10), GETDATE(), 101)
    ORDER BY t1.Date_Taken, t1.Time

    How to adjust this query to make sure i get same data and also run at the best performance.
  2. Luis Martin Moderator

    Please run this:
    Set statistics io on
    Your query
    Set statistics io off
    Post the results and also post you table indexes.
  3. davidfarr Member

    AND t1.Date_Taken<=CONVERT(VARCHAR(10), GETDATE(), 101)
    Why is the line above necessary in the query ? Does tblSel ever contain Date_Taken values that are higher than GETDATE() ? If the latest Date_Marker values are always today's date then you can leave out this additonal clause to optimize the query slightly.
    As for indexing; I will assume that tblLocation already has a clustered index on Main_ID as the Primary Key of that table. Placing a nonclustered index on Main_ID in tblSel will help if you have not already done so. Placing an index on the Location column in tblLocation might help too, but if tblLocation contains less than 1000 records then the performance advantage will only be small.
  4. gbd77rc New Member

    Hi
    If t1.Date_Taken is VARCHAR(10) and you are using style 101 (mm/dd/yyyy) your query will be return not only the last 7 months data, but the previous years data during the same period. See example here.create
    table #test1(Date_Taken
    VARCHAR(10))INSERT
    INTO #test1 (Date_Taken) SELECT '05/22/2007'INSERT
    INTO #test1 (Date_Taken) SELECT '05/22/2008'INSERT
    INTO #test1 (Date_Taken) SELECT '05/23/2008'INSERT
    INTO #test1 (Date_Taken) SELECT '05/24/2007'select
    * from #test1 where date_taken >= '05/21/2008' AND date_taken <= '05/24/2008'
    Now if it is DATETIME and you are trying to cut off the time then following example does work. Notice I work out the date range before using them. This will increase performance if you are using a stored procedure to do the selection. Now you can pass in the -7 in to the stored procedure. I take it the Date_Taken field is indexed. Is it clustered?create table #test1(
    Date_Taken
    DATETIME)INSERT
    INTO #test1 (Date_Taken) SELECT '11/22/2007 12:00:00'INSERT
    INTO #test1 (Date_Taken) SELECT '05/22/2008 17:00:00'INSERT
    INTO #test1 (Date_Taken) SELECT '05/23/2008 15:00:00'INSERT
    INTO #test1 (Date_Taken) SELECT '11/24/2006 17:00:00'DECLARE @Date1 DATETIME
    DECLARE
    @Date2 DATETIMESET
    @Date1 = CONVERT(DATETIME, CONVERT(VARCHAR(10), GETUTCDATE(), 101))SET
    @Date1 = DATEADD(m, -7, @Date1)SET
    @Date2 = CONVERT(DATETIME, CONVERT(VARCHAR(10), GETUTCDATE(), 101) + ' 23:59:59')SELECT
    @Date1, @Date2select
    * from #test1 where date_taken BETWEEN @Date1 AND @Date2
    I hope this helps.
    Regards
    Richard...

Share This Page