Performance Question

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » Performance Question

51 posts, Page 1 of 6

1 2 3 4 5 »»»

Performance Question

Rate Topic

Display Mode

Topic Options

Author

Message

scziege

Posted Thursday, July 02, 2009 3:11 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 25, 2013 3:10 AM
Points: 43, Visits: 203

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

GilaMonster

Posted Thursday, July 02, 2009 3:46 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 37,660, Visits: 29,911

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

scziege

Posted Thursday, July 02, 2009 7:07 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 25, 2013 3:10 AM
Points: 43, Visits: 203

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

GilaMonster

Posted Thursday, July 02, 2009 7:15 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 37,660, Visits: 29,911

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

scziege

Posted Thursday, July 02, 2009 7:21 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 25, 2013 3:10 AM
Points: 43, Visits: 203

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

GilaMonster

Posted Thursday, July 02, 2009 7:24 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 37,660, Visits: 29,911

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

scziege

Posted Thursday, July 02, 2009 7:35 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 25, 2013 3:10 AM
Points: 43, Visits: 203

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

GilaMonster

Posted Thursday, July 02, 2009 7:41 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 37,660, Visits: 29,911

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

GilaMonster

Posted Thursday, July 02, 2009 7:43 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 37,660, Visits: 29,911

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

Dave Ballantyne

Posted Thursday, July 02, 2009 9:20 AM

SSCommitted

Group: General Forum Members
Last Login: Friday, May 10, 2013 4:07 PM
Points: 1,943, Visits: 8,227

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 »

51 posts, Page 1 of 6

1 2 3 4 5 »»»

Permissions