By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,017 Members | 1,158 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,017 IT Pros & Developers. It's quick & easy.

Network performance for simple select statement

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
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.