Inline SQL vs stored procs on SQL Server 7 and 2000

[Guest]

Is there a difference in how SQL Server 7 and SQL 2000 processes SQL passed
from a program by an ADO command object. Reason I ask is I rewrote a couple
applications a couple years ago were the SQL statements were inline. I
basically took the SQL statements and put them into stored procs, were there
were variables in the code I used SQL parameters in the stored procs. I got
some amazing performance results by switching to stored procs. The coding
was done in VB6 using ADO.

Now using SQL 2000 and VB.NET there appears to be little or no performance
difference between using stored procs and inline sql.

Any thoughts, I kinda would like to know so if I ever would deal with SQL
Server 7 again, I might only use Stored Procs over inline sql, where SQL 2000
and 2005 I could use either. Could It be the version of SQL or ADO being
used or a combination of both? By the way there were no hardware changes on
the servers or the network that could have caused the speed up.



Thanks

Kent

 

[Cor Ligthert [MVP]]

Kentk,

Probably few of us know how it was done in ADO. Than you have more change in
the newsgroup.

microsoft.public.dotnet.data

However if the stored procedure is faster than a dynamic procedure in ADONET
depends on the brand of database.

The only one I know which builds its stored procedures in advance is DB2
from IBM.

For the rest is in avarage the stored procedure a very very little bit
faster, (there seems to be situations where it is slower), while sometimes
it is really faster, but I nowhere have exactly read where.

Cor