Query optimizing 2

Question

Query optimizing 2

Viewing 9 posts - 31 through 39 (of 39 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

Do you have the code that built the 20M*30*100 data for both tables so we can test at the level you did?
Did you do a comparison of the original code to the code to the Indexed View/Dynamic method? If so, can you show us the results as the differences in duration and resource usage?

Jeff...will try and get back to you soon....seems my desktop cant take the strain anymore :-P...maybe I'll have to rdp onto a work dev server

appreciate your interest......out of curiosity...did you run my code...what results?

please bear with me

regards

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 2

Jeff....code below.

if not clear, pls post back

use tempdb

GO

--===== Conditionally drop the tables

IF Object_id('vw_PreAg', 'V') IS NOT NULL

DROP VIEW vw_PreAg ;

IF Object_id('vw_ListCols', 'V') IS NOT NULL

DROP VIEW vw_ListCols ;

IF OBJECT_ID('CountriesInvolved', 'U') IS NOT NULL

DROP TABLE dbo.CountriesInvolved ;

IF OBJECT_ID('Sales_City', 'U') IS NOT NULL

DROP TABLE dbo.Sales_City ;

SELECT TOP 20000000 ---- NOTE 20 MILLION ROWS

Sold_To_Country = 'Country' + CAST(ABS(CHECKSUM(NEWID())) % 30 + 1 as varchar(2)),

ToCity = 'City' + CAST(ABS(CHECKSUM(NEWID())) % 100 + 1 as varchar(3)),

SalesAmount = CAST(RAND(CHECKSUM(NEWID())) * 9 + 1 as decimal(5, 2))

into Sales_city

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

CROSS JOIN sys.all_columns ac3

---=== this represents a subset of data required by OP for countrys that require pivot data

CREATE TABLE [dbo].[CountriesInvolved](

[Sold_To_Country] [nvarchar](100) NOT NULL

) ON [PRIMARY]

GO

INSERT [dbo].[CountriesInvolved] ([Sold_To_Country]) VALUES (N'Country12')

INSERT [dbo].[CountriesInvolved] ([Sold_To_Country]) VALUES (N'Country21')

INSERT [dbo].[CountriesInvolved] ([Sold_To_Country]) VALUES (N'Country24')

INSERT [dbo].[CountriesInvolved] ([Sold_To_Country]) VALUES (N'Country5')

INSERT [dbo].[CountriesInvolved] ([Sold_To_Country]) VALUES (N'Country7')

CREATE CLUSTERED INDEX [CI_STC] ON [dbo].[CountriesInvolved] ( [Sold_To_Country] ASC )

-- WITH (

-- PAD_INDEX = OFF,

-- STATISTICS_NORECOMPUTE = OFF,

-- SORT_IN_TEMPDB = OFF,

-- IGNORE_DUP_KEY = OFF,

-- DROP_EXISTING = OFF,

-- ONLINE = OFF,

-- ALLOW_ROW_LOCKS = ON,

-- ALLOW_PAGE_LOCKS = ON)

ON [PRIMARY]

GO

CREATE VIEW [dbo].[VW_ListCols]

WITH SCHEMABINDING

AS

SELECT dbo.Sales_City.ToCity AS SC,

Count_big(*) AS Cnt

FROM dbo.CountriesInvolved

INNER JOIN dbo.Sales_City

ON dbo.CountriesInvolved.Sold_To_Country = dbo.Sales_City.Sold_To_Country

GROUP BY dbo.Sales_City.ToCity

GO

--===== Create a unique clustered index on vw_ListCols

SET ARITHABORT ON

GO

SET CONCAT_NULL_YIELDS_NULL ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

SET ANSI_PADDING ON

GO

SET ANSI_WARNINGS ON

GO

SET NUMERIC_ROUNDABORT OFF

GO

CREATE UNIQUE CLUSTERED INDEX [CI_IX_ListCols] ON [dbo].[VW_ListCols] ( [SC] ASC )

--WITH (

-- PAD_INDEX = OFF,

-- STATISTICS_NORECOMPUTE = OFF,

-- SORT_IN_TEMPDB = OFF,

-- IGNORE_DUP_KEY = OFF,

-- DROP_EXISTING = OFF,

-- ONLINE = OFF,

-- ALLOW_ROW_LOCKS = ON,

-- ALLOW_PAGE_LOCKS = ON)

ON [PRIMARY]

GO

--===== Create an indexed view that efficiently pre aggregates the data

CREATE VIEW [dbo].[vw_PreAg]

WITH SCHEMABINDING

AS

SELECT dbo.sales_city.Sold_To_Country,

dbo.sales_city.ToCity,

SUM(Isnull(dbo.sales_city.SalesAmount, 0)) AS SumAmt,

Count_big(*) AS cnt

FROM dbo.Sales_city

GROUP BY dbo.sales_city.Sold_To_Country,

dbo.sales_city.ToCity

GO

--===== Create a unique clustered index on vw_PreAg

SET ARITHABORT ON

GO

SET CONCAT_NULL_YIELDS_NULL ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

SET ANSI_PADDING ON

GO

SET ANSI_WARNINGS ON

GO

SET NUMERIC_ROUNDABORT OFF

GO

CREATE UNIQUE CLUSTERED INDEX [CI_iX_preag] ON [dbo].[vw_PreAg] ( [Sold_To_Country] ASC, [ToCity] ASC )

--WITH (

-- PAD_INDEX = OFF,

-- STATISTICS_NORECOMPUTE = OFF,

-- SORT_IN_TEMPDB = OFF,

-- IGNORE_DUP_KEY = OFF,

-- DROP_EXISTING = OFF,

-- ONLINE = OFF,

-- ALLOW_ROW_LOCKS = ON,

-- ALLOW_PAGE_LOCKS = ON)

ON [PRIMARY]

GO

--===== The 'Dynamic' pivot query

DBCC FREEPROCCACHE WITH NO_INFOMSGS

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS

GO

SET STATISTICS IO ON

SET STATISTICS TIME ON

DECLARE @listCol VARCHAR(MAX)

DECLARE @query VARCHAR(MAX)

---- following creates a dynamic string to be used in the PIVOT 'IN' clause.

SELECT @listCol = Stuff((SELECT DISTINCT '],[' + SC

FROM VW_ListCols WITH ( NOEXPAND ) --the NOEXPAND required to force index view usage on on non enterprise versions

ORDER BY '],[' + SC

FOR XML PATH('')), 1, 2, '') + ']'

--PRINT @ListCol ---uncomment to review

--===== the actual PIVOT code

SET @query =

'

SELECT *

FROM (SELECT vw_PreAg.Sold_To_Country,

vw_PreAg.ToCity,

SUM(SumAmt) AS Total

FROM vw_PreAg WITH (NOEXPAND)

INNER JOIN

CountriesInvolved ON vw_PreAg.Sold_To_Country = CountriesInvolved.Sold_To_Country

GROUP BY vw_PreAg.Sold_To_Country,

vw_PreAg.ToCity) AS src PIVOT ( SUM(total) FOR ToCity IN ( ' + @listCol + ' )

) AS pvt

ORDER BY pvt.Sold_to_Country

'

--PRINT @query ---uncomment to review

EXECUTE ( @query )

SET STATISTICS IO OFF

SET STATISTICS TIME OFF

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 3

J Livingston SQL (9/25/2011)
Do you have the code that built the 20M*30*100 data for both tables so we can test at the level you did?
Did you do a comparison of the original code to the code to the Indexed View/Dynamic method? If so, can you show us the results as the differences in duration and resource usage?
Jeff...will try and get back to you soon....seems my desktop cant take the strain anymore :-P...maybe I'll have to rdp onto a work dev server
appreciate your interest......out of curiosity...did you run my code...what results?
please bear with me
regards

Heh... no fair. I asked you for your results first. 😛

I've not run your code yet. I was waiting to see if you had code for your example before I was going to change my test code to pretty much the way you did yours. Well done, by the way.

I'm actually headed out of the house for a couple of hours. Hope I don't melt like a vampire 'cause it's been awhile. :hehe: I'll stage your code for testing later on tonight.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rokh SSC Journeyman Points: 90 More actions · Answer 4

I should be silent in the mean time. I'm nothing close to an expert. I have way too many newb questions. I have reasons for not being affraid of algorithms but it doesn't mean I'm good with SQL-features and database design in general.

So I'll be close but leave the sensible talking up to you guys.

Gr,

~R

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Rokh (9/22/2011)
No I didn't.
Like stated before, the inner join is not be removed. It represents a real life situation. I've only skinned it down for this forum. Call it respect 🙂
But the inner join should stay.
Offcourse, without the inner join things will speed up. However, the big picture is that I noticed a serious performance decrease using the case/when statements. As I'm not an expert I can imagine you guys have a better alternative. If not, then I'm still grateful for your persistance and help.
Gr,
~R

How many levels of chaining (nesting) does this query have? Here's what I mean by chaining:

WITH MyCTE AS (SELECT...FROM tables),

MyChainedCTE AS (SELECT ... FROM MyCTE)

Anything which performs a sort within the CTE chain - and that includes JOINed tables - can hammer performance.

^{“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

Rokh SSC Journeyman Points: 90 More actions · Answer 6

Rokh

SSC Journeyman

Points: 90

September 26, 2011 at 10:29 am

#1386650

Two 'chains'.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 7

Jeff Moden (9/25/2011)

J Livingston SQL (9/25/2011)
Heh... no fair. I asked you for your results first. 😛
I've not run your code yet. I was waiting to see if you had code for your example before I was going to change my test code to pretty much the way you did yours. Well done, by the way.

some further thoughts....still based on Jeffs original earlier posted test rig of 1M records....I think the results indicate a performance boost...but am keen for feedback please.

I have used an indexed view for the "pre aggregation" and am coming back with the following results (taken from IO and client statistics) using the slowest of Jeff's tests...the "basic cross tab"

I am running 2008R2 64bit Dev edition on a 2GB RAM desktop

--RESULTS from five consecutive run

--(3 row(s) affected)

--Table 'vw_PreAg'. Scan count 3, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--Table 'CountriesInvolved'. 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.

--Client processing time 272426232825.6000

--Total execution time 272526252926.4000

--Wait time on server replies010210.8000

for those of you who may wish to follow along here is the code (complete with set up)

--============================ SET UP BY J MODEN ==============================================

-- Create the test environment. Nothing in this section is a part of the solution.

--=============================================================================================================

--===== Identify a nice, safe place for these tests that everyone has.

USE tempdb

;

--===== Conditionally drop the tables to make reruns easier in SSMS.

IF OBJECT_ID('temdb..#CountryCity','U') IS NOT NULL DROP TABLE #CountryCity;

IF OBJECT_ID('dbo.CountriesInvolved','U') IS NOT NULL DROP TABLE dbo.CountriesInvolved;

IF Object_id('dbo.vw_PreAg', 'V') IS NOT NULL DROP VIEW dbo.vw_PreAg ; ---drop before table SalesCity

IF OBJECT_ID('dbo.Sales_City','U') IS NOT NULL DROP TABLE dbo.Sales_City;

--===== Create the given tables

CREATE TABLE dbo.CountriesInvolved

(

Sold_To_Country NVARCHAR(100) NOT NULL

)

;

CREATE TABLE dbo.Sales_City

(

Sold_To_Country NVARCHAR(100) NULL,

ToCity NVARCHAR(100) NULL,

SalesAmount FLOAT NULL,

[RowID] [int] IDENTITY(1,1) NOT NULL ---- JLS added..needed for deletes later on

)

;

--===== Build and populate a table to hold countries and states for the random data generator

SELECT RowNum = CAST(RowNum AS INT),

CountryName = CAST(CountryName AS NVARCHAR(100)),

CityName = CAST(CityName AS NVARCHAR(100))

INTO #CountryCity

FROM (

SELECT 1,'United States of America','New York' UNION ALL

SELECT 2,'United States of America','Detroit' UNION ALL

SELECT 3,'United States of America','Los Angeles' UNION ALL

SELECT 4,'France','Paris' UNION ALL

SELECT 5,'France','Lyon' UNION ALL

SELECT 6,'Germany','Bonn' UNION ALL

SELECT 7,'Germany','Hamburg' UNION ALL

SELECT 8,'Germany','Frankfurt' UNION ALL

SELECT 9,'SomeCountry1','SomeCity1' UNION ALL

SELECT 10,'SomeCountry1','SomeCity2' UNION ALL

SELECT 11,'SomeCountry2','SomeCity1' UNION ALL

SELECT 12,'SomeCountry2','SomeCity2'

) d (RowNum, CountryName, CityName)

;

--===== Add a Clustered Index for speed

CREATE CLUSTERED INDEX ix_#CountryCity_RowNum

ON #CountryCity (RowNum) WITH FILLFACTOR = 100

;

--===== Populate and index the given tables

INSERT INTO dbo.CountriesInvolved

(Sold_To_Country)

SELECT DISTINCT

Sold_To_Country = CountryName

FROM #CountryCity

WHERE CountryName NOT LIKE 'SomeCountry%'

;

ALTER TABLE dbo.CountriesInvolved

ADD CONSTRAINT PK_CountriesInvolved

PRIMARY KEY CLUSTERED (Sold_To_Country) WITH FILLFACTOR = 100

;

WITH

cteDataGenerator AS

(

SELECT TOP 1000000

RowNum = ABS(CHECKSUM(NEWID()))%12+1,

SalesAmount = RAND(CHECKSUM(NEWID()))*99+1

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

)

INSERT INTO dbo.Sales_City

(Sold_To_Country, ToCity, SalesAmount)

SELECT Sold_To_Country = cc.CountryName,

ToCity = cc.CityName,

gen.SalesAmount

FROM cteDataGenerator gen

LEFT JOIN #CountryCity cc

ON gen.RowNum = cc.RowNum

;

ALTER TABLE [dbo].[Sales_City] ADD CONSTRAINT [PK_Sales_City] PRIMARY KEY CLUSTERED

([RowID] ASC )

CREATE NONCLUSTERED INDEX ix_Sales_City_Sold_To_Country_Cover01

ON dbo.Sales_City (Sold_To_Country, ToCity) INCLUDE (SalesAmount)

;

----+++++++++++++++++ TESTS FROM HERE ++++++++++++++++++++++++

--===== run the slowest code from JM tests =================================================================

DBCC FREEPROCCACHE WITH NO_INFOMSGS

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS

GO

--===== Basic Cross Tab =================================================================

set statistics io on

set statistics time on

SELECT

b.Sold_To_Country,

SUM(CASE WHEN ToCity = 'New York' THEN (SalesAmount) ELSE 0 END) Sales_New_York,

SUM(CASE WHEN ToCity = 'Detroit' THEN (SalesAmount) ELSE 0 END) Sales_Detroit,

SUM(CASE WHEN ToCity = 'Los Angeles' THEN (SalesAmount) ELSE 0 END) Sales_Los_Angeles,

SUM(CASE WHEN ToCity = 'Paris' THEN (SalesAmount) ELSE 0 END) Sales_Paris,

SUM(CASE WHEN ToCity = 'Lyon' THEN (SalesAmount) ELSE 0 END) Sales_Lyon,

SUM(CASE WHEN ToCity = 'Bonn' THEN (SalesAmount) ELSE 0 END) Sales_Bonn,

SUM(CASE WHEN ToCity = 'Hamburg' THEN (SalesAmount) ELSE 0 END) Sales_Hamburg,

SUM(CASE WHEN ToCity = 'Frankfurt' THEN (SalesAmount) ELSE 0 END) Sales_Frankfurt

FROM dbo.CountriesInvolved a INNER JOIN dbo.Sales_City b

ON a.Sold_To_Country = b.Sold_To_Country

GROUP BY b.Sold_To_Country

;

GO

set statistics io on

set statistics time on

--RESULTS from five consecutive run

--(3 row(s) affected)

--Table 'CountriesInvolved'. 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.

--Table 'Sales_City'. Scan count 3, logical reads 5695, 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.

-- Client Statistics

-- Client processing time 21 24 51 21 20 27.4000

-- Total execution time 1545 1575 1557 1668 1584 1585.8000

-- Wait time on server replies 1524 1551 1506 1647 1564 1558.4000

---+++++++ NOW CREATE N INDEXED VIEW "vw_PreAg ++++++++++++++

--===== Conditionally drop the view for psrtial reruns in SSMS

IF Object_id('vw_PreAg', 'V') IS NOT NULL

DROP VIEW vw_PreAg ;

GO

CREATE VIEW [dbo].[vw_PreAg]

WITH SCHEMABINDING

AS

SELECT dbo.Sales_City.Sold_To_Country,

dbo.Sales_City.ToCity,

SUM(Isnull(dbo.sales_city.SalesAmount, 0)) AS SalesAmount,

Count_big(*) AS cnt

FROM dbo.Sales_city

GROUP BY dbo.Sales_City.Sold_To_Country,

dbo.Sales_City.ToCity

GO

--===== Create a unique clustered index on vw_PreAg

SET ARITHABORT ON

GO

SET CONCAT_NULL_YIELDS_NULL ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

SET ANSI_PADDING ON

GO

SET ANSI_WARNINGS ON

GO

SET NUMERIC_ROUNDABORT OFF

GO

CREATE UNIQUE CLUSTERED INDEX [CI_iX_preag] ON [dbo].[vw_PreAg] ( [Sold_To_Country] ASC, [ToCity] ASC )

--WITH (

-- PAD_INDEX = OFF,

-- STATISTICS_NORECOMPUTE = OFF,

-- SORT_IN_TEMPDB = OFF,

-- IGNORE_DUP_KEY = OFF,

-- DROP_EXISTING = OFF,

-- ONLINE = OFF,

-- ALLOW_ROW_LOCKS = ON,

-- ALLOW_PAGE_LOCKS = ON)

ON [PRIMARY]

GO

--===== run the slowest code from JM tests this time using the

--===== pre agregated results from the indexed view "vw_PreAg"

=================================================================