April 27, 2007 at 6:56 am
Hi,
I've the followin query which is taking lots of time and reads can anyone help me in optimising this...??
declare @currdate smalldatetime
set @currdate = getdate()
Selectsub.Subscriberid
,m.Memberid
FROMET_Main.dbo.Members m With (NOLOCK)
Inner JoinET_Commerce..Subscriptions s With (Nolock) On
s.Memberid = m.Memberid
Inner Join Subscriber Sub With (Nolock) On
Sub.MemberId = m.MemberId
Inner Join et_payment..Orders o With (Nolock) On
o.Orderid = s.Orderid
WHEREs.Isactive = 1 And
s.Dateexpires > @currdate And
s.Datedeactivated is null And -- active students
Not Exists (Select 'x' From et_payment.dbo.Chargebacks CB
Where CB.OrderId = O.OrderId)
Order by sub.Subscriberid desc I/O Statistics..
(51509 row(s) affected) Table 'ChargeBacks'. Scan count 5, logical reads 54, physical reads 0, read-ahead reads 54, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Subscriptions'. Scan count 5, logical reads 25185, physical reads 1, read-ahead reads 25150, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Orders'. Scan count 5, logical reads 3843, physical reads 0, read-ahead reads 3843, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Subscriber'. Scan count 96167, logical reads 634086, physical reads 1199, read-ahead reads 13069, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Members'. Scan count 0, logical reads 544211, physical reads 1540, read-ahead reads 45976, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
April 27, 2007 at 7:58 am
Can you send the text execution plan too?
April 27, 2007 at 8:40 am
515811Select Distinct sub.Subscriberid ,m.Memberid FROM ET_Main.dbo.Members m With (Nolock) Inner Join Subscriber Sub With (Nolock) On Sub.MemberId = m.MemberId Inner Join ET_Commerce..Subscriptions s With (Nolock) On s.Memberid = m.Memberid Inner Join et_payment..Orders o With (Nolock) On o.Orderid = s.Orderid WHERE s.Isactive = @a And s.Dateexpires > getdate() And Datedeactivated is null And -- active students Not Exists (Select 'x' From et_payment.dbo.Chargebacks CB With (Nolock) Where CB.OrderId = O.OrderId) Order by sub.Subscriberid Desc110NULLNULLNULLNULL254439.8NULLNULLNULL72.15086NULLNULLSELECT0NULL 515811 |--Parallelism(Gather Streams, ORDER BY ([Sub].[SubscriberId] DESC))121ParallelismGather StreamsORDER BY ([Sub].[SubscriberId] DESC)NULL254439.801.6356051572.15086[m].[MemberId], [Sub].[SubscriberId]NULLPLAN_ROW11 515814 |--Sort(DISTINCT ORDER BY ([Sub].[SubscriberId] DESC))132SortDistinct SortDISTINCT ORDER BY ([Sub].[SubscriberId] DESC)NULL254439.80.00563063110.717691570.51525[m].[MemberId], [Sub].[SubscriberId]NULLPLAN_ROW11 515824 |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS ([Sub].[SubscriberId]))143ParallelismRepartition StreamsPARTITION COLUMNS ([Sub].[SubscriberId])NULL259643.600.43812021559.79193[m].[MemberId], [Sub].[SubscriberId]NULLPLAN_ROW11 515824 |--Nested Loops(Inner Join, OUTER REFERENCES (.[MemberId], [Expr1019]) OPTIMIZED WITH UNORDERED PREFETCH)154Nested LoopsInner JoinOUTER REFERENCES (.[MemberId], [Expr1019]) OPTIMIZED WITH UNORDERED PREFETCHNULL259643.600.54265511559.35381[m].[MemberId], [Sub].[SubscriberId]NULLPLAN_ROW11 962444 |--Nested Loops(Inner Join, OUTER REFERENCES (.[MemberId], [Expr1018]) OPTIMIZED WITH UNORDERED PREFETCH)185Nested LoopsInner JoinOUTER REFERENCES (.[MemberId], [Expr1018]) OPTIMIZED WITH UNORDERED PREFETCHNULL8808.14600.018409021539.48646[m].[MemberId],.[MemberId]NULLPLAN_ROW11 962464 | |--Hash Match(Right Anti Semi Join, HASH ([Expr1016])=([o].[OrderId]), RESIDUAL ([Expr1016]=[ET_Payment].[dbo].[Orders].[OrderId] as [o].[OrderId]))1118Hash MatchRight Anti Semi JoinHASH ([Expr1016])=([o].[OrderId]), RESIDUAL ([Expr1016]=[ET_Payment].[dbo].[Orders].[OrderId] as [o].[OrderId])NULL8808.14600.090904571111.67561.[MemberId]NULLPLAN_ROW11 41814 | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS ([Expr1016]))11211ParallelismRepartition StreamsPARTITION COLUMNS ([Expr1016])NULL418100.03474798160.07975859[Expr1016]NULLPLAN_ROW11 00 | | | |--Compute Scalar(DEFINE ([Expr1016]=CONVERT_IMPLICIT(decimal(18,0),[ET_Payment].[dbo].[ChargeBacks].[OrderId] as [CB].[OrderId],0)))11312Compute ScalarCompute ScalarDEFINE ([Expr1016]=CONVERT_IMPLICIT(decimal(18,0),[ET_Payment].[dbo].[ChargeBacks].[OrderId] as [CB].[OrderId],0))[Expr1016]=CONVERT_IMPLICIT(decimal(18,0),[ET_Payment].[dbo].[ChargeBacks].[OrderId] as [CB].[OrderId],0)418100.00020905160.04501061[Expr1016]NULLPLAN_ROW11 41814 | | | |--Table Scan(OBJECT ([ET_Payment].[dbo].[ChargeBacks] AS [CB]))11413Table ScanTable ScanOBJECT ([ET_Payment].[dbo].[ChargeBacks] AS [CB])[CB].[OrderId]41810.042462760.0023388110.04480156[CB].[OrderId]NULLPLAN_ROW11 962474 | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS ([o].[OrderId]))11811ParallelismRepartition StreamsPARTITION COLUMNS ([o].[OrderId])NULL8808.1500.045005372011.50494.[MemberId], [o].[OrderId]NULLPLAN_ROW11 962474 | | |--Hash Match(Inner Join, HASH (.[OrderId])=([o].[OrderId]), RESIDUAL ([ET_Payment].[dbo].[Orders].[OrderId] as [o].[OrderId]=[ET_Commerce].[dbo].[Subscriptions].[OrderId] as.[OrderId]))11918Hash MatchInner JoinHASH (.[OrderId])=([o].[OrderId]), RESIDUAL ([ET_Payment].[dbo].[Orders].[OrderId] as [o].[OrderId]=[ET_Commerce].[dbo].[Subscriptions].[OrderId] as.[OrderId])NULL8808.1505.2743072011.45993.[MemberId], [o].[OrderId]NULLPLAN_ROW11 985334 | | |--Bitmap(HASH (.[OrderId]), DEFINE ([Bitmap1017]))12019BitmapBitmap CreateHASH (.[OrderId])[Bitmap1017]13430.2808.074088E-08200.1241668.[MemberId],.[OrderId]NULLPLAN_ROW11 985334 | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS (.[OrderId]))12220ParallelismRepartition StreamsPARTITION COLUMNS (.[OrderId])NULL1.61481800.02850375200.1241667.[MemberId],.[OrderId]NULLPLAN_ROW11 985334 | | | |--Filter(WHERE (CONVERT_IMPLICIT(datetime,[ET_Commerce].[dbo].[Subscriptions].[DateExpires] as.[DateExpires],0)>getdate()))12322FilterFilterWHERE (CONVERT_IMPLICIT(datetime,[ET_Commerce].[dbo].[Subscriptions].[DateExpires] as.[DateExpires],0)>getdate())NULL1.61481805.909667E-06200.09566298.[MemberId],.[OrderId]NULLPLAN_ROW11 1318974 | | | |--Nested Loops(Inner Join, OUTER REFERENCES ([Uniq1006],.[SubscriptionId]) OPTIMIZED)12423Nested LoopsInner JoinOUTER REFERENCES ([Uniq1006],.[SubscriptionId]) OPTIMIZEDNULL20.3781604.259036E-05240.09565707.[MemberId],.[DateExpires],.[OrderId]NULLPLAN_ROW11 1318974 | | | |--Parallelism(Distribute Streams, RoundRobin Partitioning)12624ParallelismDistribute StreamsNULLNULL20.3781600.02855365150.03185806[Uniq1006],.[SubscriptionId]NULLPLAN_ROW11 1318971 | | | | |--Index Seek(OBJECT ([ET_Commerce].[dbo].[Subscriptions].[IX_subscriptions_datedeactivated] AS), SEEK (.[DateDeactivated]=NULL AND.[IsActive]=[@a]) ORDERED FORWARD)12726Index SeekIndex SeekOBJECT ([ET_Commerce].[dbo].[Subscriptions].[IX_subscriptions_datedeactivated] AS), SEEK (.[DateDeactivated]=NULL AND.[IsActive]=[@a]) ORDERED FORWARD[Uniq1006],.[SubscriptionId]20.378160.0031250.000179416150.003304416[Uniq1006],.[SubscriptionId]NULLPLAN_ROW01 131897131897 | | | |--Clustered Index Seek(OBJECT ([ET_Commerce].[dbo].[Subscriptions].[IX_Subscription_subscriptionID] AS), SEEK (.[SubscriptionId]=[ET_Commerce].[dbo].[Subscriptions].[SubscriptionId] as.[SubscriptionId] AND [Uniq1006]=[Uniq1006]) LOOKUP ORDERED FORWARD)12924Clustered Index SeekClustered Index SeekOBJECT ([ET_Commerce].[dbo].[Subscriptions].[IX_Subscription_subscriptionID] AS), SEEK (.[SubscriptionId]=[ET_Commerce].[dbo].[Subscriptions].[SubscriptionId] as.[SubscriptionId] AND [Uniq1006]=[Uniq1006]) LOOKUP ORDERED FORWARD.[MemberId],.[DateExpires],.[OrderId]10.0031250.0001581240.06375642.[MemberId],.[DateExpires],.[OrderId]NULLPLAN_ROW120.37816 2111224 | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS ([o].[OrderId]), WHERE (PROBE([Bitmap1017])=TRUE))13919ParallelismRepartition StreamsPARTITION COLUMNS ([o].[OrderId]), WHERE (PROBE([Bitmap1017])=TRUE)NULL156460702.36661166.061458[o].[OrderId]NULLPLAN_ROW11 15646074 | | |--Index Scan(OBJECT ([ET_Payment].[dbo].[Orders].[Partner] AS [o]))14039Index ScanIndex ScanOBJECT ([ET_Payment].[dbo].[Orders].[Partner] AS [o])[o].[OrderId]15646072.8342360.8606123163.694849[o].[OrderId]NULLPLAN_ROW11 9624496246 | |--Clustered Index Seek(OBJECT ([ET_Main].[dbo].[Members].[PK_Members] AS [m]), SEEK ([m].[MemberId]=[ET_Commerce].[dbo].[Subscriptions].[MemberId] as.[MemberId]) ORDERED FORWARD)1438Clustered Index SeekClustered Index SeekOBJECT ([ET_Main].[dbo].[Members].[PK_Members] AS [m]), SEEK ([m].[MemberId]=[ET_Commerce].[dbo].[Subscriptions].[MemberId] as.[MemberId]) ORDERED FORWARD[m].[MemberId]10.0031250.00015811127.79244[m].[MemberId]NULLPLAN_ROW18808.146 5158296244 |--Index Seek(OBJECT ([DW_Message].[dbo].[Subscriber].[IX_Subscriber_MemberId_VersionMember] AS [Sub]), SEEK ([Sub].[MemberId]=[ET_Commerce].[dbo].[Subscriptions].[MemberId] as.[MemberId]) ORDERED FORWARD)1445Index SeekIndex SeekOBJECT ([DW_Message].[dbo].[Subscriber].[IX_Subscriber_MemberId_VersionMember] AS [Sub]), SEEK ([Sub].[MemberId]=[ET_Commerce].[dbo].[Subscriptions].[MemberId] as.[MemberId]) ORDERED FORWARD[Sub].[SubscriberId]29.477660.0031250.00018942541119.3247[Sub].[SubscriberId]NULLPLAN_ROW18808.146
April 27, 2007 at 1:05 pm
I've only spent five minutes looking at this, but the first two things I'd check would be the parallelism threshold on your server and the cluster on the Subscriber table.
I'd also look at dropping the NOT IN clause and replacing with a LEFT OUTER JOIN with a check for null values.
You're getting an implicit conversion to Decimal on the ChargeBacks.OrderId. That could be the cause of the table scans against the ChargeBack table since that will prevent the index from being used.
There's probably more here that I haven't seen yet.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply