sql executive time question

Question

sql executive time question

stephenhai

Mr or Mrs. 500

Points: 553
More actions
September 3, 2010 at 9:42 am

#95069

This is my sql statement:
SELECTSCSLA.SaleCycleStateId
FROMdbo.SaleCSLeadAssign AS SCSLA WITH (NOLOCK)
INNER JOIN
(
SELECT SCS1.SaleCycleStateID
FROM dbo.SaleCycleState SCS1 WITH (NOLOCK, INDEX(IxN_SaleCycleState_SaleCycleId_SaleCycleStatusId))
WHERE scs1.salecycleid = scs1.salecycleid and SCS1.SaleCSRecordStatusCode = 1
AND SCS1.SaleCycleStatusId = 109
) AS SCS
ON SCSLA.SaleCycleStateId = SCS.SaleCycleStateId
WHERESCSLA.AssignedToOrgId = 62 AND
SCSLA.FollowupDate BETWEEN 'Jan 1 1900 12:00AM' and 'Jun 26 2010 5:18PM'
This is the execution plan:
|--Parallelism(Gather Streams)
|--Hash Match(Inner Join, HASH:([SCSLA].[SaleCycleStateId])=([SCS1].[SaleCycleStateId]))
|--Bitmap(HASH:([SCSLA].[SaleCycleStateId]), DEFINE:([Bitmap1002]))
| |--Parallelism(Repartition Streams, PARTITION COLUMNS:([SCSLA].[SaleCycleStateId]))
| |--Index Seek(OBJECT:([SmartOpsProd410].[dbo].[SaleCSLeadAssign].[IxN_SaleCSLeadAssign_AssignedToOrgId_FollowUpDate] AS [SCSLA]), SEEK:([SCSLA].[AssignedToOrgId]=62 AND [SCSLA].[FollowUpDate] >= 'Jan 1 1900 12:00AM' AND [SCSLA].[Foll
|--Parallelism(Repartition Streams, PARTITION COLUMNS:([SCS1].[SaleCycleStateId]), WHERE:(PROBE([Bitmap1002])=TRUE))
|--Index Scan(OBJECT:([SmartOpsProd410].[dbo].[SaleCycleState].[IxN_SaleCycleState_SaleCycleId_SaleCycleStatusId] AS [SCS1]), WHERE:(([SCS1].[SaleCSRecordStatusCode]=1 AND [SCS1].[SaleCycleStatusId]=109) AND [SCS1].[SaleCycleId]=[SCS1].[S
I run it three times:
ROWNUMBER CPU Reads Duration
----------- ----------- -------------------- --------------------
12 6952 480033 4326
15 7533 479987 2190
18 7436 479798 2046
I expect the 2nd and 3rd time will be very fast
But you can see the CPU, Read IO and duration is almost the same
Can anyone explain why and how to solve it
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

Steve Jones - SSC Editor SSC Guru Points: 734826 More actions · Answer 1

What do you mean "solve it"?

SQL still has to run the query, read through the data, and process the results. They could change. The time savings usually comes from things being cached in memory (raw data), not the query results.

Charles Otten Default port Points: 1475 More actions · Answer 2

I don't know your database, but I'm curious about the inline SQL. Since you are not summarizing anything and appear to just be limiting records, have you tried rewriting the SQL to: (Also, have you tried without the index hint? SQL may have a better index)

SELECT SCSLA.SaleCycleStateId

FROM dbo.SaleCSLeadAssign AS SCSLA WITH (NOLOCK)

INNER JOIN dbo.SaleCycleState SCS1 WITH (NOLOCK INDEX(IxN_SaleCycleState_SaleCycleId_SaleCycleStatusId))

ON SCSLA.SaleCycleSateID = SCS1.SaleCycleStateID

and scs1.salecycleid = scs1.salecycleid

and SCS1.SaleCSRecordStatusCode = 1

AND SCS1.SaleCycleStatusId = 109

WHERE SCSLA.AssignedToOrgId = 62 AND

SCSLA.FollowupDate BETWEEN 'Jan 1 1900 12:00AM' and 'Jun 26 2010 5:18PM'

It might help. It might not.

good luck with it though.

If the phone doesn't ring...It's me.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Ow! Why are you using Nolock? Is potentially incorrect data acceptable?

Why are you forcing an index? Are you absolutely 100% sure that you know better than the query optimiser?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Derrick Smith SSChampion Points: 10833 More actions · Answer 4

Reads are just logical reads, either from buffer cache or physical.

It still takes the same number of reads and cpu cycles to run the query, but on the 2nd and 3rd executions, the data was in ram so it didn't have to go to disk to get it, therefore it takes less time.

Optimizing this query is a whole different ballpark though...