Performance Querries - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Performance Querries

Author

Topic

paulnamroud
Starting Member

26 Posts

Posted - 2010-06-10 : 10:31:13

Hi all,

I need your ideas on the following issue:

I have a procedure that can work in 2 ways depending on the value of Parameter In:
- It can returns One Single Order if @p_order_id = SPECIFIC_VALUE
- It can return all Orders if @p_order_id = 0

The column ORDER_ID is a clustered primary key of the table Orders. The table has more that 300,000 records.

So my question is: Is it the good way to return all Orders ? or shall i have to create 2 different SPs:
- One it returns one single record
- And, another one that returns all Records

Here's a snapshot of my code:

create procedure [dbo].[pr_get_orders]
(
@p_order_id int
) as

Begin

Select *
From orders
Where (order_id = @p_order_id or @p_order_id = 0)

End

Thank you

Paul

Sachin.Nand

2937 Posts

Posted - 2010-06-10 : 10:34:06

How many columns does table Order has?Do you want to return all the columns in the table orders from the SP?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

paulnamroud
Starting Member

26 Posts

Posted - 2010-06-10 : 10:41:57

I need to return only 7 columns (the most important)

What does it change the number of columns ?

Paul

Kristen
Test

22859 Posts

Posted - 2010-06-10 : 11:26:07

" shall i have to create 2 different SPs"

If it is performance critical and the table is large, then yes. Otherwise you run the risk that one, or other, of the solutions is chosen as the query plan and it performs poorly for the other scenario.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-06-10 : 11:32:02

quote:
Originally posted by Kristen

" shall i have to create 2 different SPs"

If it is performance critical and the table is large, then yes. Otherwise you run the risk that one, or other, of the solutions is chosen as the query plan and it performs poorly for the other scenario.

That's probably the cleanest way to do it for sure.

If you don't want to / can't do that for legacy reasons then you can use dynamic SQL in this case. If you change the where clause to be something like:


@sql = N'.....
.....
WHERE
    1 = 1'

And then only add an extra line if @p_order_id <> 0 to be


IF @p_order_id <> 0 SET @sql = @sql + N'
    AND order_Id = @p_order_id'

Then you'll actually have a different statement in each case. As long as you then use sp_executeSql and pass @p_order_id in as a parameter then you should always get the right cached plan.

This is an example of a "catch all" query -- they are very common if the user gets to enter info into lots of search fields for example and is (in my opinion) one of the most appropriate places to use dynamic sql).

Here's some more information on dynamic sql:
http://www.sommarskog.se/dynamic_sql.html

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources