The Worst Possible Way to use a Stored Procedure

This topic is a pet peave of mine; I’m constantly battling with developers to not do this; however there are cases that I can’t see how to eliminate the database business logic. Have you found a way around this scenario (note: this is a trivial example since a simple foreign key could solve this problem… but conceptually the problem exists in other areas where a FK wouldn’t work):
Rule:
A Customer can’t be deleted if he has any orders.

Possible business flow:
1. Object checks to see if any orders exist, none do.
2. Someone else enters order
3. Customer is deleted

Putting the business check in the SPROC would then put the rule in a context of a transaction.

Any way to avoid this (note: see note abou FK above).

Thank you.

Business Logic? Surely business logic is a combination of two different things:

1 transformation of data (the job of the application)
2 preservation of data integrity according to current business rules (the job of the dbms)

One of the skills of the system designer is to know which things go where. It is always right to get the dbms to preserve data integrity. Applications can do so as well, of course, but should get business rules from a common place.

Does a stored procedure run slow (In general) than the application code? We have a situation where we have a heavy use of stored procedure and although there is a lot of validation needs to be performed, we take 28 hours to process 60,000 records and this looks way too slow to me.

< >

Using procedural oriented code is totally meaningless in SQL. SQL is optimized if and only if it is *not* procedural. Besides set oriented processing is orders of magnitude much more powerful then procedural resource consuming loop holes.

< >
No. That’s the second dumbest thing. The dumbest thing is to put business logic anywhere else than in the dbms. What is the business logic anyway etc a set of constraints that bring meaning to data: question is what can best implement constraints: DBMS. What is better spend time searching tons of mixed up code generated by a confused spaghetti coder or find it a specific centralized place?

< <. A change in the sproc or C# can, and usually does, break the other. This is a case of the Don't Repeat Yourself principle the Pragmatic Programmer guys talk about. >>
Then you should review your HR dept qualifications required for people you hire in db design. That is a competence issue. If you hire a developper to do dbms don’t expect him/her to do a clean job in such area.

Business Logic should not be handled applicatively (API code, stored procedure) but declaratively through the use of constraints. TRying to implement business rules applicatively is basically the most unefficient way to do it.

And finally, I’m completely confused… I’m looking for information about stored procedures, whether to use them or not… the more I read, the last I know…

And the worst thing is that I have to explain to my development team the reasons of my decisions. But there’s no simple choose.

On my first job (and my first software project at a software development firm), I had a project manager on the project who insisted that I write the stored procedures, being the DBA on the project. The client shared his opinion.

The development team, headed by a lead developer, kept pushing business logic my way, into the stored procedures. I stepped up and said, “This isn’t how code was meant to be written.” The project manager called me away and said, “Look, you can write SQL. They can’t. Provide a simple enough interface with which they can accomplish all they need to with a single database call.”

Remind me to put you on the ” do not hire list”… as a DBA , in ORACLE, and MSSQL, BL in the RDBMS is mandentory as languages used to interface with the RDBMS can be anything… and I don’t like cleaning up your mess.

and yes, DBA since 1988, so got some experience…

@Andy: Put me on that list, too. It’s quite possibly the highest compliment you could pay me and Jeremy and most of the people reading this blog.

Putting critical business logic in a black hole of no version control, no change control, and no way of doing automated repeatable testing is quite frankly the dumbest thing you can do.

The motivation is usually one of fear and mistrust of developers. The solution (dumping into the black whole of the database) is the wrong solution and only treating the symptoms (incompetent developers and fearful DBAs).

I’ll go on that “no hire” list too. I can’t imagine what it must be like for you having worked 32 years with such poor and untrustworthy developers.

What are you all talking about? How can you NOT have business logic in the DB if you have disperate systems connecting in a SOA environment? if your DB is serving data to more than exactly 1 application, how is it that you can purport to maintain data integrity and understandable logic outside of the database?

This comment:

“The motivation is usually one of fear and mistrust of developers. The solution (dumping into the black whole of the database) is the wrong solution and only treating the symptoms (incompetent developers and fearful DBAs).”

That is just priceless. Fear? YOU are calling it the “Black Hole of the Database” Who’s the one afraid of SQL here?

If you’re writing one-offs or throwing together a little blog or online shop, I guess your slow, SQL-laden scripting layer is OK, but not if you really need to make something work in an enterprise environment where homogeneous, service oriented access over a common interface is called for.

Ok Roman,

Let’s try this one:

A.) I think that more than one application or service accessing the same database is moronic. Very common in the average enterprise, but still stupid as the day is long.

B.) I’ve written an absurd amount of SQL and even stored procedures in my career. I’m not particularly afraid of a relational database.

C.) What I most definitely know is that I can write much more maintainable business logic in a real programming language like C#/Java/Ruby/Python/Scala etc. — especially when I can back that logic up with a good set of automated unit tests. A language like T-SQL or PL/SQL just results in a procedural mess and the database environment is miserable from a feedback and automation standpoint.

D.) For your information, my team has a very large application that’s performing perfectly well with zero stored procedures, only a bare handful of lines of sql embedded in the code, but a pretty good suite of automated tests that run relatively quickly.

Jeremy,
Why would you think things would be more maintainable in a scripting language? We do version control for DB structure, schema, including SPs for 3-5 DBAs across several databases on a daily basis.

There is absolutely no problem with this.

As far as automated testing goes, that’s a very subjective avenue depending on how/what you test. We can (and do) easily write tests to iterate datasets, check for correct execution, return, etc. that test all layers, including SPs. We have no issues with testing for this scenario.

You say yourself that you do not write stored procedures. I guess since your experience is very limited in this respect, then your (a bit too vehement) opinion on the subject should be taken, as they say, with a grain of salt.

One (a few) more thing(s):

Your comment:

“A.) I think that more than one application or service accessing the same database is moronic. Very common in the average enterprise, but still stupid as the day is long. ”

You may live in a world where you are allowed to specify infrastructure, design, technologies, etc. all by yourself, but most of us work for someone else .

I have a client that has a central database of students, where disperate systems access student data directly from the central admin system. (library, billing, purchasing, school admin, etc) The interface to provide, for example, “StudentsByCourse” is the SAME for all of these applications. This represents a common API that all these applications can use.

You will probably argue that this layer should be exposed using another technology, i.e. a webservice or similar, but why? There is no need to do so. I can give each system the rights to retrieve the data as necessary from a common interface.

I do understand that if, for example, the library app needed DIFFERENT fields than say, billing, it could be beneficial to have a layer in between, however it makes no logical difference to create a new SP or to add a function to a class to fit this custom need. In fact, adding the SP is easier.

All that said, I do enjoy your blog, and it is good to read arguments for/against topics such as this. I would be interested in seeing/trying some of the SQL generation frameworks mentioned (just to see how the “other” side does it and to keep an open mind).

Just one last question – did you have a bad experience with SPs as a small child? I can understand being forcefully “for” something, but so forcefully against an obviously useful (and, I admit, sometimes misused) functionality? I don’t understand.

Cheers. R.

@Roman: It’s not that SPs aren’t useful, it’s just that the end up causing a lot more problems than they solve and they end up being a much more difficult and problematic way of doing things.

There are a few limited use cases where SPs are the best way of accomplishing a particular task, but none of these cases involve putting business logic in the SPs.

Use of SPs for business logic usually reflect a larger problem in the organization. The example you described is clearly the “integrate through the database” anti-pattern which is fraught with many problems. SPs are a way of addressing some of these problems, but they are an inferior way of doing it because SPs themselves cause other problems.

It looks like you never support any system you wrote. It is very sad. You will damn yourself

Can you please explain how you manage capacity planning for the application for next, say 5 years and growing data 10 times?

How you monitor that hardware is sufficient to handle increasing of user activity? How to find when system will require hardware upgrade? And for how long this upgrade will last to next upgrade? I’m asking not only about HDD capacity – I’m about processor(s) capacity and RAM.

Or how you will deal with situation when your application that run on middle tier started depredates in performance because set of data in tables changed and some query should revised?

How to fix it in case of embedded sql if hundreds users must have 24×7 access to the system?

With SP I will localize the particular problem SP in 5 minutes and after solution would be found – upgrade of application will take several milliseconds without any interruption of users activity – just apply ALTER PROCEDURE.

Last time when we had the situation like this it took 3 (three) days for preparation to stop application server (after fix been found – that also took couple of days, application recompiled and so one) and several hours of outage for all users. Of course this happens in most busy for business time. Business owners were absolutely happy – screaming users for week time, lost of money for business interruption. It was real pleasure.

BTW – have you red this article? http://www.sommarskog.se/dynamic_sql.html What do you think about memory usage for execution plans? Especially in situation when these plans consume 70% of RAM and used only ones? (if you don’t know – two statements like “id=1” and “id= 1” will be recognized as different and compiled separately)

@Gennadi:

It’s bad form to just assume the other guy is an idiot with no experience. Perhaps you should challenge your preconceived notions and false assumptions instead of insulting other people.

It is precisely BECAUSE I have maintained such large systems that I don’t use stored procs. In my experience, except in a very few circumstances, stored procs represent a failure at some point either during development, organization, team cooperation, etc. They’re usually used to prevent “those stupid guys” from doing something “stupid” rather than adding any value. Whether it be insecure DBAs or over-bearing managers wishing to keep members of his team separate or distrusting teams working on different projects, but sharing a database, etc.

Integration of applications through the database is the least-common-denominator and is generally the worst way to do it. There are much better ways.

RE: Embedded SQL – Embedded SQL is evil. Worse than Sprocs, I think. So I agree with you there, don’t use embedded SQL.

RE: that-article I haven’t read that one in particular, but I’ve heard the argument before. Once again, I’m not doing dynamic SQL like that, and, at any rate, I use 100% parameterized queries that are consistently generated from an ORM so the procedure cache argument is out the window.

I suggest you do a little more investigation and try doing some apps my way before just dismissing it. I’ve done them your way and the better way and have drawn my conclusions. You have dismissed my arguments without having even considered my way and then threw in a red herring (i.e. not embedded/dynamic SQL).

-Chad

Rather then teach me with manner you better answer the questions. It is ordinary questions that rise every day/month/quarter if you support “large systems”. BTW what is the penalty for breaching SLA written in contract? Just number of zeros in penalty for one hour of outage above SLA and percentage of up time according SLA – this will be a good answer how large the systems are. Basically I was talking about 99.6% per month (24×7 uptime) and six zeros number for one hour in my case.

> They’re usually used to prevent “those stupid guys” from doing something “stupid” rather than adding any value.

Have you got any idea why I wrote about 70% RAM consumed for execution plans? What do you think – how this correlate with your phrase I quoted? There is a direct connection between them. But if you think that you can sacrifice 70% of RAM to nothing having in database tables that keeps millions of records – probably I’ve asked stupid question.

> So I agree with you there, don’t use embedded SQL.

Do you think it somehow change the meaning of my question:

How to fix it in case of embedded sql if hundreds users must have 24×7 access to the system?

if I will change “embedded” to “prepared”?

Again – just imagine that your application is middle tier. Hundreds of users are connected to it 24 hours per day. 1 hour of outage cost you six zeros in penalty. And you have to fix one (only one!!!) query in this application where this query is hardcoded (embedded or dynamic – doesn’t matter) in your EXE/DLL/ets. This means you have to disconnect hundreds users, stop the middle tier service, replace EXE/DLL for new version, start service, check that application is working as expected. And run the same  procedure to rollback if upgrade is failed. Just compare this with one ALTER PROCEDURE clause (say 10-20 milliseconds) for fix upgrade and another ALTER PROCEDURE (again 10-20 milliseconds) for rollback. All users still connected and likely even don’t mention that they are working with new version of SP. Again – keep in mind that the business we are talking about loosing physically, say, 10 grands per minute. BTW it is ordinary number, say, for GM assembly belt line. Can you compare “cost” of “your” solution and “mine”?

> I use 100% parameterized queries

This exactly what I meant. Thank you for good example. Can you give ANY IDEA why the query like this

SELECT id FROM table WHERE Active = 1

will run zero milliseconds but this query

DECLARE @Active_val int
SET @Active_val = 1
SELECT id FROM table WHERE Active = @Active_val

will run more then 6 minutes? It is real example and real timing. Table is completely the same and untouched between queries, server is the same, no other application or users on the server, any SQL Server cache is flushed before each call. As you can see – second query logic is absolutely the same like the first one. The difference is one – second one is acting as prepared statement (be careful – I did not say that it is prepared statement – I said that this statement will act the same way as prepared statement. FYI – I know in details how works optimizer in SQL Server). Last time when I’ve seen the developer with the attitude like yours (“I will not put any business logic into a stored procedure”) and who used only prepared statements as you – he came to me with exactly this queries and request to put this query in SP. For my question where is a problem from his point of view he answered: “it is JDBC/ODBC driver error. Through SP this would be faster”. I did SP for him and show that there is no performance improvement at all using SP. But in QA he still able to run the query in 0 milliseconds. He was amazed and had no answer. Now is your shot. Any idea? I know the answer why the timing is so different. I can proof it and explain it. (this link can give you some light in the dark but it has no full answer: http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-programming/12612/Prepared-Statement-vs-Stored-Procedure)

Also – have you got any experience how to find what particular prepared statement started to slow down application that running against SQL Server 2000? I had – I spent more then half a day to find the real script that hidden under handle of temporary SP that wraps real query. Just try to catch these calls in profiler – you will be happy. I can translate this trace in plain English like this: the SP with handle 15 runs 300 seconds. Very informative and useful – but you can test it by yourself if you don’t believe me. BTW it is not only “mine” problem: http://bytes.com/topic/sql-server/answers/497441-finding-prepared-statement-sql Please, pay attention to this words in the message ”The statement is prepared before I start the profiler and the client
keeps that connection – so I can’t see its preparation without restarting the server (it’s a web application).” Can you imagine how to restart, say ebay.com? (FYI – AFAIK ebay is using SQL Server as database server)

Rather then teach me with manner you better answer the questions. It is ordinary questions that rise every day/month/quarter if you support “large systems”. BTW what is the penalty for breaching SLA written in contract? Just number of zeros in penalty for one hour of outage above SLA and percentage of up time according SLA – this will be a good answer how large the systems are. Basically I was talking about 99.6% per month (24×7 uptime) and six zeros number for one hour in my case.

> They’re usually used to prevent “those stupid guys” from doing something “stupid” rather than adding any value.

Have you got any idea why I wrote about 70% RAM consumed for execution plans? What do you think – how this correlate with your phrase I quoted? There is a direct connection between them. But if you think that you can sacrifice 70% of RAM to nothing having in database tables that keeps millions of records – probably I’ve asked stupid question.

> So I agree with you there, don’t use embedded SQL.

Do you think it somehow change the meaning of my question:

How to fix it in case of embedded sql if hundreds users must have 24×7 access to the system?

if I will change “embedded” to “prepared”?

Again – just imagine that your application is middle tier. Hundreds of users are connected to it 24 hours per day. 1 hour of outage cost you six zeros in penalty. And you have to fix one (only one!!!) query in this application where this query is hardcoded (embedded or dynamic – doesn’t matter) in your EXE/DLL/ets. This means you have to disconnect hundreds users, stop the middle tier service, replace EXE/DLL for new version, start service, check that application is working as expected. And run the same  procedure to rollback if upgrade is failed. Just compare this with one ALTER PROCEDURE clause (say 10-20 milliseconds) for fix upgrade and another ALTER PROCEDURE (again 10-20 milliseconds) for rollback. All users still connected and likely even don’t mention that they are working with new version of SP. Again – keep in mind that the business we are talking about loosing physically, say, 10 grands per minute. BTW it is ordinary number, say, for GM assembly belt line. Can you compare “cost” of “your” solution and “mine”?

Rather then teach me with manner you better answer the questions. It is ordinary questions that rise every day/month/quarter if you support “large systems”. BTW what is the penalty for breaching SLA written in contract? Just number of zeros in penalty for one hour of outage above SLA and percentage of up time according SLA – this will be a good answer how large the systems are. Basically I was talking about 99.6% per month (24×7 uptime) and six zeros number for one hour in my case.

> They’re usually used to prevent “those stupid guys” from doing something “stupid” rather than adding any value.

Have you got any idea why I wrote about 70% RAM consumed for execution plans? What do you think – how this correlate with your phrase I quoted? There is a direct connection between them. But if you think that you can sacrifice 70% of RAM to nothing having in database tables that keeps millions of records – probably I’ve asked stupid question.

> So I agree with you there, don’t use embedded SQL.

Do you think it somehow change the meaning of my question:

How to fix it in case of embedded sql if hundreds users must have 24×7 access to the system?

if I will change “embedded” to “prepared”?

Again – just imagine that your application is middle tier. Hundreds of users are connected to it 24 hours per day. 1 hour of outage cost you six zeros in penalty. And you have to fix one (only one!!!) query in this application where this query is hardcoded (embedded or dynamic – doesn’t matter) in your EXE/DLL/ets. This means you have to disconnect hundreds users, stop the middle tier service, replace EXE/DLL for new version, start service, check that application is working as expected. And run the same  procedure to rollback if upgrade is failed. Just compare this with one ALTER PROCEDURE clause (say 10-20 milliseconds) for fix upgrade and another ALTER PROCEDURE (again 10-20 milliseconds) for rollback. All users still connected and likely even don’t mention that they are working with new version of SP. Again – keep in mind that the business we are talking about loosing physically, say, 10 grands per minute. BTW it is ordinary number, say, for GM assembly belt line. Can you compare “cost” of “your” solution and “mine”?

Rather then teach me with manner you better answer the questions. It is ordinary questions that rise every day/month/quarter if you support “large systems”. BTW what is the penalty for breaching SLA written in contract? Just number of zeros in penalty for one hour of outage above SLA and percentage of up time according SLA – this will be a good answer how large the systems are. Basically I was talking about 99.6% per month (24×7 uptime) and six zeros number for one hour in my case.

> They’re usually used to prevent “those stupid guys” from doing something “stupid” rather than adding any value.

Have you got any idea why I wrote about 70% RAM consumed for execution plans? What do you think – how this correlate with your phrase I quoted? There is a direct connection between them. But if you think that you can sacrifice 70% of RAM to nothing having in database tables that keeps millions of records – probably I’ve asked stupid question.

> So I agree with you there, don’t use embedded SQL.

Do you think it somehow change the meaning of my question:

How to fix it in case of embedded sql if hundreds users must have 24×7 access to the system?

if I will change “embedded” to “prepared”?

Again – just imagine that your application is middle tier. Hundreds of users are connected to it 24 hours per day. 1 hour of outage cost you six zeros in penalty. And you have to fix one (only one!!!) query in this application where this query is hardcoded (embedded or dynamic – doesn’t matter) in your EXE/DLL/ets. This means you have to disconnect hundreds users, stop the middle tier service, replace EXE/DLL for new version, start service, check that application is working as expected. And run the same  procedure to rollback if upgrade is failed. Just compare this with one ALTER PROCEDURE clause (say 10-20 milliseconds) for fix upgrade and another ALTER PROCEDURE (again 10-20 milliseconds) for rollback. All users still connected and likely even don’t mention that they are working with new version of SP. Again – keep in mind that the business we are talking about loosing physically, say, 10 grands per minute. BTW it is ordinary number, say, for GM assembly belt line. Can you compare “cost” of “your” solution and “mine”?

