Very Computer

Hi all,

I have made a comparison between talking with a local database and via
100Mbit's network and im a bit suprised about the difference in speed
that i recieve on these two methods.

The hardware is about the same on both the local-db and the
network-db. Windows 2000 Professional with SP3 running SQLserver 2000
with SP2.

NIC's are Intel's S-Pro on both ends connected via a hub.

If i make a lengthy query to be able to see the difference, the
difference is on certain tough queries 2 on local db and 15 across the
network.

I did all to make it fair, restarted machines between the queries so
nothng is cached.

Calling with ADO 2.5 from VB wth OLE-DB connection

How can it be that the difference is so huge !? normal FTP-traffic and
also normal file-copying across the machines show that it is around
7-9 Mb per seconds so i am running 100mbit.

Tried to change the Packet Size in the connecting string from
defaulting 4096 to higher values but no difference at all seen.

Any guru can give me an optimization tip, replication to a local db on
non critical data is a good solution, but that can't be done for
everything so im eager to get some speed from it all.

Thanks for any reply

Staffan - Sweden

SV - how much data is actually being returned by the query?

If you run SQL Profiler does the SQL sent look the same in both cases?

Are the versions of MDAC and SQL net-libs the same on the local server and the
remote machine?

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 Reply here - no email
 SQL FAQ (484 entries) see
 http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
 (faqxxx.zip in lib 7)
 or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
 or www.sqlserverfaq.com
 or www.mssqlserver.com/faq

Hi - thanks for taking the time.

The total size of the returned buffer is always less than 100kb, there
are several round-trips because of several underlying SQL-statements,
depending - it might be between 5 or 6 ado.adCmdText's that
are executed with a 'larger' more timeconsuming SP call before it all.

Same versions of everything - thats why i think 100kb on a 100Mbit
network should give me more speed.

I can always convert all the SQL-scripts to sp by themselves to have
it execute only on the sql-servers and probably gain somewhat, but i
really think i should get a better speed.

Any suggestions ?

Thanks and regards

Staffan - Sweden

Quote:>SV - how much data is actually being returned by the query?

>If you run SQL Profiler does the SQL sent look the same in both cases?

>Are the versions of MDAC and SQL net-libs the same on the local server and the
>remote machine?

> Neil Pike MVP/MCSE.  Protech Computing Ltd
> Reply here - no email
> SQL FAQ (484 entries) see
> http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
> (faqxxx.zip in lib 7)
> or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> or www.sqlserverfaq.com
> or www.mssqlserver.com/faq

 Staffan,

 100KB will get you at least 70 x 1500 byte network packets.  Shouldn't be enough
to cause that sort of slowdown.  If you copy a 100KB file from the server to the
client, initiated by the client, how long does it take?

 What net-lib is being used?  Is the user logged onto the server and the client
the same?  Try forcing the server to connect to itself with a net-lib (using an
alias) and see what difference that makes.  If this is SQL 2K then by default it's
probably using the in memory net-lib.;

 A winsock trace (assuming you're using tcp-ip sockets to connect) would be a good
idea.  A good set of tracing tools is at www.sstinc.com

Quote:> Hi - thanks for taking the time.

> The total size of the returned buffer is always less than 100kb, there
> are several round-trips because of several underlying SQL-statements,
> depending - it might be between 5 or 6 ado.adCmdText's that
> are executed with a 'larger' more timeconsuming SP call before it all.

> Same versions of everything - thats why i think 100kb on a 100Mbit
> network should give me more speed.

> I can always convert all the SQL-scripts to sp by themselves to have
> it execute only on the sql-servers and probably gain somewhat, but i
> really think i should get a better speed.

> Any suggestions ?

> Thanks and regards

> Staffan - Sweden

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 Reply here - no email
 SQL FAQ (484 entries) see
 http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
 (faqxxx.zip in lib 7)
 or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
 or www.sqlserverfaq.com
 or www.mssqlserver.com/faq

Hi Steffan,

what type of connection do you use? Make sure you're using TCP/IP which is
IMHO the fastest link to SQLServer (and NetBIOS is the slowest).

hth
GB