September 3, 2010 at 9:42 am
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
September 3, 2010 at 10:32 am
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.
September 10, 2010 at 12:49 pm
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.
September 10, 2010 at 2:01 pm
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
September 10, 2010 at 2:28 pm
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...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply