proc best practice/performance question

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)

proc best practice/performance question

Author

Topic

chrispy
Posting Yak Master

107 Posts

Posted - 2007-07-07 : 16:06:32

I have a complex SP that has two slightly different requirements from the developers. Rather then create another SP I decided to 'mode' the current SP. In simple terms, like this:


CREATE thisproc
    @mode int

AS

SELECT a
INTO #temp
FROM b

IF (@MODE = 1)
BEGIN
    SELECT x
    FROM #temp
END

IF (MODE = 2)
BEGIN
    SELECT y
    FROM #temp
END

Now granted the SELECTS are not complex above but in the actual SP I am calling functions for each select above.

Does this effect performance that much? I understand that SQL must compile the SP when called, but is it something to be concerned about?

I ask as I am trying to tune the SP even further and wondering if breaking the out into separate SPs would outweigh the benefit of the simplicity to the developer.

Thanks!

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-07 : 21:38:05

Sql doesn't compile sp every time called unless you put 'with recompile' in it.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-07 : 22:02:44

If the recordset that goes into #temp is not huge (<100 rows etc) perhaps using a table variable might help. Are you going to use any dynamic SQL? You might also want to check your query plan and see if there are any bookmark lookups or table scans.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

chrispy
Posting Yak Master

107 Posts

Posted - 2007-07-09 : 18:05:29

rmiao,

Now that I think of it I knew that. I guess I was so caught up in trying to improve the sproc that I forgot.

Thanks!!!

chrispy
Posting Yak Master

107 Posts

Posted - 2007-07-09 : 18:06:33

dinakar,
Yes it could be up to 42,000 in the temp table (zip codes), so the table var is out I would assume.

Thanks!

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-09 : 19:19:03

If you are doing any further querying on the temptables in the WHERE clause, adding index to the the temp tables can also help.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Kristen
Test

22859 Posts

Posted - 2007-07-10 : 03:27:19

I would have [thisproc] exec two separate Sprocs based on IF (@MODE = ...) so that each has an optimal query plan cached.

Don't use

SELECT a
INTO #temp

precreate the table instead, with a PK definition and/or indexes, and try an @TableVar [40K record may be OK] as dinakar said. You'll need a #TempTable though if you want to have it in scope for any Child Sprocs.

Pre-create helps optimiser, but doing SELECT ... INTO means that the Table Create on TEMPDB is locked for the duration of the SELECT operation, not just the CREATE TABLE, and leads to blocking. Also, pre-create allows you to explicitly set COLLATion in the event that TEMPDB and the Database are different.

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-10 : 09:51:43

Also, if the resultset of a procedure is determined based on the parameter, then you may not design a report whose datasourse is that specific procedure

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-07-10 : 10:57:15

You need to give us more specific information. At first glance, I have two observations:

1) you shouldn't be using a temp table

2) you should use two separate stored procedures OR you should just return both columns

But that's based on what you've given us. It is difficult to give a hard and fast rule that will always apply to every situation, and it is nearly impossible to give good advice when things are so generic. I understand that you said that your code is just an example of something more complex, but try to make your example more truly and accurately indicative of what you are doing if you can. if you do that, we can provide better guidance.

But as a rule of thumb, a stored procedure should return consistent data from call to call; it should not sometimes return different resultsets with different columns names and types depending on the input. Also, picking and choosing specific columns to display is something that the client in general should do; you should not need to create a separate stored proc for every single combination of columns that clients might ever need.

- Jeff
http://weblogs.sqlteam.com/JeffS

chrispy
Posting Yak Master

107 Posts

Posted - 2007-07-10 : 11:40:33

Thanks all for the great advice. I am going to do the following

1. Create a table variable with indexes.
2. Stop the mode and combine it into one SP or break it out into two.

Mode 1 was returning such (simplified)


LocName   JobID       B     C         
-------    ---        --   --       
Wendys      1          g    g
Wendys      9          j    k
Wendys      7          i    k
Subway      1          k    j 

While Mode 2 would use a distinct on LocName and drop two columns :

LocName   JobID             
-------    ---            
Wendys      1     
Subway      1

So as I am learning here, even if I left the two additional columns on Mode 2, the Distinct will still create a different result set, thus the need to create two separate procedures. Unless there is another way.

Thanks all!

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-10 : 11:43:52

quote:
Originally posted by chrispy
1. Create a table variable with indexes.

Not possible. You can only create indexes on temp tables. You can create a primary key (and thus clustered index) on a table variable but thats about it.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-10 : 11:45:40

btw, what are you doing with the temp table here. What else are you doing after the SELECT INTO.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-10 : 11:46:15

I see that you another post here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=85465

or is that different from what you are asking here?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-07-10 : 11:51:22

One thing to note: If you are using temp tables if you declare them first the stored procedure will recompile less.

chrispy
Posting Yak Master

107 Posts

Posted - 2007-07-10 : 11:59:45

dinakar,
Funny I was getting errors / learning about indexes on a table variables as the email notification popped in. A PK it will be then.

The other post is the start of the sproc. I am more or less taking those results and using (was using) a temp table to dump those results into so I can do the paging.

This post http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86150 Has more of the sproc on it.

Edit : temp table should of been table vars

chrispy
Posting Yak Master

107 Posts

Posted - 2007-07-10 : 12:00:17

Lamprey,
Thanks. That much I am already working on. :)

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-10 : 12:18:00

As Lamprey and Tara mentioned, create the temp table at the beginning of the proc, also create the index right after. Then do your data population, to avoid recompiles.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Kristen
Test

22859 Posts

Posted - 2007-07-10 : 12:21:51

"also create the index right after"

Note that if its a @TableVar you'll need to create the PK within the definition:


DECLARE @MyTableVar TABLE
(
    Col1 varchar(10) NOT NULL,
    Col2 varchar(10),
    PRIMARY KEY
    (
        Col1
    )
)

Kristen

chrispy
Posting Yak Master

107 Posts

Posted - 2007-07-10 : 12:24:58

Kristen,
Your sample should not be any different then what I have?

DECLARE @tempa table ( RecCount int identity(1,1) PRIMARY KEY,
LocationName varchar (100),
LocID int,
Address varchar (200)
)

chrispy
Posting Yak Master

107 Posts

Posted - 2007-07-10 : 12:31:25

dinakar,
I said "temp Table" when it should of been table var. Sorry. The PK is all I can put on the table var so there I am.

With that said, the previous sproc with the temp table being created by the SELECT INTO is running about 7 to 9 ms faster then creating the table var (according to SSMS).

Worth noting; I did index the temp table after the SELECT INTO.

Kristen
Test

22859 Posts

Posted - 2007-07-10 : 12:47:04

"Your sample should not be any different then what I have?"

Yup, just personal preference / house style for "layout".

We put explicit NULL / NOT NULL on all columns, and a COLLATE on Varchar etc. and we prefer to see the PK declared separately so we can see, and influence, which columns and in which order. (We also name the PK on permanent tables, makes the name consistent across all DBs if we have to Drop it!)

But as I say that's all just personal preference. be consistent is my only mantra for Coding Style.

Kristen

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources