SQL查询大约需要10 - 20分钟
我有一个选择（没什么复杂的）
Select * from VIEW
这个视图有大约 6000 条记录和大约 40 列。它来自 Lotus Notes SQL 数据库。所以我的 ODBC 驱动程序是 LotusNotesSQL 驱动程序。执行该查询大约需要 30 秒。我工作的公司使用EXCEL来运行查询并将所有内容写入工作表。由于我假设它会逐个单元地写入所有内容，因此过去需要 30 - 40 分钟才能完成。
然后我使用了MS access。我在 Access 上创建了一个副本本地表来存储数据。我的第一次尝试是
INSERT INTO COLUMNS OF LOCAL TABLE
FROM (SELECT * FROM VIEW)
注意这是伪代码。运行成功，但又花费了 20 - 30 分钟。然后，我使用 VBA 循环遍历数据并为每个单独的记录手动插入数据（使用 INSERT 语句）。这大约需要 10 - 15 分钟。这是我迄今为止最好的案例。
之后我需要做什么： 获得数据后，我需要按部门对其进行过滤。问题是，如果我在 SQL 查询中放置一个 where 子句（执行查询的时间从 30 秒跳到大约 10 分钟+写入本地表/excel 的时间）。我不知道为什么。 可能是因为这些列都是文本列？
如果我们将某些列更改为整数，就 where 子句而言会使其更快吗？
我正在寻找有关如何解决此问题的建议。我的老板说我们可以采用一些基于 Java 的解决方案。这会有帮助吗？我不是java人，而是c#，也许我会说服他们也使用c#，但我主要是寻找有关如何减少时间的建议。我已经将它从 40 分钟缩短到 10 分钟，但他们希望它在 2 分钟以内。
回顾一下：
执行查询大约需要 30 秒
查询在 Excel/Access 本地使用大约需要 15 - 40 分钟
需要 2 分钟以内
可以使用基于 java 的解决方案
您可以建议其他解决方案而不是 java。
I have a select from (nothing to complex)
Select * from VIEW
This view has about 6000 records and about 40 columns. It comes from a Lotus Notes SQL database. So my ODBC drive is the LotusNotesSQL driver. The query takes about 30 seconds to execute. The company I worked for used EXCEL to run the query and write everything to the worksheet. Since I am assuming it writes everything cell by cell, it used to take up to 30 - 40 minutes to complete.
I then used MS access. I made a replica local table on Access to store the data. My first try was
INSERT INTO COLUMNS OF LOCAL TABLE
FROM (SELECT * FROM VIEW)
note that this is pseudocode. This ran successfully, but again took up to 20 - 30 minutes. Then I used VBA to loop through the data and insert it in manually (using an INSERT statement) for each separate record. This took about 10 - 15 minutes. This has been my best case yet.
What i need to do after:
After i have the data, I need to filter through it by department. The thing is if I put a where clause in the SQL query (the time jumps from 30 seconds to execute the query, to about 10 minutes + the time to write to local table/excel). I don't know why. MAYBE because the columns are all text columns?
If we change some of the columns to integer, would that make it faster in terms of the where clause?
I am looking for suggestions on how to approach this. My boss has said we could employ some Java based solution. Will this help? I am not a java person but a c#, and maybe I'll convince them to use c# as well, but I am mainly looking for suggestions on how to cut down the time. I've already cut it down from 40 minutes to 10 minutes, but the want it under 2 minutes.
Just to recap:
Query takes about 30 seconds to exceute
Query takes about 15 - 40 minutes to be used locally in Excel/Access
Need it under 2 minutes
Could use a java based solution
You may suggest other solutions instead of java.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
您是否尝试过使用批量查询？本周早些时候，我在使用 C# 时也遇到了同样的问题；我必须插入大约 25000 条记录，大约花了 30 分钟。改用批量插入可将时间缩短至约 5 秒。
Have you tried using a bulk query? I had this same problem earlier in the week with C#; I had to insert about 25000 records and it took around 30 minutes. Changing to a bulk insert cut it down to about 5 seconds.
您是否在插入记录后对 Access 表建立了索引？这应该会使查询速度更快。
HAve you indexed your Access table after the records are inserted. That should make it much faster to query on.
如果不支持使用批量插入或太麻烦，一个简单的解决方案可能是使用事务：
因为大多数数据库应该是原子安全的，所以每次插入都有一定的最小开销（这是一个巨大的简化，但无论如何）。通过将所有插入包装到单个事务中，您可以避免原子提交开销。
然而，要真正提高性能，您需要进行更多基准测试。特别是，是
insert慢，还是select ... from view慢？If using a bulk insert isn't supported or too much hassle, an easy solution may be to use a transaction:
because most DB's are supposed to be atomically safe, every insert comes with a certain minimum overhead (this is a vast simplification, but whatever). By wrapping all the insert's into a single transaction, you can avoid the atomic-commit overhead.
However, to really improve performance, you'll need to benchmark some more. In particular, is it the
inserts that are slow, or theselect ... from view?尝试这样的事情：
Try something like this:
我对 Lotus Notes SQL 不太熟悉，但由于很多很多原因，文本列中包含整数这一事实听起来是一个非常糟糕的主意。
现在解决您的问题。 .. 我认为 Lotus Notes SQL 在幕后使用 NotesSQL 数据库。我认为你可以自己创建索引。您是否尝试过在 WHERE 子句中的列上创建索引？
I'm not too familiar with Lotus Notes SQL, but the fact that you have integers in text columns sounds like a pretty bad idea for many, many reasons.
Now on to your problem... I think that behind the scenes Lotus Notes SQL uses a NotesSQL database. I think that you can create indexes in this yourself. Have you tried creating an index on the column(s) which are in your WHERE clause?