December 6, 2005 at 8:49 am
I just have a question relating to the general practice of seperating sql statements from program logic and using stored procedures.
I am trying to keep all of my sql logic outside of my main winforms app by using stored procedures. However, I have the need to execute a simple statement such as:
SELECT DICTINCT [IssueType] FROM [SomeTable]
If I put this statement into its own stored procedure is that using more resources to execute rather than just passing the statement as commandtext in my app. I don't want to pass it as command text in the app because I feel that it would totally nullify the seperation of sql logic and app logic that I am trying to achieve.
What are the resource costs and advantages associated with stored procedures?
This may be super dumb but I had to ask.
Thanks,
Paul
December 6, 2005 at 9:52 am
Putting your code in stored procs is best practice.
Putting your SELECT DISTINCT into CommandText means that you have to grant read access to your table therefore someone with a valid login could execute any select statement that they want on your table. If I was malicious I would try and crash your server by doing all sorts of self joins. If you don't have a query cost governer set then you could kiss your server goodbye!
You are saving a few bytes of network traffic by having getIssueType as your command text rather than SELECT DISTINCT etc. How big an issue this might be will depend on your particular set up and the number of hits this particular procedure has.
In terms of performance the first time you hit the page with SELECT DISTINCT the query will have to be interpretted, compiled, executed. If you are using .NET then the query will be run using the sp_executeSQL stored procedure so subsequent calls will be fast.
For stored procedures the first call compiles and stores the execution plan so subsequent calls (for your example) will always use that execution plan.
On the subject of dynamic SQL I came across a performance problem caused by drivers passing unicode parameters to tables that use ASCII values. There is an article on the sql-performance.com site detailing this specific problem with JDBC drivers.
If you have a table with an indexed text field and you say SELECT FROM MyTable wHERE ='SomeValue'you get an Index Seek.
If the dynamic SQL gets through the driver as SELECT FROM MyTable wHERE =N'SomeValue'you get an Index Scan thereby introducing an unexpected performance hit.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply