Speed differences

[psilva]

Is there any reason why a query on a pl-sql window takes 3.6 seconds to retreive about 5200 records and using a TOracleDataSet takes about 5 minutes (paste of the same SQL on a sql window of a TOracleDataSet)?

If necessary I can provide a small example of the sql (it is quite complex).

Thanks

 

[Marco Kalter]

There is no obvious reason. Perhaps your application does some additional processing for each record? Like a lookup field, or a calculated field?

If not, perhaps you can use SQL Trace to verify that both queries perform the same operations on the database server.

 

[psilva]

When I say on a TOracleDataSet query window I mean during design time and getting the result by pressing the run button. It as the same speed retrieving the first 3600 records but after that it starts getting chunks of 25 records and taking about 2 seconds per 25 records. Is exactly the same query without any variables and by pasting from the pl-sql window.
If you can see any reason why it is happening I'll be very pleased.
Thanks

 

[Marco Kalter]

I can only imagine that Oracle has some problems finding these records after the 3600th record.

If you use SQL Trace you may be able to find out what the problem is.

 

[Marco Kalter]

One more thing: you could first try to set TOracleDataSet.ReadBuffer to 100. This is what PL/SQL Developer uses for the SQL Window.

 

[psilva]

I Solved by changng the sql, but I still doesn't understand why it different executing the same sql...

 

[Marco Kalter]

What changes did you make to the SQL?