Retrieving rows using a non-sensitive query on a large table will take considerably longer than a sensitive query, especially when sorting (on indexed columns). That is because it needs to create a temporary table and sort it which means a great deal more disk activity.

So what are the advantages of explicitly setting the query to non-sensitive *IF* a sensitive query would execute faster?  (They query has an existing index that can retrieve or sort the rows for the same query but it would be sensitive.) The same rows would be returned in either case.

If I want the TEDBQuery  to be non-updateable why can't I simply set TEDBQuery to ReadOnly?
Is there an advantage to forcing the query to be insensitive?

TIA
Barry

>>If I want the TEDBQuery  to be non-updateable why can't I simply set TEDBQuery to ReadOnly?

This will work, the dataset will not be editable by the user.

>>Is there an advantage to forcing the query to be insensitive?

Others have more technical knowledge, but I am pretty sure that sensitive queries create locks on tables which make certain types of DB action more complex and perhaps slower, particularly if you have many users.

i.e. I think that prior to a Delete/Post/Update the DB engine has to check whether any other sensitive query is editing the posted row, whereas if the query is non-sensitive, once it is opened the DB no longer references it at all.

Sensitive queries also have to update all indexes and other constraints / triggers on the db each time they post, even if you are not actually posting I guess that the DB must assume that you might, and therefore deal with this.

Finally, if an app crashes or drops out (i.e. network failure) while a sensitive query is "live" the DB will keep that session open. This can make it hard to undertaken DB level operations (DROP table etc.) as these require a full lock on the DB.

Overall I don't have a clear idea of the impact of any of this on performance.

I use non-sensitive queries almost all the time simply because it fools "safer" in terms of the lack of communication with the DB.

On 1/2/2015 12:30 PM, Barry wrote:
> If I want the TEDBQuery  to be non-updateable why can't I simply set TEDBQuery to ReadOnly?
> Is there an advantage to forcing the query to be insensitive?

Depends on your app and its logic. Sensitive read-only query would not
let you modify data but you would still see changes made by anybody else
while insensitive would represent a static resultset of the data.

Adam covered the fact that there are additional read locks with
sensitive results.

I'd just add that where you might want insensitive results is complex
computation scenarios (especially if you need to do multiple passes on
data and data to remain same) or anything that needs to have a static
snapshot in time.

Finally of course note that even if you request sensitive depending on
sql it might end up being non-sensitive so be prepared to deal with that.


Raul

Barry


As far as I'm concerned there are very few occasions when you shouldn't use a sensitive query, especially if it will generate a large result set and even more so if you then have to create an index that already exists on the source table.

I will always look to see if a query can be made sensitive with one exception - if I want to extract the data and do something with it and guarantee that the data I'm working on doesn't change until I'm ready for it. Forget mega scientific calculations think accounts.

The speed difference can be so great that from a user perspective you would be unusually cruel to use an insensitive result set unless its needed.

Roy Lambert

Adam


>Others have more technical knowledge

Especially if their initials are TY

>
>i.e. I think that prior to a Delete/Post/Update the DB engine has to check whether any other sensitive query is editing the posted row, whereas if the query is non-sensitive, once it is opened the DB no longer references it at all.
>
>Sensitive queries also have to update all indexes and other constraints / triggers on the db each time they post, even if you are not actually posting I guess that the DB must assume that you might, and therefore deal with this.

I'm not sure about this and lacking the source I can't look, but I can't see any reason why Tim would have a check built in for pre/post update behaviour that's triggered when a post isn't happening. At worst I can see a few bytes in memory being wasted but that's it.

>Finally, if an app crashes or drops out (i.e. network failure) while a sensitive query is "live" the DB will keep that session open. This can make it hard to undertaken DB level operations (DROP table etc.) as these require a full lock on the DB.

