RE: Query of 100 mil rows with multiple parameters

SSC Guru

Points: 186127

March 4, 2014 at 9:42 am

Once more with details:

[Expr1031] = Scalar Operator(CONVERT_IMPLICIT(int,[FlexQuotes].[dbo].[BRFCLSeaWeekHis].[Year] as [FCL].[Year],0)),

[Expr1032] = Scalar Operator(CONVERT_IMPLICIT(int,[FlexQuotes].[dbo].[BRFCLSeaWeekHis].[Week] as [FCL].[Week],0)),

[Expr1033] = Scalar Operator(CONVERT_IMPLICIT(nchar(3),[FlexQuotes].[dbo].[BRFCLSeaWeekHis].[CurrencyCode] as [FCL].[CurrencyCode],0)),

[Expr1034] = Scalar Operator(CONVERT_IMPLICIT(decimal(18,5),[FlexQuotes].[dbo].[BRFCLSeaWeekHis].[RateTotal] as [FCL].[RateTotal],0)),

[Expr1035] = Scalar Operator(CONVERT_IMPLICIT(decimal(18,5),[FlexQuotes].[dbo].[BRFCLSeaWeekHis].[SeaTotal] as [FCL].[SeaTotal],0)),

[Expr1036] = Scalar Operator(CONVERT_IMPLICIT(decimal(18,5),[FlexQuotes].[dbo].[BRFCLSeaWeekHis].[Total] as [FCL].[Total],0)),

[Expr1037] = Scalar Operator(CONVERT_IMPLICIT(nvarchar(4),[FlexQuotes].[dbo].[BRFCLSeaWeekHis].[IncotermCode] as [FCL].[IncotermCode],0)),

[Expr1038] = Scalar Operator(CONVERT_IMPLICIT(nvarchar(4),[FlexQuotes].[dbo].[BRFCLSeaWeekHis].[ConType] as [FCL].[ConType],0))

Warnings: Type conversion in expression (CONVERT_IMPLICIT(nvarchar(4),[FCL].[ConType],0)) may affect "CardinalityEstimate" in query plan choice, Type conversion in expression (CONVERT_IMPLICIT(nvarchar(4),[FCL].[IncotermCode],0)) may affect "CardinalityEstimate" in query plan choice

Set the datatype of your variables and temp table columns to be the same as the columns they are matching in BRFCLSeaWeekHis.

Then post the actual execution plan.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden