SQL 查询大约需要 10 - 20 分钟 [英] SQL Query takes about 10 - 20 minutes

问题描述

我有一个选择(没什么复杂的)

I have a select from (nothing to complex)

Select * from VIEW

这个视图有大约 6000 条记录和大约 40 列.它来自 Lotus Notes SQL 数据库.所以我的 ODBC 驱动器是 LotusNotesSQL 驱动器.执行查询大约需要 30 秒.我工作的公司使用 EXCEL 来运行查询并将所有内容写入工作表.由于我假设它逐个单元地写入所有内容，因此过去最多需要 30 - 40 分钟才能完成.

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.

然后我使用了 MS 访问.我在 Access 上制作了一个副本本地表来存储数据.我的第一次尝试是

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)

注意这是伪代码.这运行成功，但再次花费了 20 - 30 分钟.然后我使用 VBA 遍历数据并手动(使用 INSERT 语句)为每个单独的记录插入它.这大约需要 10 - 15 分钟.这是我迄今为止最好的案例.

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.

之后我需要做什么:拿到数据后，我需要按部门筛选.问题是如果我在 SQL 查询中放置了一个 where 子句(时间从执行查询的 30 秒跳到大约 10 分钟 + 写入本地表/excel 的时间).我不知道为什么.可能是因为这些列都是文本列?

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?

如果我们将某些列更改为整数，是否会使其在 where 子句方面更快?

If we change some of the columns to integer, would that make it faster in terms of the where clause?

我正在寻找有关如何解决此问题的建议.我的老板说我们可以采用一些基于 Java 的解决方案.这会有所帮助吗?我不是一个 java 人，而是一个 c#，也许我也会说服他们使用 c#，但我主要是寻找关于如何减少时间的建议.我已经将它从 40 分钟缩短到 10 分钟，但希望它不到 2 分钟.

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.

简单回顾一下:

查询执行大约需要 30 秒

Query takes about 30 seconds to exceute

在 Excel/Access 中本地使用查询大约需要 15 - 40 分钟

Query takes about 15 - 40 minutes to be used locally in Excel/Access

在 2 分钟内需要它

可以使用基于 Java 的解决方案

Could use a java based solution

您可以建议其他解决方案而不是 java.

You may suggest other solutions instead of java.

推荐答案

您是否尝试过使用批量查询?本周早些时候，我在使用 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.

这篇关于SQL 查询大约需要 10 - 20 分钟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！