> I use 100% parameterized queries

This exactly what I meant. Thank you for good example. Can you give ANY IDEA why the query like this

SELECT id FROM table WHERE Active = 1

will run zero milliseconds but this query

DECLARE @Active_val int
SET @Active_val = 1
SELECT id FROM table WHERE Active = @Active_val

will run more then 6 minutes? It is real example and real timing. Table is completely the same and untouched between queries, server is the same, no other application or users on the server, any SQL Server cache is flushed before each call. As you can see – second query logic is absolutely the same like the first one. The difference is one – second one is acting as prepared statement (be careful – I did not say that it is prepared statement – I said that this statement will act the same way as prepared statement. FYI – I know in details how works optimizer in SQL Server). Last time when I’ve seen the developer with the attitude like yours (“I will not put any business logic into a stored procedure”) and who used only prepared statements as you – he came to me with exactly this queries and request to put this query in SP. For my question where is a problem from his point of view he answered: “it is JDBC/ODBC driver error. Through SP this would be faster”. I did SP for him and show that there is no performance improvement at all using SP. But in QA he still able to run the query in 0 milliseconds. He was amazed and had no answer. Now is your shot. Any idea? I know the answer why the timing is so different. I can proof it and explain it. (this link can give you some light in the dark but it has no full answer: http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-programming/12612/Prepared-Statement-vs-Stored-Procedure)

Also – have you got any experience how to find what particular prepared statement started to slow down application that running against SQL Server 2000? I had – I spent more then half a day to find the real script that hidden under handle of temporary SP that wraps real query. Just try to catch these calls in profiler – you will be happy. I can translate this trace in plain English like this: the SP with handle 15 runs 300 seconds. Very informative and useful – but you can test it by yourself if you don’t believe me. BTW it is not only “mine” problem: http://bytes.com/topic/sql-server/answers/497441-finding-prepared-statement-sql Please, pay attention to this words in the message ”The statement is prepared before I start the profiler and the client
keeps that connection – so I can’t see its preparation without restarting the server (it’s a web application).” Can you imagine how to restart, say ebay.com? (BTW AFAIK ebay is using SQL Server as database server)

> I use 100% parameterized queries

This exactly what I meant. Thank you for good example. Can you give ANY IDEA why the query like this

SELECT id FROM table WHERE Active = 1

will run zero milliseconds but this query

DECLARE @Active_val int
SET @Active_val = 1
SELECT id FROM table WHERE Active = @Active_val

will run more then 6 minutes? It is real example and real timing. Table is completely the same and untouched between queries, server is the same, no other application or users on the server, any SQL Server cache is flushed before each call. As you can see – second query logic is absolutely the same like the first one. The difference is one – second one is acting as prepared statement (be careful – I did not say that it is prepared statement – I said that this statement will act the same way as prepared statement. FYI – I know in details how works optimizer in SQL Server). Last time when I’ve seen the developer with the attitude like yours (“I will not put any business logic into a stored procedure”) and who used only prepared statements as you – he came to me with exactly this queries and request to put this query in SP. For my question where is a problem from his point of view he answered: “it is JDBC/ODBC driver error. Through SP this would be faster”. I did SP for him and show that there is no performance improvement at all using SP. But in QA he still able to run the query in 0 milliseconds. He was amazed and had no answer. Now is your shot. Any idea? I know the answer why the timing is so different. I can proof it and explain it. (this link can give you some light in the dark but it has no full answer: http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-programming/12612/Prepared-Statement-vs-Stored-Procedure)

Also – have you got any experience how to find what particular prepared statement started to slow down application that running against SQL Server 2000? I had – I spent more then half a day to find the real script that hidden under handle of temporary SP that wraps real query. Just try to catch these calls in profiler – you will be happy. I can translate this trace in plain English like this: the SP with handle 15 runs 300 seconds. Very informative and useful – but you can test it by yourself if you don’t believe me. BTW it is not only “mine” problem: http://bytes.com/topic/sql-server/answers/497441-finding-prepared-statement-sql Please, pay attention to this words in the message ”The statement is prepared before I start the profiler and the client
keeps that connection – so I can’t see its preparation without restarting the server (it’s a web application).” Can you imagine how to restart, say ebay.com? (BTW AFAIK ebay is using SQL Server as database server)

> I use 100% parameterized queries

This exactly what I meant. Thank you for good example. Can you give ANY IDEA why the query like this

SELECT id FROM table WHERE Active = 1

will run zero milliseconds but this query

DECLARE @Active_val int
SET @Active_val = 1
SELECT id FROM table WHERE Active = @Active_val

will run more then 6 minutes? It is real example and real timing. Table is completely the same and untouched between queries, server is the same, no other application or users on the server, any SQL Server cache is flushed before each call.

As you can see – second query logic is absolutely the same like the first one. The difference is one – second one is acting as prepared statement (be careful – I did not say that it is prepared statement – I said that this statement will act the same way as prepared statement. FYI – I know in details how works optimizer in SQL Server). Last time when I’ve seen the developer with the attitude like yours (“I will not put any business logic into a stored procedure”) and who used only prepared statements as you – he came to me with exactly this queries and request to put this query in SP. For my question where is a problem from his point of view he answered: “it is JDBC/ODBC driver error. Through SP this would be faster”. I did SP for him and show that there is no performance improvement at all using SP. But in QA he still able to run the query in 0 milliseconds. He was amazed and had no answer. Now is your shot. Any idea? I know the answer why the timing is so different. I can proof it and explain it. (this link can give you some light in the dark but it has no full answer: http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-programming/12612/Prepared-Statement-vs-Stored-Procedure)

As you can see – second query logic is absolutely the same like the first one. The difference is one – second one is acting as prepared statement (be careful – I did not say that it is prepared statement – I said that this statement will act the same way as prepared statement. FYI – I know in details how works optimizer in SQL Server). Last time when I’ve seen the developer with the attitude like yours (“I will not put any business logic into a stored procedure”) and who used only prepared statements as you – he came to me with exactly this queries and request to put this query in SP. For my question where is a problem from his point of view he answered: “it is JDBC/ODBC driver error. Through SP this would be faster”. I did SP for him and show that there is no performance improvement at all using SP. But in QA he still able to run the query in 0 milliseconds. He was amazed and had no answer. Now is your shot. Any idea? I know the answer why the timing is so different. I can proof it and explain it. (this link can give you some light in the dark but it has no full answer: http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-programming/12612/Prepared-Statement-vs-Stored-Procedure)

Also – have you got any experience how to find what particular prepared statement started to slow down application that running against SQL Server 2000? I had – I spent more then half a day to find the real script that hidden under handle of temporary SP that wraps real query. Just try to catch these calls in profiler – you will be happy. I can translate this trace in plain English like this: the SP with handle 15 runs 300 seconds. Very informative and useful – but you can test it by yourself if you don’t believe me. BTW it is not only “mine” problem: http://bytes.com/topic/sql-server/answers/497441-finding-prepared-statement-sql Please, pay attention to this words in the message ”The statement is prepared before I start the profiler and the client
keeps that connection – so I can’t see its preparation without restarting the server (it’s a web application).” Can you imagine how to restart, say ebay.com? (BTW AFAIK ebay is using SQL Server as database server)

PS Sorry – I’m not spaming – only aproximately every third message appears in several minutes after send

Hi,
i am working with one application which has almost 4000 lines of code in sproc…and there are many sproc like that…
We are continuously adding requirement in tht system…
But complication here is..tht…each stored proc uses almost 5-10 tables many times…and some of tables have millions of records…we make multiple joins in such tables many times…
Now if we think to seperate our business logic in to another Business layer then i have doubt that system will perform as of now….

Regards,
Prez.

what kind of test coverage do you have for all that logic?