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
 problem in calling functions: it becames very slow

Author  Topic 

snaso
Starting Member

21 Posts

Posted - 2004-06-23 : 05:37:55
Hi All,
I've got a problem with SQL Server 2000 user defined functions.
Here follows the code:

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

This function is used before to insert a new row in a table (booking) and gives the new value of the primary key bookingcode (it's a varchar(10) field with format: 0000000001, 0000000002, 0000000003, ...)
When you call this function it takes 0 seconds. Some times (say every one or two weeks) calling this function requires more than 1 minute (!).
The only way I have found for resolving this situation is to re-compile the function. It takes more than 1 minute but it works.

The DB is vlarge (15GB data file) and it's running on a 4 Xeon with 16GB RAM.

Can anybody help me? Many thanks to evrybody is reading.



-snaso.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-06-23 : 05:44:47
This is a double posting!
Please don't do that.

I have replied to the first one here.
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=36585


Duane.
Go to Top of Page
   

- Advertisement -