473,695 Members | 2,513 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_Gene ral_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 1375
Ryan (ry********@hot mail.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****@sommarsk og.se

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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
8346
by: serge | last post by:
I have an SP that is big, huge, 700-800 lines. I am not an expert but I need to figure out every possible way that I can improve the performance speed of this SP. In the next couple of weeks I will work on preparing SQL statements that will create the tables, insert sample record and run the SP. I would hope people will look at my SP and give me any hints on how I can better write the SP.
6
6549
by: Scott CM | last post by:
I have a multi-part question regarding trigger performance. First of all, is there performance gain from issuing the following within a trigger: SELECT PrimaryKeyColumn FROM INSERTED opposed to: SELECT * FROM INSERTED
11
10835
by: galina betz | last post by:
Please help! Getting this error intermittently: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
3
4168
by: Mario.Reif | last post by:
We have developed an application which was running under DB2 v7.2.5 quite well for some years. Four weeks ago we installed DB2 v8.1.5 Express Fixpak 5 on a new Server (hardware is nearly the same as on the DB2 v7.2.5 machine). The new Server runs on Windows 2003. Last week we installed another server with the same hardware with DB2 8.1.5 Workgroup Server Fixpak 5 under Windows 2000 Server. Both servers running DB2 v8 databases are about...
9
5756
by: HC | last post by:
Hello, all, I started out thinking my problems were elsewhere but as I have worked through this I have isolated my problem, currently, as a difference between MSDE and SQL Express 2005 (I'll just call it Express for simplicity). I have, to try to simplify things, put the exact same DB on two systems, one running MSDE and one running Express. Both have 2 Ghz processors (one Intel, one AMD), both have a decent amount of RAM (Intel system...
5
3842
by: wugon.net | last post by:
question: db2 LUW V8 UNION ALL with table function month() have bad query performance Env: db2 LUW V8 + FP14 Problem : We have history data from 2005/01/01 ~ 2007/05/xx in single big table, we try separate this big table into twelve tables and create a view
4
3495
by: 73k5blazer | last post by:
Hello again all.. We have a giant application from a giant software vendor that has very poor SQL. It's a PLM CAD application, that makes a call to the db for every cad node in the assembly. So for an assembly with many parts, from remote locations, the performance goes out the window. Locally, it all works fine because network latency is <1ms. But some remote sites we have (that are growing) latency can be as high as 80-100ms.
3
12325
by: Michel Esber | last post by:
Hello, Environment: DB2 LUW v8 FP15 / Linux I have a table with 50+ Million rows. The table structure is basically (ID - Timestamp). I have two main applications - one inserting rows, and the other reading/deleting rows. The 'deleter' application runs a MIN/MAX (timestamp) for each ID and, if the difference between min/max is greater than 1h, it reads all
12
4799
by: lali.b97 | last post by:
Somewhere in a tutorial i read that if statement has performance overheads as code within the if statement cannot take benefit of pipeling of microprocessor and also that the compiler cannot agressively optimize that code. my functions as much as possible and also try to have minimum code withing if block.
0
8623
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8562
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
8830
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7655
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5837
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4339
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
2997
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2265
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1971
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.