I'm about to start a project using QODBC and I need your advice as to which provides the best query speed or whether there is anything I can do to improve the speed.
I am writing a cash flow application in Excel with ADO. Each cell will have a custom function that results in a QODBC SELECT query and there may be hundreds of function calls. I need your advice as to which one of these methods I should use (see examples below).
Option 1 - Query transaction table with JOIN. For me takes 1.15 secs per query (So 500 queries would take 10 min ... too long)
Option 2 - Query a report to try to get the same data 0.35 secs per query, so 500 queries will take 3 min. Much faster, but harder to code and the resulting code is a lot harder to understand.
Option 3 - Do one Query, return the recordset to Excel, using EXCEL functions or even ADO to perform subsequent queries in memory. Speed is nearly instant but its a lot more coding and somewhat defeats the purpose of the direct connect with QODBC. Snapshot is fine, but I had hoped to avoid a lot of SUMIFS, SUMPRODUCT, VLOOKUP formulae, but I'm leaning this way.
Option 4 - Use the transaction table so that no JOIN is needed. I've not tried this yet.
Option 5 - Something else I had overlooked and the nature of this posted question?????
Obviously, I would use Option 1 if it were faster.
In the example below, I need to filter on account. If I use the BILL table, account is not available. If I use BilIItemLine, the account is still not available unless I join to Item table, which appears to take a long time. I removed the account filter just to test the rest of the query speed, but that is the reason for my join.
Example of Option 1 - 1.15 seconds per query.
vSQL = "Select sum(itemlineamount) as vTotal " _
& "from BillItemLine Inner Join Item on BillItemLine.ItemLineItemRefListID = Item.ListID Where BillItemLine.IsPaid = False " _
& "and TxnDate >= " & "{d'" & Format(date1, "yyyy-mm-dd") & "'}" & " And TxnDate <= " & "{d'" & Format(date2, "yyyy-mm-dd") & "'}"
Example of Option 2 - 0.35 seconds per query
vSQL = "sp_report CustomTxnDetail show sum(amount) as vTotal parameters DateFrom = " _
& "{d'" & Format(date1, "yyyy-mm-dd") & "'} " & ", DateTo = " & "{d'" & Format(date2, "yyyy-mm-dd") & "'} " _
& ", TxnFilterTypes = 'Bill' " _
& ", SummarizeRowsBy = 'TotalOnly' Where PaidStatus='Unpaid'"
| 
