Processes

We all encounter once in a while people in the teams who really love processes.

Now processes are a good thing, because they can help us to work, clarify certain things and improve efficiency.

There are even processes that are absolutely mandatory, for security reasons, for example. It should be carefully chosen where to impose such a mandatory process, but then it should never be broken. Other processes are more like a tool, that we use because it helps us. Or because the majority of us or simply the boss think so. Or because they are just there. And have always been there in the last 9 months or so.

A well know example is the work of airplane pilots. When they take over a plane there is a checklist of things what to do. Each item of the checklist has to be followed. Each time.

I have introduced such a checklist for creating a release or of a software into many projects. It proved wrong to shortcut this or just do it somehow, because in the end the wrong or unknown version of the software was installed or something else went wrong. I introduced it also for creating USB-sticks that were used by non-IT-people to install a software on around 1000 devices physically located in different places throughout the country or by hardware people in their workshop. We were not stupid, what could be done via the internet was done via the internet, but machines could get hardware problems or get messed up or simply be set up for the first time. It took a bit more than an hour to do everything by the book and it never became significantly faster. But then again a failure was potentially much much more expensive than a few hours of work. And after this process had been introduced there were no failures at all with any USB-sticks prepared according to this process.

But of course we should always remember that processes are there to help us do our work better. So they should not become so extensive that they use up all the time or that they make work too hard, unless necessary for reasons like the ones mentioned above. So a good understanding is that a lot of things can be done if all who are involved or simply the boss agree to do so after thorough thinking. Do not stop using your brain because of processes.

Share Button

Comparing Images

A practical problem that I have is to sort my digital photos. Some of them have been taken with analog cameras and they have been scanned. Some of them have been scanned several times, using different resolution, different providers or different technologies.

So one issue that occurs is having two directories which contain more or less the same images from different scans.

Some of them have been sorted or enriched with useful information or just rotated correctly, others have better resolution. Maybe it is good to use different scans to create a better image than the best scan.

For a few hundred films doing that manually is possible, but a lot of work. So I thought about automating it, at least partially.

There are several issues that make it more difficult. Scans are not very accurate, so they cut of a tiny random bit in the borders. So to match two images exactly it is necessary to scale and crop them.

Colors look quite different. And then of course resolutions are different. And sometimes they have been turned by an angle of 90 or 270 degrees. Some scans miss a few images that another one has.

So, how to start?

First all images are scaled to thumbnails of approximately 65536 pixels. It turns out to be 204×307, but could of course be anything around that size retaining the rough aspect ratio.

Now all thumbnail images from the two directories are read into memory. 80 thumbnail images are no big deal…

Images in portrait orientation are rotated by 90 and 270 degrees and both variants are used. So from here on all images are in landscape format. Upside down is not supported.

All images are scaled to exactly 204×307 in memory to allow comparison.

And the average r, g and b-values from all images from each directory are calculated. The r,g,b values of each pixel are multiplied or divided by a factor, which is the square root of the quotient of these averages and constrained to the range 0..255. So this partially neutralizes the effect of different colors.

Now for each thumbnail from the first directory is compared with each thumbnail from the second directory. This is done by calculating for each pixel the sum of the squares of the differences between the r, g and b values of the two images.

These values are added up and divided by the number of pixels (204×307 in my case). The square root of this is the comparison result. For images that are actually the same, comparison results between 30 and 120 are possible. Now some heuristic is used to find matches based on these values. Also an „interpolation“ is used, if consecutive images in both directories occur with the middle one missing. This usually brings good results. In some cases manual interaction is needed. So it is possible to mark images in an web interface and then the match that was found for them is revoked. Also it is possible to provide „hints“, which means that these images should be matched.

It took about a day and half to write this and it works sufficiently well for the purpose.

But there are much more sophisticated algorithms that really recognize objects. I have a program called hugin that combines a few images to a panorama. Usually it works quite well and it can even rotate, shift and scale images and do amazing things most of the time. Sometimes it just does not work with certain images. Also Google has of course very powerful software to recognize images and compare them and even create panoramas. If we thing about face recognition… There is really good stuff around. But the first approach with some improvements gave sufficiently good results and the program will only run a couple of hundred times, then it will not be needed anymore.

This is heavily inspired by the Perl-library Image::Compare, but since I am doing something slightly different, I do not use this library any more. But subsequently it has been written in Perl. I will happily provide my source code, but I have baked into it assumptions and conventions that I have introduced myself, but that are not universal, concerning the file names and directory structures for the images.

