Answered Browse Open Query on temp table or bare table?

Hello Guys,

I have a performance/best practice question:
When doing an open-query for a browse I choose to go with my database table directly instead of compiling all the data in a temp-table first and display them after. It allows me the following:
1. No loading time
2. live update of data whilde browsing
3. low number of row load in browse buffer (arround 70)

But I will do a massive number of reading on the database.

Which is the better solution? Why? What is the limit to choose one on another ?

Best Regards,

BobyIsProgress
 
Last edited:

tamhas

ProgressTalk.com Sponsor
Say a bit about how you are controlling transaction scope. One of the reason for working against local tables or variables is so that there is no open transaction while you are doing the work and a very short transaction with no UI to commit the changes to the DB when done.
 
Yes sorry, I miss led you. The application I'm working on is a dashboard for sales management.
I will display data about ordre quantity stock etc... this value are updated other time by other user action. It's that kind of dynamic data that could change other time and be refreshed while scrolling up and down on the browse.
 

TomBascom

Curmudgeon
I see no overwhelmingly bad universal issue with NO-LOCK queries drawing data directly from the db in this way.

I would be a bit concerned about how often it refreshes and what the impact of that query is. But if the number of record reads is small and the refresh rate is "occasional" then it should be fine.

That's all generically true of any query - regardless of what you are using it for ;)

Specific to presenting data with a "browse" I _would_ use a temp-table if I were merging data from multiple sources or if a TT representation could be used to simplify or cleanup some of the data elements. For instance, the source fields might use lots of numeric codes that are better expressed in "human readable" format. Rather than use a row-display trigger to translate I would tend to do that sort of thing by building a TT and translating right up front.

I would also use a TT if my application has good separation between data access and UI layers. Or if I have ambitions to make things better that way. I wouldn't want to be the person who introduces needless binding to the db into the code base.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
If your table has many fields and your browse only shows a subset of them, you may want to add a field list to the query so your aren't bringing data to the client that it doesn't need.
 
Thank you @Rob Fitzpatrick for the idea of field list.

@TomBascom, I have another question on the matter that is related to the fact of using a temp-table or to be directly using the DB.
In term of user flow for using the app, when I'm directly connected to the DB the loading time of the data is less than 1 sec and so transparent for the user.
If I load data in a temp-table, I will have to choose on two ways of doing it:
1. loading all the data at startup and so have a big delay needed for loading them
2. ask the user to filter first their search and then load the data.

I don't really know what is the more user friendly way of doing it.

Do you have a pro and con ? Or is it that I just complicated thinks that don't have to ?
 

TomBascom

Curmudgeon
I think that you are over-thinking it,

The temp table does not need to be a full copy of the source table. It can just as easily have the same filters applied when you populate it as you are mentioning for the db table and, thus, should take no longer to build and access. If the user selects new filters empty the TT and rebuild it just as you would re-open the query on the db table.
 
in fact yes you are totally right. I will try to build it that way :)

Thank you I consider my Question Answered :)

Thank you all for your help as always
 
Top