Dear All,
I have a single table name "Remark". It contains the item code, date,
status and remark. If I want to build a query that select items out which
the latest status is still in "pending" for example. How do I make it?
Thanks
Best Rdgs
EllisEllis
SELECT <columns list>
FROM Remarks WHERE[date]=(SELECT TOP 1 [date]
FROM Remarks R WHERE
R.Itemcode=Remarks.Itemcode
ORDER BY [date] DESC)
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:uSSJ$UXbFHA.464@.TK2MSFTNGP15.phx.gbl...
> Dear All,
> I have a single table name "Remark". It contains the item code,
date,
> status and remark. If I want to build a query that select items out which
> the latest status is still in "pending" for example. How do I make it?
> Thanks
> Best Rdgs
> Ellis
>sql
Showing posts with label dear. Show all posts
Showing posts with label dear. Show all posts
Friday, March 30, 2012
Tuesday, March 20, 2012
Query Evaluvation and tempdb
Dear All,
Does SQL server store the intermediate results set in the tempdb? One of the
team members has dropped the indexes for our one large table. I was running
a
query that has multiple joins (including this table) , and this caused tempd
b
to fill up.
According to this article by Microsoft,
http://msdn2.microsoft.com/en-us/library/ms190768.aspx, I'm under impression
that table scan has stored a large set of intermediate data in tempdb, whic
h
caused it to fill up.
My team members seem to argue that temp db is only used when we use temp
tables.
Secondly, does sql server evaluate the join first, or where clause first?
(Or it depends on each query?)
Thank you much for your help in advance.> My team members seem to argue that temp db is only used when we use temp
> tables.
Your team members are wrong. Tempdb can be used for sorting on just about
any type of query. Also see a list of items here under "Causes":
http://www.aspfaq.com/2446
A
Does SQL server store the intermediate results set in the tempdb? One of the
team members has dropped the indexes for our one large table. I was running
a
query that has multiple joins (including this table) , and this caused tempd
b
to fill up.
According to this article by Microsoft,
http://msdn2.microsoft.com/en-us/library/ms190768.aspx, I'm under impression
that table scan has stored a large set of intermediate data in tempdb, whic
h
caused it to fill up.
My team members seem to argue that temp db is only used when we use temp
tables.
Secondly, does sql server evaluate the join first, or where clause first?
(Or it depends on each query?)
Thank you much for your help in advance.> My team members seem to argue that temp db is only used when we use temp
> tables.
Your team members are wrong. Tempdb can be used for sorting on just about
any type of query. Also see a list of items here under "Causes":
http://www.aspfaq.com/2446
A
Friday, March 9, 2012
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.
>
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.
>
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
A prime place to being looking is the network bandwidth..
1. try doing this query from another computer close to the server (avoiding
the 128K line.)
If the response is good begin investigating the load this is placing on the
128k line..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Vensia" wrote:
> 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 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
A prime place to being looking is the network bandwidth..
1. try doing this query from another computer close to the server (avoiding
the 128K line.)
If the response is good begin investigating the load this is placing on the
128k line..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Vensia" wrote:
> 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
>
>
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,
VensiaA prime place to being looking is the network bandwidth..
1. try doing this query from another computer close to the server (avoiding
the 128K line.)
If the response is good begin investigating the load this is placing on the
128k line..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Vensia" wrote:
> 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 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,
VensiaA prime place to being looking is the network bandwidth..
1. try doing this query from another computer close to the server (avoiding
the 128K line.)
If the response is good begin investigating the load this is placing on the
128k line..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Vensia" wrote:
> 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
>
>
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:
> > 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
> >
> >
> >
>
>|||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
!!!!
> Thanks
> Amer M J
> MCP
> "Dan Guzman" wrote:
> > > 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
> > >
> > >
> > >
> >
> >
> >|||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...
>> 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:
>> > > 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 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:
> > 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
> >
> >
> >
>
>|||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
!!!!
> Thanks
> Amer M J
> MCP
> "Dan Guzman" wrote:
> > > 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
> > >
> > >
> > >
> >
> >
> >|||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...
>> 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:
>> > > 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
>> > >
>> > >
>> > >
>> >
>> >
>> >
>
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.
>
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.
>
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,
VensiaA prime place to being looking is the network bandwidth..
1. try doing this query from another computer close to the server (avoiding
the 128K line.)
If the response is good begin investigating the load this is placing on the
128k line..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Vensia" wrote:
> 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 usin
g
> 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 mi
n
> 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 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,
VensiaA prime place to being looking is the network bandwidth..
1. try doing this query from another computer close to the server (avoiding
the 128K line.)
If the response is good begin investigating the load this is placing on the
128k line..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Vensia" wrote:
> 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 usin
g
> 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 mi
n
> 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
>
>
Saturday, February 25, 2012
Query by Time-Slices
Dear NG,
I want to query a table like this:
Customer | Money | Time
A 12 12:01
B 17 12:03
C 25 12:06
D 14 12:09
E 10 12:11
...
My target is to have the SUM of Money Transfers by TIME SLICE, eg.
12:00 - 12:05: 29
12:05 - 12:10: 39
12:10 - 12:15: 10
...
How can I query this?
Thank you very much
RudiI am not sure what format your "Time" column is stored, but perhaps this
will get you started:
SELECT SUM(SomeColumn) AS SomeColumn, 'TheTimeSlice' = CASE WHEN TheTime
BETWEEN '12:00' AND '12:05' THEN '12:00 - 12:05' WHEN TheTime BETWEEN
'12:06' AND '12:10' THEN '12:06 - 12:10' END
FROM YourTable
GROUP BY CASE WHEN TheTime BETWEEN '12:00' AND '12:05' THEN '12:00 - 12:05'
WHEN TheTime BETWEEN '12:06' AND '12:10' THEN '12:06 - 12:10' END
Keith Kratochvil
<rudolf.ball@.asfinag.at> wrote in message
news:1141651834.317186.293110@.i39g2000cwa.googlegroups.com...
> Dear NG,
> I want to query a table like this:
> Customer | Money | Time
> A 12 12:01
> B 17 12:03
> C 25 12:06
> D 14 12:09
> E 10 12:11
> ...
> My target is to have the SUM of Money Transfers by TIME SLICE, eg.
> 12:00 - 12:05: 29
> 12:05 - 12:10: 39
> 12:10 - 12:15: 10
> ...
> How can I query this?
> Thank you very much
> Rudi
>|||Thank you very much,
but if I want to split a DAY into 5-MIN-SLICES, how can I do this:
CASE?
Thank you
Rudi|||Please post table DDLs so that others can understand the datatypes, keys,
constraints etc. What is the datatype of the time column? Also time and
money are reserved words in t-SQL.
Your sample data is not very clear. Do you need rows for all 5 minute slice
irrespective of whether a transfer occured or not? If a transfer occured
exactly on 12:05 ( AM/PM ) , do you want to account for it in the 12:00 -
12:05 group or 12:05 - 12:10 group?
As a general response, for such problems consider integer division on the
expression that returns the number of minutes from the starting point. For
instance, in this case it would be: DATEDIFF( minute, 0, time_ ) / 5. You
can use this expression in your GROUP BY clause to return the resultset you
want.
Anith|||Yes you can, by relying on integer division.
CREATE TABLE Test(Customer char(1),Money int,Time smalldatetime)
INSERT INTO Test VALUES ('A',12,'12:01')
INSERT INTO Test VALUES ('B',17,'12:03')
INSERT INTO Test VALUES ('C',25,'12:06')
INSERT INTO Test VALUES ('D',14,'12:09')
INSERT INTO Test VALUES ('E',10,'12:11')
SELECT CONVERT(char(5),DATEADD(minute,
MIN(DATEDIFF(minute,'12:00',Time)/5), '12:00'),108)
, CONVERT(char(5),DATEADD(minute,
MIN(DATEDIFF(minute,'12:00',Time)/5), '12:05'),108)
, SUM("Money")
FROM Test
GROUP BY DATEDIFF(minute,'12:00',"Time")/5
DROP TABLE Test
I guess you have something to study now :-)
Gert-Jan
rudolf.ball@.asfinag.at wrote:
> Dear NG,
> I want to query a table like this:
> Customer | Money | Time
> A 12 12:01
> B 17 12:03
> C 25 12:06
> D 14 12:09
> E 10 12:11
> ...
> My target is to have the SUM of Money Transfers by TIME SLICE, eg.
> 12:00 - 12:05: 29
> 12:05 - 12:10: 39
> 12:10 - 12:15: 10
> ...
> How can I query this?
> Thank you very much
> Rudi|||With SQL 2005, you could create a mapping from time to some integer domain
(say, convert to minutes in the day and divide by 5), then use the AVG(col)
OVER (PARTITION BY integerslice) functionality that was added. This avoids
having to write all of the slices out in a big case statement.
good luck.
Conor
<rudolf.ball@.asfinag.at> wrote in message
news:1141657033.464616.225810@.j33g2000cwa.googlegroups.com...
> Thank you very much,
> but if I want to split a DAY into 5-MIN-SLICES, how can I do this:
> CASE?
> Thank you
> Rudi
>
I want to query a table like this:
Customer | Money | Time
A 12 12:01
B 17 12:03
C 25 12:06
D 14 12:09
E 10 12:11
...
My target is to have the SUM of Money Transfers by TIME SLICE, eg.
12:00 - 12:05: 29
12:05 - 12:10: 39
12:10 - 12:15: 10
...
How can I query this?
Thank you very much
RudiI am not sure what format your "Time" column is stored, but perhaps this
will get you started:
SELECT SUM(SomeColumn) AS SomeColumn, 'TheTimeSlice' = CASE WHEN TheTime
BETWEEN '12:00' AND '12:05' THEN '12:00 - 12:05' WHEN TheTime BETWEEN
'12:06' AND '12:10' THEN '12:06 - 12:10' END
FROM YourTable
GROUP BY CASE WHEN TheTime BETWEEN '12:00' AND '12:05' THEN '12:00 - 12:05'
WHEN TheTime BETWEEN '12:06' AND '12:10' THEN '12:06 - 12:10' END
Keith Kratochvil
<rudolf.ball@.asfinag.at> wrote in message
news:1141651834.317186.293110@.i39g2000cwa.googlegroups.com...
> Dear NG,
> I want to query a table like this:
> Customer | Money | Time
> A 12 12:01
> B 17 12:03
> C 25 12:06
> D 14 12:09
> E 10 12:11
> ...
> My target is to have the SUM of Money Transfers by TIME SLICE, eg.
> 12:00 - 12:05: 29
> 12:05 - 12:10: 39
> 12:10 - 12:15: 10
> ...
> How can I query this?
> Thank you very much
> Rudi
>|||Thank you very much,
but if I want to split a DAY into 5-MIN-SLICES, how can I do this:
CASE?
Thank you
Rudi|||Please post table DDLs so that others can understand the datatypes, keys,
constraints etc. What is the datatype of the time column? Also time and
money are reserved words in t-SQL.
Your sample data is not very clear. Do you need rows for all 5 minute slice
irrespective of whether a transfer occured or not? If a transfer occured
exactly on 12:05 ( AM/PM ) , do you want to account for it in the 12:00 -
12:05 group or 12:05 - 12:10 group?
As a general response, for such problems consider integer division on the
expression that returns the number of minutes from the starting point. For
instance, in this case it would be: DATEDIFF( minute, 0, time_ ) / 5. You
can use this expression in your GROUP BY clause to return the resultset you
want.
Anith|||Yes you can, by relying on integer division.
CREATE TABLE Test(Customer char(1),Money int,Time smalldatetime)
INSERT INTO Test VALUES ('A',12,'12:01')
INSERT INTO Test VALUES ('B',17,'12:03')
INSERT INTO Test VALUES ('C',25,'12:06')
INSERT INTO Test VALUES ('D',14,'12:09')
INSERT INTO Test VALUES ('E',10,'12:11')
SELECT CONVERT(char(5),DATEADD(minute,
MIN(DATEDIFF(minute,'12:00',Time)/5), '12:00'),108)
, CONVERT(char(5),DATEADD(minute,
MIN(DATEDIFF(minute,'12:00',Time)/5), '12:05'),108)
, SUM("Money")
FROM Test
GROUP BY DATEDIFF(minute,'12:00',"Time")/5
DROP TABLE Test
I guess you have something to study now :-)
Gert-Jan
rudolf.ball@.asfinag.at wrote:
> Dear NG,
> I want to query a table like this:
> Customer | Money | Time
> A 12 12:01
> B 17 12:03
> C 25 12:06
> D 14 12:09
> E 10 12:11
> ...
> My target is to have the SUM of Money Transfers by TIME SLICE, eg.
> 12:00 - 12:05: 29
> 12:05 - 12:10: 39
> 12:10 - 12:15: 10
> ...
> How can I query this?
> Thank you very much
> Rudi|||With SQL 2005, you could create a mapping from time to some integer domain
(say, convert to minutes in the day and divide by 5), then use the AVG(col)
OVER (PARTITION BY integerslice) functionality that was added. This avoids
having to write all of the slices out in a big case statement.
good luck.
Conor
<rudolf.ball@.asfinag.at> wrote in message
news:1141657033.464616.225810@.j33g2000cwa.googlegroups.com...
> Thank you very much,
> but if I want to split a DAY into 5-MIN-SLICES, how can I do this:
> CASE?
> Thank you
> Rudi
>
Monday, February 20, 2012
query analyzer's issue
Dear all,
Because of the large amount of live servers i am obligated to have in my
desktop
six or seven query analyzer sessions (linking servers is forbidden) against
them.
Sometimes and suddenly i lost my keyboard configuration and when I touch the
* key appears another character (this happen in one query analyzer not for
all)
It's a very strange behaviour.
Is there anybody here is suffering a similar problem?
Thanks in advance and best regards,XP professional with SP2.
language sp: english
keyboard: spanish.
Cheers,
"Enric" wrote:
> Dear all,
> Because of the large amount of live servers i am obligated to have in my
> desktop
> six or seven query analyzer sessions (linking servers is forbidden) agains
t
> them.
> Sometimes and suddenly i lost my keyboard configuration and when I touch t
he
> * key appears another character (this happen in one query analyzer not for
> all)
> It's a very strange behaviour.
> Is there anybody here is suffering a similar problem?
> Thanks in advance and best regards,
Because of the large amount of live servers i am obligated to have in my
desktop
six or seven query analyzer sessions (linking servers is forbidden) against
them.
Sometimes and suddenly i lost my keyboard configuration and when I touch the
* key appears another character (this happen in one query analyzer not for
all)
It's a very strange behaviour.
Is there anybody here is suffering a similar problem?
Thanks in advance and best regards,XP professional with SP2.
language sp: english
keyboard: spanish.
Cheers,
"Enric" wrote:
> Dear all,
> Because of the large amount of live servers i am obligated to have in my
> desktop
> six or seven query analyzer sessions (linking servers is forbidden) agains
t
> them.
> Sometimes and suddenly i lost my keyboard configuration and when I touch t
he
> * key appears another character (this happen in one query analyzer not for
> all)
> It's a very strange behaviour.
> Is there anybody here is suffering a similar problem?
> Thanks in advance and best regards,
Subscribe to:
Posts (Atom)