Share Button

Ketchup or Milestone

Bicycles have potentially very accurate speedometers. They measure distances to an accuracy of usually ten meters and they do this by counting the rotations of the front wheel. Now we can go into physics and see if the front wheel slips significantly, but I would not expect that to be relevant. What is a bit relevant is how much air there is in the tire. But bicycle tires usually take pressures between 4 and 6 bars and using this ensures that the resistance is lowest, the tire lasts longer and of course the measurement is more accurate. Only for cycling offroad and especially on snow or sand, lower pressures might be helpful.

Now usually the circumference of the front wheel can be configured to an accuracy of one millimeter. For this there are two methods and each method has its fans, who say that it is totally impossible to use the other method.

The milestone method, or more accurately the kilometer stone method, because we should of course use the metric system by default in an international context, works like this: The wheel circumference is roughly estimated and configured. Now we cycle long distances on roads with kilometer stones. This is not the case in every country and on every road, but in some countries national highways and even regional highways have it. Germany used to have it in the past, but they moved to a system that numbers sections and measures only within the section, which is useless for this purpose. Sweden and Switzerland do not have it. So it requires makeing use of the kilometer stones in a country that has them. Distances on road signs are very inaccurate in most countries, so they are absolutely useless for this purpose and no better than an estimation of the circumference. So now traveling 10 or better 20 kilometers on a road without steep slopes and without strong head wind allows to see the deviation and adjust the circumference appropriately. Repeating this a few times will yield the best result.

The other method is called ketchup method. It is easy. One spot on the wheel is marked with a lot of ketchup. Then it is carefully walked on a straight line for a few meters meters and the distance between the ketchup spots is measured.

Now what is the difference between the two methods?

Cyclists do not exactly follow a straight line, but go a little bit of zik-zak. This becomes more with head wind or slopes, but usually it is a value that is individual for the cyclist. So going from town A to town B, which is 100 kilometers, will yield (almost) exactly 100 kilometers with the speedometer configured by the kilometer stone method. But it will yield a bit more when configured by the ketchup method.

Both values have their relevance. The ketchup kilometers are the distance that was really traveled in the exact line. And the milestone kilometers are the distance that has been achieved in terms of the usefulness as a means of transport. The zik-zak is just a tiny inevitable inefficiency that does not bring us anywhere.

Now there are people who insist that one of the two methods is superior. And people who just do not care to measure to that accuracy.

But we can learn something from this for IT solutions. Often we have the choice, which information to show to the user. Quite often the ketchup value is chosen, which is a technical internal value, that is the most correct by some technical point of view. But it is good to think twice which value really matters for the user. And it is usually worth to go through a lot more effort and present that „milestone value“ and leave the „ketchup value“ for internal use and maybe advanced detail GUIs that show both values.

I do not care how you configure your bicycle speedometer, but I prefer the milestone method myself. But for user interfaces of software we should all think a bit and understand, what is the equivalent of ketchup and milestone method. At least if there is a significant difference, we should prefer milestone and make more useful user interfaces instead of „correct“ ones in some technical way that is irrelevant to the user.

Share Button

Object Creation: Builder vs. Constructor vs. Setter

When we create new objects, we are basically confronted with the need to provide at least one construction pattern.

Of course depending on the language we have more or less three ways to go that are commonly available.

Traditionally in OO it was mandatory to write setters and getters. In C++ or Java they really have names like getXyz or setXyz, but in Ruby or C# or Scala they can be written in such a way that they behave as if the attribute were public and could be assigned and read, by just magically calling the setters and getters internally. Actually Java does that internally for public attributes or more generally for attribute assignment and Hibernate can be configured to go via the getters and setters or via the internal attribute-assignment-getters and setters. This can be useful, to apply some DB-specific conversion in the getters and setters and to bypass it for the DB-access.

Why do we at all use these getters and setters? They were introduced to have flexibility to change the internal implementation without changing the API, because getters and setters can actually become more complex. This can be useful for DB-specific conversions in Hibernate, but apart from that in 25 years of OO-ish development this flexibility has hardly been used. Most of the time the set of attributes changes and the set of setters and getters changes simultanously. So one might ask the question, why we go by default the extra mile of adding getters and setters, when we could just make the attributes public and save some time. I am not asking, because that would decrease the life expectancy. But moving on demand from plain accessible attributes to getters and setters would be just a refactoring like changing the sets of attributes.

Now which of these are preferred and why?

