Performance Issue on my Query Execution

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

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

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.

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)

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.

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 ?

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.

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.

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.