🚀 go-pugleaf

>
> >Some of you gurus correct me if I'm wrong
> >
> >A view is like a select with a 'hidden' where clause, so the SQL still
> >'runs' on a client.
> >
> >A stored procedure 'runs' on the server.
> >
> >Thus the stored procedure should be faster.
> >
> <snip>
>
> No SQL ever 'runs' on the client. The server parses, compiles and executes
> all SQL without regard to its type. All results sets are aggragated on the
> server and then shipped, row by row to the client.
>

True. SQL is always executed at the server, however the surrounding
"procedural" statements (loops, branches, variables, arithmetic) can run
on the client as part of the application (eg. written in VB or
PowerScript) or they can run on the server within a stored procedure (or
trigger). In this case they would be written using the RDBMS vendor's language
(ie. Transact SQL in the case of SQL Server). This is usually more
efficient then running it on the client since the network overhead is
less. Obviously, mileage will vary with client and server hardware
configurations.

Since stored procedures are compiled (meaning execution plans are pre-created)
there is less runtime work to be done, and hence efficiency is improved.
Dynamic SQL from the client must be parsed and a QEP generated each time
it is run (accepting that most RDBMS's will cache parsed SQL and QEPs).

> The sequence of activity is from client to server, passing the SQL and then
> receiving first the results of the passing ("Yep, I got your request" said
> the server), and then the rows from the results set of the SQL query ("Here
> you be client, I found these for you", said the server) and finally, an
> acknowledgement that the 'conversation' is over ("Th...Th...That's all
> folks", said the server).
>

One of the other performance benefits of stored procedures is that
result sets don't have to be returned to the client. A stored proc.
could load a large result set into a temporary table on the server, and
return the rowcount to the user, who could then refine the query to
further reduce the result set (ie. drill down) before pulling it back
over the network.

Dunno who's keeping score, but I guess this means I'm putting my X
against stored procedures.

regards
Roy

--
_______________________________________________________________________________
"Frisbeetarianism is the belief that when you die, your soul goes up
on the roof and gets stuck."
                -- George Carlin


Roy Smith
  Snr. Systems Consultant         royston@sybase.com
  Sybase Court
  Maidenhead                      Office: 0628 597110
  England                         Mobile: 0831 155754