First of all, we do need to read all attributes in some way, otherwise they are just a waste of space. Just forget for the moment programming low level APIs where bits have to be counted and dummy attributes have to be added to move the useful ones to the right position. But very often it turns out that we do not need to change them during the life time of the object. Now Ruby has a nice feature of setting everything up and then calling freeze, which makes the object, but not its sub-objects, immutable. I think it would be worth considering to add something like this to Scala and Java, for example. Clojure has something like this, actually, but with a slightly different flavor.

There is some advantage in knowing that the state of objects does not change. It is easier to reason about code. It helps really for creating thread safety and reentrance. And it even helps when passing around an object reference of an object, that still „lives“ somewhere else, for example when a sub-object comes out of a getter. In functional programming this is mandatory for all internal APIs, in other areas it is just something to make life easier, where the mutation is not actually needed.

So, there the setter go away, where not needed and we end up construction with a constructor that contains all attributes or variants with reasonable default values. This makes sense, when the attributes are few and there is no no risk of mixing them up. The builder pattern helps by naming the attributes in languages, that do not allow named parameters for the constructor out of the box. So it is useful in Java, but obsolete in many other languages. If attributes are final or constant or whatever it is called, the need for setters and getters is technically even less, because nothing can go wrong, the attribute can only be read. But in Java it is best practice to write getters and we should comply.

Now the issue arises that there is only a private or public multi-argument constructor and a lot of getters or whatever is used for reading the attribute in the specific language. And then a framework needs to create objects from XML, JSON or whatever automatically. And these frameworks tend to need at least a no-argument-constructor, often a public no-argument-constructor, that should be only for framework use. And the attributes have to be made non-final. If the framework is smart, it bypasses getters and setters and the no-arg-constructor is already enough.

Some frameworks actually require the setters. There we go to the old school world. We can impose a convention that the setters and the no-arg-constructor are there ONLY for the purposes of the framework and should not be used otherwise. Maybe that is a good approach, it is somewhat cleaner. But the box is opened and mistakes with such a convention will happen, so the question arises, why we need to deal with each attribute at last seven times: The attribute itself, its getter, its setter, the multiarg-constructor, the attribute of the builder, the with-method of the builder and the build-method.

Some things are easier, when moving to a new language and dumping all the garbage-traditions in that step.

But good developers can write good software in any reasonably good language that reasonably suits the purpose.

Links

Share Button

MapStruct

In the Java sphere we often develop the same data class several times. Each layer has its own variant and they are named almost the same, with some prefix or suffix or just the package name to distinguish. The set of attributes is the same (or almost the same), they have setters and getters. Or maybe only getters.

Nobody wants to write business logic two or three or four times, no matter how much support we have for copying code between the layers. And there OO is gone. We have to use anemic data objects, which was clearly introduced as an antipattern by Martin Fowler some years ago.

Since we are now using new paradigms and every couple of years, we no longer care and no longer know. OO was 25 years ago. Now we do FP and Microservices. And new frameworks. And many layers.

So, where does this come from?

First of all, the database access layer is Hibernate. I do not know why, because I think that plain JDBC would be easier, but Hibernate is already there and cannot be removed by arguments. Now Hibernate came with the promise that we can just use plain objects (POJOs) for our data and mirror database tables to classes and columns to attributes. Some XML-stuff had to be written and everything worked. Only writing the XML was such a pain that people immediately jumped to the annotations alternative once it was there. It was better and still is. But now the POJOs are obviously cluttered with Hibernate or JPA annotations. So they have to stay in the database layer. Actually there is a much stronger argument for this. Objects contain other objects and collections of objects. And possibly everything, if we go deeper recursively. So accessing the database should be a reasonably fast operations, so some attributes are loaded lazily. That means, they are only really loaded, when we need them. Which can go terribly wrong, because the transaction is no longer around and it is too late.

Also we have our idea what data classes have to look like, so there are some layers where we want no-argument-constructors and setters and getters, some layers where we want final attributes and constructors with all attributes and only getters and again others where we prefer to use a builder. And yes, each layer has its rules that need to be followed.

So, we keep them in the DB layer and map them to almost identical service layer objects without annotations. Then we work with these, write our business logic. Procedural programming mostly. Because the objects cannot have business logic. So we have classes with methods that are kind of behaving like static methods, but are non-static, because the framework wants it like that.

And then again, we build more and more layers, because each concern needs to be dealt in its own layer. And requires its own set of data objects, possibly with its own annotations for REST or SOAP or JSON or XML or whatever.

So, how do we move data between layers? At each layer boundary the data needs to be copied to the sister objects in the new layer. Now this is kind of stupid, programming something like

class HouseL1 {
private final int a;
private final String b;

public void HouseL1(HouseL2 l2) {
this.a = l2.getA();
this.b = l2.getB();
....
}
}

or with builder or with setters and getters, it is a lot of ugly work. And even worse, all sub-objects and collections of sub-objects have to be mapped. And their sub objects. And we possibly have to stop somewhere.

So we would like to avoid doing all this tedious stuff.

What can we do?

Is Java really the right language? Of course, we are writing enterprise software and we need type safety. Not real type safety like Scala, but a little bit of it feels good.

Reconsider our whole architecture and simplify it. Maybe it is possible to get rid of some layers and write much simpler software that does the same thing, only much faster and with less bugs. Ok, I’m only kidding here. We are talking enterprise software here. And yes, sometimes the layers do have real purposes and make sense.

Try to use the same data objects in all layers anyway? It has been tried. It works, but only in very simple settings.

Create the source code for the mapping. You can write a script for this or find one or find a tool or whatever. Parse the data classes and create the source code for the transformation methods. Or just write hibernate classes and create all other layers with their preferred setup in terms of mutability and construction and annotations from that.

But we are in Java, so why not use reflection and figure out at runtime how to map it. Find a library to do it for you or write your own. Performance? No problem. We use enterprise servers, of course.

So, in the end it is a good possibility to have a Java-tool that creates the source code for the transformation as part of the compile process.

MapStruct does exactly that. We write in interface for our transformations. With some annotations non obvious mapping behavior can be specified. Keep that list small and try to make it possible to automatically recognize the mappings, where possible. Then an extension to the maven compiler-plugin is added, that involves MapStruct to create an implementation for this interface at compile time and of course compile the implementation. And voila, it works. Even for classes with builders, if only the builder uses method names that are identical to the corresponding attribute name without „with“-prefix. So deal with it, name the methods of the builder like that.

And yes, we should get rid of setters, where we do not really need them. And we should not write constructors with 20 parameters, because the parameters will get messed up. In a language like Java, that does not yet have named parameters. And we do not want to couple layers, so constructors that use the sister class from another layer are not a good idea either, if we have more than two layers or so. So there we go with a builder..

In the end of the day, we can write good software with any reasonably good language and framework. But it is worth investigating how to do certain things. And it is really worth asking the question, why we are doing this at times when it is possible to make such choices.

Share Button

Homeoffice

A lot of IT guys have to work in home office now or are at least encouraged to do so.

This is nothing new, because some companies have been entirely working like this for years. And people live anywhere in the world. They meet maybe once in a year for a company gathering.

There is some difference, though. These „remote only“ companies can choose their employees, their working area and their technologies in such a way, that it fits this model.

Some people feel more comfortable with going to work and going home, hopefully not with a very long commute, and having these two areas of life clearly separated. This is gone for the moment. But we IT people are lucky, because most of us can continue working with low risk of being infected with COVID-19.

Another important aspect is, that in person meetings are often better than just talking on the phone. Of course, when the working progress is established, smaller issues can easily and efficiently be handled by phone. But for larger issues that may be more complex, controversial or just require not only words, but also white board or something like that, it is usually better, to meet in person. At least if it is not too far to travel.

Also it is very easy to ask colleagues who sit nearby a question, it is nicer to drink coffee together…

So apart from the advantage of saving travel time the home office has its disadvantages.

But now we are learning to work like that and maybe that will benefit us, because the possibility to use homeoffice like a couple of times in a month may be useful even in the future.

Some interesting technical aspects are worth noting:

There are different ways to do the work. Some companies have the policy „bring your own device“. The work is mostly done on this device and probably VPN is already in place. So working at home just works immediately.

Also companies that use company owned laptops are often quickly set up, because they just need to setup VPN and maybe rules about the home network security.

But even for companies that use desktop computers, there are ways to deal with this. One approach is to just leave that computer running and redirect the display. This is built into the X Window System of Linux and Unix. It has been there already 30 years ago. In conjunction with Cygwin this is also possible for MS-Windows, but somewhat limited and difficult with non-cygwin applications and somewhat hard to set up. But other technologies like VNC, RDP and probably some others exist. It just requires enough band width, give a little bit of a delay, but it is absolutely possible to work with this. It may be useful to consider moving some things to the local computer, for example the IDE by just accessing the disk remotely, if that is possible with the company policies and the licenses for the IDE. Also confluence, JIRA and thes web applications might be accessed with the local browser instead of the browser on the remote computer.

Now for meetings it is good to find a good tool. Maybe use different tools simultaneously. We need to talk with good voice quality. In a group. It is better to have video. We want screen sharing. And there are even tools that do something like shared white boards. A lot can be done. Sometimes it is good to use different software simultaneously, because one has good voice and video, the other one good screen sharing and collaboration features. It is possible to use the mobile phone for one part and the computer for the other part. Good earphones can be helpful, so you can talk with your hands free. Unfortunately they are sold out in many online stores.

Share Button

ScalaUA 2020

I like visiting ScalaUA conference in Kiev every year in March or April. I did so in 2017, 2018 and 2019.

So for 2020 it was kind of difficult to perform a regular conference. So there are two options, either it could have been cancelled or it could have been postponed. That is what all other conferences did. ScalaUA being an innovative tech conference opted for a third route. The conference was held, but totally online. It is agreed that it is worth to travel to the location and meet in person. But since that was not an option, it was an innovative and reasonable approach to run the conference online.

So the question is, how did this work?

There was a bit of a fight with the tools. We used Zoom, which was probably a good choice, because it has rich features. The schedule was more or leas as it would have been normally, so the 15 minute break between was enough to get some coffee or whatever, because no lines and no distances had to be dealt with. The talk was delivered in such a way that the speaker was seen full screen when no slides were shown, but usually the slides took up the whole screen and there was a small window in the corner, which could be moved around on the screen which could show the speaker. It was possible to ask questions and put oneself on video as well as a spectator. But for better support a Slack channel was provided for each talk plus a general one and a few that I did not use. They are kept around for a week after the event, so questions can still be dealt with.

One talk was done by two people. One of them wrote on a transparent board, that was between the camera and him, which was very impressive. Of course it was converted to allow him to write from left to write and us to read from left to write. His presentation partner showed code on her computer. This talk actually did something that is not possible in the usual situation.

What was important: Starting the Zoom channel for the talk about five minutes before, because it sometimes took some time to start.

What is more challenging: If the talk is not really very interesting, it is a bit harder not to get distracted. Sitting in the audience, you have to listen.

You can see the agenda. I did not speak myself this year.

Scala 3 (former Dotty) took up a lot of space, because many things have to be redone in Scala 3 or at least can be redone in a much more elegant way.

Share Button

Apple giving up on information technology

Apple has reinvented itself radically many times and done so when things were still going well. This is part of the companies success story. And the CEO Tim Cook apparently plans to continue with this strategy. Major reinventions where:

  • Moving from the Apple II to Macintosh
  • Dropping System 9 and replacing it by the totally new OS X
  • Shifting the priority from computers to the i-pod
  • Moving from i-pod to i-phone and i-pad

There were more, but these transformations could all be explained by early anticipation of an end or major shrink of the existing business. For example i-pods were nice music players, but then each phone contained a music player and every person had a phone in the pocket anyway, so the usefulness of an i-pod was tending to zero. Apple fans are very loyal, so they kept buying apple products, even when they were becoming obsolete, but that can only work for a short time. So they made a i-phone, which was theoretically a phone, but the phone-functionally did not really work. I assume, with more recent iphones they have fixed this.

But Tim Cook is moving on. What are the strengths of Apple? Design, marketing and sales. Especially including the fans in working for free for the company to enhance sales. This is an essential part of the success story. We are already used to the fact, that useful features are radically removed, like the DVD player in laptops, when DVDs were still important. Or the standard earphone plug from phones. Apple fans feel flattered by this, because they are so advanced by losing something first that later on actually becomes obsolete.

So the next step is coming now: Apple will move on to fashion. They will design clothes, shoes and jewelry. Then they can concentrate on their real strengths, reinvent themselves once more and remain in business for another long future. Computers, tablets and phones will be phased out.

Share Button

Skillsmatter working again

The original Skillsmatter company has gone into administration and will be closed down, once all claims have been settled.

Now investors have bought assets of this old company, apparently including the name and the brand, and are now operating und the same name and webdomain skillmatter.com. This is a new company with new management and a new location. Probably some employees of the old skillsmatter are working in the new company.

They plan to continue activities of the old skillsmatter, for example conferences.

