# Running SQL Queries On A Worksheet (With Set Of Random Numbers Between 1-20 Filling Column A) Handled As An ADO

## INTRO

I am using an Excel worksheet as my "database." No headers so [F1] is assigned to column one by default. I filled the entire column, all 1,048,576 cells with RANDBETWEEN(1,20). I then hard set these values by copy/pasting as value.

It is very slow. Much slower than using Excel functions would be to generate same data. I realize that I could use 1,048,576 as a constant denominator but I wanted to practice with SQL query language, and keep model more dynamic.

EDIT: it should not say % in results not many times. I am getting the % of times each var occurred in my data set. I am basically seeing what the distribution is of randbetween(1-20) outputs are over X calls. In this instance x is 1,048,576.

My SQL Query

"SELECT Round(SUM(IIF([F1]=" & searchKey & ",1,0))*100.0/SUM(IIF([F1]<> Null,1,0)),10) From [Sheet1$];"  Is returning the total number of records in column F1 that are equal to searchKey and dividing that number by the total number of records in column F1. ## CODE Option Explicit Private Declare PtrSafe Function timeGetTime Lib "winmm.dll" () As Long Sub SqlQueryOnWorkSheet() Dim started As Long Dim cn As ADODB.Connection Dim filePath As String Dim counter As Long Dim outCome As Double Dim ended As Long started = timeGetTime filePath = "Z:\Test\Test1.xlsx" Set cn = EstablishConnection(filePath) If cn.State <> 1 Then GoTo CleanFail: For counter = 1 To 20 outCome = FindCount(cn, counter) PrintOutcome counter, outCome Next counter cn.Close Set cn = Nothing ended = timeGetTime Debug.Print "QUERIES RAN IN " & (ended - started) / 1000 & " SECONDS" Exit Sub CleanFail: Debug.Print "CONNECTION COULD NOT BE MADE" End Sub Function EstablishConnection(ByVal filePath As String) As ADODB.Connection Set EstablishConnection = New ADODB.Connection EstablishConnection.Open _ "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source='" & filePath & "';" & _ "Extended Properties=""Excel 12.0 Macro;HDR=No;IMEX=1;"";" End Function Function FindCount(ByRef cn As ADODB.Connection, ByVal searchKey As Long) As Double Dim strSql As String Dim rs As ADODB.Recordset On Error GoTo CleanFail: Set rs = New ADODB.Recordset strSql = "SELECT Round(SUM(IIF([F1]=" & searchKey & ",1,0))*100.0/SUM(IIF([F1]<> Null,1,0)),10) From [Sheet1$];"
rs.Open strSql, cn
FindCount = rs.GetString
rs.Close
Set rs = Nothing
Exit Function
CleanFail:
Debug.Print "QUERY FAILED"
End Function

Sub PrintOutcome(ByVal counter As Long, ByVal outCome As Double)
Debug.Print "Variable " & counter & " Occured " & outCome & " Many Times"
End Sub


## Results

EDIT: THIS SHOULD BE % NOT MANY -- FIXED

Variable 1 Occured 4.9837112427 % Of Time

Variable 2 Occured 5.0171852112 % Of Time

Variable 3 Occured 4.9752235413 % Of Time

Variable 4 Occured 4.9716949463 % Of Time

Variable 5 Occured 5.0051689148 % Of Time

Variable 6 Occured 4.9989700317 % Of Time

Variable 7 Occured 4.9901008606 % Of Time

Variable 8 Occured 5.0283432007 % Of Time

Variable 9 Occured 5.0018310547 % Of Time

Variable 10 Occured 5.0164222717 % Of Time

Variable 11 Occured 4.9933433533 % Of Time

Variable 12 Occured 5.0059318542 % Of Time

Variable 13 Occured 5.0333976746 % Of Time

Variable 14 Occured 4.9952507019 % Of Time

Variable 15 Occured 5.0163269043 % Of Time

Variable 16 Occured 4.9654006958 % Of Time

Variable 17 Occured 4.9822807312 % Of Time

Variable 18 Occured 5.0310134888 % Of Time

Variable 19 Occured 5.0113677979 % Of Time

Variable 20 Occured 4.9770355225 % Of Time

QUERIES RAN IN 38.754 SECONDS

• Guessing that since RANDBETWEEN is volatile, it's updating every single one of the 1,048,576 results every time the cursor moves for a read... – Comintern Aug 23 '18 at 22:13
• I don't see RANDBETWEEN in the code anywhere, so are you really testing it or are you performing a similar query to compare results? Please adjust your title to reflect what your code does. – Raystafarian Aug 23 '18 at 23:00
• @Raystafarian OK tried to give it a better title. Also adjusted name of primary subroutine....bad habits die hard!!! :-p – learnAsWeGo Aug 24 '18 at 1:22
• @Comintern hopefully more informative title more accurately represents what I am doing – learnAsWeGo Aug 24 '18 at 1:23
• My point is that if you have any cells with RANDBETWEEN in them, you should expect the performance to be poor. RANDBETWEEN is volatile function. That means every time you calculate the value of a cell with RANDBETWEEN in it, all of them recalculate. That means as the ADO cursor moves from cell to cell, you end up recalculating 1,048,576 cells. If you aggregate them in the query, you get a trillion cell calculations. That's why it's slow. – Comintern Aug 24 '18 at 1:44

The reason for the poor performance is that you are taking a non database approach to compiling the data. A query's speed is dependent on its ability to group, filter and index records.

SELECT
Round(SUM(IIF([F1]=" & searchKey & ",1,0))
*100.0
/SUM(IIF([F1]<>Null,1,0)),10)
From [Sheet1$];  The IIF() function is much slower that using a WHERE clause to filter the data. Use WHERE ([F1] Is Not Null) AND ([F1]=" & searchKey & ") so you are only retrieving the relevant records. With the records properly filtered, you can use Count(*) instead of Sum() and IIF([F1]<>Null,1,0). It would be better to group the counts and returning all the records at once. Although it doesn't matter for 20 records, writing to the Immediate Window line by line is slow. Before writing the code below, I copied the data into an Access table and ran the Query Wizard - Count Duplicates. ## PrintOccurences Sub PrintOccurences(ByRef cn As ADODB.Connection) Const strSql As String = "SELECT First([Sheet1$].[F1]) AS [F1 Field], Round((Count([Sheet1$].[F1])/ (SELECT Count(*) FROM [Sheet1$] WHERE [Sheet1$].[F1])) * 100,10) AS NumberOfDups" & vbNewLine & _ "FROM [Sheet1$]" & vbNewLine & _
"GROUP BY [Sheet1$].[F1]" & vbNewLine & _ "HAVING (((First([Sheet1$].[F1])) Is Not Null));"

Dim sb As Object
On Error GoTo CleanFail:
Set sb = CreateObject("System.Text.StringBuilder")
rs.Open strSql, cn

Do While Not rs.EOF
sb.AppendFormat_4 "Variable {0} Occured {1} Many Times" & vbNewLine, Array(rs("F1 Field").Value, rs("NumberOfDups").Value)
rs.MoveNext
Loop

Debug.Print sb.ToString
rs.Close
Set rs = Nothing
Exit Sub
CleanFail:
Debug.Print "QUERY FAILED"
End Sub


## Results

Variable 1 Occurred 5.2052052052 Many Times
Variable 2 Occurred 3.9039039039 Many Times
Variable 3 Occurred 4.8048048048 Many Times
Variable 4 Occurred 5.2052052052 Many Times
Variable 5 Occurred 4.4044044044 Many Times
Variable 6 Occurred 5.3053053053 Many Times
Variable 7 Occurred 5.005005005 Many Times
Variable 8 Occurred 5.2052052052 Many Times
Variable 9 Occurred 5.5055055055 Many Times
Variable 10 Occurred 4.8048048048 Many Times
Variable 11 Occurred 5.4054054054 Many Times
Variable 12 Occurred 4.9049049049 Many Times
Variable 13 Occurred 4.6046046046 Many Times
Variable 14 Occurred 4.1041041041 Many Times
Variable 15 Occurred 6.6066066066 Many Times
Variable 16 Occurred 5.005005005 Many Times
Variable 17 Occurred 5.5055055055 Many Times
Variable 18 Occurred 4.4044044044 Many Times
Variable 19 Occurred 5.005005005 Many Times
Variable 20 Occurred 5.1051051051 Many Times

• I am finding the % not how many times lol. Idk why I left it like that, just tired. I am finding what % of time a each number showed up. I explain in paragraph preceding code but I guess the many times threw you off (and rightfully so) – learnAsWeGo Aug 24 '18 at 13:48
• Your initial comments are much appreciated. It seems that I am using this as an expensive array rather than an ADO. – learnAsWeGo Aug 24 '18 at 13:50
• My SQL Query is returning the total number of records in column F1 that are equal to searchKey and dividing that number by the total number of records in column F1. I tried to use a Where clause but had hard time with it – learnAsWeGo Aug 24 '18 at 13:57
• @learnAsWeGo I modified my code to return the percentage. I will clean up my post later. – TinMan Aug 24 '18 at 14:50
• Basically if i want this to be fast I am going to have to do the indexing / sorting / filtering myself then run query? – learnAsWeGo Sep 25 '18 at 3:15