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.

CAST ( CAST ( YearPart AS VARCHAR ) + '-' + RIGHT ( '0' + CAST ( MonthPart AS VARCHAR ), 2 ) + '-' + RIGHT ( '0' + CAST ( DayPart AS VARCHAR ), 2 ) AS SMALLDATETIME ) AS DataCapturePeriodStart,

CASE

WHEN StatusCode = 'W' THEN 101

WHEN StatusCode = 'S' THEN 103

END AS DataType

FROM

(select count(1) AS DataCaptureCount, a.StatusCode,

DATEPART ( yy, a.TimeDetected ) AS YearPart, DATEPART ( mm, a.TimeDetected ) AS MonthPart, DATEPART ( dd, a.TimeDetected ) AS DayPart

where a.unitidsource=b.unitid and b.unitserialNumber = '20538'

GROUP BY a.StatusCode, b.SerialNumber, DATEPART ( yy, a.TimeDetected ), DATEPART ( mm, a.TimeDetected ), DATEPART ( dd, a.TimeDetected )

kumar

What indexes do you have on those tables?

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)

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.

i tried that too. But no Use.   

Is there any other way to find out the performance solution ?  Is the index keys are right??

Other option - do the filtering and date manipulation in a derived table embedded within your derived table.

sorry can you little brief about what you said. I dont understand.

 thanks

Inside your derived table (TT1) move the DATEPART bits to and the WHERE clause for the date filtering to a derived table. This often helps SQL doing first things first, instead doing everything in aggregate.

thanks man.

 I have changed my query below:

CASE

WHEN ParkEventStatusCode = 'W' THEN 101

WHEN ParkEventStatusCode = 'S' THEN 103

FROM

(select count(1) AS DataCaptureCount, a.ParkEventStatusCode,

convert(varchar(12),a.ParkEventTimeDetected,105) AS DayMonthYearPart

ON a.parkunitidsource=b.parkunitid and b.parkunitserialNumber = '20538'

AND (A.ParkEventTimeDetected BETWEEN '2008-09-14' AND '2008-09-15')

--------------

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.

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.

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?

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?

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.

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

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)