Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Performance Question Expand / Collapse
Author
Message
Posted Thursday, July 2, 2009 9:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:42 PM
Points: 10,340, Visits: 13,341
Dave Ballantyne (7/2/2009)
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 ?


Ouch, you can pretty much be sure there are page splits happening in a million row insert.

Like Gail has said, without knowing what the code is in the function there really isn't a way to make it faster because the UDF will still run a million times.

Can you at least give us some pseudocode for the UDF like:

Take the second parameter passed in

Loop through it by character, multiplying the the ASCII Code by the first parameter and if > 255 substract 255 and place that character in the 10 character string.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #746393
Posted Thursday, July 2, 2009 9:41 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:32 PM
Points: 20,798, Visits: 32,714
You said you can't post the code for the UDF, but could you post code for the UDF with a faked process? It just has to show us how it processes the data passed without showing us the actual details. I hope that makes some sense.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #746395
Posted Thursday, July 2, 2009 9:01 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 7:53 PM
Points: 9,928, Visits: 11,194
scziege (7/2/2009)
The stored procedure calls a user-defined function which generates the code. But this is not
the reason why the performance is so bad.

I would encourage you to test that. The script below will allow you to see how expensive the function call is. It is set up to call a test function that doubles a number. The function is executed one million times per run, and there are five runs. You get summary performance statistics at the end. There are comments to explain what it is doing, and where to plug your private function in.

Paul

use tempdb;
go
-- Holds numbers 1 to 1 million
create table [#418BC2CC-48A9-4BCF-9E18-B8FB7BE9D446]
(
N BIGINT PRIMARY KEY CLUSTERED,
);

-- Generate numbers
with Numbers (N)
as (
select top (1000000)
ROW_NUMBER() over (order by (select null))
from master.sys.columns C1, master.sys.columns C2, master.sys.columns C3
)
insert [#418BC2CC-48A9-4BCF-9E18-B8FB7BE9D446] with (tablockx) (N)
select N
from Numbers;
go
-- Test function - just doubles the input
create function dbo.[sfn_7125CD1E-CF5A-4386-B82C-CF52AC756A71] (@number bigint) returns bigint with schemabinding as begin return @number * 2; end;
go
-- Clear ad-hoc sql plans (warning: server-wide, run on test only!)
dbcc freesystemcache(N'SQL Plans');
go
-- Run function one million times - substitute your function here!
declare @BitBucket BIGINT;
select @BitBucket = dbo.[sfn_7125CD1E-CF5A-4386-B82C-CF52AC756A71] (N)
from [#418BC2CC-48A9-4BCF-9E18-B8FB7BE9D446];
go 5
-- Show test results
select statement_text = SUBSTRING([text], statement_start_offset / 2, (case statement_end_offset when -1 then DATALENGTH(text) else statement_end_offset end) / 2),
sample_size = execution_count,
[avg elapsed time µs] = total_elapsed_time / execution_count,
[avg elapsed time sec] = CONVERT(DEC(9, 3), total_elapsed_time / execution_count / 1000000.0),
[avg cpu time µs] = total_worker_time / execution_count,
[avg cpu time sec] = CONVERT(DEC(9, 3), total_worker_time / execution_count / 1000000.0),
[avg logical reads] = total_logical_reads / execution_count
from sys.dm_exec_query_stats as qs
cross
apply sys.dm_exec_sql_text (qs.[sql_handle]) as st
where st.[text] like '%@BitBucket%[#418BC2CC-48A9-4BCF-9E18-B8FB7BE9D446]%'
and st.[text] not like '%dm_exec_query_stats%';
go
-- Tidy up
drop table [#418BC2CC-48A9-4BCF-9E18-B8FB7BE9D446];
drop function dbo.[sfn_7125CD1E-CF5A-4386-B82C-CF52AC756A71];
go





Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #746736
Posted Thursday, July 2, 2009 9:21 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 7:53 PM
Points: 9,928, Visits: 11,194
scziege (7/2/2009)
In addition I add the execution plan as txt file. I think the most performance is consumed by the insert statement. But take a look and give me your feedback.

You should disregard the estimated cost percentages when looking at plans - they are wildly inaccurate much of the time (for good reasons).

For example, the call to your function ([Expr1000] = Scalar Operator([NestleDECodes].[dbo].[RNGCharacterMask]((8),N'34679CDFGHJKLNPRTVXY'))) is costed at 0.0000001 units (0%) since the server has no way to know how expensive the function call is in reality.

Anyhow, as I hope you will now be able to test for yourself, the function call is probably over 99% of the cost of a real execution run.

A few small points:

IF NOT EXISTS (SELECT * FROM dbo.Codes WHERE code = @tempcode)
...is frequently more efficient than...
IF (SELECT count(code) FROM codes WHERE code = @tempcode) = 0

You should be careful to match data types. That lookup on code = @tempcode is less efficient than it could be due to an implicit conversion:
CONVERT_IMPLICIT(nchar(10),[NestleDECodes].[dbo].[Codes].[Code],0)=[@tempcode]
(The Code column is defined as CHAR(10) not NCHAR(10))

The SET NOCOUNT OFF at the end of the procedure is pointless. SET NOCOUNT reverts to the setting in the outer scope when the procedure returns anyway.

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #746742
Posted Friday, July 3, 2009 12:03 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 40,385, Visits: 36,827
Paul White (7/2/2009)

You should disregard the estimated cost percentages when looking at plans - they are wildly inaccurate much of the time (for good reasons).


And they are always wildly inaccurate when there are scalar UDFs involved, because SQL estimates 0% for the udf execution, regardless of what it does.



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 #746796
Posted Wednesday, July 8, 2009 1:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 8:13 AM
Points: 43, Visits: 272
Thank's for all the good hints:

I modified my UDF an now I can show the code:


public static SqlString RNGCharacterMask(int codesize, string characters)
{
int maxSize = codesize;
int minSize = codesize;
char[] chars = new char[20];
string a;
a = characters;
chars = a.ToCharArray();
int size = maxSize;
byte[] data = new byte[1];
RNGCryptoServiceProvider crypto = new RNGCryptoServiceProvider();
crypto.GetNonZeroBytes(data);
size = maxSize;
data = new byte[size];
crypto.GetNonZeroBytes(data);
StringBuilder result = new StringBuilder(size);
foreach (byte b in data)
{ result.Append(chars[b % (chars.Length - 1)]); }
return result.ToString();
}

Hope that clearify my problem a little bit.

Thank's in advance
Post #749084
Posted Wednesday, July 8, 2009 1:37 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
Ouch.

I would modify the CLR Udf to returns a data set of '@amount' Codes and store those to a temp table.
Then delete duplicates with the codes table and re-execute the UDF returning the rowcount from the delete.

Repeat until there are no duplicate rows.

This will minimize the amount of round trips to the udf , plus the SQL logic is much more set based.
Ive never used the RNGCryptoServiceProvider class but you may only have to have one instance of if it if GetBytes returns a different value at each call.

As another thought though does all this function do return a string of variable(edit: random) characters from '34679CDFGHJKLNPRTVXY' ?

If so a pure TSQL solution should be pretty simple
Post back if this isn't clear




Clear Sky SQL
My Blog
Kent user group
Post #749089
Posted Wednesday, July 8, 2009 3:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 8:13 AM
Points: 43, Visits: 272
Can you please provide me a sample TSQL
Application which replaces the UDF Function ?

ThanK's
Post #749106
Posted Wednesday, July 8, 2009 3:37 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
try this , ill be interested to hear how it performs in comparison.
Note that the case statement on the call to GetRandomCode is required otherwise you will get the same value returned.

On my dev server it creates 15,000,000 rows in about 11 mins, cutting down the size of the union to your limited set should improve matters.

CREATE TABLE dbo.Numbers (Num INT NOT NULL PRIMARY KEY CLUSTERED);
GO
DECLARE @i INT;
SELECT @i = 1;
WHILE @i <= 10000
BEGIN
INSERT INTO dbo.Numbers(Num) VALUES (@i);
SELECT @i = @i + 1;
END;
Create View VwNewId
as
Select New_Id = newid()
go
Drop Function GetRandomCode
go
Create Function GetRandomCode(@CharsNeeded integer)
returns table
as
return
(
with cteCharsNeeded(Character)
as(
select '1' union all select '2' union all select '3' union all select '4' union all select '6' union all select '7' union all select '8' union all select '9' union all Select '0' union all
select 'A' union all select 'B' union all select 'C' union all select 'D' union all select 'E' union all select 'F' union all select 'G' union all select 'H' union all Select 'I' union all
select 'J' union all select 'K' union all select 'L' union all select 'M' union all select 'N' union all select 'O' union all select 'P' union all select 'Q' union all Select 'R' union all
select 'S' union all select 'T' union all select 'U' union all select 'V' union all select 'W' union all select 'X' union all select 'Y' union all select 'Z' union all
select 'a' union all select 'b' union all select 'c' union all select 'd' union all select 'e' union all select 'f' union all select 'g' union all select 'h' union all select 'i' union all
select 'j' union all select 'k' union all select 'l' union all select 'm' union all select 'n' union all select 'o' union all select 'p' union all select 'q' union all select 'r' union all
select 's' union all select 't' union all select 'u' union all select 'v' union all select 'w' union all select 'x' union all select 'y' union all select 'z'
),
ctenumbers(num)
as
(
Select Num from numbers
where Num <= @CharsNeeded
),
cteRandomChars(num,c)
as
(
select num,c = chars.c
from ctenumbers cross apply (select top 1 Character from cteCharsNeeded,vwNewId where num = num order by new_id) as chars(c)
)
select (
select c as [text()]
from cteRandomChars
for xml path('')) as Random

)
go
drop table #res
go
create table #res
(
random char(8)
)
insert into #res
select Random from sysobjects cross apply GetRandomCode(case when id is not null then 8 else null end)






Clear Sky SQL
My Blog
Kent user group
Post #749119
Posted Wednesday, July 8, 2009 5:30 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
Ok give this a try...

1,000,000 random strings in 24Seconds

CREATE TABLE dbo.Numbers (Num INT NOT NULL PRIMARY KEY CLUSTERED);
GO
DECLARE @i INT;
SELECT @i = 1;
WHILE @i <= 1000
BEGIN
INSERT INTO dbo.Numbers(Num) VALUES (@i);
SELECT @i = @i + 1;
END;
go
update statistics Numbers with fullscan
go

drop View VwNewCheck
go
Create View VwNewCheck
as
Select abs(checksum(NewId())) as New_Id
go




Drop Function GetRandomCode
go

Create Function GetRandomCode(@StrLen integer,@CharsNeeded char(62))
returns table
as
return
(
with cteRandomChars(num,c)
as
(
Select Num,substring(@CharsNeeded,(Select VwNewCheck.new_id%(len(@CharsNeeded)-1)+1 from VwNewCheck where num = num ),1)
from numbers
where Num <= @StrLen

)
select (
select c as [text()]
from cteRandomChars
for xml path('')) as random
)
go

create table #random
(
random char(8)
)
insert into #random
select top 15000000 Random from sysobjects cross apply sysobjects so1 cross apply GetRandomCode(case when so1.id is not null or sysobjects.id is not null then 8 else 0 end,'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789')
select @@rowcount





Clear Sky SQL
My Blog
Kent user group
Post #749154
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse