Jiri Matejka


Hi,

is it better to use stored procedures or queries in terms of performance I'm running application in ASP.NET, now the amount of data in the database is not very high, but I expect it'll grow, so I wonder about speed of queries etc.

Is it better to use SELECT ... FROM ... or to prepare stored procedure for such select What about insert/update/delete

thanks

Jiri Matejka




Re: Stored procedure vs. query

Jens K. Suessmeyer


This should be more a design question than only a performance one. They can also provide a benefit in terms of security encapsulation.

Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---





Re: Stored procedure vs. query

TimDBA

This is somewhat of an age-old debate. Some prefer inline sql and some prefer procedures. I prefer procedures because it keeps all your code in a place that is easy to manage, rather than in a web file, and provides security benefits, such as preventing a lot of sql injection attacks, and assignable permissions. As far as performance, you will likely see the same performance from either...once a statement is executed against the database, whether it is from a proc or ad-hoc sql, the statement will stay in cache until the db engine decides it is ok to take it out because it hasn't been used in awhile. In face, in 2005, the db engine takes it a step further for statement level recompiles, rather than entire procedure compiles.






Re: Stored procedure vs. query

Adamus Turner

Generally and suggestively speaking, a single line SELECT should be hard coded to avoid having multitudes of stored procedures and views on the server. This not only clutters the organization but becomes a maintenance nightmare over time.

If the query is several lines, a View is the optimal choice unless parameters are involved, then a stored procedure would be the optimal choice.

Also, update/insert/delete should follow the same ethic.

Just my twist on it,

Adamus






Re: Stored procedure vs. query

Arnie Rowland

From a security perspective, I would never allow an application to directly access data tables.

From my experience, all data access 'should' be done using Stored Procedures. The code base is centrally managed, and can easily be changed as system and business needs evolve. For example, imagine the impact on the application if data tables have to be re-organized for storage or speed issues. With stored procedures, it is only necessary to change the procedure code so that the same resultset is produced -the application doesn't care where the data comes from as long as it gets what is needed. But when using inline queries, any changes to the data stores can cause catastrophic failure of the application until all queries are located and changed. (From experience, believe me, over the life span of a project, there will be businss needs to change where/how the data is stored .)

Also stored procedures can be easily changed to allow auditing. There are a lot of applications that are currently being re-written because of HIPPA and SarBox requirements. In addition to logging changes to data, HIPPA requires logging EVEN a data read operation that touches medical data. You can't do that with an inline query.

And in a few occassions, after project roll-out, it was discovered that the application 'should have' done a better job of validating data before sending it to the data server. With a stored procedure, it was quick and easy to add validation code without having to trace out all locations in the application code where a query may have inserted data into a table. (Time from discovery, solution, testing, and 'fix': 45 minutes -no application re-deployement to 500+ desktops.)

But from a 'purely performance' perspective, with SQL 2005, there is little difference in most situations.

(Though there 'can' be large differences in certain situations.)






Re: Stored procedure vs. query

Adamus Turner

Arnie Rowland wrote:

From a security perspective, I would never allow an application to directly access data tables.

What security risk is involved with compiled code accessing the tables

Arnie Rowland wrote:

From my experience, all data access 'should' be done using Stored Procedures. The code base is centrally managed, and can easily be changed as system and business needs evolve.

There is no way to determine which stored procedures are associated with the changed tables without either looking at the source code of the application or waiting for it to break. In my experience, it is more time consuming and frustrating to keep bouncing back and forth from source code and database only to find a stored procedure or view with 2-5 lines of code.

Arnie Rowland wrote:
For example, imagine the impact on the application if data tables have to be re-organized for storage or speed issues. With stored procedures, it is only necessary to change the procedure code so that the same resultset is produced -the application doesn't care where the data comes from as long as it gets what is needed.

For storage or speed enhancement, when is a field name changed or removed outside of poor table design Why would the query need to change

Arnie Rowland wrote:
But when using inline queries, any changes to the data stores can cause catastrophic failure of the application until all queries are located and changed. (From experience, believe me, over the life span of a project, there will be businss needs to change where/how the data is stored .)

If the extremeties that you're reaching for are actually put into play, the application itself will no longer work without dramatic changes to the stored procedures. Datatypes would need to change, parameters added to the ADO, and any change of reasonable magnitude would require extensive research even with good documentation. To reiterate my previous post, inline coding is th optimal choice for 2-5 line queries regardless of the action on the table.

Arnie Rowland wrote:
Also stored procedures can be easily changed to allow auditing. There are a lot of applications that are currently being re-written because of HIPPA and SarBox requirements. In addition to logging changes to data, HIPPA requires logging EVEN a data read operation that touches medical data. You can't do that with an inline query.

Really I must be imagining application logs. (and legacy systems are often re-written to adapt to new found business needs. Auditing is no new obstacle.)

Arnie Rowland wrote:
And in a few occassions, after project roll-out, it was discovered that the application 'should have' done a better job of validating data before sending it to the data server.

Was QA sleeping

Arnie Rowland wrote:
With a stored procedure, it was quick and easy to add validation code without having to trace out all locations in the application code where a query may have inserted data into a table.

Validation is done on the form before the query is run. It's never done in the query itself.

Arnie Rowland wrote:
(Time from discovery, solution, testing, and 'fix': 45 minutes -no application re-deployement to 500+ desktops.)
First, if your company has 500+ desktops and you don't have an application server to pull updates, it's time to get one. Secondly, a 45 minute fix is a lunch break, and lastly, as a developer for over a decade, I've never experienced major changes in either applications or databases that didn't require months of planning before any changes were made.

Arnie Rowland wrote:

But from a 'purely performance' perspective, with SQL 2005, there is little difference in most situations.

(Though there 'can' be large differences in certain situations.)

Very true. There is a definitive and defendable middle ground.

Adamus






Re: Stored procedure vs. query

Arnie Rowland

Adamus Turner wrote:
Arnie Rowland wrote:

From a security perspective, I would never allow an application to directly access data tables.

What security risk is involved with compiled code accessing the tables

From the perspective of SarBox and HIPPA, developers, QA, PMs, etc., having access to some production data tables can be a significant security breach and cause audit failure. Of course, with a comprehensive security policy and good practices, this can be averted. But I have found very few organizations that as of yet, have such a policy and set of practices in place. I find a lot of 'almost' -but I also find a lot of 'back doors'.

Arnie Rowland wrote:

From my experience, all data access 'should' be done using Stored Procedures. The code base is centrally managed, and can easily be changed as system and business needs evolve.

There is no way to determine which stored procedures are associated with the changed tables without either looking at the source code of the application or waiting for it to break. In my experience, it is more time consuming and frustrating to keep bouncing back and forth from source code and database only to find a stored procedure or view with 2-5 lines of code.

I firmly disagree with this. If a solid and consistant naming convention is in place, it is relatively 'easy' to find code objects that are associated with tables. And if the organization standards are both in place and followed, source control will also contain metadata about the objects and the table they touch.

Arnie Rowland wrote:
For example, imagine the impact on the application if data tables have to be re-organized for storage or speed issues. With stored procedures, it is only necessary to change the procedure code so that the same resultset is produced -the application doesn't care where the data comes from as long as it gets what is needed.

For storage or speed enhancement, when is a field name changed or removed outside of poor table design Why would the query need to change

I have had to federate servers, move tables to different servers in the federation, and/or move tables to servers with different security patterns, etc. Changing the sproc was relatively simple, changing inline queries would have been a pain.

Arnie Rowland wrote:
But when using inline queries, any changes to the data stores can cause catastrophic failure of the application until all queries are located and changed. (From experience, believe me, over the life span of a project, there will be businss needs to change where/how the data is stored .)

If the extremeties that you're reaching for are actually put into play, the application itself will no longer work with dramatic changes to the stored procedures. Datatypes would need to change, parameters added to the ADO, and any change of reasonable magnitude would require extensive research even with good documentation. To reiterate my previous post, inline coding is th optimal choice for 2-5 line queries regardless of the action on the table.

See above.

Arnie Rowland wrote:
Also stored procedures can be easily changed to allow auditing. There are a lot of applications that are currently being re-written because of HIPPA and SarBox requirements. In addition to logging changes to data, HIPPA requires logging EVEN a data read operation that touches medical data. You can't do that with an inline query.

Really I must be imagining application logs. (and legacy systems are often re-written to adapt to new found business needs. Auditing is no new obstacle.)

Without third party tools, how would you accomplish 'READ' auditing And how would you assure the auditors that there are no 'holes' in the applications that would allow data reads without logging Access through stored procedures is one very good way to absolutely control this need.

Arnie Rowland wrote:
And in a few occassions, after project roll-out, it was discovered that the application 'should have' done a better job of validating data before sending it to the data server.

Was QA sleeping

Everyone sleeps sometimes... Stuff happens, things get missed. Unless of course, you work in a perfect world.

Arnie Rowland wrote:
With a stored procedure, it was quick and easy to add validation code without having to trace out all locations in the application code where a query may have inserted data into a table.

Validation is done on the form before the query is run. It's never done in the query itself.

And sometimes, it is overlooked, or it is done incorrectly.

Arnie Rowland wrote:
(Time from discovery, solution, testing, and 'fix': 45 minutes -no application re-deployement to 500+ desktops.)

First, if your company has 500+ desktops and you don't have an application server to pull updates, it's time to get one. Secondly, a 45 minute fix is a lunch break, and lastly, as a developer for over a decade, I've never experienced major changes in either applications or databases that didn't require months of planning before any changes were made.

And I have had to manage 'instant' changes from the 'war room' in the days immediately after major rollouts.

Arnie Rowland wrote:

But from a 'purely performance' perspective, with SQL 2005, there is little difference in most situations.

(Though there 'can' be large differences in certain situations.)

Very true. There is a definitive and defendable middle ground.

We are not in major disagreement. We have two different philosophies about controlling data access. My attitude is that the DBA be in complete control of the database, and if developers are creating inline queries, the DBA is not in control -the developers are. I constantly 'preach' that the DBA's prime objective is to protect the data at all costs -NOT to make life easy for developers. Hopefully, there is a mutual appreciation for the needs and pressures of each perspective, and a good working relationship.

Adamus






Re: Stored procedure vs. query

Adamus Turner

Arnie,

It seems we are debating 2 extremes. One side is small business where information is often avaiable-upon-request or occasionally the application developer and the DBA are the same person (my general realm of employment), and the other side is an atmosphere where information is protected by laws (i.e. medical data).

Although all programmers should share an equality in ethics, the practice is governed by allowance and often a lack of an enforced policy. Although this is no excuse for poor practice, I'm sure you can agree that certain constraints contribute to bandaids and shortcuts that don't always conform to naming conventions or follow suit with traditional approaches.

With that, the ultimate answer is really environment specific as a rule with a lot of room to argue exceptions.

Yes, it is the prime directive of a DBA to protect the data, but it is also the prime directive of the developer to ensure no garbage is allowed to enter the database. As you have mentioned, people do sleep, things are overlooked, underestimated, and even surrendered to a lesser evil.

Is there anything wrong with small queries being hard coded into any applications Sometimes yes, sometimes no. Should 'all' queries exist in the database and 'never' be hard coded This is entirely subjective.

Just my twist on it,

Adamus






Re: Stored procedure vs. query

Arnie Rowland

Debate is good. With open minds, we all learn from engaging in the process.

You are correct Adamus, it is as though we are looking at two different sides to the same coin. Ultimately, however, it is still a coin and its value depends upon how we use and protect it. Those of us that work in both large and small environments MUST keep the dialogue open and vibrant.

Years ago, I was involved with small project work, and yes, I understand the issues, problems and stresses of being a 'one person shop'. When I go flying on a small airplane, I do, however, want to know that the mechanic has followed the same well established maintenance practices as the large airlines. It may be a 'pain' for him, but it gives me (the client) assurances that I value.

Some of the points I try to drive home are this:

  • Any small project 'can' become larger than we imagined (think merger),
  • There is no such thing as "This only needs to work for 30 days, so quickly cobble something together and don't bother with design",
  • Without determination and 'resolve', good practices can be subverted by 'exigency',
  • There is no such thing as 'bad data', only inadequate efforts to produce 'good data',
  • If a developer can take a shortcut to get his/her work done with the 'least effort', he/she will,
  • If a DBA allows any of the above, he/she deserves the results, and finally,
  • There is always someone that will find exceptions to whatever we propose. (There are few absolutes in existence.)






Re: Stored procedure vs. query

Adamus Turner

Do you know the mechanic is following the same well established maintenance practice or do subconsciously hope so Smile

I doubt they're paid the same.

Admaus






Re: Stored procedure vs. query

Arnie Rowland

I do so want to believe...

And salary doesn't enter into the calculus. The mechanic has made his/her trade-offs -working environment, scheduling freedoms, proximity to 'homebase', etc. Any person working for below 'scale' is effectively subsidizing their job, and, I hope, they are consciously deciding that what they are getting is worth the subisdy.

If market rate is $50/hr, and a employee is accepting $35/hr, he/she is effectively paying $15/hr for the job. For some folks, that may be a good trade-off, better hours, better benefits, job security, less commute time, etc.

I could imagine a FBO mechanic in Homer, Alaska, with proximity to great fishing, hunting, incredible outdoor environment, being unwilling to move to work at O'Hare for double the pay. Salary isn't the only measurement of job worth/satisfaction.

This has evolved off topic, and I appreciate that you have been willing to invest your time and effort in these exchanges.

Thanks Adamus!






Re: Stored procedure vs. query

Anonymous

I believe stored procedures are the way to go for the following reasons.

1. Stored procedures are pre-compiled and therefore have a mapped execution plan which means they execute faster, granted poor queries will be just as bad in inline sql or in stored procedures so you should always ensure your queries are as efficient as possible regardless of their runtime location.

2. Stored procedures prevent sql injection attacks, this method of hacking / intrusion is the process whereby an attacker could potentially write a query into a textbox in your application, if the sql is running inline and you just use string concatenation to build your query it leaves you open to attack. By using stored procedures with mapped parameters you ensure that sql injection attacks cannot occur.

3. Permissions, the Sql server dev team spent numerous hours building security into sql server to allow specific users to only execute specific queries. Again this could be controlled via an application, but why write more code than necessary, and why re-invent the wheel. Each stored procedure can be individually granted execute permissions for a specific user, this provides a layer of abstraction from your database tables, as all access to them is controlled via stored procedures.

4. Database maintainability, if naming conventions are strictly adhered to it means you know exactly what each stored procedure is doing. Your DBA's will thank you for this as it makes their job easier also.

5. There are times when having the stored procedure as an extra layer of abstraction can come in handy, if you wanted to add a check, or filter some extra data before it gets into an application. The sort of minor modification that usually users cannot describe until they have a working version and then want the exact same thing only where the status of the records being processed should not be dead for example. All this can be achieved without having to go through an entire build and re-deploy process.

6. On Rapid Development projects where the code is thrown together quickly in a "Just get it done we promised the client this for tomorrow" situation, if the stored procedures are written and aren't exactly the most efficient then they can be modified by the DBA on the server and updated without having to re-deploy an entire application and cause delays or downtime to the client.

7. Stored Procedures can be easily code generated if you should wish to do so, I've found on larger projects that just creating the basic stored procedures (select all, select by key, insert, update, delete) for each database table speeds up development effort dramatically.

