SP in VB or in the SQL itself , which is better

[srajeevkumar]

Hi,

Is it the best practice to declare and define stored procedures using an ADO object within VB or is it better to declare and define and compile it in the SQL server itself.
Can someone explain as to why one is better than the other.

Rgds

 

[hmckillop]

Is it the best practice to declare and define stored procedures using an ADO object within VB "

What do you mean by declaring and defining a stored proc using an ADO object - ADO is a method of communicating with datasources not really for defining the structure and objects within that datasource (though technically you can do this)

All procs should be defined using SQL Server itself as that is where they live and are stored, compiled and executed etc. You can dynamically create a stored procedure but that would defeat the purpose of a stored proc - i.e. a precompiled piece of code boosting performance, reducing network traffic, efficient reuse of code and enhanced security.

"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[TheDrider]

Are you asking whether it's better embed SQL code in the VB or in the SQL Server?

You should definitely put your SQL code into stored procedures. A few reasons this is proper practice:

First it separates your presentation from your data. If the database changes, you can tweak the procedures without needing to change any VB code.

Second, it allows SQL Server to compile and optimize the SQL code. If your SQL code is in the VB, it has to be recompiled every time you use.

Hope that helps.

 

[SQLSister]

Other reasons to use SP in SQL server vice dynamic code in the application:

Someone else mentioned the reduction of network traffic and I want to expand onthat a bit. NEtwork traffic can be a big bottleneck if you are runnning a web application where the number of users is not necessarily predictable. Yeah a millisecond here and there doesn't seem like much, but if you have a miilion poeple on your site, then suddenly it is. It's a lot easier to program like you will have a million customers than to fix the existing code later when you have a slow production system and an angry herd of managers.

From a dba standpoint, I insist that all code that accesses my database be written in SPs, so I can manage it and make sure it is efficiently written. It also means that I know what will be affected when I need to make a structural change to the database. No surprises becasue I didn't knkow someone was doing something.

You can set permissions at the sp level rather than the table level when you use sps (Unless you use dynamic SQL)which is easier for me as the dba to manage as well as more secure.

Finally, we have a bunch of different applications which access some of the same data. They can each use the sps as written rather than rewriting the code for each application.
(Note: If you use the same SP in multiple applications it is critical to make sure this is noted in the comments of the sp. Don't want to fix a problem in one application only to break another one.)



Questions about posting. See faq183-874

 

[srajeevkumar]

Thanks very much to all your inputs.
yes when I meant by using Stored procedure in VB is by dynamically creating stored procedure during run time of the VB program itself as compared to pre created compiles SP in the SQL server itself.
Thanks very very much . I feel more enlightened now.

 

[MichaelaLee]

I understand that its better to write the stored procedures, but if it has alot of parameters it seems to run a little slower than a dynamic sql statement (Created in VB code). But I beleive you all are correct for all the reasons given above. What do you think.
Michael

 

[SQLSister]

Just as with everything else, it depends on what the SP is doing. If you are using dynamic SQL in the sp, then it probably is not going to improve performance.

If the most efficient execution plan changes wildly from execution to execution (even without using dynamic SQL), then the benefits of pre-compiling the execution plan may not be there and the database may at times use a less efficent execution plan. Some things have to be taken on a case by case basis.

These kinds of problems and their fixes can get very complex and the best route I have found towards understanding them is to get a good book on performance tuning. This is an extremely complex subject which is not taught to most people and, in my opinion, should be the basis of your computer education once you get beyond the trainee level. Even at the trainee level, people should be taught which code is usually more efficent in general, so that they don't start out writing poorly performing code. For instance, they should be taught that cursors should be avoided.

Questions about posting. See faq183-874

 

[ESquared]

Whenever I am curious about two methods I set out to prove which one is better. I program it both ways and try it.

Sometimes even after I have a seemingly good, working program or procedure, I still mess with it, trying to squeeze another few seconds or milliseconds off the execution time.

This has taught me a lot about what's more efficient and what's less efficient.

-------------------------------------
A sacrifice is harder when no one knows you've made it.