February 2, 2012 at 2:55 am
Hi, in my environment I have a query as:
SELECT [VpIVRCallValidationLog].[ID], [CustomerNumber], [CustomerFullName]
, [ANI], [TransactionTime], [SourceNumber], [DestinationNumber], [Description], [InsertionTime]
, [TransactionStatus], [IVRPath]
FROM [VeriBranchVakifBank].[dbo].[VpIVRCallValidationLog] with(nolock)
where TransactionTime > DATEADD(hour,-5,GETDATE())
and ([ANI] IN (
SELECT '0090' + [TelefonNo] collate Turkish_CI_AS FROM [GB_Basvuru].[dbo].[TuzakTelefonlar] WITH(NOLOCK) WHERE AktifMi = 1
) )
1. case is this query, resulting actual execution plan is 1.sqlplan attached. 15 seconds.
2. case is the same query, when I make DATEADD(hour,-10,GETDATE()) means more rows for VpIVRCallValidationLog. Result 0 seconds.
As you can see two execution plans are same, using same Index Seek, Key Lookup, Clustered Index Scan.
I see actual number of rows = 24 million for TuzakTelefonlar in 1.sqlplan
1756 for 2.sqlplan. 1756 is true, table has 1756 rows.
Where does 24 million rows come from? Why 1. execution plan is too slow?
Any help appreciated, I couldn't understand what is going on. Thanks.
February 2, 2012 at 3:35 am
sporoy (2/2/2012)
As you can see two execution plans are same, using same Index Seek, Key Lookup, Clustered Index Scan.
That's not what I see. One's using Nested Loops and the other a Hash Match. Looks like the extra rows are tipping the balance in the optimiser to make it pick a better plan. You should check the statistics on both databases, but it hasn't got a lot of information to work with in order to pick a good plan as :
1) This is cross database
2) It's cross-collation
3) you're checking for equality on a derived field ('0090' + [TelefonNo])
This pretty much rules out it being able to work out how many rows may be a match for the IN.
You could add a computed column on [GB_Basvuru].[dbo].[TuzakTelefonlar] as something like :
ANI AS '0090' + [TelefonNo] COLLATE Turkish_CI_AS
and then index the computed column + AktifMi, which may help the optimiser make a better decision, but I'd try to go back to the overall database design and rethink it ultimately.
February 2, 2012 at 5:48 am
Thanks Howard,
I now realized that 24 million is populated by the cartesian product of
[TuzakTelefonlar] X [VpIVRCallValidationLog] with(nolock)
where TransactionTime > DATEADD(hour,-5,GETDATE())
This is due to nested loop choice then.
February 2, 2012 at 6:14 am
By the way, I tried computed index, it also resolved the cross join..
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply