How do I QUICKLY check many sql database tables an

I am working on a SQLite database analysis tool in Java. My current objective is to check to see which tables and views in the database contain no records. My problem is a matter of speed. My biggest slowdown occures when trying to determine if a view is empty. I have tested individual queries against a single view in SQLiteSpy and I can't get the return time down below 1-2 seconds. Scaled up to hundreds of views, that performance is unacceptable to the point of not even bothering with the feature. Using my own code, I can check around 120 tables, with the largest being around 100,000 rows, with an average speed of 3.74ms.

My current query:

 SELECT exists(SELECT 1 FROM table LIMIT 1);

As I said, this works fine for my tables, but with the views, it is too slow.

I have also tried the following:

  SELECT count(*) FROM table;  
  SELECT count(1) FROM table;
  SELECT 1 FROM table LIMIT 1;

I have also tried selecting a known column with a limit of one and had that take several seconds.

My last resort is to query the tables first and then the views and have a single timeout on the whole process. My test database has around 250 tables and views and each are not very large. My goal is to be able to report which are empty in under 1.5 seconds max. I want to be able to provide a feature like SQLiteSpy where the tables and views in the treeView are marked with either a green plus or a red minus symbol. I just can't figure out how they are able to mark them all so fast.

回答1:

I think I understand why querying the views could be expensive. Unlike a plain table, a query on the view potentially has to examine lots of rows in the underlying table or tables to determine that it has at least one row. Combine that with the fact that the SQLlite query optimizer might not be that "clever".

So I suspect that the answer is that checking for empty views could be slow no matter how you do it.

回答2:

This is SQLite specific.

You could try running the ANALYZE command, then querying the sqlite_statN tables to see which tables have a row count of zero.