Inline SQL vs stored procs on SQL Server 7 and 2000

24 May 2006 4:01 PM

kentk

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

24 May 2006 4:27 PM

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

Show quoteHide quote

"kentk" <ke***@discussions.microsoft.com> schreef in bericht
news:988A1A8A-60F4-4B2F-BA5A-15624E7E5010@microsoft.com...
> 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
>
>