I would have expected the server to sort this out (don't use c/s enough to say one way or the other) but I have experienced it, infrequently, with f/s. You could well be right as to the root cause. On the negative side, if there's an app crash the temporary tables will be left cluttering up the disk.

>I use non-sensitive queries almost all the time simply because it fools "safer" in terms of the lack of communication with the DB.


I do LIKE that type, at least I hope it was a type <vbg>


Roy Lambert

Raul

>Depends on your app and its logic. Sensitive read-only query would not
>let you modify data but you would still see changes made by anybody else
>while insensitive would represent a static resultset of the data.

Almost, but not quite true (at least until V3 anyway). To see changes others have made needs something happening that will trigger a refresh of the data.

>Adam covered the fact that there are additional read locks with
>sensitive results.
>
>I'd just add that where you might want insensitive results is complex
>computation scenarios (especially if you need to do multiple passes on
>data and data to remain same) or anything that needs to have a static
>snapshot in time.

Totally agree

>Finally of course note that even if you request sensitive depending on
>sql it might end up being non-sensitive so be prepared to deal with that.

True but as Barry describes it I don't think that would be a problem. I dread to think what would happen if the reverse (ask for insensitive and get sensitive)  were possible.

Roy Lambert

On 1/3/2015 4:24 AM, Roy Lambert wrote:
> Almost, but not quite true (at least until V3 anyway). To see changes others have made needs something happening that will trigger a refresh of the data.

I definitely see this with v2 (in C/S mode at least - have not tried f/s ).

You do need a sufficiently large table or otherwise RemoteReadSize might
mask the change in the initial row fetch.

Sample scenario:
- app instance A opens a sensitive query to the table
- app instance B changes the data in the underlying table at this point
- app instance A now will see new data while looping thru resultset

Easiest way to see is just to make instance A pause (i.e.

....
qryA.RequestSensitive := True;
qryA.Active := True;
ShowMessage('Go change something in underlying table');
while qryA.Eof = false do
begin
 //will see the change made while dialog was active

....
)

Raul

Raul

>> Almost, but not quite true (at least until V3 anyway). To see changes others have made needs something happening that will trigger a refresh of the data.
>
>I definitely see this with v2 (in C/S mode at least - have not tried f/s ).
>
>You do need a sufficiently large table or otherwise RemoteReadSize might
>mask the change in the initial row fetch.
>
>Sample scenario:
>- app instance A opens a sensitive query to the table
>- app instance B changes the data in the underlying table at this point
>- app instance A now will see new data while looping thru resultset
>
>Easiest way to see is just to make instance A pause (i.e.
>
>...
>qryA.RequestSensitive := True;
>qryA.Active := True;
>ShowMessage('Go change something in underlying table');
>while qryA.Eof = false do
>begin
> //will see the change made while dialog was active

Yes. You illustrate my point perfecty - you are carrying out an action that causes a refresh of the data. There have been lots (and lots) of posts asking about getting data to refresh after user A has changed it so that user B etc can see the changes. I just didn't want anyone thinking that simply by using sensitive queries they would solve that problem.

Providing you don't move off the current buffer or try and edit a record I think the display will stay the same until the PC turns to dust.

Roy Lambert

On 1/4/2015 4:14 AM, Roy Lambert wrote:
> Yes. You illustrate my point perfecty - you are carrying out an action that causes a refresh of the data. There have been lots (and lots) of posts asking about getting data to refresh after user A has changed it so that user B etc can see the changes. I just didn't want anyone thinking that simply by using sensitive queries they would solve that problem.

I was just just showing that with sensitive resultset you would see
changes made by somebody else as you go thru the resultset in the normal
usage (i.e. the main difference between using static and sensitive
resultsets in EDB).

The issue of seeing changes to a record you're currently on without
moving a cursor is a slightly different issue IMHO. Not sure how Tim
plans to implement it but i for sure would like an ability to turn that
capability off (for performance reasons) - maybe add another cursor type
"sensitive with notify" for that case. Time will tell.

Raul

Raul wrote:

On 1/2/2015 12:30 PM, Barry wrote:
>> If I want the TEDBQuery  to be non-updateable why can't I simply set TEDBQuery to ReadOnly?
>> Is there an advantage to forcing the query to be insensitive?

>Depends on your app and its logic. Sensitive read-only query would not
>let you modify data but you would still see changes made by anybody else
>while insensitive would represent a static resultset of the data.

Here is an example that may clear things up. The app will display a status grid that shows the operating status of equipment in a building. The grid accesses a single table (no joins) and this table is being updated (hourly, or every minute?) in the background by another program. It is considerably faster for the status grid access this table if it is sensitive because it can use the existing indexes for sorting and the where clause.

>Adam covered the fact that there are additional read locks with
sensitive results.<

>I'd just add that where you might want insensitive results is complex
>computation scenarios (especially if you need to do multiple passes on
>data and data to remain same) or anything that needs to have a static
>snapshot in time.

Correct. But in this case all of the computations have been done by the other program that is running in the background.

>Finally of course note that even if you request sensitive depending on
>sql it might end up being non-sensitive so be prepared to deal with that.

Yes. But in this case since the grid is not updating the data, the only affects of a non-sensitive query will be speed.

Raul