Board index » delphi » How to keep a reasonable response time when a time-consuming SQL statement running

How to keep a reasonable response time when a time-consuming SQL statement running

D4,SQL server7.0
I have a table which has near 1,000,000 recards,when I run a time-consuming
SQL statement, such as 'select' statement returning a large result dataset
and with  'group by' and 'order by' clauses, the response time for other
clients becomes unendurable.

Thanks for any tip.

 

Re:How to keep a reasonable response time when a time-consuming SQL statement running


Quote
Lan wrote in message <8c3nnp$5q...@bornews.borland.com>...
>D4,SQL server7.0
>I have a table which has near 1,000,000 recards,when I run a time-consuming
>SQL statement, such as 'select' statement returning a large result dataset
>and with  'group by' and 'order by' clauses, the response time for other
>clients becomes unendurable.

1. Make sure you have appropriate indexes on the table - whatever fields you
are trying to select by and order by.

2. If you are not using a Where clause in your select statement, nows the
time to start. If you are already using a Where clause, then try to restrict
it further. The user should not need to fetch any significant fraction of
those million records.

3. Unless you are explicitly fetching all the records (e.g. executing a
TQuery.Last or if the user pulls the scrollbar thumb all the way to the
bottom of a DGBrid), then all records shouldn't be getting fetched - only
one, or if a DBGrid is attached, enough to fill the grid. More records are
only fetched as needed. Are you using the BDE or a 3rd party product to
connect to MSSQL7? If a 3rd party product, see if it offers choice of
server-side client vs client-side cursor. Choose server-side.

--
Wayne Niddery - WinWright Consulting
RADBooks - http://members.home.net/wniddery/
You have a Right to Free Speech, but not the right to make me listen, nor to
use my property as a soapbox.

Re:How to keep a reasonable response time when a time-consuming SQL statement running


This is a question of performance tuning.  As an application developer, all
I can suggest is
1. Avoid TTable whenever possible.
2. Limit the number of records in the result set to the minimum.
3. Tune your SQL to get the best performance.
4. Use stored proc for very complex but slow SQL.
5. And last, look for a stable and fast BDE replacement.

There are a lot that can be done in harware and database configuration too.
But that is beyond the scope of an application developer.

Ping

Quote
Lan <strongl...@china.net> wrote in message

news:8c3nnp$5q317@bornews.borland.com...
Quote
> D4,SQL server7.0
> I have a table which has near 1,000,000 recards,when I run a
time-consuming
> SQL statement, such as 'select' statement returning a large result dataset
> and with  'group by' and 'order by' clauses, the response time for other
> clients becomes unendurable.

> Thanks for any tip.

Re:How to keep a reasonable response time when a time-consuming SQL statement running


The problem is better stated
"How can I redesign the database OR restructure the Query so that
the DBMS can look at fewer rows?"

Quote
Lan <strongl...@china.net> wrote in message

news:8c3nnp$5q317@bornews.borland.com...
Quote
> D4,SQL server7.0
> I have a table which has near 1,000,000 recards,when I run a
time-consuming
> SQL statement, such as 'select' statement returning a large result dataset
> and with  'group by' and 'order by' clauses, the response time for other
> clients becomes unendurable.

> Thanks for any tip.

Other Threads