Re: Inline SQL vs stored procs on SQL Server 7 and 2000


"kentk" <kentk@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:415B31B1-6183-44BB-A1FF-DE4F43893A41@xxxxxxxxxxxxxxxx
How about I refraise the question then. Has anyone noticed a performance
difference when using inline sql vs stored procs when using SQL Server 7
and
SQL 2000? Specifically would a SQL 2000 Server perform better than SQL
Server 7 when passing a SQL string from VB 6.0 using ADO?

"Bob Butler" wrote:

"kentk" <kentk@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D60B1B84-289F-4006-9052-880309BC4A5E@xxxxxxxxxxxxx
Now using SQL 2000 and VB.NET there appears to be little or no


--
try asking in a group with "dotnet" in the name; this group is for VB
6.0
and earlier
VB.Net is Not.VB



If everything is equal then a Stored Procedure should be 'faster' than a SQL
Query (Apples to Apples). That would be true no matter what platform or
language is in use.

But it wouldn't surprise me to find out under some scenarios that *overall*
performance might be the reverse.

That is why you still need to ask in a dotnet group, because comparing a
VB6/ADO app with a VB.Net/ADO.NET app quickly becomes a matter of Apples and
Oranges. For example:

1) A equivalent VB.Net app will always run slower than its VB6 counterpart.
So it may just be the 'code' itself.
2) An initial run of a VB.Net app will take measurably longer than
subsequent runs, thus how is your VB.Net app configured, structured?
3) Are you using a DataView or a DataTable?
4) The default access model in ADO.Net is essentially that of a
'disconnected' recordset, which (again depending on what you are doing) can
be a tiny more expensive than ADO's 'connected' default. (This is based on
the provider used.)
5) Related to the above OLE DB providers are a bit 'faster' than .Net
providers.
6) Which .Net version are you using, I have noted interesting difference
between VS2003 and VB2005.

The above is not a complete list, but it might help you sort out where the
problem lies.

hth
-ralph


.

Relevant Pages

  • Re: ODBC Timeout problems but very hard to pin down
    ... APP 1: Access 2000 database ... I ran SQL Profiler to capture the ... validated by SQL Server. ... As it happens, the default display contains ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Openrowset Jet OLEDB
    ... Front end app means the app we wrote and wich the users use to modify the ... This works fine in our development enviroment where the sql service is ... And what do you mean by "speedily import an mdb into the ... > Microsoft Access Developer's Guide to SQL Server ...
    (microsoft.public.sqlserver.security)
  • Re: SQL 2000 NT AUTHORITYSYSTEM Permission Problem
    ... Not associated with a trusted SQL Server connection. ... However, if you deal with the Domain controller, most of the controls there ... > Forms app and a VB6 app. ...
    (microsoft.public.sqlserver.security)
  • data access strategy
    ... I've developed an app for our company using VB.NET and SQL server express running on our SBS server 2003 Standard ... What I'd like is some advice about how I can allow remote clients to access a SQL database that lives inside our LAN. ... I believe I have opened port 8082 on my firewall and pointing it to my SQL server machine. ...
    (microsoft.public.sqlserver.connect)
  • Re: Change Table Ownership.
    ... System tables are there for SQL to run properly. ... I support the Professional Association for SQL Server ... > created a new user/pass combination. ... > All the tables for the main app are listed as "system" and the main app ...
    (microsoft.public.sqlserver.server)