General DB access question -- paging query results

[David Schwartz 426]

I must have missed the memo on this, so I'm wondering how to deal with it.

 

I have some local DB tables that I access with my app and all works fine. Then I decided to move the tables to a DB server. Oops. Performance issues arise.

 

I'm using SQL components that double as Tables, so I've got to put in a SQL statement like 'select * from xyz' to get the data to load.

 

That's fine except a couple of tables have BLOB fields that slow things down significantly when they're moved to the server.

 

So I split the BLOB fields out into a separate table and I load them up as-needed (whenever the main table's record changes). This works fine, but it seems a bit convoluted.

 

What I'm wondering is ... when you've got controls like listviews that only show a dozen or so lines, how do you paginate the result sets so it only loads up the records being viewed?

 

Also, how do you NOT load up things like BLOBs unless you actually want to see them? (Since I pre-defined the tables with the fields so I can refer to field vars in the code rather than using FieldByName for everything, if I don't do a 'select * from...' then an error arises saying a data field is missing.)

 

It would seem that the controls would need to work in conjunction with the queries to manage paging properly.

 

There's a DBListBox in the standard palette, but no DBListView. In this case, I'm using DevExpress cxGrids, and I suspect they have a way to do this but I didn't really look into it.

 

Do LiveBindings help solve this problem vs. regular data-aware controls? (I haven't really played with them yet.)

 

Otherwise, how do you set up a basic pagination scheme for fetching small chunks of data from the server based on what's being viewed?

Edited November 25, 2018 by David Schwartz

[Markus Kinzler 174]

You need a control with a "virual" mode, loading data as needed. In your case you may have a look on VirtualTreeView.

[Attila Kovacs 629]

Which DAC and DBMS?

Here is some reading for FDAC http://docwiki.embarcadero.com/RADStudio/Tokyo/en/Fetching_Rows_(FireDAC)

Set up a new project and start playing with the options.

 

 

Edited November 25, 2018 by Attila Kovacs

[David Schwartz 426]

7 minutes ago, Markus Kinzler said:

You need a control with a "virual" mode, loading data as needed. In your case you may have a look on VirtualTreeView.

It's a more general question than that.

 

I mean, what do you do with apps that migrated from client/server to DB servers and now they have performance issues because of this?

 

It's not like we can go through and replace every DB control that resembles a list with a VirtualTreeView.

 

Maybe they need to be replaced with something, but surely not all with the same component.

[Uwe Raabe 2057]

Not sure about your use case, but FireDAC has some tricks to load only a specific number of records on open and fetch the following when necessary. There is also an option to fetch blobs for a record only when needed.

[Attila Kovacs 629]

13 minutes ago, David Schwartz said:

<how do you delete posts here?>

You can't. A 14 years old research has shown that deleting / editing posts are better not allowed.

Edited November 25, 2018 by Attila Kovacs

[Markus Kinzler 174]

13 minutes ago, Attila Kovacs said:

You can't. A 14 years old research has shown that deleting / editing posts are better not allowed.

In this case it was just a double post. I removed it. @David Schwartz you can report a post and ask someone of the team to remove it.

 

I will remove this post tomorrow, too.

[David Schwartz 426]

19 minutes ago, Uwe Raabe said:

Not sure about your use case, but FireDAC has some tricks to load only a specific number of records on open and fetch the following when necessary. There is also an option to fetch blobs for a record only when needed.

My question refers to a use-case that's similar to thousands of others like it: suppose you have an app built in, say, D7 that was originally designed as a client/server app, using standard Delphi components available at the time, and it's now exhibiting poor performance after replacing the local file server with a DB server (MS SQL Server, Oracle, MySQL, whatever) because the 243 SQL queries on the 178 forms are all set to do "select * from xxx" in order to populate the controls on the form. So loading up some forms can literally take 10-15 minutes. 

 

It's a very general question: how does one usually deal with this?

 

(In my experience, Management usually says, "We're going to rebuild the app in C#/.NET because Delphi clearly can't handle this workload." But I'm asking for a more TECHNICAL explanation that doesn't just throw the baby out with the bathwater and start over from scratch.)

[David Schwartz 426]

17 minutes ago, Markus Kinzler said:

In this case it was just a double post. I removed it. @David Schwartz

Yes, it was. I posted my reply, then realized I wanted to quote the message it was replying to. When you hit the Quote button, it opens a new reply. It's not clear how to copy that into an existing reply. So you end up with a duplicate post and no way to delete one of them. 

 

I just don't get the point of a policy that simply causes a lot of needless clutter and additional work for people who are already over-worked (Ie., the moderators/admins)

Edited November 25, 2018 by David Schwartz

[Uwe Raabe 2057]

I guess, the application uses BDE components? These are indeed not very capable of this scenario. I suggest to replace these with FireDAC components, which should handle this out of the box or can easily be configured to do so. There are semi-automated tools to help with this replacement.

 

There are other alternatives for replacement, but the BDE components are not suitable for this job unless you start to change the DB access behavior of the application.

[Uwe Raabe 2057]

1 hour ago, David Schwartz said:

Management usually says, "We're going to rebuild the app in C#/.NET because Delphi clearly can't handle this workload."

BTW, rebuilding with C#/.NET will cost a multiple of time and money with no real benefit but plenty of problems.

[David Schwartz 426]

10 hours ago, Uwe Raabe said:

BTW, rebuilding with C#/.NET will cost a multiple of time and money with no real benefit but plenty of problems.

I don't think anybody is getting my point.

 

I've seen this problem arise in numerous projects over the past decade that use virtually every DAC and DB server there is.  THEY ARE NOT THE PROBLEM.

 

What I have NOT seen is any sort of coherent discussion or approach that anybody took, nor have I seen anything cited anywhere that helps Management get their arms around the REAL problem.

 

As I said at the very beginning, IT'S A GENERAL QUESTION! People keep trying to make it specific to my current situation or this or that component lib. It's NOT!

 

Quote

BTW, rebuilding with C#/.NET will cost a multiple of time and money with no real benefit but plenty of problems.

Well, if nobody can address it BROADLY, and instead they can only speak to how to deal with this DAC or that DB, then there shouldn't be much surprize over the fact that Management isn't getting any useful input to justify anything else.

 

Management does not seem to understand the impacts that fundamental architectural differences have going from a traditional Client/Server to a server-based "cloud" solution. And if we as "technical experts" cannot shed light on this issue without bringing up obscure details rooted in what specific DAC or DB is involved, then there's a big disconnect happening and nothing useful being communicated to fill the gap.

 

All I can point to is a bunch of projects I've seen that were supposed to take "only 18 months" and not one of them was even close to functional after 24 months. Meanwhile, migrating the Delphi app would have taking way less than 18 months. But again, the perception on the part of Management has usually been that "the problem is Delphi" where the REAL TRUTH is "the problem is the architectural change that made the current app unfit for the job". Delphi is irrelevant. 

 

If all you can do is talk about the DACs -- an inherently Delphi-specific technology -- how in the heck can you convince anybody that the problem is NOT Delphi? Any app written in Visual Studio or any other platform that adheres to a classic Client/Server architecture is going to have the exact same architectural problems. But nobody is talking about THAT!

 

Is anybody starting to get my point now?

 

[Attila Kovacs 629]

I've learned in the very beginning, that if I can't find answers to my problem, neither similar questions, then most probably I'm doing something wrong and the problem is non-existent.

[David Schwartz 426]

2 minutes ago, Attila Kovacs said:

I've learned in the very beginning, that if I can't find answers to my problem, neither similar questions, then most probably I'm doing something wrong and the problem is non-existent.

My company has some clients who are complaining that a few of our screens are taking over 10 minutes to load up.

 

This code has been around for over 10 years, far longer than I've been working there, so I know it's not me. (It's not code I've ever touched.)

 

In fact, it's working perfectly "as designed" and there are no bugs present.

 

It is actually an "architectural defect".

 

Regardless, can I have them give you a call so you can tell them that this is a non-existent problem?

[Attila Kovacs 629]

You forgot the "I'm doing something wrong" part.

Last time I've seen a similar case was a incidental table blocking. If one specific form was opened (anywhere in the network), some tables were blocked even for reading.

I also don't know what is the architectural defect, if only few customers are complaining. I hope it's reproducible and you can narrow it down.

Edited November 26, 2018 by Attila Kovacs

[David Schwartz 426]

22 minutes ago, Attila Kovacs said:

You forgot the "I'm doing something wrong" part.

Last time I've seen a similar case was a incidental table blocking. If one specific form was opened (anywhere in the network), some tables were blocked even for reading.

I also don't know what is the architectural defect, if only few customers are complaining. I hope it's reproducible and you can narrow it down.

As I said, this code has been working "as designed" for over a decade, long before I came on the scene, and I have not touched it. 

 

The problem existed before I arrived. How can I be doing something wrong?

 

I don't know how many customers are complaining or are unhappy with it. I've only been told about two who have voiced their complaints.

 

Personally, if a form took over one minute to load up, I'd ditch the software. But these folks seem to be much more tolerant than I am.

 

The architectural defect I'm talking about has been pointed at several times above: we have datasets that have to run SQL queries phrased as "select * from ..." because that's how things were written in D7 10 years ago, and there's no paging going on anywhere.

 

These queries ran very quickly when the file server was in the same building and they only had a year or two of historical data present. But today they literally take several minutes (some up to 15!) to run on the remote server, processing over a decade of data (they're really hairy queries that apparently aren't time-boxed) and many return over 100,000 rows of data to the client before the user is allowed to view the first 10 and do anything.

 

As I said, this is working 100% as-designed! Nobody considers it a "bug" in any traditional sense. But clearly, 10-15 minute response times -- while not a "bug" -- are extremely "unfriendly" from the users' standpoint.

 

Explaining this in terms of Delphi's DACs or the server are fruitless -- it's the lack of paging in the queries that's causing the problems. This is an architectural defect, not a "bug" in anything, since it's all working perfectly correctly and "as-designed".

Edited November 26, 2018 by David Schwartz

[Attila Kovacs 629]

I have to guess, is this a kind of -we show a big list in a grid- kind of architecture defect?

BDE delivered the first X row immediately meanwhile the rest was dled in the background, so it wasn't a big deal on a local network.

I'm sure that those who updated to zeos lib back in the time faced the very same problem as it fetched everything and it took too much time.

With most recent libs it's better, but it's even better not to load too much. I've for example a form where I can say, load me just X month of data.

And of course just the fields the grid needs.

Then there are the other type of apps, where you get an empty form (db controls without data), a search field, and a db-navigator. 

Yeah, it's ugly, not as transparent, but for sure the less resource taking variant.

However, 10-15 minutes are just too much. If it's a local network there is something else too. It's 10GB on 100mbit and ~100GB on 1000mbit network.

 

Edited November 26, 2018 by Attila Kovacs

[Attila Kovacs 629]

1 hour ago, David Schwartz said:

it's the lack of paging in the queries that's causing the problems

I've linked you a page where is written how for example firedac solves (helps) the problem.

 

Rowset Fetching allows you to specify the number of records that will be fetched from the server in one network round trip. The rowset size is controlled by the FetchOptions.RowsetSize property. Bla bla...

 

But it's not just that.

The DB control you are using for displaying the data (and your code ofc.), should not force the dataset to fetch everything at the beginning, by doing things, like displaying sums on the bottom, auto sizing column based on values, sorting on client side, whatever..

Also consider splitting up the lists to live data, near past and history.

 

Edited November 26, 2018 by Attila Kovacs

[Guest]

You question is valid, sure. I have been battling these things for many years. It goes like this (think a 25-year time-span):

 

Internal snappy network => BDE Client server.

Then came VPN solutions and people wanted to work from home.

Ditch the BDE is favour of something more modern.

These "more moden" DACs (as have been said here already) have some stuff to alleviate the apparent problems you are mentioning. But from above posts is seems you are not interested in the details.

Time passes even more and we realise that connecting an application on a user machine to the RDBMs client API and then the "internet" is not the best of choices. Mainly because most RDBMs client libs are "blocking" and single-threaded (wrong nomenclature perhaps in this statement).

Starting to code with "remoting" solutions a lot of us do not want to ditch all the nice DB-aware controls we are so happy to use. Enters ... no actual good "standard" for this.

In posts you can read about different remoting solutions for the TDataset "ecology". From what i have been able to discern most houses sport their own.

I am in the process of creating such an solution for my clients' back-office needs (i also have web-browser based solutions, but they are only Delphi server-side, client is JavaScript).

So remoting can be done using sessions where you have a "live" dataset on the server and "sync" with client side datasets OR you could go the REST way. In case of REST you would probably have to re-write you application. Or go UniGUI or IW or such is perhaps yet another way (not my favourite).

Either way check out the ecology of 3rd party libs for either "way" of doing it. Did you mention cxGrid? DX supplies a "serverdataset", this is /one/ way of alleviating the problems. But that's on a more detailed level too.

 

HTH,

 

/Dany

[Guest]

22 hours ago, David Schwartz said:

Management usually says, "We're going to rebuild the app in C#/.NET because Delphi clearly can't handle this workload."

It has nothing to do with the language but with the developers.

 

Most Delphi developers will drop a component on a form and have their job finished in 2 hours.

Most C# developers will define some models, dataservices, repositories, viewmodels and some unittests for all that and after 4 days they implement the infrastructure layer for sql-server or webapi or whatever. 

 

Well if the data grows the C# developer will add some paging mechanism for a responsive app (around 2 days incl. unittesting)

The Delphi developer is looking for a component which will behave as the old one but with paging included and then telling the management that it is not possible.

[Guest]

"Paging" is one method. Dataset key buffering (available in IBObjects, FireDAC) is another method IMHO.

[Lars Fosdal 1791]

Setting RowSetSize to f.x. 100 doesn't really help you if you need to get rows 19.900 -> 19.999 - because that implies that you also need to read all the rows before 19.900 to get there.

 

Pagination and getting the top (n) rows from an arbitrary offset, f.x starting at row 20.000 - is not something that FireDAC handles well by itself, and server side pagination is very much per SQL Server type.

 

Some examples:

Oracle: https://blogs.oracle.com/oraclemagazine/on-top-n-and-pagination-queries

SQL Server: https://stackoverflow.com/questions/109232/what-is-the-best-way-to-paginate-results-in-sql-server

 

 

[David Schwartz 426]

17 hours ago, Schokohase said:

It has nothing to do with the language but with the developers.

 

Most Delphi developers will drop a component on a form and have their job finished in 2 hours.

Most C# developers will define some models, dataservices, repositories, viewmodels and some unittests for all that and after 4 days they implement the infrastructure layer for sql-server or webapi or whatever. 

 

Well if the data grows the C# developer will add some paging mechanism for a responsive app (around 2 days incl. unittesting)

The Delphi developer is looking for a component which will behave as the old one but with paging included and then telling the management that it is not possible.

I get your point, but I'm not sure I agree with your initial point. I've had this beef with Delphi for ages -- the entire IDE and even some language "features" (limitations, actually) are built around the same usage paradigm embraced with D2 for building Client/Server applications. 

 

When anybody mentions allowing things in Delphi like separately compiled classes (which C# supports), there's a lot of resistance from the peanut gallery saying, "Oh, this isn't pascalish!" and it never gets anywhere. The Delphi language and IDE have been calcified in this respect since they were born!

 

Consequently, C# and Visual Studio have evolved to the point where you can build apps that use some kind of MVC-like architecture (among others) that are supported to some extent within the IDE itself. Trying to build anything other than the regular Client/Server style app in Delphi becomes quite a chore, and you're fighting the IDE at nearly every step.

 

You're right in your characterization of Delphi vs. C# devs, but not for lack of innovative thinking. The simple approach in Delphi is like falling off a log, while a more innovative approach that's reasonable to take in C# is far too convoluted for most Delphi users to even consider, since it goes against everything the Delphi environment has been optimized for over time.

 

In a world where Client/Server architectures are pretty much dead, the wizards in charge of Delphi's future continue to crank out release after release with features that make it quicker and easier to build more ever more complex Client/Server apps.

 

If you want to build a REST-based server in Delphi, they offer a solution by providing a pre-built server framework that can be extended by creating plug-in modules (DLLs basically), but the IDE and components still have no knowledge of the dynamics inherent in such designs. But there's no particular benefit of one server over another -- the IDE sees them all as foreign objects that require the programmer to manipulate directly.

 

I keep trying to avoid specific DACs in this discussion in part because we're using ASTA components in our app, and over time the programmers have written code that's highly specific to ASTA's features. AND because the Delphi IDE only really knows one paradigm (ie., Client/Server), the code is infused at every possible level with business logic and DB actions that have always been seen as perfectly legitimate for C/S apps, but they cannot survive a transformation to a more modern server-based architecture where all of the business logic and DB interactions happen at the other end of something like an HTTP connection (eg, REST requests).

 

It's really easy to SAY: "Oh, just replace ASTA with ____" but that's a HUGE project it its own right, regardless of whatever _____ might be.

 

There's still the matter of extricating all of the business logic and embedded SQL from the client code and moving it to the server side. (This also presumes that everybody sees these issues as a problem from the outset.)

 

Yes, you're right. It's really seductively easy to drop a few components on a form in Delphi and have a simple client/server type app running in an hour or two.

 

When we can build an MVC-like app that uses, say, a REST-based service, all within the Delphi IDE just as quickly, then we'll have made some real progress.

 

Knowing how such a thing is structured and what's needed is one thing; but having to build it within the Delphi IDE is like swimming upstream against a strong current -- something most of us prefer to avoid, and is very hard to justify from a complexity standpoint to Management. It's hard enough finding good Delphi devs. But having a crazy complex application architecture that nobody can figure out easily doesn't help. These things are fairly common in the C#/VS ecosystem, not in Delphi's world.

 

[Dmitry Arefiev 101]

16 hours ago, Lars Fosdal said:

is not something that FireDAC handles well by itself

http://docwiki.embarcadero.com/RADStudio/Tokyo/en/Fetching_Rows_(FireDAC)#Rows_Paging

FireDAC transparently modifies SQL command and applies keywords like TOP, OFFSET, etc depending on DBMS. So, why "is not something that FireDAC handles well by itself" ?

[Lars Fosdal 1791]

2 hours ago, Dmitry Arefiev said:

http://docwiki.embarcadero.com/RADStudio/Tokyo/en/Fetching_Rows_(FireDAC)#Rows_Paging

FireDAC transparently modifies SQL command and applies keywords like TOP, OFFSET, etc depending on DBMS. So, why "is not something that FireDAC handles well by itself" ?

 

How does the generated SQL look for retrieving the top 100 results at row 19.900 for SQL Server?