This has some implications. If you have bought a ticket for a conference from skillsmatter and this conference did not take place, the new skillsmatter is not obliged to give a refund. This is the old skillsmatters or more precisely its administrators Resolvegroup (See FAQ #13).

The other option is to hope that they will reschedule the events, which they promise to do. They promise to honor tickets from old Skillsmatter, even though they do not have to.

Please, make a choice between the two options. If you get money back from Resolve, you cannot use the same ticket to go to a conference of the new Skillsmatter. It would be abusive and I assume that they will check.

Concerning the two conferences that I liked to visit from Skillsmatter, Scala Exchange and Clojure Exchange, alternatives have been created, Functional Scala and ReClojure. Functional Scala is there to stay, at least for the next couple of years. So it will compete with a potentially revived Scala Exchange. For 2020 I have opted for Functional Scala and already bought the ticket. It was also said that reClojure intends to continue as well, but I consider it possible that they will merge efforts with Clojure Exchange.

Share Button

Pagination of Database Query Results

This article is highly inspired by the blog post We need tool support for keyset pagination.
Please consider reading the original first and then my interpretation and additional thoughts about this idea.

We have a typical database base backed web application. It can be a rich client. It can be a NoSQL database. Whatever, but for the moment maybe a transactional SQL database and a web application will be used as the most common and best understood example.

We kind of construct a query, of which the result set is so large, that we do not want to transfer, render and display it all at once. Think of Google, where you might have millions of hits and only see the first 10 or 100 or so. Now you can navigate through the result set, usually by going to the next pages successively.

Nice web applications tell you how many records there are exactly. And then calculate how to split this up into a large number of pages and allowing you to go to an arbitrary page, or at least to some of the pages (last, next 10 pages) immediately.

There are several problems with this. First of all: the set you are basing this on changes with time. This could be fixed by creating some temporary snapshot in memory, in the database or in some kind of db-cache and keeping that around for some time. This is expensive and usually not worth the price, so we live with some misbehavior.

The second problem is the count. Count actually needs to do a full scan of the result set, there is no decent short cut. Now it would usually be good enough to estimate the size of the result set and that is exactly what google does. Sometimes the estimation is ridiculously bad, but we have gotten used to it. That is much better than using the real count, because it really speeds up the application by a great factor and improves user experience overall. Of course it is a bit more challenging to program an estimation and only worth it for big result sets. We could still jump to any of the next 10 pages by just increasing the size of the current query by 11 and showing the required segment. Since we sort the result by something, we could just get the first N results sorted backwards and then wrap that in a query that sorts them forward. So dropping the count can help for performance without too much loss in functionality.

Now the next problem is, that having navigated through a lot of pages, the usual way of doing a query and then constraining it with some mechanism like „rownum“, „row_number“, „limit/offset“ to the subset we want to display always requires obtaining the resultset from the beginning to our page. Usually that does not matter, because we navigate through the application manually and that means not too many pages from the beginning. But it can become an issue, if the application is heavily used.

Now we sort by something. Usually not the id, but some business logic relevant keys or a combination. They do not have to be unique, but we can always add the ID (or more generally the primary key) as the last sort key to make it unique. Then the next page can be found by just adding a condition based on our sort key. Assuming we want to show N records. The query might be

SELECT * FROM TABX 
  WHERE cond1 AND cond2 
  ORDER BY A, B, C ASC 
  LIMIT n;

Now the last record of the result has A=a1, B=b1, C=c1.
Then the next page can be obtained by

SELECT * FROM TABX 
  WHERE cond1 AND cond2 
   AND (A>a1 OR A=a1 AND B>b1 OR A=a1 AND B=b1 AND C>c1) 
  ORDER BY A, B, C ASC 
  LIMIT n;

To skip three pages and get the fourth of the following pages is done like this:

SELECT * FROM TABX 
  WHERE cond1 AND cond2 
   AND (A>a1 OR A=a1 AND B>b1 OR A=a1 AND B=b1 AND C>c1) 
  ORDER BY A, B, C ASC
  LIMIT n OFFSET 3*n;

We do use (or simulate) OFFSET here, but in a more intelligent way, because we do not force it to work harder than necessary by rebasing our query on what we already know.

We get a bit more consistency, because new records being inserted in the beginning will not be seen, but they will at least not disturb the succession of our result set. Maybe that is worth more than the performance gain, because we get it with a reasonable effort. caches, snapshots or even keeping it in „session memory“ are not really serious approaches to this issue, they will just sooner or later blowup our application in production, at the worst possible moment.

Share Button