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 12345»»»

Performance Question Expand / Collapse
Author
Message
Posted Thursday, July 2, 2009 3:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 8, 2014 5:24 AM
Points: 43, Visits: 265
Dear all,

I have a database in which exists a table with 100 Million rows. At the Moment no
partationing, one column caled code with a varchar(10) Datatype as the primary key.

Then I have a stored procedure with a paramter how many Codes I want to create.
In a while loop a make a lookup to the code table to check if the code exists if not
I insert the code to the code table.

Has someone an idea to increase the performance. If i run the stored procedure
with 1 Million rows it runs 15 Minutes, that is too long.

Thanks for all replies

Thorsten
Post #746075
Posted Thursday, July 2, 2009 3:46 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #746095
Posted Thursday, July 2, 2009 7:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 8, 2014 5:24 AM
Points: 43, Visits: 265
Here are the needed Informations:

CREATE TABLE [dbo].[Codes](
[Code] [char](10) NOT NULL,
CONSTRAINT [PK_Codes] PRIMARY KEY CLUSTERED
(
[Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

-------------------------------------------------------------------------------------------------
THE STORED PROCEDURE

ALTER PROCEDURE [dbo].[CreateCodes]
-- Add the parameters for the stored procedure here
@amount bigint
AS
BEGIN

SET NOCOUNT ON

declare @tempcode as nvarchar(100)
declare @counter as bigint

set @counter = 1
while @counter <= @amount
begin
set @tempcode = (SELECT [dbo].[RNGCharacterMask] (8 ,'34679CDFGHJKLNPRTVXY') )
if ((select count(code) from codes where code = @tempcode) = 0)
begin
insert into codes (code) values (@tempcode)
set @counter = @counter + 1
continue
end
else
continue
end

set nocount off

END
-----------------------------------------------------------------------------------------------
The Stored Procedure calls a User defined function which generates the codes. But this ist not
the reason why the performance ist so bad.

In addition I add the excecution plan as txt file. and I think the most performance is consumed by the insert statement. But take a look and give me your feedbacks.




  Post Attachments 
excecutionplan.txt (27 views, 21.86 KB)
Post #746225
Posted Thursday, July 2, 2009 7:15 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
What are you trying to achieve here? What is this function supposed to do and why?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #746233
Posted Thursday, July 2, 2009 7:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 8, 2014 5:24 AM
Points: 43, Visits: 265
Hey,

in the data base we have codes which will printed on packages.

We have two things to do:

First we have the generate new codes in some interfalls. Generating means between 10 and 50 million new codes.

the other thing is to query the database to get a code which is not used, the state for used will be insert in the future.

I hope I can clearify your question.

greetings
Post #746239
Posted Thursday, July 2, 2009 7:24 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
Can you post the code for that function? Running a function repeatedly in a loop is not the fastest way of doing things, but if I put the function into a set-based insert, it'll still be run repeatedly.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #746243
Posted Thursday, July 2, 2009 7:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 8, 2014 5:24 AM
Points: 43, Visits: 265
I can not give you the code of the function
because of security reasons....

Only as much the function generates a code with a security cryption provider.


But I have one Problem because I have to ensure that exact
the amount of codes is generate.

You understand.
Post #746263
Posted Thursday, July 2, 2009 7:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
I can possibly write a set-based alternative, but it's not going to perform much better. The function will still have to be called a couple million times and that's going to cost.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #746270
Posted Thursday, July 2, 2009 7:43 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
I have a gut-feel that this is not the best approach. What uses these codes and why can a code not be generated when it's needed rather than a few million getting generated upfront?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #746274
Posted Thursday, July 2, 2009 9:20 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:50 AM
Points: 1,949, Visits: 8,315
Scalar udfs are generally quite slow and should be avoided.
Try to isolated the issue , what is performance like if you make it a sequential count ?

Also i notice that code is used is the clustered primary key , it could be page splitting on the inserts ?




Clear Sky SQL
My Blog
Kent user group
Post #746369
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse