Roy Lambert wrote:


>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.<

That was my sentiments too. Thanks.

Barry

On 1/4/2015 1:06 PM, Barry wrote:
> 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.

Yes, sensitive would make more sense here - you would not want to reload
query every time there is a smallest change. Open sensitive query once
for the life of the grid and just refresh on demand.

If the grid is smart enough it should be able to minimize re-drawing
even by only updating changed cells and take advantage of paging etc.

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

Sounds good. How does your grid know to refresh data? Is it fixed
interval (timer) or have you built in messaging?

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

The main issue as is see it here is that using non-sensitive query you'd
need to close and reopen query all the time to display changes and this
will likely trigger a re-draw of the grid. Sensitive would eliminate the
need for first and hopefully limit the 2nd (with a good grid control).

Raul

Raul wrote:

On 1/4/2015 1:06 PM, Barry wrote:
>> 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.<<

>Yes, sensitive would make more sense here - you would not want to reload
query every time there is a smallest change. Open sensitive query once
for the life of the grid and just refresh on demand.

If the grid is smart enough it should be able to minimize re-drawing
even by only updating changed cells and take advantage of paging etc.
<

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

>Sounds good. How does your grid know to refresh data? Is it fixed
interval (timer) or have you built in messaging?<

It takes approx 0.2 seconds to open the sensitive query compared to around 3 seconds for the insensitive query.
For the moment, I will refresh the query every 30 seconds which takes even less time. I can definitely see the need for a messaging system if the background updates were happening every few seconds. In which case I could have the background process update a one row table with a timestamp and have the client app poll that table every 3-5 seconds or so to see if the timestamp has changed (which indicates the status table has changed). That would eliminate any unnecessary refreshes for the grid table. It is not as efficient as a messaging system, but should work. (I will have to look into messaging to see how difficult it would be).

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

>The main issue as is see it here is that using non-sensitive query you'd
need to close and reopen query all the time to display changes and this
will likely trigger a re-draw of the grid. <

An insensitive query, if I am not mistaken, will reload the row changes when it is refreshed too. I shouldn't have to close and reopen the insensitive query to see the row changes that were made from another process/user. Otherwise, like you said, it would be really slow and CPU intensive.

Barry

On 1/4/2015 4:01 PM, Barry wrote:
> For the moment, I will refresh the query every 30 seconds which takes even less time. I can definitely see the need for a messaging system if the background updates were happening every few seconds. In which case I could have the background process update a one row table with a timestamp and have the client app poll that table every 3-5 seconds or so to see if the timestamp has changed (which indicates the status table has changed). That would eliminate any unnecessary refreshes for the grid table. It is not as efficient as a messaging system, but should work. (I will have to look into messaging to see how difficult it would be).

Polling would likely be OK in this case and then wait for v3 which
hopefully will have some type of messaging built-in.

> An insensitive query, if I am not mistaken, will reload the row changes when it is refreshed too. I shouldn't have to close and reopen the insensitive query to see the row changes that were made from another process/user.

You're right of course - one little beer and i'm posting nonsense

Refresh works for both similarly. With sensitive query and regular
dataset navigation one might not need always even need refresh (one
always does for insensitive). However in this case i do agree that
sensitive with refresh is the best (and fastest) option.

Raul

Barry


Reading your post to Raul then, unless there are a lot of calculated columns which would need to be handled in OnCalcFields, I would probably just use a table set to read only. The type of query you're talking about is essentially a table with a filter applied, Since its sensitive and there are no joins a simple filter can be used. A major reason I do this where I can is that I can change index and hence display order without having to close/open the query.

Roy Lambert

Raul,

That would make for a great bumper sticker.

   "Don't Drink And SQL"

LOL

Barry