469,568 Members | 1,413 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,568 developers. It's quick & easy.

Network performance for simple select statement

I've just inherited a system and have some concerns about the speed of
connections to a remote server (SQL2000). If I do a simple select
statement on the table below, it takes 14 minutes to retrive 6 million
rows across a 2Mb line. Obviously it's a reasonable amount of data to
retrieve, but I would have thought this would be quicker if I'm honest.
Run locally, this is 50 seconds.

My thoughts are that there may be some issues with our connection (we
get general network errors sporadically, which are being looked at),
but wanted some thoughts if the performance is acceptable for what it
is doing with what is available. I don't think there is a SQL issue,
but want to check if this sounds about right.

It's early days, so I'm after a general impression of the speed of
retrieval for the amount of data on the available bandwidth. Assuming a
best performance scenario, what is the minimum time it should take as a
best guess ?

Thanks

Ryan

CREATE TABLE [FIELD_VALUES] (
[DEALER_DATA_ID] [int] NOT NULL ,
[FIELD_CODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[FIELD_VALUE] [numeric](15, 5) NULL ,
[CHANGED_TYPE] [int] NULL ,
CONSTRAINT [PK_FIELD_VALUES] PRIMARY KEY CLUSTERED
(
[DEALER_DATA_ID],
[FIELD_CODE]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

Jul 23 '05 #1
1 1273
Ryan (ry********@hotmail.com) writes:
I've just inherited a system and have some concerns about the speed of
connections to a remote server (SQL2000). If I do a simple select
statement on the table below, it takes 14 minutes to retrive 6 million
rows across a 2Mb line. Obviously it's a reasonable amount of data to
retrieve, but I would have thought this would be quicker if I'm honest.
Run locally, this is 50 seconds.


Hm, your maximum row size is 28 bytes. 28 * 6 million is 168 million.
2 MBit/s, means 250000 Bytes/s. 168 / 0.25 = 672. 672/60 = 11.2 minutes.
So it does not look unreasonable.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Scott CM | last post: by
11 posts views Thread by galina betz | last post: by
3 posts views Thread by Mario.Reif | last post: by
12 posts views Thread by lali.b97 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.