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 !

How to improve Performance hit in SQL

Posted Date: 17 May 2008	Resource Type: Articles	Category: Databases
Author: Pramod N. Pawar	Member Level: Gold
Rating:	Points: 10

To exectute the Sql string SQL provieds exec() or sp_executesql statement from which sp_executesql is the better choice. It re-use previous plans that match the exact query.It also has the advantage of supporting output parameters so you can return info from the dynamic session back into the calling stored procedure.

It is always better to use stored procedure than building the query on the client.

Following are the advantage to Use SProc and reverse of this could be diasvantage of SQL Query :
> Gives the benefits of a compiled query plan.
> It saves that plan so that when you re-run the query, you don't have to figure out the plan all over again - saving you time and server expense.
> It prevents the opportunity for a sql injection attack.
> Security

Putting data access code in a stored procedure allows you to change the behavior without recompiling the application.

In my opinion once you decide to use dynamic sql, it should be built on the client. Because the string handling is usually better in Application level( any programming language) and you can usually build/debug/test faster and easier in application code.

Happy Programming.

Responses

Author: Mahesh Raj 07 Jun 2008

Member Level: Gold Points : 1

This is very good information,Continue posting such useful articles.

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: Group By Functions in SQL Server Part 1
	Previous Resource: How to get the different date time formats
	Return to Discussion Resource Index
	Post New Resource
	Category: Databases

Post resources and earn money!

Related Resources

dotNet Slackers BizTalk Adaptors Web Design