T-SQL Performance and Recomendations? <A good list> 3

[RovirozM]

Hi guys,

I'm new on T-SQL SP's World! I know how to create them now and I feel confortable with them....

I'm just curious about if I'm writing my code on the best performance (Clean for the SQL Performance).

I mean, for example: I was using Cursors or Temporal Tables until somebody told me that Start using "Table Type Variables" instead of that and I found a great performance on the Code!!! That was Great!

Guys, with your long experience on T-SQL, which recommendations you give to novice T-SQL programmers in order to start doing things in a correct way? Creating good performance code as the example I gave you above?

I don't know if extra Space between the code will less performance, or using IF's instead one Case, etc... I only use what I think first and it works (But not sure if this is the best...)

All this list that you gave me (or us) will be very helpful

Thanks for any comments!

MR

 

[markros]

Check this link

http://wiki.lessthandot.com/index.php/SQL_Server_Programming_Best_Practices

and you may want to explore other wiki topics on this site.

PluralSight Learning Library

 

[markros]

See also

http://www.simple-talk.com/sql/t-sql-programming/ten-common-sql-programming-mistakes/

Also quite recently there was a similar question discussed at
MSDN T-SQL forum.

PluralSight Learning Library

 

[SQLSister]

If you learn to just write code that will likely be more performant (no guarantees as much depends on the specific datbase design) instead of having to replace later, you will find it takes no longer to write code that has a better chance of perfoming better than the "easy" way to write the code. Usually the easy way is just because that is the way the dev isfamiliar with. So good for you for looking for the better way to write the code to start with.

First temp tables often perform better than table variables, it depends on the amount of data you are storing in them (larger data sets seem to do better with temp tables in my experience). Additionally you can index temp tables which can really speed things up.

Avoid cursors and replace with set-based logic. Most of the time the query used in the cursor can easily be adjusted to be an update, insert or delete query.

Try not to use views that reference views (these can be performance killers).

Try to avoid using correlated subqueries, these can be replaced with joins often or derived tables and performance can really improve.

Database design can kill you right from the start. Avoid things like key-value tables, tables that are denormalized and you will have to join to 37 times to get the correct answer because it might be in one of 37 different fields. The correct datatype can help performance too as you don't need to use alot of manipulation every time you query to see what you need to see. Don't try to do math on a varchar field, store the data as integer or decimal. Don;t store long comma delimted strings in one field, you can guanatee you will need to seach for one of those values, so split them out to a related table.

Read up on sargabiliy. It can make a huge difference in how you design queries from the start to understand how the database determines if it will even use an index.

Never select more columns than you need. Don't use select * on production databases either. Why waste server and network resources for no reason.

Is anyone honestly going to look through all the records of that query that returns a million results? Consider requiring additional filters if your queries return too many records for people to deal with (this is unfortunately not true when doing reporting.)

Never develop on a dev database with significantly fewer records than prod. You really don't want to find out that the search which was lightning fast on dev times out on prod and now no one can search and fixing it is URGENT!!!!!


"NOTHING is more important in a database than integrity." ESquared

 

[gmmastros]

I agree with everything SQLSister said except about the table variables. It's true that temp tables usually perform better than table variables when there are a lot of records. But the definition of A LOT is wiggy and varies. Most importantly though, you can index a table variable. You don't have as freedom as you do with temp tables, but it can be done. You see, you can declare a primary key on a table variable, and PK's are indexed.

Declare @temp table(col1 int, col2 int, col3 int primary key(col1,col2))


-George

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