如何快速检查许多sql数据库表和视图，以查看它们是否为空或包含记录 [英] How do I QUICKLY check many sql database tables and views to see if they are not empty or contain records

问题描述

我正在使用Java中的SQLite数据库分析工具。我目前的目标是检查数据库中的哪些表和视图不包含记录。我的问题是速度的问题。我最大的减速发生在尝试确定视图是否为空时。我已经测试单个查询在SQLiteSpy的单个视图，我不能得到返回时间低于1-2秒。缩放到数百个视图，这种性能是不可接受的，甚至不烦恼的功能。使用我自己的代码，我可以检查大约120个表，最大的是大约100,000行，平均速度为3.74ms。

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.

我当前的查询：

 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.

我也尝试过以下操作：

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

我也试过选择一个限制为1的已知列，需要几秒钟。

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

我最后的办法是首先查询表，然后查询视图，并在整个过程中有一个超时。我的测试数据库有大约250个表和视图，每个都不是很大。我的目标是能够报告哪些是空在不到1.5秒最大。我想能够提供一个功能，如SQLiteSpy，其中treeView中的表和视图标记为绿色加号或红色减号。我只是不知道他们如何能够标记他们这么快。

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.

推荐答案

这是 SQLite 。

您可以尝试运行 ANALYZE 命令，然后查询 sqlite_statN 表，以查看哪些表有行计数为零。

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

这篇关于如何快速检查许多sql数据库表和视图，以查看它们是否为空或包含记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！