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

SQL Help Expand / Collapse
Author
Message
Posted Thursday, July 17, 2014 12:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 1:41 PM
Points: 8, Visits: 36
I have a Contract table with a CoverageCode column. I what pull 5 records for each CoverageCode. Currently I am doing this:
SELECT TOP 5 * FROM Contract WHERE CoverageCode = 1
UNION
SELECT TOP 5 * FROM Contract WHERE CoverageCode = 2
etc.....there are over 100 if I go this route

Is there a better way to do this?
Post #1593794
Posted Thursday, July 17, 2014 12:55 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:42 PM
Points: 1,288, Visits: 3,691
p_shaw (7/17/2014)
I have a Contract table with a CoverageCode column. I what pull 5 records for each CoverageCode. Currently I am doing this:
SELECT TOP 5 * FROM Contract WHERE CoverageCode = 1
UNION
SELECT TOP 5 * FROM Contract WHERE CoverageCode = 2
etc.....there are over 100 if I go this route

Is there a better way to do this?


Suggestion, do something like this


WITH PRE_SEL AS 
(
SELECT
ROW_NUMBER() OVER (PARTITION BY CoverageCode ORDER BY (SELECT NULL)) AS PRT_RID
,*
FROM Contract
)
SELECT * FROM PRE_SEL WHERE PRT_RID <=5;

Post #1593796
Posted Thursday, July 17, 2014 1:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 1:41 PM
Points: 8, Visits: 36
Worked great! Thanks
Post #1593816
Posted Monday, July 21, 2014 4:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 5:27 PM
Points: 27, Visits: 445
I would approach that using TOP and CROSS APPLY, eg:

SELECT c2.*
FROM ( SELECT DISTINCT CoverageCode FROM dbo.[Contract] ) x
CROSS APPLY ( SELECT TOP 5 * FROM dbo.[Contract] c WHERE x.CoverageCode = c.CoverageCode ) c2

If there is an index on CoverageCode, that will really help too, or if the CoverageCodes are stored in their own table ( so we don't have to do the DISTINCT on dbo.Contract ) then that was the most efficient in my simple test rig.

I found this marginally more efficient than the CTE at a scale of 1 million rows, and the CTE appears unable to get an accurate estimated rowcount. This is unlikely to matter at small volumes. but it's always worth considering alternatives : )

My results :
(see attachment)

My simple test rig:
USE tempdb
GO

-- !!WARNING - this is not to be run on a production server
IF OBJECT_ID('dbo.[Contract]') IS NOT NULL DROP TABLE dbo.[Contract]
IF OBJECT_ID('dbo.[CoverageCodes]') IS NOT NULL DROP TABLE dbo.CoverageCodes
GO
CREATE TABLE dbo.[Contract]
(
rowId INT IDENTITY PRIMARY KEY,
CoverageCode INT NOT NULL
)
GO

CREATE INDEX _idx ON dbo.[Contract] ( CoverageCode )
GO

;WITH cte AS (
SELECT TOP 1000000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
CROSS JOIN master.sys.columns c2
CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.[Contract] ( CoverageCode )
SELECT rn % 101
FROM cte
GO

SELECT DISTINCT CoverageCode
INTO dbo.CoverageCodes
FROM dbo.[Contract]
GO

CREATE UNIQUE INDEX _idx2 ON dbo.[CoverageCodes] ( CoverageCode )

GO


DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO


-- 1)
SELECT c2.*
FROM ( SELECT DISTINCT CoverageCode FROM dbo.[Contract] ) x
CROSS APPLY ( SELECT TOP 5 * FROM dbo.[Contract] c WHERE x.CoverageCode = c.CoverageCode ) c2
GO

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO

-- 2)
;WITH PRE_SEL AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY CoverageCode ORDER BY (SELECT NULL)) AS PRT_RID
,*
FROM Contract
)
SELECT * FROM PRE_SEL WHERE PRT_RID <=5
GO

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO

-- 3)
SELECT c2.*
FROM dbo.CoverageCodes x
CROSS APPLY ( SELECT TOP 5 * FROM dbo.[Contract] c WHERE x.CoverageCode = c.CoverageCode ) c2



  Post Attachments 
temp.png (1 view, 15.74 KB)
Post #1594518
Posted Monday, July 21, 2014 6:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:56 AM
Points: 7,120, Visits: 13,491
wBob (7/21/2014)
I would approach that using TOP and CROSS APPLY, eg:

SELECT c2.*
FROM ( SELECT DISTINCT CoverageCode FROM dbo.[Contract] ) x
CROSS APPLY ( SELECT TOP 5 * FROM dbo.[Contract] c WHERE x.CoverageCode = c.CoverageCode ) c2

.
.
.


How does this query compare against Eirikur's? The one you've measured omits a processing step: [Contract] -> [CoverageCodes].


“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 #1594543
Posted Monday, July 21, 2014 7:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 5:27 PM
Points: 27, Visits: 445
... if the CoverageCodes are stored in their own table ...


I'm speculating coverageCodes could be a foreign key (as the name would suggest), but of course we don't have this info from the OP. In the event they are not in a separate table and performance of this query was important to them, then they could implement it.
Post #1594602
Posted Monday, July 21, 2014 1:47 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:48 PM
Points: 23,015, Visits: 31,536
wBob (7/21/2014)
I would approach that using TOP and CROSS APPLY, eg:

SELECT c2.*
FROM ( SELECT DISTINCT CoverageCode FROM dbo.[Contract] ) x
CROSS APPLY ( SELECT TOP 5 * FROM dbo.[Contract] c WHERE x.CoverageCode = c.CoverageCode ) c2

If there is an index on CoverageCode, that will really help too, or if the CoverageCodes are stored in their own table ( so we don't have to do the DISTINCT on dbo.Contract ) then that was the most efficient in my simple test rig.

I found this marginally more efficient than the CTE at a scale of 1 million rows, and the CTE appears unable to get an accurate estimated rowcount. This is unlikely to matter at small volumes. but it's always worth considering alternatives : )

My results :
(see attachment)

My simple test rig:
USE tempdb
GO

-- !!WARNING - this is not to be run on a production server
IF OBJECT_ID('dbo.[Contract]') IS NOT NULL DROP TABLE dbo.[Contract]
IF OBJECT_ID('dbo.[CoverageCodes]') IS NOT NULL DROP TABLE dbo.CoverageCodes
GO
CREATE TABLE dbo.[Contract]
(
rowId INT IDENTITY PRIMARY KEY,
CoverageCode INT NOT NULL
)
GO

CREATE INDEX _idx ON dbo.[Contract] ( CoverageCode )
GO

;WITH cte AS (
SELECT TOP 1000000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
CROSS JOIN master.sys.columns c2
CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.[Contract] ( CoverageCode )
SELECT rn % 101
FROM cte
GO

SELECT DISTINCT CoverageCode
INTO dbo.CoverageCodes
FROM dbo.[Contract]
GO

CREATE UNIQUE INDEX _idx2 ON dbo.[CoverageCodes] ( CoverageCode )

GO


DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO


-- 1)
SELECT c2.*
FROM ( SELECT DISTINCT CoverageCode FROM dbo.[Contract] ) x
CROSS APPLY ( SELECT TOP 5 * FROM dbo.[Contract] c WHERE x.CoverageCode = c.CoverageCode ) c2
ORDER BY 2, 1
GO

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO

-- 2)
;WITH PRE_SEL AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY CoverageCode ORDER BY (SELECT NULL)) AS PRT_RID
,*
FROM Contract
)
SELECT * FROM PRE_SEL WHERE PRT_RID <=5
ORDER BY CoverageCode, rowId
GO

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO

-- 3)
SELECT c2.*
FROM dbo.CoverageCodes x
CROSS APPLY ( SELECT TOP 5 * FROM dbo.[Contract] c WHERE x.CoverageCode = c.CoverageCode ) c2
ORDER BY 2, 1



Two things.

One, I really dislike ORDER BYs that use ordinal positions to identify the column(s) to ORDER BY. It obfuscates something that really shouldn't be. You really should specify the column names explicitly.

Two, iirc, using ordinal position in the ORDE BY clause has been deprecated and may be removed from future versions of SQL Server. Sorry, some one with a little more time on their hands may want to verify this but I do recall reading this several years back. Who knows when they will actually pull it.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1594809
Posted Monday, July 21, 2014 3:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 5:27 PM
Points: 27, Visits: 445
Good spot Lynne, that was just in the demo so I could check the results were consistent between the three queries. Sorting is not a requirement from the OP so we can take it out altogether. Guess what, even better results for the FK query, Duration 1, CPU 0 even from a cold cache!


  Post Attachments 
temp.png (3 views, 15.58 KB)
Post #1594845
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse