Problems calling functions: very SLOW

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Problems calling functions: very SLOW

Author

Topic

snaso
Starting Member

21 Posts

Posted - 2004-06-23 : 05:30:20

Hi All.
I've got a web based application that uses a vlarge DB (15GB data file!) with many user defined functions and stored procedures.

The primary key of every table is a varchar(10) field:
0000000001
0000000002
0000000003
...
When I am to insert a new value in a table I call a function that takes the maximum primary key of a table (casting it to int) then adds 1 and convert it back to the varchar format(by adding the correct number of 0s) and return this new value:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION GENERAKEYBOO()
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @key VARCHAR(10)
SELECT @key=MAX(BOOKINGCODE) FROM BOOKING
SELECT @key = CAST(ISNULL(@key,0) + 1 AS VARCHAR)
WHILE(LEN(@key)<>10)
SELECT @key = '0' + @key
RETURN @key
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Some times (say every one or two weeks) calling one of these functions (there's one for each "main" table of DB) requires a great amount of time (say a minute, while it usually requires 0 seconds) so the ASP code goes ahead without the needed code and the system crashes.

The only solution I've found is to re-compile one (apparently randomly choosen) of these functions (it takes more than one minute) and it goes ok for another one or two weeks.

Does someone of you know what's happening? Why these functions some time became so slow? I thanks everybody is reading, bye bye.

-snaso.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-06-23 : 05:42:07

Why don't you just make the column and identity int column?

You also dont need to loop until you have 10 characters.
In fact you don't even need a udf

Try something like this:
select RIGHT('0000000000' + LTRIM(STR(max(bookingcode) + 1)), 10)
from booking

Duane.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-06-23 : 17:45:18

You can update BOOKING in the same statement that retrieves the next key. You should get rid of the function alltogether.

update BOOKING SET @key = BOOKINGCODE = RIGHT('0000000000' + LTRIM(STR(BOOKINGCODE) + 1), 10)

A 15GB DB with many functions and procedures is pretty large for such inefficient code to have ever passed QA to begin with. Anything else you might have been wondering about lurking inside there someplace?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-23 : 18:25:30

Just as an fyi, functions that do loops or selects from tables are REALLY, REALLY bad ideas inside the SELECT list. If you have 80k rows for example, you would have to loop through 80k x number of loops. Tables are even worse. You're actually running that select 80k times. I would have a rather large review of your code. Does that make sense?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-06-23 : 18:35:07

quote:

The primary key of every table is a varchar(10) field:
0000000001
0000000002
0000000003

??? WHY ??? why would you do this? why not use an indentity with an int column? you can always display it with zeroes padding it, but let SQL internally store it as an int and let SQl handle the incrementing and all that.

Better yet, learn some database theory and how to determine more natural keys for tables in your database.

- Jeff

snaso
Starting Member

21 Posts

Posted - 2004-07-13 : 03:43:58

Hi All,
I thank you for all answers (I've not been able to work at my DB for a few days...).

I'm not able at this stage to change PKs, I've to keep tham like this (I hope to have time in the future for changing them).

About the WHILE loop inside functions, thank you for the suggestions. I will change every function and I will let you know if the weird problem (functions became slow untill I don't recompile one of them....) disappear.

Ah, another thing: please, before being so hard with me, take into account I'm facing DB problems from last august.... I'm learning faster as I can!

Snaso

-snaso.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-13 : 07:01:47

We don't mean to be harsh. We just want to see you get the issues taken care of. You'd be surprised at some of the things we see on here though. :)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources