Performance problem with Parameters

[Anitivirus]

Hi,

I have a user defined function which has 5 parametes to pass, now while running the UDF using hardcoded parametes value its taking only 10 seconds , but while running the UDF by declaring the parametes value and

assigned them value its taking more than 3 minutes.



here is the example



First case



select * from UDF_Test('A',76,'C',987,''X')



This one taking only 10 minutes to run.



Second case



Declare @P1 varchar,

@P2 Int,

@P3 varchar,

@P4 int,

@P5 varchar



SET @P1 = 'A'

@P2= 76

@P3='C'

@P4 = 987

@P5='X'



Select * from UDF_Test(@P1,@P2,@P3,@P4,@P5)



Any help will be appreciated.



Thanks

 

[ca8msm]

And what does "UDF_Test" do? One potential thing that springs to mind could be parameter sniffing but that's just a guess.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[gmmastros]

Can you post the code for the UDF? It may help us to help you.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I don't know if this is causing your problem or not, but...

In your example, all of the string parameters are single character strings. As such, your bad code should probably still work. However, if this was just an example and the data is different than what you are showing, it could account for the problem you are experiencing.

Anyway... The problem I see with your code is that you are declaring varchar's without specifying the size of them. When you declare a varchar without specifying the size, it defaults to 1 character. As you can see from this example...

[COLOR=blue]Declare[/color] @Blah [COLOR=blue]VarChar[/color]

[COLOR=blue]Set[/color] @Blah = [COLOR=red]'Hello World'[/color]
[COLOR=blue]Select[/color] @Blah

Output...

[tt][blue]
----
H

(1 row(s) affected)[/blue][/tt]

I strongly encourage you to get in the habit of [!]Always[/b] specifying the size of your varchars.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Anitivirus]

Thanks George , the example I have given is just the example where i just tried to give the concept of my problem, in my original query the size of the variables are declared arrording to the numbers of characters and the value is assigned here is just an example.

 

[ESquared]

I would look at the execution plan differences between the two queries as the first step to resolve it. If the UDF's work doesn't show up in the execution plan, make a stored procedure version of it and try that. Or you might have to just run the statements from the UDF inline, modifying them so they'll work (such as removing RETURN and putting in a PRINT so you can see what it is doing).

A trace can also be instructive.