Twenty tips to write a good stored procedure

Question

Twenty tips to write a good stored procedure

Viewing 15 posts - 181 through 195 (of 244 total)

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

G.R.Prithiviraj Kulasingham SSCarpal Tunnel Points: 4105 More actions · Answer 1

10. WHERE clauses - In a WHERE clause, the various operators used directly affect how fast a query can run. Here are the conditional operators used in the WHERE clause, ordered by their performance.
=, >, =, <=, , !=, !>, !<
for details, refer to the article: http://msdn.microsoft.com/en-us/library/ms190276.aspx

Completely wrong. The artcile referenced talks about the presidence and not about the time taken for the operation.

Looks like this line is the cause of confusion:

The MSDN article says "When two operators in an expression have the same operator precedence level, they are evaluated left to right based on their position in the expression."

This line explains about how an expression will be evaluated and not on the performance of the article.

Cheers,
Prithiviraj Kulasingham

http://preethiviraj.blogspot.com/

jswong05 Hall of Fame Points: 3553 More actions · Answer 2

ANSI SQL92 is the other code, instead. which will work for Oracle adn other SQL-92 DBs. Microsofter always uses tactics to push other people away, reader beware. :hehe:

Jason
http://dbace.us
😛

jswong05 Hall of Fame Points: 3553 More actions · Answer 3

ANSI SQL92 is the other code, instead. which will work for Oracle adn other SQL-92 DBs. Microsofter always uses tactics to push other people away, reader beware.

Jason
http://dbace.us
😛

jpendergraft SSC Rookie Points: 49 More actions · Answer 4

17. CREATE TABLE vs. SELECT INTO - Select * INTO works fine for small tables, but when dealing with large record sets or long-running queries, it creates locks on the system objects within the tempdb database. As a result, other queries and procedures that need to create objects within the tempdb database will have to wait for the long-running query to complete. This is because when an object is created, an exclusive lock is taken against the sysobjects, syscolumns, sysindexes tables.

What would the impact be if the select into was SELECT Top 0 * INTO FROM

to create the table struction and then do an insert into it?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Paul White (8/13/2009)
In the most recent build of 2005 and 2008, I would tend to allocate a number of equal-sized files to tempdb (maximum four), and leave the trace flag off.

Agreed. It's very hard to hit major TempDB SGAM contention in 2005 and above. I generally recommend starting with files = 1/4 the number of cores (min 2, max 8), watching for a while and if there's still contention double the number of files. Monitor again and if there's still contention, double the files again. I haven't seen a case where it was necessary to go to more files than 1/2 the number of cores, and that was a 12 processor server with massive Temp table usage.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

jswong05 (8/13/2009)
ANSI SQL92 is the other code, instead. which will work for Oracle adn other SQL-92 DBs. Microsofter always uses tactics to push other people away, reader beware. :hehe:

Oh, so neither Oracle nor mySQL have any non-SQL92 extensions to the SQL language?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

jpendergraft (8/13/2009)
What would the impact be if the select into was SELECT Top 0 * INTO FROM
to create the table struction and then do an insert into it?

The same slowness as just creating the table separately. It's totally not necessary.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

jswong05 (8/13/2009)
ANSI SQL92 is the other code, instead. which will work for Oracle adn other SQL-92 DBs. Microsofter always uses tactics to push other people away, reader beware. :hehe:

ANSI code may be fine for simple GUI interface stuff... but it's pretty lame compared to the extensions available in both SQL Server and Oracle. Except for the simple GUI interface stuff, code portability especially ,for high speed complex batch code, is just going to make for some slow code. True portability is a myth.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jswong05 Hall of Fame Points: 3553 More actions · Answer 9

True portability is a myth.

True, basic compatibility is expected. Nothing is 100% compatible to another thing.:-P Why don't database vendors got together and make one ANSI standard, they don't want to!!!!! They want to cut throat of each other to get ahead in the market.

Jason
http://dbace.us
😛

Andy DBA SSCommitted Points: 1776 More actions · Answer 10

John te Lintelo (8/11/2009)
Andy DBA (8/10/2009)
So my question is: Does anyone have strong recommendations (other than readability:-D) regarding LEFT JOIN versus either of the other two? Are there any circumstances in SQL Server where it might outperform them? Does it really take a 3% hit for the filter? (OK, 3 questions)
NOT IN:
SELECT * FROM Sales.Customer WHERE CustomerID NOT IN (SELECT CustomerID from Sales.CustomerAddress)
NOT EXISTS:
SELECT * FROM Sales.Customer C WHERE NOT EXISTS (SELECT 1 FROM Sales.CustomerAddress CA where C.CustomerID = CA.CustomerID)
LEFT JOIN:
SELECT * FROM Sales.Customer C LEFT JOIN Sales.CustomerAddress CA ON C.CustomerID = CA.CustomerID WHERE CA.CustomerID IS NULL
Using a LEFT JOIN will not return the same results if there are multiple CustomerAddress records that match a Customer record.

I think you may have missed the where clause in my LEFT JOIN example:w00t:. All three of these queries will return the same result set. ie Customer records without matching CustomerAddress records.

I noticed that the optimizer creates a slightly different execution plan for the LEFT JOIN query and my question was whether or not anyone knew if it would perform differently than the other two (which have identical plans).

So far only one person responded with a guess that the LEFT JOIN would make better use of indices, but that's not apparent to me when I look at the execution plan. If there is any difference, I'm guessing it's insignificant, but the only way to really know is to do some testing.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

jswong05 (8/13/2009)
True portability is a myth.
True, basic compatibility is expected. Nothing is 100% compatible to another thing.:-P Why don't database vendors got together and make one ANSI standard, they don't want to!!!!! They want to cut throat of each other to get ahead in the market.

I wouldn't want them to... competition spurs innovation.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Justa Developer SSC-Addicted Points: 423 More actions · Answer 12

Its funny, I came here looking for what I thought would be exactly this article, but it didn't end up helping me at all.

What I wanted was more of a best practices article I think -- With suggestions about error checking, commands that shouldn't be used in triggers or stored procedures, commenting code, naming conventions, performance hints, etc.

I think probably error checking would be good for an article in and of itself. Do you have special error handling stored procedures? Do they email the dba? Do they raise errors? Record errors into an error table?

I wish I did more with stored procedures in general, I'd give writing such an article a shot... Maybe I should post an idea like that to the forums, let you all rip it up until I get it right, and then we'd post it together?

Or maybe my google-foo just isn't good enough and such an article on error handling already exists?

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 13

Introductory error handling article[/url]. It includes 2000 and 2005/2008 syntax.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 14

Andy DBA (8/13/2009)
John te Lintelo (8/11/2009)
Andy DBA (8/10/2009)
So my question is: Does anyone have strong recommendations (other than readability:-D) regarding LEFT JOIN versus either of the other two? Are there any circumstances in SQL Server where it might outperform them? Does it really take a 3% hit for the filter? (OK, 3 questions)
NOT IN:
SELECT * FROM Sales.Customer WHERE CustomerID NOT IN (SELECT CustomerID from Sales.CustomerAddress)
NOT EXISTS:
SELECT * FROM Sales.Customer C WHERE NOT EXISTS (SELECT 1 FROM Sales.CustomerAddress CA where C.CustomerID = CA.CustomerID)
LEFT JOIN:
SELECT * FROM Sales.Customer C LEFT JOIN Sales.CustomerAddress CA ON C.CustomerID = CA.CustomerID WHERE CA.CustomerID IS NULL
Using a LEFT JOIN will not return the same results if there are multiple CustomerAddress records that match a Customer record.
I think you may have missed the where clause in my LEFT JOIN example:w00t:. All three of these queries will return the same result set. ie Customer records without matching CustomerAddress records.
I noticed that the optimizer creates a slightly different execution plan for the LEFT JOIN query and my question was whether or not anyone knew if it would perform differently than the other two (which have identical plans).
So far only one person responded with a guess that the LEFT JOIN would make better use of indices, but that's not apparent to me when I look at the execution plan. If there is any difference, I'm guessing it's insignificant, but the only way to really know is to do some testing.

With Oracle it is more efficient to use NOT IN than LEFT JOIN and test for IS NULL. Their optimizer uses has a very efficient way of evaluating NOT IN.

I'm not sure about SQL Server, it could be that the data will determine which is quicker.

If you are in doubt it is best to try both on real data and then take the quicker.

Justa Developer SSC-Addicted Points: 423 More actions · Answer 15

Grant Fritchey (8/13/2009)
Introductory error handling article[/url]. It includes 2000 and 2005/2008 syntax.

Oh, that is lovely, thanks Grant! I'm learning tons and tons!