July 23, 2008 at 7:34 am
Okay, I am not sure where to start in my troubleshooting. The situation is this: I can run a set of queries, using temp tables, that takes less than one second to complete. If I try and process it in a single query, it takes over an hour. I connect to several databases as well as using OpenQuery within to access a DB2 server. My end goal is to create this query as a view (I would prefere not using a function to process with temp tables / table variables). I reallize I can do this with a stored procedure, but I want to understand why I cannot create this as an efficient view (or what I am doing wrong). I will provide the queries (my tables and databases are aliased here for security) and the estimated execution plans for those who may want to dig into what it is (or should be) trying to do.
Any help would be appreciated! Thanks.
P.S. I reallize these tables are not optimized, but I want to understand why there is such a difference between processing with the temp tables and without. I did not write these tables or the view. Also note that there is a function used in the view vw_RMCDetail.
[h1]Using temp tables:[/h1]
[h2]SQL[/h2]
SELECT *
INTO #Tmp1
FROM OPENQUERY(DB2Server, 'SELECT DISTINCT FNFAC@, FNFEE@ FROM DB2Schema.FP WHERE FNPRV@ = ''***'' AND FNFAC@ BETWEEN ''001'' AND ''999''')
SELECT C.Fac, L.Label, I.ProcID AS ProcCode
INTO #Tmp2
FROM Fac.dbo.vw_RMCDetail C WITH (NOLOCK)
JOIN dbo.Inclusion I WITH (NOLOCK)
ON (C.FacID = I.FacID OR I.FacID IS NULL)
AND (C.MarketID = I.MarketID OR I.MarketID IS NULL)
AND (C.RegionID = I.RegionID OR I.RegionID IS NULL)
AND (C.State = I.State OR I.State IS NULL)
LEFT JOIN Exclusion E WITH (NOLOCK)
ON (C.FacID = E.FacID OR E.FacID IS NULL)
AND (C.MarketID = E.MarketID OR E.MarketID IS NULL)
AND (C.RegionID = E.RegionID OR E.RegionID IS NULL)
AND (C.State = E.State OR E.State IS NULL)
AND I.ProcID = E.ProcID
JOIN Labels L WITH (NOLOCK)
ON I.ProcID = L.ProcID
WHERE E.ProcID IS NULL
UDF: [Fac].[dbo].[udf_CurrentBrandFac]
-- =============================================
-- Description:This is designed to find the most current record
--([dbo].[udf_CurrentBrandFac]([FacID],[StartDate]))
-- =============================================
CREATE FUNCTION [dbo].[udf_CurrentBrandFac]
(-- Add the parameters for the function here
@ID int,
@Date DateTime)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @ResultVar int
-- Add the T-SQL statements to compute the return value here
Set @ResultVar =(Select Case When FacID = @ID and Max(StartDate) = @Date Then 1 Else 0 End
From dbo.BrandFac
Where FacID = @ID
And StartDate <= GetDate()
Group By FacID)
-- Return the result of the function
RETURN @ResultVar
SELECT B.Fac, B.Label, B.ProcCode, F.FDFEE$
FROM #Tmp1 FP
JOIN RptDBExtract.dbo.FEEDTL F WITH (NOLOCK)
ON F.FDFEE@ = FP.FNFEE@
JOIN #Tmp2 B
ON B.Fac = FP.FNFAC@
AND B.ProcCode = F.FDPROC
[h2]Estimated Execution Plan[/h2]
|--Table Insert(OBJECT: ([#Tmp1]), SET: ([#Tmp1].[FNFAC@] = [MSDASQL].[FNFAC@],[#Tmp1].[FNFEE@] = [MSDASQL].[FNFEE@]))
|--Table Spool
|--Top(ROWCOUNT est 0)
|--Remote Scan(SOURCE: (DB2Server), OBJECT: (SELECT DISTINCT FNFAC@, FNFEE@ FROM DB2Schema.FP WHERE FNPRV@ = '***' AND FNFAC@ BETWEEN '001' AND '999'))
|--Table Insert(OBJECT: ([#Tmp2]), SET: ([#Tmp2].[Fac] = [Fac].[dbo].[Fac].[Fac] as [C].[Fac],[#Tmp2].[Label] = [RptDB].[dbo].[Labels].[Label] as [L].[Label],[#Tmp2].[ProcCode] = [RptDB].[dbo].[Inclusion].[ProcID] as .[ProcID]))
|--Top(ROWCOUNT est 0)
|--Filter(WHERE: ([RptDB].[dbo].[Exclusion].[ProcID] as [E].[ProcID] IS NULL))
|--Hash Match(Right Outer Join, HASH: ([E].[ProcID])=(.[ProcID]), RESIDUAL: (([Fac].[dbo].[Fac].[FacID] as [C].[FacID]=[RptDB].[dbo].[Exclusion].[FacID] as [E].[FacID] OR [RptDB].[dbo].[Exclusion].[FacID] as [E].[FacID] IS NULL) AND ([Fac].[dbo].[Market].[MarketID] as [M].[MarketID]=[RptDB].[dbo].[Exclusion].[MarketID] as [E].[MarketID] OR [RptDB].[dbo].[Exclusion].[MarketID] as [E].[MarketID] IS NULL) AND ([Fac].[dbo].[Region].[RegionID] as [R].[RegionID]=[RptDB].[dbo].[Exclusion].[RegionID] as [E].[RegionID] OR [RptDB].[dbo].[Exclusion].[RegionID] as [E].[RegionID] IS NULL) AND ([Expr1076]=[RptDB].[dbo].[Exclusion].[State] as [E].[State] OR [RptDB].[dbo].[Exclusion].[State] as [E].[State] IS NULL) AND [RptDB].[dbo].[Inclusion].[ProcID] as .[ProcID]=[RptDB].[dbo].[Exclusion].[ProcID] as [E].[ProcID]))
|--Table Scan(OBJECT: ([RptDB].[dbo].[Exclusion] AS [E]))
|--Hash Match(Right Outer Join, HASH: ([CI].[FacID])=([C].[FacID]))
|--Table Scan(OBJECT: ([Fac].[dbo].[FacIOC] AS [CI]))
|--Hash Match(Inner Join, HASH: ([L].[ProcID])=(.[ProcID]), RESIDUAL: ([RptDB].[dbo].[Inclusion].[ProcID] as .[ProcID]=[RptDB].[dbo].[Labels].[ProcID] as [L].[ProcID]))
|--Table Scan(OBJECT: ([RptDB].[dbo].[Labels] AS [L]))
|--Nested Loops(Inner Join, WHERE: (([Fac].[dbo].[Fac].[FacID] as [C].[FacID]=[RptDB].[dbo].[Inclusion].[FacID] as .[FacID] OR [RptDB].[dbo].[Inclusion].[FacID] as .[FacID] IS NULL) AND ([Fac].[dbo].[Market].[MarketID] as [M].[MarketID]=[RptDB].[dbo].[Inclusion].[MarketID] as .[MarketID] OR [RptDB].[dbo].[Inclusion].[MarketID] as .[MarketID] IS NULL) AND ([Fac].[dbo].[Region].[RegionID] as [R].[RegionID]=[RptDB].[dbo].[Inclusion].[RegionID] as .[RegionID] OR [RptDB].[dbo].[Inclusion].[RegionID] as .[RegionID] IS NULL) AND ([Expr1076]=[RptDB].[dbo].[Inclusion].[State] as .[State] OR [RptDB].[dbo].[Inclusion].[State] as .[State] IS NULL)))
|--Table Spool
| |--Hash Match(Inner Join, HASH: ([MC].[FacID])=([C].[FacID]))
| |--Nested Loops(Inner Join, OUTER REFERENCES: ([RM].[MarketID]))
| | |--Hash Match(Inner Join, HASH: ([RM].[MarketID])=([M].[MarketID]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES: ([R].[RegionID]))
| | | | |--Table Scan(OBJECT: ([Fac].[dbo].[Region] AS [R]))
| | | | |--Clustered Index Seek(OBJECT: ([Fac].[dbo].[RegionMarket].[PK_RegionMarket] AS [RM]), SEEK: ([RM].[RegionID]=[Fac].[dbo].[Region].[RegionID] as [R].[RegionID]) ORDERED FORWARD)
| | | |--Table Scan(OBJECT: ([Fac].[dbo].[Market] AS [M]))
| | |--Clustered Index Seek(OBJECT: ([Fac].[dbo].[MarketFac].[PK_MarketFac] AS [MC]), SEEK: ([MC].[MarketID]=[Fac].[dbo].[RegionMarket].[MarketID] as [RM].[MarketID]) ORDERED FORWARD)
| |--Filter(WHERE: ([BC].[CurrentRecord]=(1)))
| |--Compute Scalar(DEFINE: ([BC].[CurrentRecord]=[Fac].[dbo].[udf_CurrentBrandFac]([Fac].[dbo].[BrandFac].[FacID] as [BC].[FacID],[Expr1077])))
| |--Hash Match(Inner Join, HASH: ([C].[Fac])=([FM].[FMFAC@]), RESIDUAL: ([Fac].[dbo].[Fac].[Fac] as [C].[Fac]=[RptDBExtract].[dbo].[FACMAS].[FMFAC@] as [FM].[FMFAC@]))
| |--Nested Loops(Inner Join, OUTER REFERENCES: ([LE].[FacID]))
| | |--Hash Match(Inner Join, HASH: ([LE].[FacID])=([CA].[FacID]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES: ([BC].[FacID]))
| | | | |--Compute Scalar(DEFINE: ([Expr1077]=CONVERT_IMPLICIT(datetime,[Fac].[dbo].[BrandFac].[StartDate] as [BC].[StartDate],0)))
| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES: (.[BrandID]))
| | | | | |--Clustered Index Scan(OBJECT: ([Fac].[dbo].[Brand].[PK_Brand] AS ))
| | | | | |--Clustered Index Seek(OBJECT: ([Fac].[dbo].[BrandFac].[PK_BrandFac] AS [BC]), SEEK: ([BC].[BrandID]=[Fac].[dbo].[Brand].[BrandID] as .[BrandID]) ORDERED FORWARD)
| | | | |--Clustered Index Seek(OBJECT: ([Fac].[dbo].[LegalEntities].[PK_LegalEntities] AS [LE]), SEEK: ([LE].[FacID]=[Fac].[dbo].[BrandFac].[FacID] as [BC].[FacID]) ORDERED FORWARD)
| | | |--Compute Scalar(DEFINE: ([Expr1076]=upper([Fac].[dbo].[FacAddress].[State] as [CA].[State])))
| | | |--Table Scan(OBJECT: ([Fac].[dbo].[FacAddress] AS [CA]))
| | |--Clustered Index Seek(OBJECT: ([Fac].[dbo].[Fac].[PK_FacName] AS [C]), SEEK: ([C].[FacID]=[Fac].[dbo].[LegalEntities].[FacID] as [LE].[FacID]) ORDERED FORWARD)
| |--Table Scan(OBJECT: ([RptDBExtract].[dbo].[FACMAS] AS [FM]))
|--Table Scan(OBJECT: ([RptDB].[dbo].[Inclusion] AS ))
|--Compute Scalar(DEFINE: ([Expr1008]=[Expr1008]))
|--Nested Loops(Left Outer Join)
|--Constant Scan
|--Assert(WHERE: (CASE WHEN [Expr1007]>(1) THEN (0) ELSE NULL END))
|--Stream Aggregate(DEFINE: ([Expr1007]=Count(*), [Expr1008]=ANY(CASE WHEN [Fac].[dbo].[BrandFac].[FacID]=[@ID] AND [Expr1003]=[@Date] THEN (1) ELSE (0) END)))
|--Stream Aggregate(DEFINE: ([Expr1003]=MAX([Fac].[dbo].[BrandFac].[StartDate]), [Fac].[dbo].[BrandFac].[FacID]=ANY([Fac].[dbo].[BrandFac].[FacID])))
|--Clustered Index Scan(OBJECT: ([Fac].[dbo].[BrandFac].[PK_BrandFac]), WHERE: ([Fac].[dbo].[BrandFac].[FacID]=[@ID] AND [Fac].[dbo].[BrandFac].[StartDate]<=getdate()))
|--Hash Match(Inner Join, HASH: (.[ProcCode], [FP].[FNFEE@])=([F].[FDPROC], [F].[FDFEE@]), RESIDUAL: ([tempdb].[dbo].[#Tmp2].[ProcCode] as .[ProcCode]=[RptDBExtract].[dbo].[FEEDTL].[FDPROC] as [F].[FDPROC] AND [tempdb].[dbo].[#Tmp1].[FNFEE@] as [FP].[FNFEE@]=[RptDBExtract].[dbo].[FEEDTL].[FDFEE@] as [F].[FDFEE@]))
|--Hash Match(Inner Join, HASH: ([FP].[FNFAC@])=(.[Fac]), RESIDUAL: ([tempdb].[dbo].[#Tmp2].[Fac] as .[Fac]=[tempdb].[dbo].[#Tmp1].[FNFAC@] as [FP].[FNFAC@]))
| |--Table Scan(OBJECT: ([tempdb].[dbo].[#Tmp1] AS [FP]))
| |--Table Scan(OBJECT: ([tempdb].[dbo].[#Tmp2] AS ))
|--Table Scan(OBJECT: ([RptDBExtract].[dbo].[FEEDTL] AS [F]))
[h1]Using a single query:[/h1]
[h2]SQL[/h2]
SELECT B.Fac, B.Label, B.ProcCode, F.FDFEE$
FROM OPENQUERY(DB2Server, 'SELECT DISTINCT FNFAC@, FNFEE@ FROM DB2SCHEMA.FP WHERE FNPRV@ = ''***'' AND FNFAC@ BETWEEN ''001'' AND ''999''') FP
JOIN RptDBExtract.dbo.FEEDTL F WITH (NOLOCK)
ON F.FDFEE@ = FP.FNFEE@
JOIN (
SELECT C.Fac, L.Label, I.ProcID AS ProcCode
FROM Fac.dbo.vw_RMCDetail C WITH (NOLOCK)
JOIN dbo.Inclusion I WITH (NOLOCK)
ON (C.FacID = I.FacID OR I.FacID IS NULL)
AND (C.MarketID = I.MarketID OR I.MarketID IS NULL)
AND (C.RegionID = I.RegionID OR I.RegionID IS NULL)
AND (C.State = I.State OR I.State IS NULL)
LEFT JOIN Exclusion E WITH (NOLOCK)
ON (C.FacID = E.FacID OR E.FacID IS NULL)
AND (C.MarketID = E.MarketID OR E.MarketID IS NULL)
AND (C.RegionID = E.RegionID OR E.RegionID IS NULL)
AND (C.State = E.State OR E.State IS NULL)
AND I.ProcID = E.ProcID
JOIN Labels L WITH (NOLOCK)
ON I.ProcID = L.ProcID
WHERE E.ProcID IS NULL) B
ON B.Fac = FP.FNFAC@
AND B.ProcCode = F.FDPROC
UDF: [Fac].[dbo].[udf_CurrentBrandFac]
-- =============================================
-- Description:This is designed to find the most current record
--([dbo].[udf_CurrentBrandFac]([FacID],[StartDate]))
-- =============================================
CREATE FUNCTION [dbo].[udf_CurrentBrandFac]
(-- Add the parameters for the function here
@ID int,
@Date DateTime)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @ResultVar int
-- Add the T-SQL statements to compute the return value here
Set @ResultVar =(Select Case When FacID = @ID and Max(StartDate) = @Date Then 1 Else 0 End
From dbo.BrandFac
Where FacID = @ID
And StartDate <= GetDate()
Group By FacID)
-- Return the result of the function
RETURN @ResultVar
[h2]Estimated Execution Plan[/h2]
|--Filter(WHERE: ([RptDB].[dbo].[Exclusion].[ProcID] as [E].[ProcID] IS NULL))
|--Nested Loops(Left Outer Join, WHERE: (([Fac].[dbo].[Fac].[FacID] as [C].[FacID]=[RptDB].[dbo].[Exclusion].[FacID] as [E].[FacID] OR [RptDB].[dbo].[Exclusion].[FacID] as [E].[FacID] IS NULL) AND ([Fac].[dbo].[Market].[MarketID] as [M].[MarketID]=[RptDB].[dbo].[Exclusion].[MarketID] as [E].[MarketID] OR [RptDB].[dbo].[Exclusion].[MarketID] as [E].[MarketID] IS NULL) AND ([Fac].[dbo].[Region].[RegionID] as [R].[RegionID]=[RptDB].[dbo].[Exclusion].[RegionID] as [E].[RegionID] OR [RptDB].[dbo].[Exclusion].[RegionID] as [E].[RegionID] IS NULL) AND ([Expr1077]=[RptDB].[dbo].[Exclusion].[State] as [E].[State] OR [RptDB].[dbo].[Exclusion].[State] as [E].[State] IS NULL) AND [RptDB].[dbo].[Inclusion].[ProcID] as .[ProcID]=[RptDB].[dbo].[Exclusion].[ProcID] as [E].[ProcID]))
|--Nested Loops(Left Outer Join, WHERE: ([Fac].[dbo].[Fac].[FacID] as [C].[FacID]=[Fac].[dbo].[FacIOC].[FacID] as [CI].[FacID]))
| |--Nested Loops(Inner Join, WHERE: ([RptDB].[dbo].[Inclusion].[ProcID] as .[ProcID]=[RptDB].[dbo].[Labels].[ProcID] as [L].[ProcID]))
| | |--Nested Loops(Inner Join, WHERE: ([Fac].[dbo].[Fac].[Fac] as [C].[Fac]=[MSDASQL].[FNFAC@] as [FP].[FNFAC@] AND [MSDASQL].[FNFEE@] as [FP].[FNFEE@]=[RptDBExtract].[dbo].[FEEDTL].[FDFEE@] as [F].[FDFEE@]))
| | | |--Nested Loops(Inner Join, WHERE: ([Fac].[dbo].[RegionMarket].[MarketID] as [RM].[MarketID]=[Fac].[dbo].[Market].[MarketID] as [M].[MarketID] AND ([Fac].[dbo].[Market].[MarketID] as [M].[MarketID]=[RptDB].[dbo].[Inclusion].[MarketID] as .[MarketID] OR [RptDB].[dbo].[Inclusion].[MarketID] as .[MarketID] IS NULL)))
| | | | |--Nested Loops(Inner Join, WHERE: ([RptDB].[dbo].[Inclusion].[ProcID] as .[ProcID]=[RptDBExtract].[dbo].[FEEDTL].[FDPROC] as [F].[FDPROC]))
| | | | | |--Nested Loops(Inner Join, WHERE: (([Fac].[dbo].[Fac].[FacID] as [C].[FacID]=[RptDB].[dbo].[Inclusion].[FacID] as .[FacID] OR [RptDB].[dbo].[Inclusion].[FacID] as .[FacID] IS NULL) AND ([Expr1077]=[RptDB].[dbo].[Inclusion].[State] as .[State] OR [RptDB].[dbo].[Inclusion].[State] as .[State] IS NULL) AND ([Fac].[dbo].[Region].[RegionID] as [R].[RegionID]=[RptDB].[dbo].[Inclusion].[RegionID] as .[RegionID] OR [RptDB].[dbo].[Inclusion].[RegionID] as .[RegionID] IS NULL)))
| | | | | | |--Nested Loops(Inner Join, WHERE: ([Fac].[dbo].[RegionMarket].[RegionID] as [RM].[RegionID]=[Fac].[dbo].[Region].[RegionID] as [R].[RegionID]))
| | | | | | | |--Nested Loops(Inner Join, WHERE: ([Fac].[dbo].[MarketFac].[MarketID] as [MC].[MarketID]=[Fac].[dbo].[RegionMarket].[MarketID] as [RM].[MarketID]))
| | | | | | | | |--Sort(ORDER BY: ([C].[FacID] ASC))
| | | | | | | | | |--Hash Match(Inner Join, HASH: ([C].[FacID])=([MC].[FacID]))
| | | | | | | | | |--Filter(WHERE: ([BC].[CurrentRecord]=(1)))
| | | | | | | | | | |--Compute Scalar(DEFINE: ([BC].[CurrentRecord]=[Fac].[dbo].[udf_CurrentBrandFac]([Fac].[dbo].[FacAddress].[FacID] as [CA].[FacID],[Expr1078])))
| | | | | | | | | | |--Hash Match(Inner Join, HASH: ([C].[Fac])=([FM].[FMFAC@]), RESIDUAL: ([Fac].[dbo].[Fac].[Fac] as [C].[Fac]=[RptDBExtract].[dbo].[FACMAS].[FMFAC@] as [FM].[FMFAC@]))
| | | | | | | | | | |--Merge Join(Inner Join, MERGE: ([LE].[FacID])=([CA].[FacID]), RESIDUAL: ([Fac].[dbo].[FacAddress].[FacID] as [CA].[FacID]=[Fac].[dbo].[LegalEntities].[FacID] as [LE].[FacID]))
| | | | | | | | | | | |--Clustered Index Scan(OBJECT: ([Fac].[dbo].[LegalEntities].[PK_LegalEntities] AS [LE]), ORDERED FORWARD)
| | | | | | | | | | | |--Merge Join(Inner Join, MERGE: ([C].[FacID])=([CA].[FacID]), RESIDUAL: ([Fac].[dbo].[Fac].[FacID] as [C].[FacID]=[Fac].[dbo].[FacAddress].[FacID] as [CA].[FacID]))
| | | | | | | | | | | |--Clustered Index Scan(OBJECT: ([Fac].[dbo].[Fac].[PK_FacName] AS [C]), ORDERED FORWARD)
| | | | | | | | | | | |--Sort(ORDER BY: ([CA].[FacID] ASC))
| | | | | | | | | | | |--Hash Match(Inner Join, HASH: ([BC].[FacID])=([CA].[FacID]))
| | | | | | | | | | | |--Compute Scalar(DEFINE: ([Expr1078]=CONVERT_IMPLICIT(datetime,[Fac].[dbo].[BrandFac].[StartDate] as [BC].[StartDate],0)))
| | | | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES: (.[BrandID]))
| | | | | | | | | | | | |--Clustered Index Scan(OBJECT: ([Fac].[dbo].[Brand].[PK_Brand] AS ))
| | | | | | | | | | | | |--Clustered Index Seek(OBJECT: ([Fac].[dbo].[BrandFac].[PK_BrandFac] AS [BC]), SEEK: ([BC].[BrandID]=[Fac].[dbo].[Brand].[BrandID] as .[BrandID]) ORDERED FORWARD)
| | | | | | | | | | | |--Compute Scalar(DEFINE: ([Expr1077]=upper([Fac].[dbo].[FacAddress].[State] as [CA].[State])))
| | | | | | | | | | | |--Table Scan(OBJECT: ([Fac].[dbo].[FacAddress] AS [CA]))
| | | | | | | | | | |--Table Scan(OBJECT: ([RptDBExtract].[dbo].[FACMAS] AS [FM]))
| | | | | | | | | |--Clustered Index Scan(OBJECT: ([Fac].[dbo].[MarketFac].[PK_MarketFac] AS [MC]))
| | | | | | | | |--Clustered Index Scan(OBJECT: ([Fac].[dbo].[RegionMarket].[PK_RegionMarket] AS [RM]))
| | | | | | | |--Table Scan(OBJECT: ([Fac].[dbo].[Region] AS [R]))
| | | | | | |--Table Scan(OBJECT: ([RptDB].[dbo].[Inclusion] AS ))
| | | | | |--Table Scan(OBJECT: ([RptDBExtract].[dbo].[FEEDTL] AS [F]))
| | | | |--Table Scan(OBJECT: ([Fac].[dbo].[Market] AS [M]))
| | | |--Remote Scan(SOURCE: (DB2Server), OBJECT: (SELECT DISTINCT FNFAC@, FNFEE@ FROM DB2SCHEMA.FP WHERE FNPRV@ = '***' AND FNFAC@ BETWEEN '001' AND '999'))
| | |--Table Scan(OBJECT: ([RptDB].[dbo].[Labels] AS [L]))
| |--Table Scan(OBJECT: ([Fac].[dbo].[FacIOC] AS [CI]))
|--Table Scan(OBJECT: ([RptDB].[dbo].[Exclusion] AS [E]))
|--Compute Scalar(DEFINE: ([Expr1008]=[Expr1008]))
|--Nested Loops(Left Outer Join)
|--Constant Scan
|--Assert(WHERE: (CASE WHEN [Expr1007]>(1) THEN (0) ELSE NULL END))
|--Stream Aggregate(DEFINE: ([Expr1007]=Count(*), [Expr1008]=ANY(CASE WHEN [Fac].[dbo].[BrandFac].[FacID]=[@ID] AND [Expr1003]=[@Date] THEN (1) ELSE (0) END)))
|--Stream Aggregate(DEFINE: ([Expr1003]=MAX([Fac].[dbo].[BrandFac].[StartDate]), [Fac].[dbo].[BrandFac].[FacID]=ANY([Fac].[dbo].[BrandFac].[FacID])))
|--Clustered Index Scan(OBJECT: ([Fac].[dbo].[BrandFac].[PK_BrandFac]), WHERE: ([Fac].[dbo].[BrandFac].[FacID]=[@ID] AND [Fac].[dbo].[BrandFac].[StartDate]<=getdate()))
July 23, 2008 at 7:42 am
It would be easier to read your execution plans if you saved them as .sqlplan and attached the files to your post. Then we could open them in SSMS and see the graphical representation.
I would recommend still using a temp table for the OPENQUERY results on the DB2 table and then using single query for the joining. My guess is the join on the OPENQUERY results is killing your performance. I always avoid joining across linked servers or OpenQuery/Rowset connections as it is always less efficient than getting the data and then joining.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 23, 2008 at 8:00 am
Jack,
Thanks for the quick reply.
Sorry about not including the .sqlplan version, I was hoping that the text would be enough. It took me a while to rename the tables and databases in each of these files (HIPPA and all I would rather be extra cautious) but I went ahead and did just that and am now including them.
As for the OpenQuery, I would have thought that that might be the problem, but if I look at a subset of the tables, including the OpenQuery, it does not seem to effect the query that much. I have tried joining different ways, with and without different sub-queries, joining the tables in different combinations, but only when all of the tables are included does the performance drop through the floor. AND, if I create a tempt table for the OpenQuery statement only, performance is still atrocious.
Thanks again for the input!
July 23, 2008 at 8:01 am
It looks to me like you've got a reasonably efficient proc there, so I have to ask, why do you want to change it to a view, instead of keeping the proc?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 23, 2008 at 8:11 am
GSquared,
First, I don't have a proc... I created the sql while trying to troubleshoot the issue with the query. I am not trying to change an existing proc but replace a table that is rebuilt every night. Second, while I will most likely end up creating and using a stored procedure for this specific app, I would still like to know WHY this query takes so long; partly because I can use this knowledge to improve other SQL statements, but mostly because I don't like not knowing why. Also, I find views are much easier for people who are new to T-SQL to use in generating SQL for reports, and most of our report writers are fairly new to SQL on SQL Server. I like to try and keep any solution or piece of code that I put in place as robust and scalable as possible! 😀
Thank you for the feedback!
July 23, 2008 at 8:45 am
If currently the data from DB2 is being loaded nightly, why is there a need to hit it dynamically now?
Also your biggest hit as a percentage of the query plan in both cases is the Table Scan on FEEDTL. I would put an index on FDFEE on this table. This should greatly improve performance.
I also wonder why you are doing all the A.ID = B.ID or B.ID is null. You could just do LEFT OUTER JOIN's where A.ID = B.ID and this may eliminate some table scans as well.
As I said before I am not a big fan of joining across linked servers as this will cause hit on performance. I would do it in a proc and put the openquery data in a temp table and then join on the temp table. This allows SQL Server to better manage the query. If your report writers can't reference a proc then they need to learn. In SSRS it is simple enough and I assume Crystal would be similar.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 23, 2008 at 9:37 am
I agree with Jack. It's been my experience that you're better off loading external data into a temp table and then processing it with everything else. I also agree that you should eliminate the generation of the second temp table and simply use that query as part of the join or make it a derived table and then join it. Either way, you eliminate a major piece of your process there. I also agree that making the joins into OUTER joins should help. Finally, so that I don't just post an "I agree" message, you might want to consider adding an index to the temporary table after you get it loaded. I'd put the index on FNFAC@ so that the join is effecient. You'll want to test it with & without this because the cost of adding the index might outweigh the cost of doing a HASH join to return the data.
"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
July 23, 2008 at 10:23 am
If I join the #Tmp2 table sql directly to #Tmp1 then I am back to poor performance... + 1 hour instead of < 1 second. I understand that an index will help improve performance, and that it is bad practice to query across multiple servers, but why would my base query work fine, even joining the openquery, when done with all but one or two of the tables (and it doesn't seem to matter which one) but bomb when I use all of the tables?
July 23, 2008 at 10:30 am
to answer that, look at the execution plan.
It really depends on which tables you add, but adding anything can make the optimizer choose different options in its attempts at making the query perform.
You've got table scans going on in the queries I saw. Added to that, hash joins, good for large data sets, but not terribly efficient over all. Welcome to tuning. It's hard work and there's no quick and easy solution most of the time.
"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
July 23, 2008 at 10:48 am
I have begun playing around with tuning... my understanding of the execution plan is a little limited, but I was hoping that an elapsed time difference by a factor of over 3600 would be caused by 1) an error in SQL Server or 2) (and the more likely) an "error" or bad practice in my SQL.
Thanks everyone for your feedback! I have a solution in place, but I will continue to play around with this query, as a learning tool for myself if for no other reason... it sounds like I have some more learning to do! 🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply