Board index » delphi » increasing speed of querying on MS SQL 6.5

increasing speed of querying on MS SQL 6.5

Hello,

I'm writing an application which has to display a lot of informations at
once. There are about ten tables relating to a record in an eleventh table.
Each table will be accessed via a query.
But every query has an overhead of about 500ms. (SQL-Server tracing told
that the execution takes about some milliseconds). The whole access to the
data needs about 6 seconds !!
Some tests showed that the BDE needs a lot of time with opening an internal
connection or anything this kind.

There I see two ways around this problem:

1.) Using a query with all subqueries in it. But there is no way of
receiving the records. A BDE-Error occurs when I reach the next section of
data.
the query SQL looks like this:

  SELECT field1, field2, field3 FROM foo1 WHERE lngID = 17
  SELECT fieldx, fieldy FROM foo2 WHERE lngID = 17
  SELECT another_field FROM foo3 WHERE lngID = 17

There is no problem on executing this SQL on the Server. The result is
correct. But how to get the data ?

2.) Fasten the access of each query. But I don't know how to decrease the
overhead of the BDE. Is there any way of direct access to the data
functions of SQL-Link to MS SQL ?

Has anybody some ideas on how to solve the speed problem.

thanks

Mike

--------------------------------------
Michael Schwarzl
Softwareentwicklungen
Zeisigstra?e 61
A-5023 Salzburg
AUSTRIA
schwa...@msse.co.at

 

Re:increasing speed of querying on MS SQL 6.5


Michael Schwarzl <schwa...@msse.co.at> wrote in article
<01bc1c61$720d9730$ef3c50c1@pentium>...

Quote
> Hello,

> I'm writing an application which has to display a lot of informations at
> once. There are about ten tables relating to a record in an eleventh
table.
> Each table will be accessed via a query.
> But every query has an overhead of about 500ms. (SQL-Server tracing told
> that the execution takes about some milliseconds). The whole access to
the
> data needs about 6 seconds !!
> Some tests showed that the BDE needs a lot of time with opening an
internal
> connection or anything this kind.

> There I see two ways around this problem:

> 1.) Using a query with all subqueries in it. But there is no way of
> receiving the records. A BDE-Error occurs when I reach the next section
of
> data.
> the query SQL looks like this:

>   SELECT field1, field2, field3 FROM foo1 WHERE lngID = 17
>   SELECT fieldx, fieldy FROM foo2 WHERE lngID = 17
>   SELECT another_field FROM foo3 WHERE lngID = 17

> There is no problem on executing this SQL on the Server. The result is
> correct. But how to get the data ?

> 2.) Fasten the access of each query. But I don't know how to decrease the
> overhead of the BDE. Is there any way of direct access to the data
> functions of SQL-Link to MS SQL ?

One way of doing it would be to compose a SINGLE query that will fetch all
the
data you need, something like this:

SELECT  A.FIELD1, A.FIELD2, A.FIELD3, B.FIELDX, B.FIELDY, C.ANOTHER_FIELD
FROM FOO1 A, FOO2 B, FOO3 C
WHERE A.INGID = 17
AND B.INGID = A.INGID
AND C.INGID = A.INGID

It should work and it should work much faster then executing 11 queries
with same criteria.
Bad news is that this sort of query (from more than one table )doesn't
return live result set, i.e. it
is READ ONLY.

Hope this was some help.

Robert Merksa, INTER-NET Zagreb, Croatia

Re:increasing speed of querying on MS SQL 6.5


How about consider writing a stored-procedure with input parameters ?
It will execute in lighting fast speed and less network overhead.

--
<Tommy_F...@MSN.COM>

Robert <rmer...@alf.tel.hr> wrote in article
<01bc1cb5$09c38c80$d7cd64ce@robert2>...

Quote

> Michael Schwarzl <schwa...@msse.co.at> wrote in article
> <01bc1c61$720d9730$ef3c50c1@pentium>...
> > Hello,

> > I'm writing an application which has to display a lot of informations
at
> > once. There are about ten tables relating to a record in an eleventh
> table.
> > Each table will be accessed via a query.
> > But every query has an overhead of about 500ms. (SQL-Server tracing
told
> > that the execution takes about some milliseconds). The whole access to
> the
> > data needs about 6 seconds !!
> > Some tests showed that the BDE needs a lot of time with opening an
> internal
> > connection or anything this kind.

> > There I see two ways around this problem:

> > 1.) Using a query with all subqueries in it. But there is no way of
> > receiving the records. A BDE-Error occurs when I reach the next section
> of
> > data.
> > the query SQL looks like this:

> >   SELECT field1, field2, field3 FROM foo1 WHERE lngID = 17
> >   SELECT fieldx, fieldy FROM foo2 WHERE lngID = 17
> >   SELECT another_field FROM foo3 WHERE lngID = 17

> > There is no problem on executing this SQL on the Server. The result is
> > correct. But how to get the data ?

> > 2.) Fasten the access of each query. But I don't know how to decrease
the
> > overhead of the BDE. Is there any way of direct access to the data
> > functions of SQL-Link to MS SQL ?

> One way of doing it would be to compose a SINGLE query that will fetch
all
> the
> data you need, something like this:

> SELECT  A.FIELD1, A.FIELD2, A.FIELD3, B.FIELDX, B.FIELDY, C.ANOTHER_FIELD
> FROM FOO1 A, FOO2 B, FOO3 C
> WHERE A.INGID = 17
> AND B.INGID = A.INGID
> AND C.INGID = A.INGID

> It should work and it should work much faster then executing 11 queries
> with same criteria.
> Bad news is that this sort of query (from more than one table )doesn't
> return live result set, i.e. it
> is READ ONLY.

> Hope this was some help.

> Robert Merksa, INTER-NET Zagreb, Croatia

Other Threads