Loading all DB entries each time vs loading only new ones (and how to identify them)

I have a JSF Page (Primefaces) with a datatable that contains entries from a Database. Thoes entries represent transactions created by users.

Each time this page is loaded, I do this:

  • Clear the table
  • Call a service that gives me all entries
  • Fill it again with these entries

Since the Bean containing the entries could be SessionScoped, I wonder if it would be better to only load new entries each time.

But then I thought that a “Select x,y,z from table” is probably very fast anyway, and that I don’t know yet how I would know which entries are new.

I am looking for a good way to load the new ones. So far, the best idea I had is to load only the ones that were created after the newest one in my datatable.

Any other suggestions ? I did not find many “good practices” for dataTables containing DB entries.


First, make sure your program really needs an optimization. If your program is simple and fast enough for all current use cases, don’t make things complicate by trying to add some fancy optimized loading mechanism for the sake of some speed noone really requires.

But let’s assume your users are complaining about the slowness of your program when loading the data in stake, so next thing is to measure the current speed and make sure the performance is really in relation to the number of records returned by your query. For many db applications I have seen, this was not the case, often the number of queries (and not the number of returned records) is what dominates the speed of the loading process.

So if you are 100% that is causing the problem, for the optimization you have in mind next thing is to make sure the already loaded records don’t get changed between two page load operations, so loading only newer records and merging them with the already loaded data will lead to a valid result which actually reflects what is in the database.

When you know for sure that is the case, you need a column in your table which allow you to distinguish “older” and “newer” records. This may be typically an autoincremented ID key or a timestamp value which indicates the creation time of the record. Without such a column, you won’t have much chances to make this kind of optimization work. If there is such a column at hand, calculate the maximum ID value or timestamp value of the already loaded data and add a “WHERE” condition of the form ".. WHERE id > maxID" to your query.

Finally, don’t forget to measure again to verify your optimization really increased the performance. If it does, keep it, if not, do yourself a favor and throw your optimized solution away by replacing it by the unoptimized (but most probably simpler, more maintainable) original code.

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *