Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Query of 100 mil rows with multiple parameters Expand / Collapse
Author
Message
Posted Sunday, March 2, 2014 3:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 7, 2014 6:07 AM
Points: 8, Visits: 30
Thanks for the suggestion, but pretty much the same result

Are there any other tools I can use to dig deeper?

Table '#Result____00000005022E'. Scan count 0, logical reads 51, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Carriers______00000005022B'. Scan count 0, logical reads 170, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Commodities____00000005022A'. Scan count 0, logical reads 1066, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Cons____00000005022C'. Scan count 0, logical reads 3366, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Incoterms____00000005022D'. Scan count 0, logical reads 13464, physical reads 1, 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.
Table 'BRFCLSeaWeekHis'. Scan count 1, logical reads 778057, physical reads 1, read-ahead reads 775951, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#TradeLanes____000000050229'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 36410 ms, elapsed time = 73626 ms.


  Post Attachments 
Execution plan.sqlplan (2 views, 377.27 KB)
Post #1546740
Posted Sunday, March 2, 2014 6:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:38 PM
Points: 35,371, Visits: 31,912
I guess my first recommendation would be to make the PK a nonclustered index. It's currently way too wide to be of any practical use as a clustered index and will cause BTree of the index on quotetype column to bloat quite badly (remember that all the columns of a clustered index are added to all nonclustered indexes) making it read many more pages per lookup than it should need to. I would put a new clustered index on year and week because that seems to be predominate in all of your queries.



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1546753
Posted Monday, March 3, 2014 1:57 AM This worked for the OP Answer marked as solution


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 6,813, Visits: 14,028
A significant number of implicit conversions are interfering with optimisation of this query. Here's a few recommendations to start with:
Cast @YearFrom and @YearTo to same datatype as BRFCLSeaWeekHis.Year
Cast @QuoteType to same datatype as BRFCLSeaWeekHis.QuoteType
Temp table #TradeLanes: cast PortFrom and PortTo to same datatype as BRFCLSeaWeekHis.PortFrom and BRFCLSeaWeekHis.PortTo
Temp table #Incoterms: cast Code to same datatype as BRFCLSeaWeekHis.IncotermCode

Get these done, run the query again and post the 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
Exploring Recursive CTEs by Example Dwain Camps
Post #1546818
Posted Tuesday, March 4, 2014 9:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 7, 2014 6:07 AM
Points: 8, Visits: 30
Ok, now I think we are getting close. Down to 47 secs, but now it also suggests a new index:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[BRFCLSeaWeekHis] ([PortFrom],[PortTo],[QuoteType],[Year])
INCLUDE ([Week],[CarrierID],[ConType],[ConSize],[Commodity],[IncotermCode],[CurrencyCode],[RateTotal],[SeaTotal],[Total])

@Jeff, thanks for the suggestion. I was not aware that all columns got copied down.


  Post Attachments 
Execution plan.sqlplan (3 views, 340.91 KB)
Post #1547404
Posted Tuesday, March 4, 2014 9:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 6,813, Visits: 14,028
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1547413
Posted Tuesday, March 4, 2014 10:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 7, 2014 6:07 AM
Points: 8, Visits: 30
Wow - now I am down to 1 sec. after I added the index:

CREATE NONCLUSTERED INDEX [IX_BRWEEKHIS_TEST]
ON [dbo].[BRFCLSeaWeekHis] ([PortFrom],[PortTo],[QuoteType],[Year])
INCLUDE ([CurrencyCode],[RateTotal],[SeaTotal],[Total])

However, the last two conversions I don't understand, because they seem correct when declared into the temp table. I will dig a bit more into that, but now it is really great! Thank you very much!


  Post Attachments 
Execution plan.sqlplan (0 views, 330.95 KB)
Post #1547436
Posted Tuesday, March 4, 2014 10:27 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:19 AM
Points: 40,208, Visits: 36,617
ChrisM@Work (2/28/2014)
Secondly, your ordinary index has most of the columns of the clustered index as INCLUDE columns. This is unnecessary because nonclustered indexes contain the cluster keys - they're already included behind the scenes.


Don't want to derail, but I disagree with this recommendation. What happens if someone changes the clustered index.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1547438
Posted Tuesday, March 4, 2014 11:08 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:19 AM
Points: 1,034, Visits: 6,811
GilaMonster (3/4/2014)
ChrisM@Work (2/28/2014)
Secondly, your ordinary index has most of the columns of the clustered index as INCLUDE columns. This is unnecessary because nonclustered indexes contain the cluster keys - they're already included behind the scenes.


Don't want to derail, but I disagree with this recommendation. What happens if someone changes the clustered index.


Since the OP is satisfied for the time being, derailing is moot. I don't recall ever seeing this discussed on ssc so I guess now is a good time.
If someone changes the clustered index and removes one of those columns, then the plan changes (for MarkHK's query) and a lookup drags the time down again. However:
1. How often do you change a clustered index on a production db?
2. If you did, wouldn't you be on high alert for performance dips caused by plan changes?
3. With a clustered index having this many keys, would you switch to an identity column? I would, provided that analysis of index usage patterns showed the clustered index isn't particularly useful to existing queries.
I'll stand by the suggestion



Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




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
Post #1547455
Posted Tuesday, March 4, 2014 11:11 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:19 AM
Points: 1,034, Visits: 6,811
Jeff Moden (3/2/2014)
I guess my first recommendation would be to make the PK a nonclustered index. It's currently way too wide to be of any practical use as a clustered index and will cause BTree of the index on quotetype column to bloat quite badly (remember that all the columns of a clustered index are added to all nonclustered indexes) making it read many more pages per lookup than it should need to. I would put a new clustered index on year and week because that seems to be predominate in all of your queries.



Yes - but I'd try PortFrom and PortTo as cluster keys because they may be more selective and also more common as leading edge in other indexes - promoting MERGE or NLIJ's over hash joins.



Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




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
Post #1547458
Posted Tuesday, March 4, 2014 11:36 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:19 AM
Points: 40,208, Visits: 36,617
ChrisM@home (3/4/2014)
1. How often do you change a clustered index on a production db?


I do fairly often, but then that's what I do mostly (database tuning on various badly performing systems for clients)

2. If you did, wouldn't you be on high alert for performance dips caused by plan changes?


Yes, but which is better, be on alert for plan changes due to indexes no longer being covering, or knowing that my indexes will still be covering no matter what happens to the clustered index? Given that there's no overhead from the second option, I'll take that any day, especially as I may not be the one changing the clustered index, could be a consultant that the client gets in months after I last looked at their system.

3. With a clustered index having this many keys, would you switch to an identity column?


Maybe. Maybe not. Depends on a lot of factors.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1547464
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse