Optimization - 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 2000 Forums

Transact-SQL (2000)

Optimization

Author

Topic

sagavb
Starting Member

18 Posts

Posted - 2009-05-19 : 22:39:34

Hi friends,

What are things that need to be taken care to create a optimised stored procedure.

Saga

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-19 : 23:07:56

The question is too general. It can be anything under the sun. Get a book or search the internet for query optimization.

KH
[spoiler]Time is always against us[/spoiler]

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-19 : 23:24:05

While definitely too general a request, here are some guidelines you can follow right away:

1. Avoid cursors
2. Use SARG-able expressions (you can find the definition and examples online) in WHERE and JOIN clauses
3. Avoid cursors
4. Minimize JOINs wherever possible
5. Analyze query plans and compare performance while tuning your procedure. Don't just change things assuming it will improve
6. Avoid cursors
7. Keep statistics up-to-date (part of maintenance, not just optimization)
8. Whatever you do, DON'T USE CURSORS

sagavb
Starting Member

18 Posts

Posted - 2009-05-25 : 02:43:21

Thanks for the suggestion friend.

quote:
Originally posted by robvolk

While definitely too general a request, here are some guidelines you can follow right away:

1. Avoid cursors
2. Use SARG-able expressions (you can find the definition and examples online) in WHERE and JOIN clauses
3. Avoid cursors
4. Minimize JOINs wherever possible
5. Analyze query plans and compare performance while tuning your procedure. Don't just change things assuming it will improve
6. Avoid cursors
7. Keep statistics up-to-date (part of maintenance, not just optimization)
8. Whatever you do, DON'T USE CURSORS

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2009-05-27 : 14:04:17

I think robvolk wants to emphasize that you avoid cursors.

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL & VB obviously!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-27 : 14:07:39

Only use CURSORs as the very last resort when every other set-based attempt has failed.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources