Interestingly I've done a quick little experiment and have found THE OPPOSITE of post #1 to be true. Hard coding sql statements were both QUICKER and caused zero bloat, while utilizing QueryDef both took longer and caused lots of bloat. Can anyone explain why QueryDef is causing bloat?
I set up a simple table with 4 columns
ID
Category (byte)
Quantity (double)
UnitPrice (currency)
I then populated the table with 10,000 records of random data. The db was then split.
In the front end I made a paramatized Select query to sum(qty * unitprice) and group by category. The paramter asks for a category to return the sum of the prices for one category.
I have two vba functions, each one returns the same thing: the Sum of a price given a category. One function uses QueryDef and the paramterized query, the other function uses a string to build the sql statement on the fly. I used a loop to execute both of the functions 1000 times. The one that uses querydef bloats the front end and is slower, while the one that uses the string has zero effect on the front end's file size.
String version (zero bloat, executed 1000 times in about 30 seconds):
Code:
Public Function TestOne(cat As Byte) As Currency
Dim rs As DAO.Recordset
Dim sql As String
sql = "SELECT ShoppingList.Category, Sum([Quantity]*[UnitPrice]) AS Price, Count(ShoppingList.Category) AS Cnt" & _
" FROM ShoppingList" & _
" WHERE ShoppingList.Category=" & cat & _
" GROUP BY ShoppingList.Category;"
Set rs = CurrentDb.OpenRecordset(sql)
TestOne = rs!Price
rs.Close
Set rs = Nothing
End Function
QueryDef version (417kb to 4.3mb, executed 1000 times in about 46 seconds):
Code:
Public Function TestTwo(cat As Byte) As Currency
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Set qd = CurrentDb.QueryDefs("qrySums")
qd.Parameters("cat") = cat
Set rs = qd.OpenRecordset
TestTwo = rs!Price
qd.Close
rs.Close
Set qd = Nothing
Set rs = Nothing
End Function
The referenced parameter query:
Code:
PARAMETERS cat Byte;
SELECT ShoppingList.Category, Sum([Quantity]*[UnitPrice]) AS Price, Count(ShoppingList.Category) AS Cnt
FROM ShoppingList
WHERE (((ShoppingList.Category)=[cat]))
GROUP BY ShoppingList.Category;