SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query of 100 mil rows with multiple parameters


Query of 100 mil rows with multiple parameters

Author
Message
MarkHK
MarkHK
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 30
Hello,

I have a challange with a table with around 100 million rows which look like:

CREATE TABLE [dbo].[BRFCLSeaWeekHis](
[Year] [smallint] NOT NULL,
[Week] [smallint] NOT NULL,
[PortFrom] [char](5) NOT NULL,
[PortTo] [char](5) NOT NULL,
[CarrierID] [char](4) NOT NULL,
[ConType] [varchar](4) NOT NULL,
[ConSize] [smallint] NOT NULL,
[Commodity] [nvarchar](30) NOT NULL,
[IncotermCode] [varchar](4) NOT NULL,
[QuoteType] [varchar](2) NOT NULL,
[CurrencyCode] [char](3) NOT NULL,
[RateTotal] [decimal](18, 2) NOT NULL,
[SeaTotal] [decimal](18, 2) NOT NULL,
[Total] [decimal](18, 2) NOT NULL,
CONSTRAINT [PK_BRFCLSeaWeekHis] PRIMARY KEY CLUSTERED
(
[Year] ASC,
[Week] ASC,
[PortFrom] ASC,
[PortTo] ASC,
[CarrierID] ASC,
[ConType] ASC,
[ConSize] ASC,
[Commodity] ASC,
[IncotermCode] ASC,
[QuoteType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_BRFCLSeaWeekHis_QuoteType] ON [dbo].[BRFCLSeaWeekHis]
(
[QuoteType] ASC
)
INCLUDE ( [Year],
[Week],
[PortFrom],
[PortTo],
[CarrierID],
[ConType],
[ConSize],
[Commodity],
[IncotermCode],
[CurrencyCode],
[RateTotal],
[SeaTotal],
[Total]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]



The SP I run looks more or less like this, and takes aprox. 50 secs



CREATE PROCEDURE [dbo].[spSchBuyingRatesHistoryWeek]
(
@TradeLanes varchar(6000),
@Containers varchar(4000),
@QuoteType nvarchar(2),
@Incoterms nvarchar(500),
@CurrencyCode char(3),
@DateFrom smalldatetime,
@DateTo smalldatetime,
@Commodities nvarchar(4000),
@Carriers varchar(4000),
@IncludeCheapest bit = 1,
@IncludeMostExpensive bit = 1,
@OrderBy varchar(10) = 'SeaTotal',
@IsIndicatorsCommodityDependant bit = 0
)
AS
BEGIN
SET NOCOUNT ON

-- SET @TradeLanes = '<ROOT><TradeLane PortFrom="CNSHA" PortTo="DKCPH" /></ROOT>'
----SET @Containers = '<ROOT><Container ConType="DV" ConSize="20" /><Container ConType="DV" ConSize="40" /><Container ConType="HC" ConSize="40" /></ROOT>'
--SET @Containers = '<ROOT><Container ConType="DV" ConSize="20" /></ROOT>'
--SET @QuoteType = 'I'
--SET @Incoterms = '<ROOT><Incoterm Code="FOB" /></ROOT>'
--SET @CurrencyCode = 'USD'
--SET @DateFrom = '2010-01-01'
--SET @DateTo = '2012-12-31'
--SET @Commodities = '<ROOT><Commodity Name="FAK" /></ROOT>'
--SET @Carriers = '<ROOT><Carrier Code="KKLU" /></ROOT>'
--SET @IncludeCheapest = 1
--SET @IncludeMostExpensive = 1
--SET @OrderBy = 'SeaTotal'

DECLARE @tradeLanesHandle int
DECLARE @containerHandle int
DECLARE @carrierHandle int
DECLARE @commodityHandle int
DECLARE @incotermsHandle int

--Iniciate the xml handlers
EXEC sp_xml_preparedocument @tradeLanesHandle OUTPUT, @TradeLanes
EXEC sp_xml_preparedocument @containerHandle OUTPUT, @Containers
EXEC sp_xml_preparedocument @carrierHandle OUTPUT, @Carriers
EXEC sp_xml_preparedocument @commodityHandle OUTPUT, @Commodities
EXEC sp_xml_preparedocument @incotermsHandle OUTPUT, @Incoterms

SELECT PortFrom, PortTo INTO #TradeLanes FROM OPENXML(@tradeLanesHandle, '/ROOT/TradeLane') WITH (PortFrom nchar(5),PortTo nchar(5)) AS tradelanes;
CREATE UNIQUE CLUSTERED INDEX IX_2 on #TradeLanes (PortFrom, PortTo);

SELECT coms.Name INTO #Commodities FROM OPENXML(@commodityHandle, '/ROOT/Commodity') WITH ([Name] nvarchar(30)) AS coms;
CREATE UNIQUE CLUSTERED INDEX IX_4 on #Commodities ([Name]);

SELECT cars.Code INTO #Carriers FROM OPENXML(@carrierHandle, '/ROOT/Carrier') WITH (Code nchar(4)) AS cars;
CREATE UNIQUE CLUSTERED INDEX IX_5 on #Carriers (Code);

SELECT ConType, consize INTO #Cons FROM OPENXML (@containerHandle, '/ROOT/Container') WITH (ConType nvarchar(4), ConSize smallint) AS cons;
CREATE UNIQUE CLUSTERED INDEX IX_6 on #Cons (ConType, consize);

SELECT Code INTO #Incoterms FROM OPENXML (@incotermsHandle, '/ROOT/Incoterm') WITH (Code nvarchar(4)) AS cons;
CREATE UNIQUE CLUSTERED INDEX IX_7 on #Incoterms (Code);

DECLARE @WeekFrom int,@WeekTo int, @YearFrom int, @YearTo int;
SET @WeekFrom = dbo.sf_IsoWeekOfYear(@DateFrom);
SET @WeekTo = dbo.sf_IsoWeekOfYear(@DateTo);
SET @YearFrom = DATEPART(yy, @DateFrom);
SET @YearTo = DATEPART(yy, @DateTo);

SELECT dbo.sf_WeekdayFromIsoWeekNumber([Year], [Week],7) AS [Day],
[Year], [Week], FCL.PortFrom, FCL.PortTo, FCL.CarrierID, FCL.ConType, FCL.ConSize, FCL.Commodity, FCL.IncotermCode, FCL.QuoteType, FCL.CurrencyCode, RateTotal, SeaTotal, Total
FROM
BRFCLSeaWeekHis AS FCL
INNER JOIN #cons c ON fcl.ConType = c.ConType AND fcl.ConSize = c.ConSize
INNER JOIN #TradeLanes as tl on tl.PortFrom = FCL.portFrom AND tl.PortTo = FCL.portTo
INNER JOIN #Incoterms as i ON i.Code = FCL.IncotermCode
WHERE QuoteType = @QuoteType AND
(CASE WHEN FCL.Year > @YearFrom THEN 1 WHEN (FCL.Week >= @WeekFrom AND FCL.Year = @YearFrom) THEN 1 ELSE 0 END) = 1
AND (CASE WHEN FCL.Year < @YearTo THEN 1 WHEN (FCL.Week <= @WeekTo AND FCL.Year = @YearTo) THEN 1 ELSE 0 END) = 1
AND (CASE WHEN @Commodities IS NULL THEN 1 WHEN Commodity IN (SELECT Name FROM #Commodities) THEN 1 ELSE 0 END) = 1
AND (CASE WHEN @Carriers IS NULL THEN 1 WHEN CarrierID IN (SELECT Code FROM #Carriers) THEN 1 ELSE 0 END) = 1

--Remove tmp tables
IF OBJECT_ID('tempdb..#Tradelanes') IS NOT NULL
DROP TABLE #Tradelanes
IF OBJECT_ID('tempdb..#Commodities') IS NOT NULL
DROP TABLE #Commodities;
IF OBJECT_ID('tempdb..#Carriers') IS NOT NULL
DROP TABLE #Carriers;
IF OBJECT_ID('tempdb..#cons') IS NOT NULL
DROP TABLE #cons;
IF OBJECT_ID('tempdb..#Incoterms') IS NOT NULL
DROP TABLE #Incoterms

--Remove handles
EXEC sp_xml_removedocument @tradeLanesHandle
EXEC sp_xml_removedocument @commodityHandle
EXEC sp_xml_removedocument @carrierHandle
EXEC sp_xml_removedocument @containerHandle
EXEC sp_xml_removedocument @incotermsHandle
END



I attached the xml of the execution plan. Any ideas on how I could optimize this?
Attachments
Execution plan.sqlplan (11 views, 361.00 KB)
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41749 Visits: 20006
Hi Mark

Two or three points to consider.
Firstly this is a "Catch-all query". In a nutshell, the number of rows returned could vary dramatically depending upon the parameters passed in. You'll have a minimum number of rows of n and a maximum of about 25 million. No single execution plan could be optimal for both. Gail Shaw has an excellent article here describing how you can deal with this type of query.
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.
Thirdly, and this point follows on from 2: since every nonclustered index contains the cluster keys, it might be advantageous for you to use a single surrogate key instead, such as an identity column.
SSC has an excellent indexing article by David Durant here.

“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
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41749 Visits: 20006
For a quick win, change your non-clustered index like so:

CREATE NONCLUSTERED INDEX [IX_BRFCLSeaWeekHis_QuoteType] ON [dbo].[BRFCLSeaWeekHis]
(
[QuoteType] ASC, Year
)
INCLUDE (
[CurrencyCode],
[RateTotal],
[SeaTotal],
[Total])



and your query like so:
WHERE QuoteType = @QuoteType 
AND FCL.Year >= @YearFrom
AND FCL.Year <= @YearTo
AND (CASE WHEN FCL.Year > @YearFrom THEN 1
WHEN (FCL.Week >= @WeekFrom AND FCL.Year = @YearFrom) THEN 1
ELSE 0 END) = 1
AND (CASE WHEN FCL.Year < @YearTo THEN 1 WHEN (FCL.Week <= @WeekTo AND FCL.Year = @YearTo) THEN 1 ELSE 0 END) = 1
AND (CASE WHEN @Commodities IS NULL THEN 1 WHEN FCL.Commodity IN (SELECT Name FROM #Commodities) THEN 1 ELSE 0 END) = 1
AND (CASE WHEN @Carriers IS NULL THEN 1 WHEN FCL.CarrierID IN (SELECT Code FROM #Carriers) THEN 1 ELSE 0 END) = 1


with OPTION RECOMPILE.

“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
MarkHK
MarkHK
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 30
Hi Chris,

Thanks for the leads, but even with recompile and changed index, the time is the same.

In relation to your first post, then I will not likely get anymore than a few 1000 rows as result (maybe 10t-20t), as it will not include more than 5 trade lanes and there are around 250.000 different tradelanes in the table. Does that make it easier to optimize for that specific purpose?

Furthermore, where can I see if the CPU or the disk (or something third) is the bottleneck?
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41749 Visits: 20006
mark.kristensen (2/28/2014)
Hi Chris,

Thanks for the leads, but even with recompile and changed index, the time is the same.

In relation to your first post, then I will not likely get anymore than a few 1000 rows as result (maybe 10t-20t), as it will not include more than 5 trade lanes and there are around 250.000 different tradelanes in the table. Does that make it easier to optimize for that specific purpose?

Furthermore, where can I see if the CPU or the disk (or something third) is the bottleneck?


Thanks for the feedback, Mark. Can you post the actual execution plan please?
Efficient filtering for those tradelanes is the first thing I'd pursue. You currently have way too many rows coming off the big table. That stacks up as logical reads if the data is in cache, physical reads if not.

“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
MarkHK
MarkHK
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 30
I have attached a file to the first post - is that what you need or if not, then please tell me how to get it Smile
MarkHK
MarkHK
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 30
I have this from the stats

Table '#Result_000000050139'. 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_00000005013C'. 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_00000005013B'. Scan count 0, logical reads 1066, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Cons_00000005013D'. Scan count 0, logical reads 3366, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Incoterms_00000005013E'. Scan count 0, logical reads 13464, 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.
Table 'BRFCLSeaWeekHis'. Scan count 1, logical reads 806044, physical reads 0, read-ahead reads 800135, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#TradeLanes_00000005013A'. 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 = 36020 ms, elapsed time = 77212 ms.
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41749 Visits: 20006
mark.kristensen (2/28/2014)
I have attached a file to the first post - is that what you need or if not, then please tell me how to get it Smile


Execution plan with the mods I suggested?

“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
MarkHK
MarkHK
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 30
Ah - here it is
Attachments
Execution plan.sqlplan (12 views, 377.00 KB)
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41749 Visits: 20006
Thanks.

Try placing the nonclustered index keys the other way around:

CREATE NONCLUSTERED INDEX [IX_BRFCLSeaWeekHis_QuoteType] ON [dbo].[BRFCLSeaWeekHis]
(
Year, [QuoteType] ASC
)
INCLUDE (
[CurrencyCode],
[RateTotal],
[SeaTotal],
[Total])

“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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search