General information
Forum:
Microsoft SQL Server
Category:
Stored procedures, Triggers, UDFs
Environment versions
SQL Server:
SQL Server 2000
>Got it. I only included the two parts that are different between the two. That is the queries to FLAIRTR2 and CATR1.
>
>Batch Version Plan:
>SELECT FLAIRTR2.*
>FROM FLAIRData.dbo.FlairTR2 FLAIRTR2
>INNER JOIN #Temp TMP
> ON FLAIRTR2.FiscalYear = TMP.FY
> AND FLAIRTR2.Account = TMP.Account
> AND FLAIRTR2.CFI = TMP.CFI
> AND FLAIRTR2.SWDN = TMP.SWDN
> AND FLAIRTR2.PrimaryDoc7 = TMP.Prima
>
>(1 row(s) affected)
>
>StmtText
>----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> |--Parallelism(Gather Streams, ORDER BY:([FLAIRTR2].[UniqueID] ASC))
> |--Sort(ORDER BY:([FLAIRTR2].[UniqueID] ASC))
> |--Compute Scalar(DEFINE:([FLAIRTR2].[PrimaryDoc7]=substring([FLAIRTR2].[PrimaryDoc], 1, 7)))
> |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([FLAIRData].[dbo].[FLAIRTR2] AS [FLAIRTR2]))
> |--Nested Loops(Inner Join, OUTER REFERENCES:([TMP].[Site], [TMP].[GLC], [TMP].[PrimaryDoc], [TMP].[SWDN], [TMP].[CFI], [TMP].[Account], [TMP].[FY]) WITH PREFETCH)
> |--Sort(ORDER BY:([TMP].[FY] ASC, [TMP].[Account] ASC, [TMP].[CFI] ASC, [TMP].[GLC] ASC, [TMP].[PrimaryDoc] ASC, [TMP].[SWDN] ASC, [TMP].[Site] ASC))
> | |--Table Scan(OBJECT:([tempdb].[dbo].[#Temp_______________________________________________________________________________________________________________000000001D3A] AS [TMP]))
> |--Index Seek(OBJECT:([FLAIRData].[dbo].[FLAIRTR2].[IX_FLAIRTR2_ReconKey] AS [FLAIRTR2]), SEEK:([FLAIRTR2].[FiscalYear]=[TMP].[FY] AND [FLAIRTR2].[Account]=[TMP].[Account] AND [FLAIRTR2].[CFI]=[TMP].[CFI] AND [FLAIRTR2].[GLC]=[TM
>
>(8 row(s) affected)
>
>StmtText
>----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>SELECT CATR1.*
>FROM FLAIRData.dbo.CATR1 CATR1
>INNER JOIN #Temp TMP
> ON CATR1.FiscalYear = TMP.FY
> AND CATR1.Account = TMP.Account
> AND CATR1.CFI = TMP.CFI
> AND CATR1.SWDN11 = TMP.SWDN
> AND CATR1.AgencyDocNumber = TMP.PrimaryDoc
> AND CATR1
>
>(1 row(s) affected)
>
>StmtText
>----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> |--Compute Scalar(DEFINE:([CATR1].[SWDN11]=substring([CATR1].[SWDN], 1, 11)))
> |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([FLAIRData].[dbo].[CATR1] AS [CATR1]))
> |--Parallelism(Gather Streams)
> |--Nested Loops(Inner Join, OUTER REFERENCES:([TMP].[Site], [TMP].[PrimaryDoc], [TMP].[SWDN], [TMP].[CFI], [TMP].[Account], [TMP].[FY]) WITH PREFETCH)
> |--Sort(ORDER BY:([TMP].[FY] ASC, [TMP].[Account] ASC, [TMP].[CFI] ASC, [TMP].[SWDN] ASC, [TMP].[PrimaryDoc] ASC, [TMP].[Site] ASC))
> | |--Table Scan(OBJECT:([tempdb].[dbo].[#Temp_______________________________________________________________________________________________________________000000001D3A] AS [TMP]))
> |--Index Seek(OBJECT:([FLAIRData].[dbo].[CATR1].[IX_CATR1_Recons] AS [CATR1]), SEEK:([CATR1].[FiscalYear]=[TMP].[FY] AND [CATR1].[Account]=[TMP].[Account] AND [CATR1].[CFI]=[TMP].[CFI] AND [CATR1].[SWDN11]=[TMP].[SWDN] AND [CATR1].[Ag
>
>
>
>
>I'm not able to get the SP Version's Plan since it uses a temporary table within the stored procedure. However, it does use a Clustered Index Scan and goes through all 17million records before the join.
>
>I'm thinking you're right, given the way I've done this. The RECOMPILE option (that a couple of people suggested) did not work though.
>
>Richard
Post the exact indexes definition on the two FLAIRData's tables and a solution we find it.
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only