Query data is very slow

Dear all,
I have SQL Server 2000 installed at the HO server (Pentium 4, memory 512
Mb). The client side (Pentium 4, memory 256 Mb) connect to the server using
leased line 128 Kbps. The distance between HO and client side is 10 miles.
Using Query Analyzer, to retrieve 40.000 records, it takes time about 4 min
30 seconds.
The SQL statatement that I execute is "SELECT * FROM tblInvoiceMain"
If I execute the SQL from the HO computer, it just takes time 4 seconds.
What can I do to fix the problem ?
Thanks.
Regards,
Vensia> What can I do to fix the problem ?
1) add a WHERE clause to your query
2) request only the columns needed instead of all columns
3) get a faster network connection
Hope this helps.
Dan Guzman
SQL Server MVP
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:eNhWKQ2BGHA.1288@.TK2MSFTNGP09.phx.gbl...
> Dear all,
> I have SQL Server 2000 installed at the HO server (Pentium 4, memory 512
> Mb). The client side (Pentium 4, memory 256 Mb) connect to the server
> using
> leased line 128 Kbps. The distance between HO and client side is 10 miles.
> Using Query Analyzer, to retrieve 40.000 records, it takes time about 4
> min
> 30 seconds.
> The SQL statatement that I execute is "SELECT * FROM tblInvoiceMain"
> If I execute the SQL from the HO computer, it just takes time 4 seconds.
> What can I do to fix the problem ?
> Thanks.
> Regards,
> Vensia
>
>|||I second the suggestions given by Dan.
A where clause reduces the search time and the query will work faster if you
use an insexed coloumn in the where clause.
Specifying the name of the coloumn that you need will reduce the time taken
to retireve the extra not-needed coloumns.
A faster n/w conn is usually preferred :-D
Also do check whether any other query is applying any locks on the table !!!
!
Thanks
Amer M J
MCP
"Dan Guzman" wrote:

> 1) add a WHERE clause to your query
> 2) request only the columns needed instead of all columns
> 3) get a faster network connection
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
> news:eNhWKQ2BGHA.1288@.TK2MSFTNGP09.phx.gbl...
>
>|||Actually I have added the where clause in the application and index in the
table.
The user reported to me that it takes a long time to open Invoice form.
At the server side, the invoice form can be opened in several seconds but at
the client side (10 miles from the server), it takes minutes to open the
form.
Then I use Query Analyzer to run SQL statement "SELECT * FROM
tblInvoiceMain" to check the execution time.
From the PC at server side, it takes only 4 seconds but at the client side,
it takes 4 minutes 30 seconds.
I just wonder if there is something wrong with the database setting.
Anyway, is there any relation with the log file size ? How to truncate the
log file in SQL Server 2000 ?
Thanks.
"Amer M J" <AmerMJ@.discussions.microsoft.com> wrote in message
news:5F35A98F-EBBA-4F86-B8D5-F06CA84BAE0E@.microsoft.com...
> I second the suggestions given by Dan.
> A where clause reduces the search time and the query will work faster if
you
> use an insexed coloumn in the where clause.
> Specifying the name of the coloumn that you need will reduce the time
taken
> to retireve the extra not-needed coloumns.
> A faster n/w conn is usually preferred :-D
> Also do check whether any other query is applying any locks on the table
!!!![vbcol=seagreen]
> Thanks
> Amer M J
> MCP
> "Dan Guzman" wrote:
>
512[vbcol=seagreen]
miles.[vbcol=seagreen]
4[vbcol=seagreen]
seconds.[vbcol=seagreen]|||Hi
It has nothing to do with SQL Server. It is your network that is the
problem.
128Kbps = 16 kilo bytes per second. The practical throughput on your line,
assuming there is nothing else using it is about 10 kilo bytes per second.
If each row returned is 2'000 bytes, you can only transfer 5 rows per second
over the line.
And you are moving 40'000. Work that out.
The fact that the performance is acceptable when accessed locally indicates
at a glance your have network performance problems.
In a true n-tier application, there should be no reason to move 40'000 rows
around. I would seriously look at how your application and data access are
architected.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:Oa2tQYcCGHA.1232@.TK2MSFTNGP10.phx.gbl...
> Actually I have added the where clause in the application and index in
> the
> table.
> The user reported to me that it takes a long time to open Invoice form.
> At the server side, the invoice form can be opened in several seconds but
> at
> the client side (10 miles from the server), it takes minutes to open the
> form.
> Then I use Query Analyzer to run SQL statement "SELECT * FROM
> tblInvoiceMain" to check the execution time.
> From the PC at server side, it takes only 4 seconds but at the client
> side,
> it takes 4 minutes 30 seconds.
> I just wonder if there is something wrong with the database setting.
> Anyway, is there any relation with the log file size ? How to truncate the
> log file in SQL Server 2000 ?
> Thanks.
> "Amer M J" <AmerMJ@.discussions.microsoft.com> wrote in message
> news:5F35A98F-EBBA-4F86-B8D5-F06CA84BAE0E@.microsoft.com...
> you
> taken
> !!!!
> 512
> miles.
> 4
> seconds.
>