SQL Server Performance

Performance Issue on my Query Execution

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by kumaravel v, Oct 5, 2008.

  1. kumaravel v New Member

    hi
    I am facing performance problem when i execute the below query.
    The table call tEvent which contains more than 2million records.
    another one Tunit table contains 3500 records.SELECT 13893,
    CAST ( CAST ( YearPart AS VARCHAR ) + '-' + RIGHT ( '0' + CAST ( MonthPart AS VARCHAR ), 2 ) + '-' + RIGHT ( '0' + CAST ( DayPart AS VARCHAR ), 2 ) AS SMALLDATETIME ) AS DataCapturePeriodStart,DataCaptureCount, MaxParkEventTimeDetected, MaxParkEventLastModified,
    CASE WHEN StatusCode = 'A' THEN 100
    WHEN StatusCode = 'W' THEN 101 WHEN StatusCode = 'O' THEN 102
    WHEN StatusCode = 'S' THEN 103ELSE 104
    END AS DataType
    FROM
    (select count(1) AS DataCaptureCount, a.StatusCode,MAX ( a.TimeDetected ) AS MaxTimeDetected, MAX ( a.LastModified ) AS MaxLastModified,
    DATEPART ( yy, a.TimeDetected ) AS YearPart, DATEPART ( mm, a.TimeDetected ) AS MonthPart, DATEPART ( dd, a.TimeDetected ) AS DayPartfrom tEvent a, tunit b
    where a.unitidsource=b.unitid and b.unitserialNumber = '20538'AND ( '2008-09-14 00:00:00' <= A.TimeDetected AND A.TimeDetected < '2008-09-15 00:00:00' )
    GROUP BY a.StatusCode, b.SerialNumber, DATEPART ( yy, a.TimeDetected ), DATEPART ( mm, a.TimeDetected ), DATEPART ( dd, a.TimeDetected ) ) AS TT1
    ----
    when i execute this script,, it takes more than 5 to 6 min.
    can anyone suggest me how to reduce the execution time to less than 1min. Any solution no problem.
    thanks and advance
    kumar
  2. Adriaan New Member

    What indexes do you have on those tables?
  3. kumaravel v New Member

    TParkEvent Table :
    Pk_TparkEvent (clustered), wms_ix_criticalValues (non-unique, Non_clustered), wms_ix_InsertNotificationBuffer(Non-Unique, Non-Clustered), wms_ix_ParkEventLastModified (Non-Unique, Non-Clustered), wms_ix_ParkEventTimeDetected(non-Unique, non-Clustered)
    TParkUnit Table :
    Pk_TparkUnit (Clustered)
  4. Adriaan New Member

    You might use CONVERT(VARCHAR(10), A.TimeDetected, 120) or DATEADD(hh, DATEDIFF(hh, 0, A.TimeDetected), 0) to get rid of the time of day, and group on that expression.
    The DATEPART calls may be slowing you down.
  5. kumaravel v New Member

    i tried that too. But no Use.
    Is there any other way to find out the performance solution ? Is the index keys are right??
  6. Adriaan New Member

    Other option - do the filtering and date manipulation in a derived table embedded within your derived table.
  7. kumaravel v New Member

    sorry can you little brief about what you said. I dont understand.
    thanks
  8. kumaravel v New Member

    thanks man.
    I have changed my query below:SELECT 13893, DayMonthYearPart AS DataCapturePeriodStart,DataCaptureCount, MaxParkEventTimeDetected, MaxParkEventLastModified,
    CASE WHEN ParkEventStatusCode = 'A' THEN 100
    WHEN ParkEventStatusCode = 'W' THEN 101 WHEN ParkEventStatusCode = 'O' THEN 102
    WHEN ParkEventStatusCode = 'S' THEN 103ELSE 104 END AS DataType
    FROM
    (select count(1) AS DataCaptureCount, a.ParkEventStatusCode,MAX ( a.ParkEventTimeDetected ) AS MaxParkEventTimeDetected, MAX ( a.ParkEventLastModified ) AS MaxParkEventLastModified,
    convert(varchar(12),a.ParkEventTimeDetected,105) AS DayMonthYearPart from tparkEvent a INNER JOIN tparkunit b
    ON a.parkunitidsource=b.parkunitid and b.parkunitserialNumber = '20538'
    AND (A.ParkEventTimeDetected BETWEEN '2008-09-14' AND '2008-09-15')GROUP BY a.ParkEventStatusCode, b.ParkUnitSerialNumber, convert(varchar(12),a.ParkEventTimeDetected,105) ) AS TT1
    --------------
    Is it ok what you were suggesting me ? If i run this query, it takes 1.40min. i gave parameter just 1 day only....
    my people are expecting less than 30sec .
    awaiting your reply.
  9. Adriaan New Member

    Well, you've already taken off about 75% of execution time by using one CONVERT instead of three DATEPART calls.
    You might try adding OPTION (MAXDOP 1) after the TT1 alias to see if you're suffering from unwanted parallellism.
  10. kumaravel v New Member

    thanks for your immediate reply yar.
    As per your suggession, i have added the OPTION (MAXDOP 1) after alias TT1.
    But it takes long time after i add this OPTION. I dont know why?
  11. Adriaan New Member

    That means that SQL was using parallellism exactly the way that it was intended, and you can drop the OPTION clause.
    Check the execution plans - which operations are taking up the biggest percentages?
  12. kumaravel v New Member

    Sort cost is 62%
    Clustered Index seek cost : 19%
    Index Seek Cost : 18%
    the rest are all 0%. This status i am see from the begin of the query. Still it shows the same status.
  13. Adriaan New Member

    Check the details - sorting what, seeking which (clustered) index? And give us the details of the index: which colums, and in which order.
  14. kumaravel v New Member

    As per your requisition, i have mentioned my table information.
    tParkEvent Table
    It contains more that 3millions recordz and the index keys are in the order:
    ParkUnitIdSource(Non-Unique, Non-Cluster), PK_TParkEvent(clustered), WMS_IX_InsertNotificationBuffer(Non-Unique, Non-Clusterd), WMS_IX_ParkEventLastModified (non-clustered), WMS_IX_ParkEventTimeDetected(non-clustered),
    tParkUnit table
    It contains 4000 rows.
    Index key is : PK_TParkUnit (clustered)
  15. Adriaan New Member

    You're not telling us which column(s) are in each index.
    And you're not telling us the details for the 62%, 19% and 18% costs.
  16. kumaravel v New Member

    tParkEvent Table : It contains more than 3millions records and the index keys are in the order: ParkUnitIdSource(Non-Unique, Non-Cluster), PK_TParkEvent(clustered), WMS_IX_InsertNotificationBuffer(Non-Unique, Non-Clusterd), WMS_IX_ParkEventLastModified (non-clustered), WMS_IX_ParkEventTimeDetected(non-clustered), tParkUnit table : It contains 4000 rows.Index key is : PK_TParkUnit (clustered)Execution Plan Result : 62% Argument : ORDER BY:([Expr1002] ASC, [a].[ParkEventStatusCode] ASC)Output List : [a].[ParkEventLastModified], [a].[ParkEventStatusCode], [a].[ParkEventTimeDetected], [Expr1002]
    19% :Argument :- OBJECT:([VMP01_DataCenter].[dbo].[TParkUnit].[PK_TParkUnit] AS ), SEEK:(.[ParkUnitId]=[a].[ParkUnitIdSource]), WHERE:(.[ParkUnitSerialNumber]='20538') ORDERED FORWARDObject :- [VMP01_DataCenter].[dbo].[TParkUnit].[PK_TParkUnit] AS Output List :- .[ParkUnitSerialNumber]
    18% :Argument :- OBJECT:([VMP01_DataCenter].[dbo].[TParkEvent].[WMS_IX_ParkEventTimeDetected] AS [a]), SEEK:([a].[ParkEventTimeDetected] >= 'Sep 14 2008 12:00AM' AND [a].[ParkEventTimeDetected] <= 'Sep 15 2008 12:00AM') ORDERED FORWARDObject :- [VMP01_DataCenter].[dbo].[TParkEvent].[WMS_IX_ParkEventTimeDetected] AS [a]
    Output List :- [Bmk1000]
    IS IT OK ?
  17. Adriaan New Member

    Well, you still haven't told us the column(s) that each of those indexes is covering, but something tells me the ParkEventStatusCode is not covered.
    Perhaps add an index on ParkEventStatusCode, and INCLUDE ParkEventLastModified and ParkEventTimeDetected.
  18. kumaravel v New Member

    Table index key :
    1. TParkUnit :
    PK_TParkUnit clustered, unique, primary key located on PRIMARY ParkUnitId
    2. TParkEvent :
    PK_TParkEvent clustered, unique, primary key located on PRIMARY ParkEventId
    WMS_IX_ParkEventLastModified nonclustered located on PRIMARY ParkEventLastModified, ParkUnitIdSource, ParkEventStatusCode
    WMS_IX_ParkEventTimeDetected nonclustered located on PRIMARY ParkEventTimeDetected, ParkUnitIdSource, ParkEventStatusCode
    WMS_IX_TParkEvent_CriticalValues nonclustered located on PRIMARY EventCodeId, ParkUnitIdSource, ModuleId, ParkEventTimeReset, ParkEventTimeDetected
    WMS_IX_TParkEvent_ModuleId nonclustered located on PRIMARY ModuleId
    WMS_IX_TParkEvent_ParkEventLastModified nonclustered located on PRIMARY ParkEventLastModified
    WMS_IX_TParkEvent_ParkUnitIdSource nonclustered located on PRIMARY ParkUnitIdSource
    WMS_IX_TParkEvent_ParkUnitIdSource_ParkEventStatusCode nonclustered located on PRIMARY ParkUnitIdSource, ParkEventStatusCode
    this is how the index keys are defined in both tables. Kindly let me know what i have to do further.
    thanks for your help.
  19. Adriaan New Member

    I would make at least one change, because ParkEventStatus is poorly covered: drop the ParkUnitIdSpource column from WMS_IX_TParkEvent_ParkUnitIdSource_ParkEventStatusCode. This column already has an index of its own.
    The ParkEventStatus column is covered by the WMS_IX_ParkEventLastModified index, but only as the third column, so its selectivity is low there.

Share This Page