September 10, 2002 at 2:55 am
I am inserting a large number of rows into a table using a SELECT INTO statement. One of the columns is being reformatted by a user defined function. e.g.
SELECT a,b,dbo.FormatDate(c) INTO TableA FROM TableB
When I execute the statement, the CPU goes to 100% utilisation, and the insert takes ages (disk light only flickers occasionally).
If I change the statement to do the re-formatting in-line e.g.
SELECT a,b,SUBSTRING(c,5,4) + SUBSTRING(c,1,2) INTO TableA FROM TableB
In this case CPU usage is minimal, the statement executes 2secs instead of 45secs.
Has anybody come across this performance degradation using a UDF before?
September 10, 2002 at 4:28 am
I have not noticed this but could be a known issue, however I keep the current SP installed and start SQL 2000 with SP1 so may have been an issue I missed. Can you post the actually UDF create statement and a value of c we can look at.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
September 10, 2002 at 5:11 am
Antares.
Unfortunately I cannot post the UDF (for security reasons - even though there is nothing secret in the functions, I'm not allowed to post the code).
However, what the UDF does is to take 2 parameters, a date and a time, and return a datetime. The input parameters are actually integers (i.e. 20020905 and 115, which needs to be converted to 2002-09-05 01:15). So all the UDF does is to convert the integers to char fields and then substring them into the correct format.
I have optimised the function to uses as few SQL statements as possible (which geve a minor improvement). I tried hard-coded values rather than variables to the function - this made no difference.
The only thing that made any difference was putting the conversion in-line.
I can illustrate the problem with a simple function using the code below:-
create function Test (@val1 integer) returns char(10)
as
begin
declare @ret char(10)
set @ret = convert(char(10),@val1)
return @ret
end
I have then replicated the contents of the "order details" table in the Northwind database into a table called TestTable to end up with a table with 500,000 rows.
I then execute the following statements to populate some other table from TestTable:-
select dbo.test(orderId) as OrderId,productId,UnitPrice into Test1 from TestTable
select convert(char(10),orderId) as OrderId,productId,UnitPrice into Test2 from TestTable
The first method takes 116secs, the second method takes 2secs.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply