Hi to all,
I have a big performance problem with looping threw rdoresultset.
I made some very complex sql queries. They link like 5 tables and uses a
couple subqueries. The number of returned rows is something like 1000. Query
execution takes about 1 second, which is completly acceptable. But before I
can display result to the user, I need to perform some calculations over
returned rows. To loop threw resultset (movenext) takes 2 seconds, which is
VERY slow! The procedure is very optimized, so each value in resultset in
fetched only once. How can I access all data in the resultset in a much more
efficient way? I do not need to update any row, all I need to is to get
values from resultset.
Query is optimized for fast data access. I can not perform arithmetic
operations directly in query. The amount of data returned by the server is
something like 15kb, which is very small. I am using RDO and cursor library
is rdUseOdbc. For resultset I tryed to use different parameters, ie
readonly, forwardonly,... but performance does not change. And the server I
uses is SQL serer 2000.
Any idea would be really appreciated!!
Marko ErzenHi
As you don't show code then it is not possible to say if your evaluation
that the work can not be done in T-SQL is correct. You don't say how you are
connecting, but using OLEDB and not ODBC would help. If you have to use ODBC
makes sure that you are not using tracing on the ODBC connection. Also try
using rdUseServer instead of rdUseODBC. You may also want to see if ADO
performs better.
John
"Marko Erzen" wrote:
> Hi to all,
> I have a big performance problem with looping threw rdoresultset.
> I made some very complex sql queries. They link like 5 tables and uses a
> couple subqueries. The number of returned rows is something like 1000. Que
ry
> execution takes about 1 second, which is completly acceptable. But before
I
> can display result to the user, I need to perform some calculations over
> returned rows. To loop threw resultset (movenext) takes 2 seconds, which i
s
> VERY slow! The procedure is very optimized, so each value in resultset in
> fetched only once. How can I access all data in the resultset in a much mo
re
> efficient way? I do not need to update any row, all I need to is to get
> values from resultset.
> Query is optimized for fast data access. I can not perform arithmetic
> operations directly in query. The amount of data returned by the server is
> something like 15kb, which is very small. I am using RDO and cursor librar
y
> is rdUseOdbc. For resultset I tryed to use different parameters, ie
> readonly, forwardonly,... but performance does not change. And the server
I
> uses is SQL serer 2000.
>
> Any idea would be really appreciated!!
> Marko Erzen
>
>|||Hi
If I use the rdUseServer then query executes four times slower. But movenext
method is fast, very fast. The total duration of the query and local
processing is almost equal using the tdUseODBC. But in this case all other
queries in the application slows down too much. So rduseServer is not a good
option.
I also tryed using the ADO connection. The performance is almost the same as
using the RDO object. And I am not tracing queries.
Here is a chunk of the SQL in question:
SELECT am.datum, rtm.hotel_code, rtm.summary_code, rtm.room_type,
rtm.regular_rooms AS total_rooms, restm.definite,
ISNULL((SELECT SUM(ISNULL(re.nr_of_rooms,0)) FROM
reservations re,
reservation_type rest
WHERE re.reservat_type = rest.reservat_type AND
re.reservation_nr > 0 AND re.reservation_nr = re.group_res_nr AND
NOT (re.arrival < '2004-06-07' AND re.check_in = 0) AND
re.canceled_on IS NULL AND
((re.group_arrival <= '2004-07-02' AND re.group_departure > '2004-06-07') OR
(re.group_arrival = re.group_departure AND re.group_arrival = '2004-06-07'))
AND
re.group_arrival <= am.datum AND
re.group_departure > am.datum AND
rest.definite = restm.definite AND
re.hotel_code = rtm.hotel_code AND
re.summary_code= rtm.summary_code AND
re.room_type = rtm.room_type AND
am.datum BETWEEN '2004-06-07' AND '2004-07-02' AND
rest.definite IN (0) AND
re.hotel_code IN ('GHE','HH','HVP') ),0) AS occ_rooms ,
ISNULL((SELECT COUNT(DISTINCT roomstat_nr) FROM roomstat rs WHERE
rs.hotel_code = rtm.hotel_code AND
rs.summary_code = rtm.summary_code AND
rs.room_type = rtm.room_type AND
rs.fromdate <= am.datum AND
rs.todate > am.datum AND
rs.fromdate <= '2004-07-02' AND
rs.todate >= '2004-06-07' AND
am.datum BETWEEN '2004-06-07' AND '2004-07-02' AND
rs.hotel_code IN ('GHE','HH','HVP') ),0) AS oo
FROM avail am,
room_type rtm,
reservation_type restm WHERE
am.datum BETWEEN '2004-06-07' AND '2004-07-02' AND
rtm.regular < 5 AND
restm.definite IN (0) AND
rtm.hotel_code IN ('GHE','HH','HVP')
GROUP BY am.datum, rtm.hotel_code, rtm.summary_code, rtm.room_type,
rtm.regular_rooms, restm.definite
ORDER BY am.datum, rtm.hotel_code, rtm.summary_code, rtm.room_type,
restm.definite
"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:B97359C8-E1C1-4207-AA51-AA846D1FB9C7@.microsoft.com...
> Hi
> As you don't show code then it is not possible to say if your evaluation
> that the work can not be done in T-SQL is correct. You don't say how you
are
> connecting, but using OLEDB and not ODBC would help. If you have to use
ODBC
> makes sure that you are not using tracing on the ODBC connection. Also try
> using rdUseServer instead of rdUseODBC. You may also want to see if ADO
> performs better.
> John
> "Marko Erzen" wrote:
>
Query
before I
is
in
more
is
library
server I|||Hi
SUM(ISNULL(re.nr_of_rooms,0)) is not needed, you may get a warning for
nulls but it would expect nr_of_rooms to be not null with a default of
0.
You may want to use derived tables for your sub-queries. This is
usually quicker for larger result sets.
I would prefer to use JOIN syntax as this may make it clearer.
Look at the query plan and possibly try the index tuning wizard.
John
