Re: Stored Procedures

Michel-

I have the idea that you are now at a company which has a lot of (in past called) batch processing which is probably still a main purpose for large computers on banks and I think you are now at a likewise company.

In your former job there was probably much more real time processing with many short processes at the database server.

I think that this is also a reason how you should do your executions on your database server.

I hope that I did understand you well.

-Cor

"Michel Posseth [MCP]" <MSDN@xxxxxxxxxxx> schreef in bericht news:ePLfywLeIHA.536@xxxxxxxxxxxxxxxxxxxxxxx
Cor ,

First of all Cor maybe my initiall comment was understood different by you as i intended it to be


"A stored procedure is not saved in its compiled version outside the cache, the same as any other Transact-SQL statement"


It is the execution plan that is eventually "compiled" and yes i know this is also happening for inline T-SQL , however with a SP this can in the case of a SQL batch and with optimizations much more efficient beside the advantage of encapsulation security etc etc ... for wich alone i would favor a SP

However my response to you was triggered by my personal question regarding this comment of you

Be aware however, that stored procedures are *not* compiled with exception from a DB2 database.

I know you are right seen from the common perspective ( don`t know about that DB2 thingy )

However when is something called then "Compiled" as our .Net / Java assembly`s are JITTED we just create the SQL counterpart of an execution plan
in my opinion,,,, i was just wondering how you thought about that

Also my remark about the Inside SQL server books was not intended in a offensive way , i am really a fan of the series especially of Kalen Dalenay as she writes in such a great style and gives somuch insight in how SQL server works behind the scenes .


Regards

Michel Posseth












"Cor Ligthert[MVP]" <notmyfirstname@xxxxxxxxx> schreef in bericht news:0EA7477B-E269-4DDC-81AB-D14547C57BB5@xxxxxxxxxxxxxxxx
Michel-

I saw this on this page

"A stored procedure is compiled at execution time, like any other Transact-SQL statement"

Maybe would I have better written it in another way.

"A stored procedure is not saved in its compiled version outside the cache, the same as any other Transact-SQL statement"

As it is in your book in another way, you can give it in my idea to your boss to use as fuel.

-Cor


"Michel Posseth [MCP]" <MSDN@xxxxxxxxxxx> schreef in bericht news:eDOizFKeIHA.5296@xxxxxxxxxxxxxxxxxxxxxxx
Will you show me where it is written this?
definition of a SP "A stored procedure is a group of Transact-SQL statements compiled into a single execution plan."


I will show you were it is written

http://msdn2.microsoft.com/en-us/library/aa174792.aspx

I would say Cor buy yourself the Books inside SQL server





"Cor Ligthert[MVP]" <notmyfirstname@xxxxxxxxx> schreef in bericht news:70E60779-130F-432E-9595-3C75B982E60A@xxxxxxxxxxxxxxxx
Michel-

AFAIK is there no difference in SQL by a stored sproc and a dynamic sproc.
Although a stored sproc is always the same and seems to work something smoother in the SQL serve3r cache that is used.
(There is also a slight difference in processing time, as I have seen even on a Jet database).

Will you show me where it is written this?
definition of a SP "A stored procedure is a group of Transact-SQL statements compiled into a single execution plan."

-Cor

"Michel Posseth [MCP]" <MSDN@xxxxxxxxxxx> schreef in bericht news:%23jMQvs9dIHA.5348@xxxxxxxxxxxxxxxxxxxxxxx
Be aware however, that stored procedures are *not* compiled with exception from a DB2 database.


Well ,,, this depends on your definition i guess

And yes i have read the inside SQL server 2000 and 2005 , :-)

definition of a SP "A stored procedure is a group of Transact-SQL statements compiled into a single execution plan."

Or how would you define a extended stored procedure written in C++ ( 2000 ) or .Net ( 2005 ) ( my experiences by the way regarding the lather is that they are much slower as there "uncompiled" transact SQL scounterparts )

The point i try to make is ,, how would someone define "compiled" especially when dealing with .Net and Java assemblys can we call those compiled ?


Just my thoughts

Michel






"Cor Ligthert[MVP]" <notmyfirstname@xxxxxxxxx> schreef in bericht news:52894810-6AE0-415B-AABE-CDEB79BB0792@xxxxxxxxxxxxxxxx
si_owen,

You have to add the Sqlparameters to the command.

Something as

creditSqlCommand.parameters.add(New Sqlparameter("@tokenString",1)
creditSqlCommand.parameters.add(New Sqlparameter("@currentDateString","Hello")

Be aware however, that stored procedures are *not* compiled with exception from a DB2 database.

Cor









.

Relevant Pages

  • Re: Stored Procedures
    ... the same as any other Transact-SQL statement" ... case of a SQL batch and with optimizations much more efficient beside the ... "A stored procedure is not saved in its compiled version outside the ... statements compiled into a single execution plan." ...
    (microsoft.public.dotnet.languages.vb)
  • Re: MSDE Slow in executing Stored Procedures
    ... I've ran SQL Query Analyzer and executed both stored procedure and sql ... The execution plan looks the same. ...
    (microsoft.public.sqlserver.msde)
  • Re: Stored Procedures
    ... "A stored procedure is compiled at execution time, ... "A stored procedure is not saved in its compiled version outside the cache, the same as any other Transact-SQL statement" ... AFAIK is there no difference in SQL by a stored sproc and a dynamic sproc. ...
    (microsoft.public.dotnet.languages.vb)
  • Execution plan for stored procedures w multiple statements
    ... I read somewhere that SQL Server 2000 as of service pack ... creates a separate execution plan for each sql ... statement in a stored procedure so if the execution plan ...
    (microsoft.public.sqlserver.server)
  • RE: View interpretation by the query optimizer
    ... SQL View can be thought of as either a virtual table or a stored query. ... the SQL Server just map the view to the select ... The stored procedure is pre-complied on the server side ... to run the execution plan than the ad-hoc queries. ...
    (microsoft.public.sqlserver.programming)
Quantcast