July 21, 2009 at 2:45 am
scziege (7/21/2009)
Every hint, the problem ist that I have to check wether the key existsin a table of 130 Million rows and after that I have to create 100 Million
unique keys.
Yes. You said.
More than one basic method has been posted which will generate and add around a million new unique rows in a few tens of seconds. Chances are though that it is your function which is slow (especially since it re-creates the class used to generate the code once per row).
Given the excellent contributions so far, I think it might be time for you to put some effort in and post a testable rig of your own if you would like people to commit more of their time.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 21, 2009 at 3:00 am
Also, are you having unrealistic expectations of what can be achieved?
What would be an acceptable time-scale for the execution?
How can are you away from that now ?
What is now your bottleneck ? CPU/IO ?
What does your best performing TSQL routine now look like ?
It should be pretty simple for you to post a complete reproduction script.
July 21, 2009 at 6:37 am
I see a few issues with the stored procedure that could attribute to the performance hit. Your tempcode variable, which you insert into your code column, doesn't match in datatype or size, therefore a conversion would occur each time the insert is done (which is time consuming). Also, using the COUNT function walks through all the rows of the table, where using the EXISTS function will stop once a match is found. I believe there could be two ways to approach this problem. One is to use the IF NOT EXISTS, and do an insert. The other would be to wrap the insert into a try/catch block and if the insert fails, try again. Since your code is the primary key of the table, the insert would fail if a duplicate exists.
without your user-defined function, I could not benchmark these two approaches, but here is the code for each:
--'IF NOT EXISTS' METHOD
ALTER PROCEDURE [dbo].[CreateCodes]
-- Add the parameters for the stored procedure here
@amount bigint
AS
BEGIN
SET NOCOUNT ON
declare @tempcode as char(10)
while @amount > 0
begin
set @tempcode = (SELECT [dbo].[RNGCharacterMask] (8 ,'34679CDFGHJKLNPRTVXY') )
if NOT EXISTS(select 1 from codes where code = @tempcode)
begin
insert into codes (code) values (@tempcode)
set @amount = @amount - 1
end
end
set nocount off
END
--'TRY CATCH' Method
ALTER PROCEDURE [dbo].[CreateCodes]
-- Add the parameters for the stored procedure here
@amount bigint
AS
BEGIN
SET NOCOUNT ON
declare @tempcode as char(10)
while @amount > 0
begin
set @tempcode = (SELECT [dbo].[RNGCharacterMask](8 ,'34679CDFGHJKLNPRTVXY') )
begin try
insert into codes (code) values (@tempcode)
set @amount = @amount - 1
end try
begin catch
end catch
END
set nocount off
END
July 21, 2009 at 5:22 pm
All good points Derek, but they have already been covered at least once in this thread (here for instance.)
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 22, 2009 at 2:45 am
Thanks for all the hints.
I play arround a little bit and I found a way which fits to my needs.
I need now 50 seconds to insert 1 Million rows into a table in which
are 130 Million rows, that is suitable
Thanks
July 22, 2009 at 2:51 am
That's good to hear. Care to share some details?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 6 posts - 46 through 50 (of 50 total)
You must be logged in to reply to this topic. Login to reply