C# Tutorials and offshore development in India

Prizes & Awards

My Profile

AdSense Revenue

Active Members
Today

Last 7 Days

more...

New Feature: Community Sites: Create your own .NET community website and start earning from Google AdSense ! It's Free !

Sql Optimization

Posted Date: 30 May 2008 Resource Type: Tips Category: General

Posted By: Jessie Member Level: Gold
Rating:

Points: 3

Sql Optimization
When i was a fresher query optimization was a night mare to me. After working for quite sometime with that i got few ideas about how to optimize the query so that we gain better performance. Here are some tips. Hope the beginners will find it useful.

1) Avoid using DISTINCTclause
2) Use Co-related query
" Correlated sub queries are those in which the column in outer query is referenced in inner query. Inner query is executed once for each selected outer row."
3) Avoid using UNION. Instead try UNIONALL
4) Use Constrains instead of triggers.
5) Mention SET NOCOUNT ON in stored procedure.
6) Use stored procedure instead of writing queries because stored procedure is there in complied form.
7) Do not fetch unnecessary rows in result set and try to filter it programatically in client end. Just restrict the fetch by using where clasue.
Like that fetch only the necessary columns.
8) Keeping table in fully normalized form also sometimes complicates the query.
So be reasonable while normalizing the tables

Responses

Author: Sonu Fernandes 30 May 2008

Member Level: Gold Points : 2

Thank you,

Author: RajaSekaran R 03 Jun 2008

Member Level: Gold Points : 2

Some more points,
1.Avoid cursors in sp
2.Design table with primary key and indexes
3.use Nolock for tables
4.Do not use mulitiple inheritence at the same time.
5.if u are using loops check whther it is ending rpoperly.
6.Drop table temp table once its use is over.

Author: Jessie 04 Jun 2008

Member Level: Gold Points : 0

Thanks for the update

Author: Jessie 04 Jun 2008

Member Level: Gold Points : 0

Thanks for the update

Feedbacks

Popular Tags

What are tags ?

Search Tags

(No tags found.)

Post Feedback

This is a strictly moderated forum. Only approved messages will appear in the site. Please use 'Spell Check' in Google toolbar before you submit.
You must Sign In to post a response.

	Next Resource: KeyCode For developers
	Previous Resource: To lock your computer:
	Return to Discussion Resource Index
	Post New Resource
	Category: General

Post resources and earn money!

Related Resources

dotNet Slackers BizTalk Adaptors Web Design

web conferencing services