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.
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 GOSET ANSI_NULLS ON GOCREATE FUNCTION GENERAKEYBOO()RETURNS VARCHAR(10)ASBEGINDECLARE @key VARCHAR(10)SELECT @key=MAX(BOOKINGCODE) FROM BOOKINGSELECT @key = CAST(ISNULL(@key,0) + 1 AS VARCHAR) WHILE(LEN(@key)<>10) SELECT @key = '0' + @keyRETURN @keyENDGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOThis 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 |
|
|
|
|