Dynamic Parameter in SP

Question

Dynamic Parameter in SP

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

dj_meier SSCrazy Points: 2732 More actions · Answer 1

I would have to agree with Antares' last comment, that writing a master/slave style proc with all possible combinations will give you the best performance. This can add up to substantially more work, in my case 28 sub procs, but the performance and reliability more than make up for this. Sometimes I've found with dynamic SQL the server won't "realize" it needs a new execution plan, so it uses the cached plan and performance suffers greatly.

-Dan

Henmi Ten Centuries Points: 1015 More actions · Answer 2

I'm still curious about this

I rerun again the coalesce version and watch its execution plan carefully and indeed it uses the index. However, I'm really confuse why most people here said the coalesce version would suffer the performance when it's executed against large table. Any reasonable reason?

BTW, I'm pretty sure that Antares686's solution is really affordable but it'd take more coding tasks. I'll go for that until I got a reason why the colesce version could hurt performance, then I have no choice.

Any help?

Thanks in advance

Hendry

rprice Old Hand Points: 303 More actions · Answer 3

It might be worth looking at how this routine is used in the real world before coding out a load of routines which will be used 1% of the time. If there is a particular pattern of use, and there usually is, code it so that for that specific uses an optimized and cached procedure is used. For the rest, use a basic routine. You can use profiler to collect information about what paramaters are being passed if it's currently in prodution. If it's not in production, I'd start with a basic routine and as you learn more about the use and which combination of parameters slow it down, code up the specific cases.

well0549 SSCrazy Points: 2259 More actions · Answer 4

I have tested this a lot of times. And i saw that for a stored proc with 10 paremeters there is NEVER a good plan.

Using the EXEC SP_EXECUTESQL variant always creates a new QUERY plan optimized for that query.... If the queryplan is not optimal it wouldn't be optimal for the coalesce version either........ ( or am I wrong here )

The only overhead the SP _Execute SQL will have is building the Execution plan.... This takes about 30 millisecs on my server. But the resulting query is so much faster....

Only if I execute the same query 4 or 5 times the coalesce version will be faster. But if i run the query several times with every parameter filled ( a different one each time) , the SP_ECECUTESQL version is much faster.

So try this not with 2 or 3 paremeters but with more parameters. And if you would have 8 or 9 parameters Creating 9 different procedures will be faster. But if you have 9 parameters you should have effectively 81 different queries..... So that is a lot of maintenance

with this variant the maintenance will be minimal

Edited by - well0549 on 12/09/2002 05:11:22 AM

Henmi Ten Centuries Points: 1015 More actions · Answer 5

well0549, Thanks very much for your helpful feedback. I found sp_ExecuteSQL useful too.

Now, I think I need to test all the version in any possible cases (as stated by rprice) so that I'll know which one is actually the best under which circumstances.

Nothing's perfect, Right?

Hendry

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 6

Antares66

With respect to coalesce your example misses the point. Using coalesce on static variables is fine, but linking back to a column is the problem. Have a look at this only the last statement uses any index for filtering

declare @fn varchar(100)

declare @ln varchar(100)

set @fn = 'Nancy'

set @ln = NULL

select *

from northwind.dbo.employees

where firstname = coalesce(@fn, firstname)

AND lastname = coalesce(@ln, lastname)

set @fn = NULL

set @ln = 'Davolio'

select *

from northwind.dbo.employees

where firstname = coalesce(@fn, firstname)

AND lastname = coalesce(@ln, lastname)

set @fn = NULL

set @ln = 'Davolio'

select *

from northwind.dbo.employees

where firstname = @fn

OR lastname = @ln

set @fn = NULL

set @ln = 'Davolio'

select *

from northwind.dbo.employees

where lastname = @ln

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

Antares686 SSC Guru Points: 125444 More actions · Answer 7

Thanks Simon, I beleive the answer COALESCE may or may not utilize an index depending on the scenario of involved.

If against multiple values (column or data) compared to a single column it generally will.

If it is against a single value (data not so much column unless in another table) to multiple columns will most likely not utilize an index.

well0549 SSCrazy Points: 2259 More actions · Answer 8

So could we conclude to say.....

If you need to have Dynamic parameters in your sproc

keep the following in mind :

1. If there are a few parameters use seperate stored procedures for

each query. Make the SPROC so that it calls a specific stored procedure

that executes that specific query. Never have multiple different

queries in one SPROC

2. If there are many optional paremeters it is preferable to use Dynamic

SQL insead of a coalesce variant.

3. If because of security reasons Dynamic SQL is not an option use coalesce

or code all the stored procedures......

Or am I completely wrong here ?

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 9

If you get to point 3 then consider the following,

You probably (should) have 5 or 6 main filtering options, that should always be specified. Code this as fixed conditions and then add others as optional i.e

WHERE firstname = @firstname

AND (gender = coalesce(@gender,gender)

OR surname = coalesce(@surname,surname))

This will at least one index and thus save a table (or clustered index) scan.

The other point to remember when a developer says "I'm not doing that cos it will take to long, it runs instantly anyway why should I", they probably have a table with 100 customers, rather than the 100,000 that will be in the live system.

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

willhaney Mr or Mrs. 500 Points: 596 More actions · Answer 10

I use IsNull instead of Coalesce, it's faster. BTW, either way, if there are indexes for the column, the indexes WILL be used.

Use Northwind

Declare

@CustomerIDnchar( 5 )

, @CompanyNamenvarchar( 40 )

Select

@CustomerID = 'ALFKI'

, @CompanyName= 'Alfreds Futterkiste'

Select

*

From

Customersc ( nolock )

Where

c.CustomerID = IsNull( @CustomerID, c.CustomerID )

And c.CompanyName = IsNull( @CompanyName, c.CompanyName )

jtrzes SSC Journeyman Points: 87 More actions · Answer 11

I prefer the using the (field = @parm or @parm is null) technique to the coalesce. I seem to recall the coalesce method giving erroneous results if the field could contain null values.

Does anyone have any ideas on how to implement the same type of logic to return all for a null parameter if the parameter is to be used in a containstable query against a full-text index? I keep getting an error and cannot figure out what to search for that will return all the rows.