Stored Procedures aren't necessarily the solution to all problems when it comes to data access but they do have a lot of benefit to the development team on a whole. In my experience of writing various types of applications the only times I've come into issues with data access is when I have tried to use inline sql and the clients have changed their minds!

If you are using Sql Server 2005 it now has CLR stored procedures too which allow more complex stored procedures to be created in the .Net language us developers are comfortable in, so we no longer have to go looking up the syntax for cursors and raising error numbers in T-Sql.

Advice:

Don't prefix stored procedures with sp_ or xp_ as these are prefixes for system stored procedures.

try using the following

Table Names : Customer, Order

usp_Customer_Insert

usp_Customer_Select_All

usp_Customer_Select_By_Customer_ID

usp_Customer_Update

usp_Order_Select_All

usp_Order_Select_By_Order_ID

By using the table name first in the stored procedure name you can keep all of the stored procedures for a table in one section, if you use the command type first watch what happens.

usp_Insert_Customer

usp_Select_All_Customers

usp_Select_Customer_By_Customer_ID

usp_Select_All_Orders

usp_Select_Order_By_Order_ID

usp_Update_Customer

All of the tables related SPs are mixed together and aren't too easy to lookup.

Whichever naming convention you use, make sure it is logical, and don't change it mid-way through a project if you find it doesn't work for you.

Hope this helps!

Pete





Re: Stored procedure vs. query

Anonymous

Oh yes and one last thing,

Whilst using stored procedures if you decide a field should be moved from one table to another table the stored procedures can be modified to perform the correct join and still return the exact same data to the calling application. Again a change to a single stored procedure could resolve the issue, and stop you having to do 40 deployments of your application or go rebuilding projects and re-opening the code.

Which means, you get flexibility to develop as you see fit!





Re: Stored procedure vs. query

Adamus Turner

I have found that stored procedures are often used and abused as a catch-all for the lack of knowledge or skills in programming. Even views are being replaced with ADO technology. Most new developers entering the field or DBA's taht dabble in programmgin feel more comfortable defending stored procedures simply because they do not know that better and more task specific alternatives exist.

So they resort to a primal instinct of..."Use a stored procedure"

Note to new programmers: Just because you can doesn't mean you should.

Adamus






Re: Stored procedure vs. query

Adamus Turner

Peter J wrote:

Advice:

Don't prefix stored procedures with sp_ or xp_ as these are prefixes for system stored procedures.

try using the following

Table Names : Customer, Order

usp_Customer_Insert

usp_Customer_Select_All

usp_Customer_Select_By_Customer_ID

usp_Customer_Update

usp_Order_Select_All

usp_Order_Select_By_Order_ID

By using the table name first in the stored procedure name you can keep all of the stored procedures for a table in one section, if you use the command type first watch what happens.

usp_Insert_Customer

usp_Select_All_Customers

usp_Select_Customer_By_Customer_ID

usp_Select_All_Orders

usp_Select_Order_By_Order_ID

usp_Update_Customer

All of the tables related SPs are mixed together and aren't too easy to lookup.

Whichever naming convention you use, make sure it is logical, and don't change it mid-way through a project if you find it doesn't work for you.

First of all, your SELECT * should be views at the least. Secondly, What is your convention on JOINS Would the usp_ be:

usp_Customers_Invoices_Vendors_Retail_Sales_Update_Customer_Forecast_For_Next_Fiscal_Year

You'd have to have the object explorer full screen just to see the names.

...and if you're stored procedures are doing multiple tasks such as updating, inserting, and deleting several tables, what is your consistent convention then

I don't think the DBA would like you much at all.

I honestly challenge anyone to post a naming convention that truly defines what a complicated stored procedure is doing and explain how this convention defines the JOINED tables as well as creates a maintenance ease in times of change.

Maybe I am the one that is misguided.

Adamus