我正在使用Excel-VBA将ADO连接到MS-Access数据库。 它正在执行Access数据库中的存储过程。 返回有大约900条记录，包含13个字段。 我使用以下VBA代码：
Dim RS As ADODB.Recordset Call OpenDatabase 'Subroutine that opens an ADO connection: DatabaseName Set RS = DatabaseName.Execute("SELECT * FROM My_Procedure") 'This DOES return a recordset Do While Not RS.EOF Debug.Print RS(0) RS.MoveNext Loop Call CloseDatabase 'Another sub数据库连接在这里：
Sub OpenDatabase Dim ConnString as String Set DB = New ADODB.Connection ConnString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & My_DB_Path & "; Persist Security Info=False;" With DB .ConnectionString = ConnString .ConnectionTimeout = 10 .Open End With End Sub以下是名称通用的查询：
SELECT Col1, Col2, Col3, (SELECT Col4 FROM t2 where t2.Col1 = t1.Col1 AND t2.Col2 = t1.Col2 and Col4 IS NOT NULL) As Col4, (SELECT Col5 FROM t2 where t2.Col1 = t1.Col1 AND t2.Col2 = t1.Col2 and Col5 IS NOT NULL) As Col5, ... (Same through Col13) FROM t1在存储过程中，字段1,2和3都非常简单，一切都快速移动。 但是，字段＃4-13不幸是更复杂的select语句，这似乎是问题的一部分
这不是一个更好的方法，但在MS-Access的限制范围内，这是我必须做的，以我需要呈现它的方式格式化数据。
当我在Access中运行此过程时，计算和显示DataSheet视图中的所有内容可能需要15-20秒。 当我运行上面的VBA代码时，Do循环大约需要45秒来打印所有900行RS（0,1,2），但是使用Debug.Print RS（3-> 12），它需要超过280秒每场。 我怀疑它是在每次我在VBA中要求它时重新计算所有这些嵌入式子查询，但我不知道为什么。
理想情况下，我希望Access运行该过程并生成结果，我只需将每条记录拉入VBA变量以进行进一步处理。 有关如何加快此检索过程的任何想法？
编辑添加样本数据：
这是查询正在运行的数据的一般化示例，以及完成后应该看起来的样子。 输入如下：
Col1|Col2|Col3|Col4|Col5|... A |01 |X | | A |01 | |Y | A |02 |X | | A |02 | |Y | B |01 | |X | B |02 | |X | B |02 |Y | | B |02 | | |Z输出如下：
Col1|Col2|Col3|Col4|Col5|... A |01 |X |Y | A |02 |X |Y | B |01 | |X | B |02 |Y |X |ZI am using Excel-VBA to connect with ADO to an MS-Access database. It is executing a stored procedure I have in the Access database. There are about 900 records being returned with 13 fields. I am using the following VBA code:
Dim RS As ADODB.Recordset Call OpenDatabase 'Subroutine that opens an ADO connection: DatabaseName Set RS = DatabaseName.Execute("SELECT * FROM My_Procedure") 'This DOES return a recordset Do While Not RS.EOF Debug.Print RS(0) RS.MoveNext Loop Call CloseDatabase 'Another subThe database connection is made here:
Sub OpenDatabase Dim ConnString as String Set DB = New ADODB.Connection ConnString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & My_DB_Path & "; Persist Security Info=False;" With DB .ConnectionString = ConnString .ConnectionTimeout = 10 .Open End With End SubHere is the query with names generalized:
SELECT Col1, Col2, Col3, (SELECT Col4 FROM t2 where t2.Col1 = t1.Col1 AND t2.Col2 = t1.Col2 and Col4 IS NOT NULL) As Col4, (SELECT Col5 FROM t2 where t2.Col1 = t1.Col1 AND t2.Col2 = t1.Col2 and Col5 IS NOT NULL) As Col5, ... (Same through Col13) FROM t1Within the stored procedure, fields 1, 2, and 3 are all very simple and everything moves zippy fast. However, fields #4-13 are unfortunately all more complicated select statements and this seems to be part of the problem
This was not exactly a preferable way to do it, but within the limits of MS-Access, this is what I had to do to get the data formatted in the way I need to present it.
When I run this procedure in Access, it takes maybe 15-20 seconds to calculate and display everything in the DataSheet view. When I run the VBA code above, the Do loop takes about .45 seconds to print all 900 rows of RS(0,1,2), but with Debug.Print RS(3->12), it takes more than 280 seconds per field. I suspect that it is recalculating all these embedded subqueries every time I ask for it in VBA, but I do not know why.
Ideally, I want Access to run the procedure and generate the results, and I just pull each record into a VBA variable for further processing. Any ideas of how to speed this retrieval process up?
EDIT TO ADD SAMPLE DATA:
This is a generalized sample of the data the query is operating on and what it is supposed to look like when done. Input is like:
Col1|Col2|Col3|Col4|Col5|... A |01 |X | | A |01 | |Y | A |02 |X | | A |02 | |Y | B |01 | |X | B |02 | |X | B |02 |Y | | B |02 | | |ZOutput is like:
Col1|Col2|Col3|Col4|Col5|... A |01 |X |Y | A |02 |X |Y | B |01 | |X | B |02 |Y |X |Z最满意答案
从您当前的示例数据和所需结果，您当然可以优化查询。 现在，您运行9个单独的子查询以从每个相应列中检索非空值。 简单地说，使用列（3 - 13 MAX()上的MAX()运行聚合查询，在前两列上分组：
SELECT Col1, Col2, Max(Col3) As C3, Max(Col4) As C4, Max(Col5) As C5, ...
Max(Col13) As C13
FROM t1
GROUP BY Col1, Col2
From your current sample data and desired results, you can certainly optimize the query. Right now, you run 9 separate subqueries to retrieve non-null values from each respective column. Simply, run an aggregate query using MAX() on columns, 3 - 13, grouped on first two columns:
SELECT Col1, Col2, Max(Col3) As C3, Max(Col4) As C4, Max(Col5) As C5, ...
Max(Col13) As C13
FROM t1
GROUP BY Col1, Col2
更多推荐
发布评论