Jump to content

MichaelT

Members
  • Content Count

    1
  • Joined

  • Last visited

  • Days Won

    1

MichaelT last won the day on December 28 2018

MichaelT had the most liked content!

Community Reputation

2 Neutral

Recent Profile Visitors

The recent visitors block is disabled and is not being shown to other users.

  1. MichaelT

    General DB access question -- paging query results

    A more general answer: You should not fetch all columns, run a full table scan and transfer all rows to your application. Nothing wrong with full table scan. Access was pretty good at SELECT everything from everywhere but SQL database simply don't work that way. a) select dedicated columns - list the columns you want to retrieve b) limit the number of rows c) a) + b) retrieve a well defined result set Introducing an index on a table emulates record oriented database. When it comes to online transaction retrieve columns using indices. Single lines should be treated as single lines and well defined result sets as well defined result sets. In general it's not wise to transfer data that is not required. You simply start to lock more data than should be treated or just retrieve the key values and requery. NexusDB ist one example for the best of both worlds. In a '4GL' like environment data is retrieved from the server, put into a local buffer (maybe on the app-server side) and the application logic refers to the data stored in those buffers. We would call those buffers in-memory data-set. Kinda LUW. Also in such cases a) makes lots of sense. In such environments you will have to live with a limited cache per user which is pretty helpful. If you want to reduce the traffic use a middleware or allow db access components to handle the pain for you. FireDAC and other components allow you to fine tune what you want to retrieve (Fetch Options). Those settings allow to lesser the pain and as you pointed out that works. The problem behind the scenes is Entity Relationship. Entity Relationship is about the insane idea to impose semantics on master-data and treat those as anything else but lookup tables. There is no such thing as 'master-data'. It's nothing but time independent transactional data. I fairly remember a data model that does still hold anything but time dependent what was called master-data once. From the very logic maintaining transactional data where there is no transaction makes no sense. If you have a server-side process on the server-side unless you don't get the data from another source too. Paginating results: Fetch what you need an fetch on demand. That works no matter what implementation technology is used, because it's the way things are intended to be.
×