Help us tune a query

Question

Post reply

Help us tune a query

Viewing 15 posts - 61 through 75 (of 103 total)

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

I think I found out how many... would it be right around 106,916?

The only thing I need to know now is how many users are in the Users table? Would that be right around 1.2 million?

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Jeff Moden (7/14/2009)
Something else is going on as well. Look at the difference in durations on your machine compared to mine.

Look at the difference in reads as well.

I'm running exactly the code I posted (comments and all) just with GOs added between the three tests and the names of the temp tables changed. Weird.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

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

Okidoki... thought I'd throw my hat into the ring on this, as well. I didn't try G-Squared's code yet, but here's mine.

First, the test harness. It create a 1.2 million row Users table and 185,000 row UserPoints table for 106,192 separate users. I think that'll be pretty close to what's happening in real life. Of course, I do all of this in a nice safe place... TempDB. Here's the code for the test harness... the details are in the comments

--===== Setup the experiment in a nice safe place

USE TempDB

--drop table userpoints, users --BE CAREFUL NOT TO DROP THE TABLES IN THE WRONG DB!!!!

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

-- Create some test tables and the necessary indexes for performance.

-- This is NOT part of the solution. It's just the test harness.

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

--===== Declare some obviously named programmable variables for testing changes

DECLARE @Rows INT,

@Users INT,

@StartDate SMALLDATETIME,

@Days INT

--===== Presets... make changes for the UserPoints table size and shape here

SELECT @Rows = 185000,

@Users = 106916,

@StartDate = '2001-01-01',

@Days = DATEDIFF(dd,@StartDate,GETDATE())

--===== Create and populate the UserPoints table on the fly

-- according to the presets above

SELECT TOP (@Rows)

UserPointsRecordID = IDENTITY(INT,1,1),

UserID = ABS(CHECKSUM(NEWID())) % @Users + 1,

Date = RAND(CHECKSUM(NEWID())) * @Days + @StartDate,

PointsScored = ABS(CHECKSUM(NEWID())) % 5,

PointsCategory = ABS(CHECKSUM(NEWID())) % 2 + 1

INTO dbo.UserPoints

FROM Master.sys.All_Columns ac1

CROSS JOIN Master.sys.All_Columns ac2

--===== Create and populate the Users table on the fly

-- with 1.2 million rows to simulate the membership

SELECT TOP 1200000

UserID = IDENTITY(INT,1,1),

DisplayName = NEWID() -- Easier than coming up with 1.2 million names

INTO dbo.Users

FROM Master.sys.All_Columns ac1

CROSS JOIN Master.sys.All_Columns ac2

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

-- I believe these are the required indexes for the maximum performance. I didn't

-- include any FK's because they won't change performance on this.

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

ALTER TABLE dbo.UserPoints

ADD CONSTRAINT PK_UserPoints_UserPointsRecordID

PRIMARY KEY CLUSTERED (UserPointsRecordID)

CREATE NONCLUSTERED INDEX IX_UserPoints_Composite

ON dbo.UserPoints (UserID ASC, PointsCategory ASC)

INCLUDE (Date,PointsScored)

ALTER TABLE dbo.Users

ADD CONSTRAINT PK_Users_UserID

PRIMARY KEY CLUSTERED (UserID)

GO

Here's the stored proc I came up with...

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

-- I believe this is the way the stored procedure should look (except for the

-- order of the parameters which I don't know)

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

--drop procedure dbo.GetPoints

CREATE PROCEDURE dbo.GetPoints

@MaxResults INT,

@CutOffDate DATETIME = NULL,

@PointsCategory INT = NULL,

@AdditionalUserID INT = NULL

AS

--===== Supress the auto-display of rowcounts to prevent false echo's

SET NOCOUNT ON

;

--===== Declare some local variables that will help prevent doing an "OR"

DECLARE @LoPointsCategory INT,

@HiPointsCategory INT

;

--===== Preset the local variables

SELECT @LoPointsCategory = ISNULL(@PointsCategory,1),

@HiPointsCategory = ISNULL(@PointsCategory,2),

@CutOffDate = ISNULL(@CutOffDate,DATEADD(dd,DATEDIFF(dd,0,GETDATE())-30,0))

;

--===== Create the required result set

WITH

cteTotal AS

(--==== Create the required sums for each UserID. Additional user will NOT show up if

-- no points for category within cut off date.

SELECT UserID,

RecentPoints = SUM(CASE WHEN Date >= @CutOffDate THEN PointsScored ELSE 0 END),

AllPoints = SUM(PointsScored)

FROM dbo.UserPoints

WHERE UserID 57832

AND PointsCategory BETWEEN @LoPointsCategory AND @HiPointsCategory

GROUP BY UserID

)

,

cteRowNum AS

(--==== Now, add the row number so we can pick the max results

SELECT RowNum = ROW_NUMBER() OVER (ORDER BY RecentPoints Desc),

UserID,

RecentPoints,

AllPoints

FROM cteTotal

)--==== Return the max results and the additional user id if there is one

SELECT rn.RowNum, u.UserID, u.DisplayName, rn.RecentPoints, rn.AllPoints

FROM cteRowNum rn

INNER JOIN dbo.Users u

ON u.UserID = rn.UserID

WHERE (rn.RowNum <= @MaxResults OR rn.UserID = @AdditionalUserID)

ORDER BY rn.RowNum

GO

... and here's the code I used to test it... (figured I'd start getting used to (ugh! GAG!) semi colons on this one)

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

-- Demonstrate the usage and performance of the proc

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

set statistics time on

;

DECLARE @AdditionalUserID INT

SELECT @AdditionalUserID = 4500

--===== Demo both categories and an additional user

EXEC dbo.GetPoints

@MaxResults = 5,

@CutOffDate = NULL, --can be any valid date or null

@PointsCategory = NULL, --Null = both

@AdditionalUserID = @AdditionalUserID

;

PRINT REPLICATE('=',100)

;

--===== Demo category 1 (forum) and an additional user

EXEC dbo.GetPoints

@MaxResults = 5,

@CutOffDate = NULL, --can be any valid date or null

@PointsCategory = 1,

@AdditionalUserID = @AdditionalUserID

;

PRINT REPLICATE('=',100)

;

--===== Demo category 2 (QoD) and an additional user

EXEC dbo.GetPoints

@MaxResults = 5,

@CutOffDate = NULL, --can be any valid date or null

@PointsCategory = 2,

@AdditionalUserID = @AdditionalUserID

;

PRINT REPLICATE('=',100)

;

--===== Demo both categories for top 10 and no additional user

EXEC dbo.GetPoints

@MaxResults = 10,

@CutOffDate = NULL, --can be any valid date or null

@PointsCategory = NULL, --Null = both

@AdditionalUserID = NULL

;

PRINT REPLICATE('=',100)

;

set statistics time off

;

Last but not least, here's the run performance results on my poor ol' 7 year old 1.8 Ghz single p4...

[font="Courier New"]

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 844 ms, elapsed time = 1249 ms.

SQL Server Execution Times:

CPU time = 844 ms, elapsed time = 1250 ms.

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

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 531 ms, elapsed time = 658 ms.

SQL Server Execution Times:

CPU time = 531 ms, elapsed time = 658 ms.

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

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 547 ms, elapsed time = 1498 ms.

SQL Server Execution Times:

CPU time = 547 ms, elapsed time = 1523 ms.

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

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 906 ms, elapsed time = 1179 ms.

SQL Server Execution Times:

CPU time = 906 ms, elapsed time = 1179 ms.

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

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

[/font]

It would be interesting to see what those times come out to be on a more recent PC. 😉

Again... the details for everything are in the comments for the code.

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

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 4

To satisfy your curiosity, here's your test run through our 'production level' backup server.

SQL Server Execution Times:

CPU time = 109 ms, elapsed time = 129 ms.

SQL Server Execution Times:

CPU time = 109 ms, elapsed time = 129 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 125 ms, elapsed time = 125 ms.

SQL Server Execution Times:

CPU time = 125 ms, elapsed time = 125 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 171 ms, elapsed time = 188 ms.

SQL Server Execution Times:

CPU time = 171 ms, elapsed time = 188 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

Clear Sky SQL
My Blog[/url]

Mel Harbour SSC Eights! Points: 857 More actions · Answer 5

Ok, I've run a modified version of Jeff's suggestion:

IF @PointsCategory IS NULL

BEGIN

WITH

cteTotal AS

(--==== Create the required sums for each UserID. Additional user will NOT show up if

-- no points for category within cut off date.

SELECT UserID,

RecentPoints = SUM(CASE WHEN Date >= @CutoffDate THEN PointsScored ELSE 0 END),

AllPoints = SUM(PointsScored)

FROM dbo.UserPoints

WHERE UserID 57832

GROUP BY UserID

)

,

cteRowNum AS

(--==== Now, add the row number so we can pick the max results

SELECT RowNum = ROW_NUMBER() OVER (ORDER BY RecentPoints Desc),

UserID,

RecentPoints,

AllPoints

FROM cteTotal

)--==== Return the max results and the additional user id if there is one

SELECT rn.RowNum AS RowNumber, u.UserID, u.DisplayName, rn.RecentPoints, rn.AllPoints

FROM cteRowNum rn

INNER JOIN dbo.Users u

ON u.UserID = rn.UserID

WHERE (rn.RowNum = @CutoffDate THEN PointsScored ELSE 0 END),

AllPoints = SUM(PointsScored)

FROM dbo.UserPoints

WHERE PointsCategory = @PointsCategory AND UserID 57832

GROUP BY UserID

)

,

cteRowNum AS

(--==== Now, add the row number so we can pick the max results

SELECT RowNum = ROW_NUMBER() OVER (ORDER BY RecentPoints Desc),

UserID,

RecentPoints,

AllPoints

FROM cteTotal

)--==== Return the max results and the additional user id if there is one

SELECT rn.RowNum AS RowNumber, u.UserID, u.DisplayName, rn.RecentPoints, rn.AllPoints

FROM cteRowNum rn

INNER JOIN dbo.Users u

ON u.UserID = rn.UserID

WHERE (rn.RowNum <= @MaxResults OR rn.UserID = @AdditionalUser)

ORDER BY rn.RowNum

END

with the following results:

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

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 'Users'. Scan count 1, logical reads 5228, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'UserPoints'. Scan count 2, logical reads 353, 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 = 688 ms, elapsed time = 2098 ms.

SQL Server Execution Times:

CPU time = 688 ms, elapsed time = 2098 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

And the execution plan attached. Note that I've adjusted the forum setup to avoid having to zip up these sqlplans.

Mel HarbourProject ManagerRed Gate Software

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Mel Harbour (7/15/2009)
Note that I've adjusted the forum setup to avoid having to zip up these sqlplans.

Excellent. Thank you for that.

I'll look over this and suggest any possible index enhancements later.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 7

Hmm,

i dont like the scan on the Users table. Really not sure why its done that.

Maybe you sort out the top X users , then join to the Users table

Maybe try bringing this condition (rn.UserID = @AdditionalUser) upto the UserPoints table..

Clear Sky SQL
My Blog[/url]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

Mel Harbour (7/15/2009)
Ok, I've run a modified version of Jeff's suggestion:

Something sure is wrong... look at the difference between the CPU and the duration compared to my box.

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

Also, here's what the new zipless sqlplan does in IE...

The XML page cannot be displayed

Cannot view XML input using style sheet. Please correct the error and then click the Refresh button, or try again later.

--------------------------------------------------------------------------------

Switch from current encoding to specified encoding not supported. Error processing resource 'http://www.sqlservercentral.co...

{ ?xml version="1.0" encoding="utf-16"? }

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

Jeff Moden (7/15/2009)
Mel Harbour (7/15/2009)
Ok, I've run a modified version of Jeff's suggestion:
Something sure is wrong... look at the difference between the CPU and the duration compared to my box.

Something else must be going on as well... if you're running a server, it should outpace my poor ol' desktop by quite a margin on all fronts. And the huge disparity between CPU and duration tells me that there's either a bad index (can't see your plan so I'll take Dave's word on the scan on User's which shouldn't be happening) or something is wrong with the "pipe" itself.

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

Mel Harbour SSC Eights! Points: 857 More actions · Answer 11

You should just be able to right-click and save as for the sqlplan. I'll attach a zipped version to this post as well, just to be on the safe side. Perhaps at some point we should write a plugin to the site to do graphical representations of sqlplans online...

Mel HarbourProject ManagerRed Gate Software

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 12

Jeff Moden (7/15/2009)
Also, here's what the new zipless sqlplan does in IE...
The XML page cannot be displayed
Cannot view XML input using style sheet. Please correct the error and then click the Refresh button, or try again later.
--------------------------------------------------------------------------------
Switch from current encoding to specified encoding not supported. Error processing resource 'http://www.sqlservercentral.co...
{ ?xml version="1.0" encoding="utf-16"? }

Curious, FireFox works fine , IE dosent seem to see it as a .sqlplan file ????

Out of my depth with that one

Clear Sky SQL
My Blog[/url]

Mel Harbour SSC Eights! Points: 857 More actions · Answer 13

Firefox's handling of MIME types has always been somewhat different to IEs. The MIME type that InstantForums is applying is application/octet-stream. Not quite sure why IE is getting upset at the moment. I'll look into it when I have a bit of time.

Mel HarbourProject ManagerRed Gate Software

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 14

Mel Harbour (7/15/2009)
You should just be able to right-click and save as for the sqlplan. I'll attach a zipped version to this post as well, just to be on the safe side. Perhaps at some point we should write a plugin to the site to do graphical representations of sqlplans online...

Heh... that, of course, works but you've shifted the burden from the provider of the info to the user of the info. Six one... half dozen the other. 😛

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 15

Mel Harbour (7/15/2009)
You should just be able to right-click and save as for the sqlplan. I'll attach a zipped version to this post as well, just to be on the safe side. Perhaps at some point we should write a plugin to the site to do graphical representations of sqlplans online...

The index scan is because of the IX being used. Apparently, it doesn't contain a User ID reference or it's in the wrong order. I'll have to read back an find out what the indexes on the User table look like.

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