August 4, 2016 at 12:51 pm
Here is an updated test. It may not be perfect but it is better than the last regarding test data.
if object_id('dbo.eTally') is not null
DROP FUNCTION [dbo].[eTally]
GO
/****** Object: UserDefinedFunction [dbo].[eTally] Script Date: 8/4/2016 9:32:22 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[eTally] (@Range int, @StartZero bit)
returns table
with schemabinding
as return(
with e1(n) as (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
e2(n) as (select 1 from e1 a cross join e1 b),
e4(n) as (select 1 from e2 a cross join e2 b),
etally(n) as (select n = row_number() over (order by (select null)) from e4 a cross join e4 b)
select top (@Range)
n - cast(@StartZero as int) as n
from
etally);
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.addresses') IS NOT NULL DROP TABLE dbo.addresses;
CREATE TABLE [dbo].[addresses](
[AddID] [int] NOT NULL PRIMARY KEY CLUSTERED,
[ClientId] [int] NOT NULL,
[AddrType] [nvarchar](10) NOT NULL
)
--INSERT INTO addresses VALUES (1001, 9999, 'Mailing')
-- ,(1002, 9999, 'Billing')
-- ,(1003, 9999, 'Work')
-- ,(1004, 9999, 'Billing')
-- ,(1005, 9998, 'Mailing')
-- ,(1006, 9998, 'Home')
-- ,(1007, 9997, '');
--===== Declare some obviously named variables
DECLARE @NumberOfRows INT,
@StartValue INT,
@EndValue INT,
@Range INT
;
--===== Preset the variables to known values
SELECT @NumberOfRows = 20000000,
@StartValue = 1000,
@EndValue = 999999,
@Range = @EndValue - @StartValue + 1
;
--===== Conditionally drop the test table to make reruns easier in SSMS
IF OBJECT_ID('tempdb..#SomeTestTable','U') IS NOT NULL
DROP TABLE #SomeTestTable
;
--===== Create the test table with "random constrained" integers and floats
-- within the parameters identified in the variables above.
WITH basedata as (
SELECT TOP (@NumberOfRows)
AddId = n + 1000,
SomeRandomInteger = ABS(CHECKSUM(NEWID())) % @Range + @StartValue
FROM dbo.eTally(@NumberOfRows,0)
), intermediatedata as (
SELECT
AddId,
SomeRandomInteger,
V3 = SomeRandomInteger % 3,
V5 = SomeRandomInteger % 5,
V7 = SomeRandomInteger % 7,
V11 = SomeRandomInteger % 11,
V13 = SomeRandomInteger % 13
FROM basedata
), finaldata as (
SELECT
AddId,
SomeRandomInteger,
V3,
V5,
V7,
V11,
V13,
-- SomeRandomValue = case ABS(CHECKSUM(NEWID())) % 5 + 1 when 1 then 'Billing' when 2 then 'Mailing' when 3 then 'Work' when 4 then 'Home' else '' end
SomeRandomValue = case when V3 = 0 and V5 = 0 and V7 = 0 and V11 = 0 and V13 = 0
then case ABS(CHECKSUM(NEWID())) % 6 + 1 when 1 then 'Billing' when 2 then 'Mailing' when 3 then 'Work' when 4 then 'Home' when 5 then '' else null end
when V3 = 0 and V5 = 0 and V7 = 0 and V11 = 0 and V13 != 0
then case ABS(CHECKSUM(NEWID())) % 5 + 1 when 1 then 'Billing' when 2 then 'Mailing' when 3 then 'Work' when 4 then 'Home' else null end
when V3 = 0 and V5 = 0 and V7 = 0 and V11 != 0 and V13 = 0
then case ABS(CHECKSUM(NEWID())) % 5 + 1 when 1 then 'Billing' when 2 then 'Mailing' when 3 then 'Work' when 4 then '' else null end
when V3 = 0 and V5 = 0 and V7 != 0 and V11 = 0 and V13 = 0
then case ABS(CHECKSUM(NEWID())) % 5 + 1 when 1 then 'Billing' when 2 then 'Mailing' when 3 then '' when 4 then 'Home' else null end
when V3 = 0 and V5 != 0 and V7 = 0 and V11 = 0 and V13 = 0
then case ABS(CHECKSUM(NEWID())) % 5 + 1 when 1 then 'Billing' when 2 then '' when 3 then 'Work' when 4 then 'Home' else null end
when V3 != 0 and V5 = 0 and V7 = 0 and V11 = 0 and V13 = 0
then case ABS(CHECKSUM(NEWID())) % 5 + 1 when 1 then '' when 2 then 'Mailing' when 3 then 'Work' when 4 then 'Home' else null end
when V3 = 0 and V5 != 0 and V7 != 0 and V11 != 0 and V13 != 0
then case ABS(CHECKSUM(NEWID())) % 5 + 1 when 1 then 'Billing' else null end
when V3 != 0 and V5 = 0 and V7 != 0 and V11 != 0 and V13 != 0
then case ABS(CHECKSUM(NEWID())) % 5 + 1 when 1 then 'Mailing' else null end
when V3 != 0 and V5 != 0 and V7 = 0 and V11 != 0 and V13 != 0
then case ABS(CHECKSUM(NEWID())) % 5 + 1 when 1 then 'Work' else null end
when V3 != 0 and V5 != 0 and V7 != 0 and V11 = 0 and V13 != 0
then case ABS(CHECKSUM(NEWID())) % 5 + 1 when 1 then 'Home' else null end
when V3 != 0 and V5 != 0 and V7 != 0 and V11 != 0 and V13 = 0
then case ABS(CHECKSUM(NEWID())) % 5 + 1 when 1 then '' else null end
else null end
FROM intermediatedata
WHERE V3 = 0 or V5 = 0 or V7 = 0 or v11 = 0 or v13 = 0
)
INSERT INTO addresses
SELECT
AddId,
SomeRandomInteger,
--V3,
--V5,
--V7,
--V11,
--V13,
SomeRandomValue
-- INTO #SomeTestTable
FROM finaldata
WHERE SomeRandomValue is not null
order by SomeRandomInteger;
delete top (50) percent from dbo.addresses;
--CREATE NONCLUSTERED INDEX NCLIDX_DBO_ADDRESSES_CLIENT_ID_ADDRTYPE_INCL_ADDID ON dbo.addresses (ClientId ASC, AddrType ASC) INCLUDE (AddID);
CREATE NONCLUSTERED INDEX NCLIDX_DBO_ADDRESSES_CLIENT_ID_ADDRTYPE_INCL_ADDID1 ON dbo.addresses (ClientId ASC, AddrType ASC) INCLUDE (AddID) WHERE AddrType != 'Work';
raiserror('Eirikur Eiriksson Solution Starting',10,1) with nowait;
set statistics io,time on;
WITH BASE_DATA AS
(
SELECT
AD.AddID
,AD.ClientId
,AD.AddrType
,CASE
WHEN AD.AddrType = 'Billing' THEN 1
WHEN AD.AddrType = 'Mailing' THEN 2
WHEN AD.AddrType = 'Work' THEN 3
WHEN AD.AddrType = 'Home' THEN 4
ELSE 5
END AS TRNK
FROM dbo.addresses AD
WHERE AD.AddrType != 'Work'
)
,RANKED_DATA AS
(
SELECT
BD.AddID
,BD.ClientId
,BD.AddrType
,BD.TRNK
,MIN(BD.TRNK) OVER
(
PARTITION BY BD.ClientId
) AS MRNK
FROM BASE_DATA BD
)
,FINAL_RESULTS AS
(
SELECT
RD.AddID
,RD.ClientId
,RD.AddrType
,ROW_NUMBER() OVER
(
PARTITION BY RD.ClientId
ORDER BY (SELECT NULL)
) AS TRID
FROM RANKED_DATA RD
WHERE RD.TRNK = RD.MRNK
)
SELECT
FR.AddID
,FR.ClientId
,FR.AddrType
FROM FINAL_RESULTS FR
WHERE FR.TRID = 1;
set statistics io,time off;
raiserror('Eirikur Eiriksson Solution Ending',10,1) with nowait;
raiserror('Lynn Pettis Solution Starting',10,1) with nowait;
set statistics io,time on;
with basedata as (
select
rn = row_number() over (partition by ad.ClientId order by case when ad.AddrType = 'Billing' then 1
when ad.AddrType = 'Mailing' then 2
when ad.AddrType = 'Work' then 3
when ad.AddrType = 'Home' then 4
else 5
end, ad.AddID desc),
ad.AddID,
ad.ClientId,
ad.AddrType
from
dbo.addresses ad
where
ad.AddrType != 'Work'
)
select
AddID,
ClientId,
AddrType
from
basedata
where
rn = 1
order by
ClientId;
set statistics io,time off;
raiserror('Lynn Pettis Solution Ending',10,1) with nowait;
select
*
from
dbo.addresses
order by
ClientId asc,
case when AddrType = 'Billing' then 1
when AddrType = 'Mailing' then 2
when AddrType = 'Work' then 3
when AddrType = 'Home' then 4
else 5
end,
AddID desc;
Play with it. I changed the nonclustered index to a filtered index.
Here are the stats from one of my runs:
Eirikur Eiriksson Solution Starting
Table 'Worktable'. Scan count 3, logical reads 2702512, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'addresses'. Scan count 1, logical reads 2691, 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 = 2153 ms, elapsed time = 2279 ms.
Eirikur Eiriksson Solution Ending
Lynn Pettis Solution Starting
Table 'addresses'. Scan count 1, logical reads 2691, 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 = 671 ms, elapsed time = 1445 ms.
Lynn Pettis Solution Ending
August 4, 2016 at 1:21 pm
Thanks!
Viewing 2 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply