-2

I am using Delphi 10.1 with FireDac and am using an SQLite Database. I am opening a Query from a timer.Event after the form shows because it takes a bit to load the Query with all records on startup and I want to let the user see something is happening, so showing the form first then loading data make sense to me.

I want to use a progress bar to show the progress as the data is loaded into the Query. I can get the progress using the GetRecords event of the Query and the Query.RecordCount which shows how many records have been fetched so far, but is there a way to get the total records of an SQLite table prior to calling Query.Open or within the GetRecords Event? This way I will be able to show progress as records load.

Robert Griffin realsol
  • 187
  • 2
  • 9
  • Why don't you just get it first on a separate query ? : SELECT COUNT(*) FROM TABLE – Marc Guillot Oct 19 '16 at 18:36
  • Thanks. I haven't messed around with SQL for 10 years. You comment works perfectly and is fast. Actually, it is as fast as the original Query when not bound to the StringGrid. 7000 records take about 5 seconds to load the StringGrid and I have an SSD drive. I tried disabling the controls for the Query, but as soon as I enabled them, the loading of the string grid started it's 5 second load time. Even unbound the Grid and Bound it after the Query loaded, but still took time to load the grid. It is definitely not a DBGrid which seems to load only what it needs to display. – Robert Griffin realsol Oct 19 '16 at 19:57
  • Clearly you can't detect how many rows are in a query result or table without opening it, just as you can't see how many words are on the page of a book without opening the book. As Marc said, use `COUNT(*)` with the same WHERE clause that your actual query will use to load the rows. – Ken White Oct 19 '16 at 20:06
  • Correct. But since I am using a StringGrid to display the data, That is where it seems to take the most time, unlike a DBGrid which only loads what is visible to the user. I don't see where I can limit this so it only loads the visible rows, then fetches more when the user scrolls down. The SELECT * FROM TABLE in the Query's Editor loads in an instant. – Robert Griffin realsol Oct 19 '16 at 20:28
  • That's why a DBGrid exists. – Sertac Akyuz Oct 19 '16 at 22:42
  • @ Sertac, when creating a Multi-Device app, DBGrid, nor any DBControls exist. You have to bind either a Grid or StringGrid component using LiveBindings. – Robert Griffin realsol Oct 20 '16 at 00:14
  • Tough chance. A DBGrid has a lot of code to achieve that. – Sertac Akyuz Oct 20 '16 at 00:30
  • You should have mentioned in your q that your are creating a multi-device app, -1 – MartynA Oct 20 '16 at 06:43

1 Answers1

0

Thanks for the SQL Tip, SELECT COUNT(*) FROM TABLE Marc.

What actually fixed the slowness of the opening of the Query was not the Query at all, but the populating of the StringGrid. The StringGrid was only there for me and won't be displayed to the user. Disconnecting it from the Query made all the difference in speed.

Thanks, all.

Robert Griffin realsol
  • 187
  • 2
  • 9
  • The idea of SO is that answers help future readers, not just the OP. I'm afraid your answer will not be much help without it saying e.g, how you were populating the StringGrid, by LiveBindings or what. Also, if you are using LiveBindings **your question** should say so, and the two Sqlite and progress-bar tags are irrelevant because Live Bindings are known to be much slower when using a StringGrid than a TDBGrid. Etc ... I'm surprised this answer got upvoted, fwiw. – MartynA Oct 20 '16 at 12:53