Magical limit of failure between 70 million and 100 million rows in one table

Details for the issue

What did you do?

I imported a table from a .csv file with many lines.

What did you expect to see?

I thought I could browse / filter / sort the table data after the import.

What did you see instead?

The whole DB Browser got stuck when opening the "Browse data" tab (endless loop?).

Useful extra information

It's quite simple to reproduce.
Successful Case: Just create a .csv file with an integer column only, e.g. column "A" with values 0 to 69999999. You can import this table easily and browse its contents afterwards.
Failure Case: Create the same .csv file with values up to 99999999. Now, you cannot browse / filter / sort the imported table, because the program gets stuck (full single threaded CPU utilisation!).

What operating system are you using?

Windows: ( version: Windows 7 64 Bit )
Linux: ( distro: ___ )
macOS: ( version: ___ )
Other: ___

What is your DB4S version?

3.12.1
3.12.0
3.11.x
Other: nightly build of 13th February 2021

Did you also

Try out the latest nightly version: https://github.com/sqlitebrowser/sqlitebrowser#nightly-builds
Search for an existing similar issue: https://github.com/sqlitebrowser/sqlitebrowser/issues?utf8=%E2%9C%93&q=is%3Aissue%20

Interesting. Wonder if this is something like running out of ram for , and it all goes downhill from that point?

How much ram is in the computer this is happening on?

No, it's not the RAM, at least not the system RAM! Maybe a 32-Bit-issue within the 64-Bit-version? (The machine has 64 GB of RAM. You don't get any idea of a performance problem since all the files [.csv and created database] are completely loaded within the file cache, once they came from disk.)
For my example the "DB Browser for SQLCipher.exe" process (started by the "SQLiteDatabaseBrowserPortable.exe" process) needs far less than 100 MB after importing and saving the 100-million-file. The file gets 1.2 GB in size.
When I click on the "Browse data" tab, the GUI instantly freezes and the process now uses 2.1 GB of RAM - this is not rising, only one CPU core is utilized to the fullest.
These around 2 GB of RAM maybe do look like some 32-Bit-issue.

Which version of the SqlCipher version are you using - the 32-bit or the 64-bit? Out of interest, have you tried 'the other one' just to see if that has the same issues?

I don't know or even decided this on my own.
I tested the portable-apps-installation of 3.12.1. I don't know what exactly the portable launcher launches. As this version failed (it does not show any data before freezing with exactly 1 GB of RAM in use, for my example), I looked for the nightly build and copied its contents into the "App\SQLiteDatabaseBrowser64" directory of the portable build. So the portable launcher again decides what exactly is started (and how).
On another try I took a "clean" VM (also Win 7 64 Bit, never any kind of DB Browser on it before), unpacked the nightly build and directly started the "DB Browser for SQLite.exe" on it. Then I took the same steps with the 100-million-.csv and tried to browse the result after I saved the imported table data - but the result stays the same: With or without portable launcher, the table now shows the first entries and freezes afterwards. In the last case, the Task Manager shows around 1.6 GB of RAM in use, staying at this value, even so one CPU core is again completely in use.

Another fun fact:
The problem does not seem to arise always at the earliest possible time! Yesterday I gave up testing and left a DB Browser while importing more than 100 million rows of my original table (8 columns, 6 times integer, 2 times a short string). I even did not save the changes. Today I found it like that. Ignoring the unsaved state, I started to browse the data --- and it worked!
After some test filtering, to see if the result looks valid, I saved the data and switched between tabs - and it froze again. As always, the DB Browser also freezes when a successfully saved database has been loaded and should be browsed.
I tried to reproduces this case with my test .csv (without saving before browsing) but it did not work (until now).

@esrat

Interesting. Wonder if the recent threading changes in our development builds would have any positive effect on this?

@esrat Would you be ok to try this recent nightly build, and see if it acts exactly the same?

https://nightlies.sqlitebrowser.org/win64/DB.Browser.for.SQLite-2021-02-13-win64.zip

It's a .zip file build, so you just need to extract the .zip file to a temp directory somewhere and run either of the two executables. One executable uses standard SQLite, one uses the SQLCipher (eg encryption) version of SQLite. Either one should be fine for testing this behaviour.

and it froze again.

One thing to check is how many rows are in the table, and how long a simple select count(*) table takes, as this is a common query DB4S uses. If you are importing squillions of rows, then this query could take a while?

and directly started the "DB Browser for SQLite.exe" on it.

Again, the 32-bit version or the 64-bit version?

Out of interest, is the data private, or can you share the database? If we could look at the same database, it might be easier to identify where the problem lies. We could create our own database, but again, whether yours has an index, etc, all has a bearing.

@esrat

@esrat Would you be ok to try this recent nightly build, and see if it acts exactly the same?

He's done that - " I looked for the nightly build and copied its contents into the "App\SQLiteDatabaseBrowser64" directory of the portable build." 😉

He's done that ...

Oh, somehow didn't spot that. Sorry. 😉

@chrisjlocke

testTable_100000000.rar.zip

@chrisjlocke

If you are importing squillions of rows, then this query could take a while?

Since I can easily load a previously saved database (only not browse and filter and sort):

Execution finished without errors.
Result: 1 rows returned in 10693ms
At line 1:
SELECT count(*)
FROM testTable_100000000

I'm fine with that. No problems with waiting some seconds. The "Browse Data" tab always took some seconds when it was still working (with less rows). That's no problem - I was just happy to be able to look through my .csv data. Browsing data with text editors or spreadsheet software simply gets impossible when the file gets bigger than some MB.

Again, the 32-bit version or the 64-bit version?

I always and only used 64-Bit-versions, that's why I also copied the contents (of exactly the win64-file, @justinclift mentioned) into the 64-Bit-directory I mentioned before.

Out of interest, is the data private, or can you share the database?

I only use exactly these steps I mentioned before to reproduce the problem and my mentioned numbers. I do nothing more than creating a new database, importing, saving and browsing. - No indexes, nothing more. The whole process only takes the time of importing the .csv file.

So the database only contains the contents of this one table of the .csv file. A JAVA code example to create this file would be:

PrintStream ps = new PrintStream(pathToOutputFile);

ps.println("A");
for (int i = 0; i < 100000000; i++)
    ps.println(String.format("%8d", i));
ps.close();

I can put some copy online if you can't execute this yourself.

Edit: It can actually be packed quite well, only with RAR (ZIP gets 100 times bigger!?), so I had to rename the file to be able to attach it right here.

Excellent, thanks for posting the database.
Just a nod that Justin and Martin have built dbhub.io to share (and use) SQLite databases online. Forgot to mention that earlier, to ease sharing databases.
Anyway. Thanks for the file. I know we're trying to improve this area of the program and hopefully this'll help identify an issue.
👍

@MKleusberg

On my Windows machine, things are a bit worse.
Simply importing 100,000,000 rows crashes the Browse Data tab. Simply clicking on it crashes DB4S. I know @MKleusberg (thus tagging him here, and I'll find the other open topic) was working on this part of the code.
Actually, even opening DB4S crashes it. I can open the database, but within five seconds, it closes itself (well, dies with a coronary). I'll try reducing the number of rows to confirm it is simply the large number of rows.

@MKleusberg

Hmmm, next Patreon purchase for @MKleusberg might need to be a bunch of ram. 😉

On my Windows machine, things are a bit worse. [...]

That's really odd. I never had problems with importing or opening the always successfully saved database - as long as I never touch the "Browse Data" tab! These actions never let the DB Browser use more than maybe 40 to 50 MB of RAM, according to the Process Explorer's shown Private Bytes. - This only explodes when hitting the browsing tab, freezing the application.
But even in this case I never see huge amounts of RAM, nothing more than the already mentioned 1 or 2 GB. Maybe it should be more and it is not allocated due to 32-Bit issues. But somehow I doubt this since I also never saw these bigger numbers for only slightly smaller tables when browsing still worked!
So I really don't think that a huge RAM is needed for this to work. It just accelerates repetitive harddisk operations a lot.

I've got a new finding: I CAN use the browsing tab if I'm fast enough!

When I'm starting filtering within the first few milliseconds, then the browsing works. I've got to click and type fast to get the resulting table to some smaller size. (It's not just inputing something into the filter field. If I use "%" to not reduce the number of rows then it still freezes.) At least this works when loading a previously saved database. I wasn't successful directly after importing (but maybe I was simply to slow).

Interesting Qt bug report. It was nearly 10 years ago though.

It wouldn't hurt for someone to either open a new bug report (and point at that one), or comment on the existing one, saying something along the lines of "Nearly a decade on since this bug report, and 70million+ rows is a more common place occurrence for users these days. Perhaps this could be revisited?"

and 70million+ rows is a more common place occurrence for our users these days

ie, scrolling through the Qt bug list.... /childish snigger...

I mean we could try creating a new issue or reopening this one because the assumption that the problem goes away when building for 64 bit platforms is wrong. As far as I know on most 64 bit platforms and operating systems an integer is still 32 bits wide. However, I do see their point that this would be a non-trivial change in Qt...

If we open new issue and Qt fixes it, we cannot get the binary patched version (Windows, MacOS) Qt 5.15 Its Commercial-Only LTS Phase 😞

The example below works for me with 53,000,000 rows but doesn't work with 54,000,000 rows.
The behavior is identical to the issue.

Nice! Interestingly enough I can go up to 71,582,788 without any issues. It only starts to fail at 71,582,789. You said earlier that DB4S starts to fail at 56,512,727 rows for you. Any idea why the number you get now is even smaller? Did you maybe update Qt in the meantime or similar? I'm mainly asking because I can't make any sense of these numbers but maybe they somehow depend on the Qt version. I'm using Qt 5.15.2 on Debian by the way.

The number 30 referenced in the bug is no longer hardcoded, it depends on the style. There might lay the difference and not in the Qt version.

Interesting Qt bug report. It was nearly 10 years ago though.

It wouldn't hurt for someone to either open a new bug report (and point at that one), or comment on the existing one, saying something along the lines of "Nearly a decade on since this bug report, and 70million+ rows is a more common place occurrence for users these days. Perhaps this could be revisited?"

And HDPI seems to influence too, I guess the bigger the section size in pixels, the lower the number of rows that you can display, according to the source code I referenced before.

Yes, it is not hadrcoded if I set value to 1 56,512,727 rows can be rendered again (128 DPI font)
In reality, 1 can't be set, min is 30

defaultSectionSize = (o == Qt::Horizontal ? 100
                              : qMax(q_func()->minimumSectionSize(), 30));

But I still wonder how 9c2cec6 is displayed properly.

But I still wonder how 9c2cec6 is displayed properly.

Maybe we were giving lower row counts than the actual? Or some other difference in the model, which could be interesting to know, since might uncover some workaround.

Workaround clipped at 1,000,000 rows (maybe this value should be in settings).

Any thoughts?

diff --git a/src/TableBrowser.cpp b/src/TableBrowser.cpp
index 951c79d6..fa41aaf8 100644
--- a/src/TableBrowser.cpp
+++ b/src/TableBrowser.cpp
@@ -688,6 +688,7 @@ void TableBrowser::updateRecordsetLabel()
     // Get all the numbers, i.e. the number of the first row and the last row as well as the total number of rows
     int from = ui->dataTable->verticalHeader()->visualIndexAt(0) + 1;
     int total = m_model->rowCount();
+    int real_total = m_model->realRowCount();
     int to = from + ui->dataTable->numVisibleRows() - 1;
     if(to < 0)
             to = 0;
@@ -718,10 +719,13 @@ void TableBrowser::updateRecordsetLabel()
         txt = tr("determining row count...");
         break;
     case SqliteTableModel::RowCount::Partial:
-        txt = tr("%1 - %2 of >= %3").arg(from).arg(to).arg(total);
+        txt = tr("%L1 - %L2 of >= %L3").arg(from).arg(to).arg(total);
         break;
     case SqliteTableModel::RowCount::Complete:
-        txt = tr("%1 - %2 of %3").arg(from).arg(to).arg(total);
+        txt = tr("%L1 - %L2 of %L3").arg(from).arg(to).arg(real_total);
+        if (real_total != total) {
+            txt.append(tr(" (clipped at %L1 rows)").arg(total));
+        }
         break;
     }
     ui->labelRecordset->setText(txt);
diff --git a/src/sqlitetablemodel.cpp b/src/sqlitetablemodel.cpp
index 525d92ee..d21cbae4 100644
--- a/src/sqlitetablemodel.cpp
+++ b/src/sqlitetablemodel.cpp
@@ -27,6 +27,7 @@ SqliteTableModel::SqliteTableModel(DBBrowserDB& db, QObject* parent, const QStri
     , m_db(db)
     , m_lifeCounter(0)
     , m_currentRowCount(0)
+    , m_realRowCount(0)
     , m_encoding(encoding)
 {
     // Load initial settings first
@@ -97,6 +98,11 @@ void SqliteTableModel::handleRowCountComplete (int life_id, int num_rows)
     if(life_id < m_lifeCounter)
         return;
 
+    m_realRowCount = static_cast<unsigned int>(num_rows);
+    if (num_rows > 1'000'000) {
+        num_rows = 1'000'000;
+    }
+
     m_rowCountAvailable = RowCount::Complete;
     handleFinishedFetch(life_id, static_cast<unsigned int>(num_rows), static_cast<unsigned int>(num_rows));
 
@@ -178,6 +184,11 @@ int SqliteTableModel::rowCount(const QModelIndex&) const
     return static_cast<int>(m_currentRowCount);
 }

+int SqliteTableModel::realRowCount() const
+{
+    return static_cast<int>(m_realRowCount);
+}
+
 int SqliteTableModel::columnCount(const QModelIndex&) const
 {
     return static_cast<int>(m_headers.size());
@@ -637,6 +648,7 @@ bool SqliteTableModel::insertRows(int row, int count, const QModelIndex& parent)
     {
         m_cache.insert(i + static_cast<size_t>(row), std::move(tempList.at(i)));
         m_currentRowCount++;
+        m_realRowCount++;
     }
     endInsertRows();

@@ -670,6 +682,7 @@ bool SqliteTableModel::removeRows(int row, int count, const QModelIndex& parent)
         {
             m_cache.erase(static_cast<size_t>(row + i));
             m_currentRowCount--;
+            m_realRowCount--;
         }

         endRemoveRows();
@@ -868,6 +881,7 @@ void SqliteTableModel::clearCache()
     m_cache.clear();

     m_currentRowCount = 0;
+    m_realRowCount = 0;
     m_rowCountAvailable = RowCount::Unknown;
 }

diff --git a/src/sqlitetablemodel.h b/src/sqlitetablemodel.h
index 0f9e1e33..b4b91689 100644
--- a/src/sqlitetablemodel.h
+++ b/src/sqlitetablemodel.h
@@ -35,6 +35,7 @@ public:

     /// returns logical amount of rows, whether currently cached or not
     int rowCount(const QModelIndex &parent = QModelIndex()) const override;
+    int realRowCount() const;

     int columnCount(const QModelIndex &parent = QModelIndex()) const override;
     size_t filterCount() const;
@@ -191,6 +192,7 @@ private:
     /// the full row count, when the row-count query returns)
     RowCount m_rowCountAvailable;
     unsigned int m_currentRowCount;
+    unsigned int m_realRowCount;

     std::vector<std::string> m_headers;

For me, that's an acceptable workaround. Not sure about the value, though. Might be good if changeable from Preferences. Another option is to add it to Settings although not making it visible, in order not to clutter the interface, but would be available for command-line modification, and we could direct people to increase it if needed and knowing the possible effect if too high.

Not sure about the value, though.

Yes, 10,000,000 is maybe a better choice for the default value.

Added databrowser/rows_limit settings value (default 10,000,000). Tested with -o databrowser/rows_limit=5000000 and ./sqlitebrowser -O databrowser/rows_limit=5000000, everything seems to work as it should.

Sorry for the extreme necro, but if it hasn't already been added to a bucket list of changes: I agree that this would be good if changeable from Preferences. I just encountered the issue of a CSV being truncated in the Browse Data view, and it took a while to find this solution. And even then, it's still not ideal because (AFAIK) launching DB Browser for SQLite directly from a registered file in Windows Explorer will not execute the flag needed, so I have to run the program then open/click-and-drag the file(s).

Do not let the row count wrap around for tables with more than INT_MA…

Navigation Menu

Repository navigation

Magical limit of failure between 70 million and 100 million rows in one table #2592

Details for the issue

What did you do?

What did you expect to see?

What did you see instead?

Useful extra information

What operating system are you using?

What is your DB4S version?

Did you also

21 remaining items

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Magical limit of failure between 70 million and 100 million rows in one table #2592

Description

Details for the issue

What did you do?

What did you expect to see?

What did you see instead?

Useful extra information

What operating system are you using?

What is your DB4S version?

Did you also

Activity

justinclift commented on Feb 17, 2021

esrat commented on Feb 17, 2021

chrisjlocke commented on Feb 17, 2021

esrat commented on Feb 18, 2021

justinclift commented on Feb 18, 2021

chrisjlocke commented on Feb 18, 2021

chrisjlocke commented on Feb 18, 2021

justinclift commented on Feb 18, 2021

esrat commented on Feb 18, 2021

chrisjlocke commented on Feb 18, 2021

chrisjlocke commented on Feb 18, 2021

justinclift commented on Feb 18, 2021

esrat commented on Feb 18, 2021

esrat commented on Feb 18, 2021

21 remaining items

justinclift commented on Aug 11, 2021

chrisjlocke commented on Aug 11, 2021

sandman7920 commented on Aug 11, 2021

mgrojo commented on Aug 11, 2021

mgrojo commented on Aug 11, 2021

sandman7920 commented on Aug 11, 2021

sandman7920 commented on Aug 11, 2021

mgrojo commented on Aug 11, 2021

sandman7920 commented on Aug 12, 2021

mgrojo commented on Aug 12, 2021

sandman7920 commented on Aug 12, 2021

sandman7920 commented on Aug 12, 2021

eightsideddie commented on Oct 22, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions