December 22, 2009 at 9:50 am
I used the row counts to limit the number of downloadcodes loaded to the temp file. Operating in a partial vacuum (more downloadocdes than members but no real numbers) why put 100,000 codes in the temp file when only a few thousand (or less) are needed.
Let us know how the code you finally use works.
December 22, 2009 at 10:27 am
Lynn Pettis (12/22/2009)
set @MemberCount = @@rowcount; -- How many members with out download codesset rowcount @MemberCount; -- SQL Server 2000
Lynn, that's a neat little trick I never thought of. I was working on using dynamic SQL to build a SELECT TOP X query based on the row count. Then I thought "this is ridiculous, no one would ever do this", and then I saw your solution. It's always the little things that help me most. Thanks.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
December 22, 2009 at 10:30 am
Greg Snidow (12/22/2009)
Lynn Pettis (12/22/2009)
set @MemberCount = @@rowcount; -- How many members with out download codesset rowcount @MemberCount; -- SQL Server 2000
Lynn, that's a neat little trick I never thought of. I was working on using dynamic SQL to build a SELECT TOP X query based on the row count. Then I thought "this is ridiculous, no one would ever do this", and then I saw your solution. It's always the little things that help me most. Thanks.
Using my code, would you like to see how I would change it for SQL Server 2005?
December 22, 2009 at 10:34 am
Here is my SQL Server 2005/2008 code for the same problem:
--create download code table
create table #DownloadCode
(
dlc varchar(14) not null,
dlcmembernumber numeric(13,0),
dlcdatereceived datetime,
dlcdateexpires datetime,
dlcissuedby varchar(50)
)
alter table #DownloadCode add primary key clustered (dlc)
insert into #DownloadCode(dlc)
select '2Kk6zf439f' union
select '2T3NhCD6b9' union
select '37NfDzD4J3' union
select '42kGCcHM9D' union
select '4H4jzDGG93' union
select '4yCH6MjjKk'
--create members table
create table #Members
(
membernumber numeric(13,0) not null
)
alter table #Members add primary key clustered (membernumber)
insert into #Members(membernumber)
select 1 union
select 2 union
select 3 union
select 4 union
select 5
create table #tmpMembers(
MemberId int identity(1,1),
membernumber numeric(13,0)
);
create table #tmpDownloadCode(
DLCId int identity(1,1),
dlc varchar(14) not null
);
declare @MemberCount int;
insert into #tmpMembers(membernumber)
select
m.membernumber
from
#Members m
left outer join #DownloadCode dc
on m.membernumber = dc.dlcmembernumber
where
dc.dlcmembernumber is null;
set @MemberCount = @@rowcount; -- How many members with out download codes
insert into #tmpDownloadCode(dlc)
select top (@MemberCount)
dlc
from
#DownloadCode
where
dlcmembernumber is null;
select
m.membernumber,
d.dlc,
dc.*
from
#DownloadCode dc
inner join #tmpDownloadCode d
on (dc.dlc = d.dlc)
inner join #tmpMembers m
on (d.DLCId = m.MemberId);
declare @DateReceived datetime;
update #DownloadCode set
dlcmembernumber = m.membernumber,
@DateReceived = dlcdatereceived = getdate(),
dlcissuedby = 'DTS Package',
dlcdateexpires = dateadd(yy, 1, @DateReceived)
from
#DownloadCode dc
inner join #tmpDownloadCode d
on (dc.dlc = d.dlc)
inner join #tmpMembers m
on (d.DLCId = m.MemberId);
select * from #DownloadCode;
December 22, 2009 at 10:35 am
Greg Snidow (12/22/2009)
Lynn Pettis (12/22/2009)
set @MemberCount = @@rowcount; -- How many members with out download codesset rowcount @MemberCount; -- SQL Server 2000
Lynn, that's a neat little trick I never thought of. I was working on using dynamic SQL to build a SELECT TOP X query based on the row count. Then I thought "this is ridiculous, no one would ever do this", and then I saw your solution. It's always the little things that help me most. Thanks.
I'd love to see it. I'm actually using 2008 right now to work on these posts, but I don't know anything that was not in 2000, except for CTE's. If you would be so willing, it would be a good opportunity for me to see a solution work both ways. Thanks.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
December 22, 2009 at 10:39 am
Greg Snidow (12/22/2009)
Greg Snidow (12/22/2009)
Lynn Pettis (12/22/2009)
set @MemberCount = @@rowcount; -- How many members with out download codesset rowcount @MemberCount; -- SQL Server 2000
Lynn, that's a neat little trick I never thought of. I was working on using dynamic SQL to build a SELECT TOP X query based on the row count. Then I thought "this is ridiculous, no one would ever do this", and then I saw your solution. It's always the little things that help me most. Thanks.
I'd love to see it. I'm actually using 2008 right now to work on these posts, but I don't know anything that was not in 2000, except for CTE's. If you would be so willing, it would be a good opportunity for me to see a solution work both ways. Thanks.
Already posted it. Actually, that is a port of the 2000 version. Given a bit more time, I'd come up with something different. Give me a bit of time.
December 22, 2009 at 10:53 am
Here is the original SQL Server 2005/2008 code I had envisioned earlier in the post:
--create download code table
create table #DownloadCode
(
dlc varchar(14) not null,
dlcmembernumber numeric(13,0),
dlcdatereceived datetime,
dlcdateexpires datetime,
dlcissuedby varchar(50)
)
alter table #DownloadCode add primary key clustered (dlc)
insert into #DownloadCode(dlc)
select '2Kk6zf439f' union
select '2T3NhCD6b9' union
select '37NfDzD4J3' union
select '42kGCcHM9D' union
select '4H4jzDGG93' union
select '4yCH6MjjKk'
--create members table
create table #Members
(
membernumber numeric(13,0) not null
)
alter table #Members add primary key clustered (membernumber)
insert into #Members(membernumber)
select 1 union
select 2 union
select 3 union
select 4 union
select 5;
with UnassignedMembers (
RowNumber,
MemberNumber
) as (
select
row_number() over (order by m.membernumber),
m.membernumber
from
#Members m
left outer join #DownloadCode dc
on (m.membernumber = dc.dlcmembernumber)
where
dc.dlcmembernumber is null)
,
UnassignedDLCs (
RowNumber,
DLC
) as (
select top (select count(*) from UnassignedMembers)
row_number() over (order by dc.dlc),
dc.dlc
from
#DownloadCode dc
where
dc.dlcmembernumber is null)
update #DownloadCode set
dlcmembernumber = um.MemberNumber,
dlcdatereceived = getdate(),
dlcissuedby = 'DTS Package'
from
#DownloadCode dc
inner join UnassignedDLCs ud
on dc.dlc = ud.DLC
inner join UnassignedMembers um
on ud.RowNumber = um.RowNumber;
select * from #DownloadCode;
December 22, 2009 at 11:01 am
Slight mod to the code for testing:
--create download code table
create table #DownloadCode
(
dlc uniqueidentifier not null,
dlcmembernumber numeric(13,0),
dlcdatereceived datetime,
dlcdateexpires datetime,
dlcissuedby varchar(50)
)
alter table #DownloadCode add primary key clustered (dlc)
insert into #DownloadCode(dlc)
select newid()
from dbo.Tally
where N <= 1000000;
--create members table
create table #Members
(
membernumber numeric(13,0) not null
)
alter table #Members add primary key clustered (membernumber)
insert into #Members(membernumber)
select N
from dbo.Tally
where N <= 25000;
set statistics io on;
set statistics time on;
with UnassignedMembers (
RowNumber,
MemberNumber
) as (
select
row_number() over (order by m.membernumber),
m.membernumber
from
#Members m
left outer join #DownloadCode dc
on (m.membernumber = dc.dlcmembernumber)
where
dc.dlcmembernumber is null)
,
UnassignedDLCs (
RowNumber,
DLC
) as (
select top (select count(*) from UnassignedMembers)
row_number() over (order by dc.dlc),
dc.dlc
from
#DownloadCode dc
where
dc.dlcmembernumber is null)
update #DownloadCode set
dlcmembernumber = um.MemberNumber,
dlcdatereceived = getdate(),
dlcissuedby = 'DTS Package'
from
#DownloadCode dc
inner join UnassignedDLCs ud
on dc.dlc = ud.DLC
inner join UnassignedMembers um
on ud.RowNumber = um.RowNumber;
set statistics time off;
set statistics io off;
select * from #DownloadCode;
Statistics results (running on a dual quad-core server):
(25000 row(s) affected)
Table '#Members____________________________________________________________________________________________________________000000000410'. Scan count 18, logical reads 338, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#DownloadCode_______________________________________________________________________________________________________00000000040F'. Scan count 19, logical reads 165734, physical reads 0, read-ahead reads 3, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 50782, 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 = 2502 ms, elapsed time = 818 ms.
(25000 row(s) affected)
(1000000 row(s) affected)
Commented the TOP out of the UnassignedDLC cte and reran:
(1000000 row(s) affected)
(25000 row(s) affected)
Table '#Members____________________________________________________________________________________________________________000000000412'. Scan count 9, logical reads 169, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#DownloadCode_______________________________________________________________________________________________________000000000411'. Scan count 27, logical reads 90211, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 8, logical reads 50778, 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 = 4106 ms, elapsed time = 2014 ms.
(25000 row(s) affected)
(1000000 row(s) affected)
December 22, 2009 at 11:12 am
Thanks Lynn. I'm going to have to take some time to figure this out, since I have never taken the time to figure out the new stuff like OVER().
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
December 22, 2009 at 11:16 am
No problem. The more you learn, the more effective you can be in solving your opportunities (other peoples problems).
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply