OVER() against sub query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

OVER() against sub query

Author

Topic

tm
Posting Yak Master

160 Posts

Posted - 2007-10-25 : 10:24:14

I am wondering if anyone can enlighten me or comment on when to use OVER() function instead of sub query

Using OVER() is much more readable and much easier to write but have found it is taking much much longer.

Using OVER() function took 4 minute and 30 seconds to run compared with 2 seconds with sub query (please see below example and some details)

-- =======

declare @a table (Customer_ID bigint, Batch_ID bigint, [File_ID] bigint, Code_ID bigint)
declare @b table (Batch_ID bigint, [File_ID] bigint)
declare @c table ([File_ID] bigint, FileSource varchar(5))
declare @d table (Customer_ID bigint, supptype smallint, EffectiveDate datetime, ProdOpt varchar(50))

-- Actual rows in table 20625 instead of 4
insert into @a
select 1,1,1, 1
union all
select 2,1,1,2
union all
select 2,1,1,3
union all
select 3,1,1,3

-- Actual rows in table = 86 instead of 1
insert into @b
select 1,1

-- Actual rows in table = 12 instead of 1
insert into @c
select 1, 'files'

-- Actual rows in table = 2338 instead of 7
insert into @d
select 1, 1, getdate(), 'aa'
union all
select 1,2, getdate(), 'aa'
union all
select 1,3, getdate(), 'aa'
union all
select 1,5, getdate(), 'aa'
union all
select 2,3, getdate(), 'aa'
union all
select 3,5, getdate(), 'bb'
union all
select 3,6, getdate(), 'bb'

-- With actual tables with additional columns
-- Time to complete : 4 minutes 30 seconds
SELECT
CUST.Customer_ID, Code_ID ,
prodopt,
SUPP.EffectiveDate,
FileSource
FROM
@a CUST INNER JOIN @b BATCHF ON CUST.Batch_ID = BATCHF.Batch_ID
INNER JOIN @c FILET ON BATCHF.[File_ID] = FILET.[File_ID]
INNER JOIN
(SELECT Customer_ID, EffectiveDate, ProdOpt, supptype
FROM @d WHERE supptype = 1
UNION ALL
SELECT DISTINCT Customer_ID, EffectiveDate, ProdOpt,
MIN(supptype) OVER(PARTITION BY Customer_ID) AS supptype
FROM @d WHERE supptype BETWEEN 2 AND 4
UNION ALL
SELECT DISTINCT Customer_ID, EffectiveDate, ProdOpt,
MIN(supptype) OVER(PARTITION BY Customer_ID) AS supptype
FROM @d WHERE supptype > 4
) SUPP
ON CUST.Customer_ID = SUPP.Customer_ID

-- With actual tables with additional columns
-- Time to complete : 2 seconds
SELECT
CUST.Customer_ID, Code_ID ,
prodopt,
SUPP.EffectiveDate,
FileSource
FROM
@a CUST INNER JOIN @b BATCHF ON CUST.Batch_ID = BATCHF.Batch_ID
INNER JOIN @c FILET ON BATCHF.[File_ID] = FILET.[File_ID]
INNER JOIN
(
SELECT Customer_ID, EffectiveDate, ProdOpt, supptype
FROM @d
WHERE RTRIM(CAST(Customer_ID AS VARCHAR)) + CAST(supptype AS CHAR(1)) in
(SELECT RTRIM(CAST(Customer_ID AS VARCHAR)) + CAST(supptype AS CHAR(1))
FROM @d WHERE supptype = 1
UNION ALL
SELECT RTRIM(CAST(Customer_ID AS VARCHAR)) + CAST(MIN(supptype) AS CHAR(1))
FROM @d WHERE supptype BETWEEN 2 AND 4
GROUP BY Customer_ID
UNION ALL
SELECT RTRIM(CAST(Customer_ID AS VARCHAR)) + CAST(MIN(supptype) AS CHAR(1))
FROM @d WHERE supptype > 4
GROUP BY Customer_ID
)
) SUPP
ON CUST.Customer_ID = SUPP.Customer_ID

-- The difference in time is due to using OVER()
-- If this query runs on it's own it takes less than one second

SELECT Customer_ID, EffectiveDate, ProdOpt, supptype
FROM @d WHERE supptype = 1
UNION ALL
SELECT DISTINCT Customer_ID, EffectiveDate, ProdOpt,
MIN(supptype) OVER(PARTITION BY Customer_ID) AS supptype
FROM @d WHERE supptype BETWEEN 2 AND 4
UNION ALL
SELECT DISTINCT Customer_ID, EffectiveDate, ProdOpt,
MIN(supptype) OVER(PARTITION BY Customer_ID) AS supptype
FROM @d WHERE supptype > 4

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources