0

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.

Justin Wiseman
  • 780
  • 1
  • 8
  • 27
  • If you read the post, you'd know that his issue isn't with the tables, it's with the VIEWS. – Bill Gregg Jun 12 '13 at 12:56
  • 1
    Have you tried `SELECT * FROM table LIMIT 1;` ? (I note that you use 'table' in your examples ... and I'm doing the same here. I assume it is a *notional* placeholder for the name of a table or view.) – Stephen C Jun 12 '13 at 12:57
  • table is a variable. I am looping over a list of table names and putting the current table name into the query. I would love to use a PreparedStatement for that, but you can't do it with different tables. (that i am aware of). BTW, "SELECT * FROM table LIMIT 1;" just took about 20.7 seconds on my problem view. That is laughably slow. – Justin Wiseman Jun 12 '13 at 13:03

2 Answers2

0

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.

Stephen C
  • 698,415
  • 94
  • 811
  • 1,216
0

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.

Gilbert Le Blanc
  • 50,182
  • 6
  • 67
  • 111
  • Thanks, I did not know about ANALYZE. The sqlite_stat1 table does contain information about row numbers, however it does not contain that information for all tables. It says that, in the table, "There is normally one row per index". Since it doesn't contain that info for all tables and views, it won't suit my needs. – Justin Wiseman Jun 12 '13 at 16:21
  • @Justin Wiseman: You have that many tables without an index? I think that one of the sqlite_ tables would relate indexes back to tables, and tables to views. – Gilbert Le Blanc Jun 12 '13 at 16:55
  • My test database may have a horrible schema, but as a database analysis tool, my application needs to be able to work with any SQLite database. Worst case scenario would be no indexes at all. (if possible?) – Justin Wiseman Jun 12 '13 at 17:14
  • @Justin Wiseman: Fine. Querying the sqlite_ tables is much faster than querying each individual table and view. Get the information you can from the sqlite_ tables first. Fill in the rest with the slower queries. SQLite has to be able to find tables and views to execute queries. The information has to be in the sqlite_ tables somewhere. – Gilbert Le Blanc Jun 12 '13 at 17:18