Where does a query really executes?

  • Thread starter Thread starter Jordi Rico
  • Start date Start date
J

Jordi Rico

Hi, I have a doubt I'd like to solve.
We are making an app, in which we use many sql queries in order to
return datatables.
But today someone has told us not to use sql queries, but stored
procedures, because he says that s.p. execute directly in the sql
server and queries execute in the client app, so they go slower.
Is it true? How can a query execute in the client app? I thought that
they executed in the sql server and returned the results you wanted.

Thanks for the replies and sorry for my bad english...
 
Jordi,

Obviously, sql queries execute in the database server, not the client.

There are some marginal reasons to use stored procedures instead of sql
statements, but where they execute is not one of them.

Kerry Moorman
 
Just what I thought.
Well, we have had to change all code written in 3 weeks for this
reason.... When I think how easy was being a cook when I was younger...
:)
Thanks for your replies!
 
Do a performance comparison and you will become quite aware of why dynamic
SQL is "bad"... Further, you can strictly define what a remote user is
allowed to do and not do if you provide them with no access to the tables.
While the DataReader and DataWriter roles are there for that purpose, it is
often preferable to completely hide things from users and database objects
such as views and stored procedures are preferable in this regard.
--
Christopher W. Robin
Database Administrator
Florida Fish and Wildlife Commission
 
Performance is another place where dynamic paramterized queries will
win out over stored procedures.
When both are executed the first time they are going to be compiled,
stored procedures are not pre-compiled at server startup and sitting
around ready to be used. Where stored procedures can sometimes win out
is that since they are always in the same form it is a far better
chance they when reused they are caught by the server.

However for most CRUD uses stored procedures take a huge,huge
performance penalty because of the use of coalese or using OR statments
to make the stored procedures usable in multiple conditions. Even
something as small as allowing the stored procedure to take in 2-4
optional arguments you are talking close to a 1/3 increase in the time
it would take verse a parameterized dynamic query.

All the things you mention about views can also apply to dynamic
queries. Besides what most people view as the security is that the
user would not even have access to the views, they would just have
access to the stored procedures. The stored procedures would have
access to the underlying tables and views. However even this is a
straw horse because the cracker got into the database they would access
to all your CRUD stored procedures. The only thing the stored
procedures vs direct access to the tables would do is slow them down a
little. If you are worried that the cracker is going to get that far
into your database that view and roles vs stored procedures for
security is going to stop them then you need to switch to a different
web application server and database.
 
Hi,

Nice compact written.

Cor

Performance is another place where dynamic paramterized queries will
win out over stored procedures.
When both are executed the first time they are going to be compiled,
stored procedures are not pre-compiled at server startup and sitting
around ready to be used. Where stored procedures can sometimes win out
is that since they are always in the same form it is a far better
chance they when reused they are caught by the server.

However for most CRUD uses stored procedures take a huge,huge
performance penalty because of the use of coalese or using OR statments
to make the stored procedures usable in multiple conditions. Even
something as small as allowing the stored procedure to take in 2-4
optional arguments you are talking close to a 1/3 increase in the time
it would take verse a parameterized dynamic query.

All the things you mention about views can also apply to dynamic
queries. Besides what most people view as the security is that the
user would not even have access to the views, they would just have
access to the stored procedures. The stored procedures would have
access to the underlying tables and views. However even this is a
straw horse because the cracker got into the database they would access
to all your CRUD stored procedures. The only thing the stored
procedures vs direct access to the tables would do is slow them down a
little. If you are worried that the cracker is going to get that far
into your database that view and roles vs stored procedures for
security is going to stop them then you need to switch to a different
web application server and database.
Click to expand...
 
Back
Top