In old advantagedatabase it was normal to have a "select * from mytable" without loading all data to client and doing after a .filtered .... It opened only a cursor and was super fast.

In edb i see "select * from table" needs >20 seconds for a table having lot of rows (>200.000) and columns (>50), this is in edbmanager.

I thinked that edb is optimized for such situations ... or we have a config-problem?

Yusuf Zorlu
MicrotronX

Yusuf,

Do you have RequestSensitive set to TRUE ?

--
Fernando Dias
[Team Elevate]

Fernando Dias wrote:
>> Do you have RequestSensitive set to TRUE ?

NO, it was a query within ElevateDBManager withouth the two Checkboxes on left marked.

Yusuf Zorlu
MicrotronX

Yusuf


As Fernando is hinting - try it with request sensitive checked - if a sensitive result set can be returned you'll see plenty of speed. If not a lot of the time is taken writing the temporary table to disk.

Its also worth checking the execution plan since this will let you know what's going on behind the scenes and will often suggest ways to improve performance.

Roy Lambert

Roy,

do we have any performance-problems if we use sensitive queries in all cases where this is possible?
Anything that can happen if we use them a lot?

Yusuf Zorlu
MicrotronX

Yusuf Zorlu wrote:
>>
do we have any performance-problems if we use sensitive queries in all cases where this is possible?
Anything that can happen if we use them a lot?
>>

No and in most cases sensitive results sets are likely preferable.  You need to be aware of the rules for obtaining sensitive results sets (indexes might be needed etc) and that edits would affect actual underlying data - see this section of manual for lot of useful info on this :

https://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Result_Set_Cursor_Sensitivity
  
Raul

Yusuf


Pretty much as Raul says.

A sensitive result set is pretty much like opening a table. Grab a handle, apply any filters and GO

If you want columns in from other tables AND want to be able to edit the base table then don't use JOIN use a subselect instead.

Roy Lambert

Yusuf,

<< do we have any performance-problems if we use sensitive queries in all cases where this is possible?
Anything that can happen if we use them a lot? >>

No, no problems.  In fact, this is what the ODBC driver does if the client application doesn't specify that it *wants* a static (insensitive) result set for a given SQL SELECT statement.

Tim Young
Elevate Software
www.elevatesoft.com

I had recently done some testing on one of our applications relating to this.  We had a query that was selecting a subset of our product file that would take about 3 seconds as it was insensitive and the result was about 75,000 records.  If I changed the query to sensitive the result was about 0.5 seconds.  The problem we ran into was there were subsequent filters that were set on the result.  The insensitive results would filter quickly (< 1 second) but the sensitive result was slow at about 2 seconds for the same result.  Ultimately we had better overall useability with the insensitive result set.

Greg

>I had recently done some testing on one of our applications relating to this. We had a query that was selecting a subset of our product file that would take about 3 seconds as it was insensitive and the result was about 75,000 records. If I changed the query to sensitive the result was about 0.5 seconds. The problem we ran into was there were subsequent filters that were set on the result. The insensitive results would filter quickly (< 1 second) but the sensitive result was slow at about 2 seconds for the same result. Ultimately we had better overall useability with the insensitive result set.

My bet would be on not having indices for the filters you were setting. Insensitive result set c75k records to scan, sensitive - now many? It might be interesting to just apply the filters to table as a query in EDBManager and see what it tells you/

Roy