How bad is this remote database (vs local)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

How bad is this remote database (vs local)

Author

Topic

youngster
Starting Member

10 Posts

Posted - 2007-11-09 : 17:19:06

Hey!

Assume I have a remote database (about 10 GB) and some of the tables contain more that 5 000 000 rows. Connecting to this remote database i have a 100 Mbit line.

I guess this is a question to those experienced (with a large database). Would I have much to gain if I hade the database on the local computer or would this barely be noticeable (in speed)?

Edit: Also, a second question. Suppose we have tables A and B in a database. Also assume I'm executing a query on table A and it takes x seconds to complete. Will this number x increase as the table B is growing?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-09 : 17:40:23

By remote, I assume you mean connected accross a LAN somehow via QA.

I connect to my server via wireless laptop WIFI over DSL at 100 mbs. I routinely deal with a over 70 million rows of data with no measurable difference to when I am connected remotely to the server's desktop or via laptop through QA or EM.

As tables grow, query response times may increase due to the row number.

Depending on "how" you are querying the data, and what you are doing with it, there are differences.

My users use SAS, Access, Excel MS Query to retrieve and analyze data, and they see performance delays due to the size of their data sets. However, if they pull down the source data to their local computer and query from that (using SAS or access or excel) it seems to be faster.

Does that help?

as in most cases, it depends on the efficiency of your data structure, the quality of your indexes and how well your queries are written. Not so much on the "how" you are connected.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-09 : 22:19:04

10GB is a small database.

Are you currently seeing slow response time from some queries? Care to post them so that we can help optimize them?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-10 : 00:22:41

Application shouldn't run on sql server anyway.

youngster
Starting Member

10 Posts

Posted - 2007-11-16 : 08:03:47

By remote I did mean thorugh internet (a VPN channel)
This query has been running in over 10 min:

SELECT *
FROM dbo.databaseone dbA INNER JOIN
dbo.databasetwo dbB ON dbA.id_type = dbB.id_Type AND dbB.date > '2007-01-03 17:15:00'

dbA: Size = 24K, rows = 19
dbB: size = 6196152K, rows = 49380613

dbA.id_type is the only key

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-16 : 08:15:37

DO you really need all these rows to be transfered to your local computer?

youngster
Starting Member

10 Posts

Posted - 2007-11-16 : 08:21:53

2667796 rows are returned.

That one is kind of a dummy query. Do you think it would be much faster if the database was not over the internet but maybe in the same building through a much faster line?

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-16 : 09:33:01

Try SELECT COUNT(*)
FROM dbo.databaseone dbA INNER JOIN
dbo.databasetwo dbB ON dbA.id_type = dbB.id_Type AND dbB.date > '2007-01-03 17:15:00'

Or, instead of COUNT(*), use SUM(of something)
There is absolutely no difference if database is local or remote in terms of the performance.
The only difference is the network part, when SQL server returns the result to remote computer.
So select * will be slow, and select sum(something) - fast as it returns one row.

With few exception, application which requests 2.6M rows from server is a BAD application
What are you going to do with them? Scroll in a window? You cant even put it into Excel as it is limited
to 65K rows. And if you printed it 100 lines per page, you would have 26 *thousands* pages.

Definitely, 2M rows is just a temporary result used for some other processing.
So dont pull it from server, continue processing on the server.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources