Speed: Querydef or recordset string?

[CharlesDFW]

Anyone out there know which is quicker?<br><br>A created and compiled querydef with a parameter set by code<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;or<br>Opening a recordset using a generated SQL string.<br><br>Documentation suggests that the querydef that has been used a few times, edited, saved, and run can take full advantage of Rushmore. But I'm wondering whether it will really operate appreciably more quickly than a new recordset.<br><br>Someone commented that the preferred approach is to generate the SQL string. Preferred by whom, and why?<br><br>Appreciate any feedback.<br><br>

 

[DougP]

I usually alway use SQL.<br>It is what Access is using when it processes anything you do with a front end. Like the QBE grid.<br><br>Also once you know it its easier to implement and requires only one line of code to bring back a whole host of criteria.<br>Here is the minimum you need<br> Dim db as database, rst as recordset, SQL as string<br> Set db = CurrentDb<br> ' SQL string.<br> SQL = &quot;SELECT * FROM Orders WHERE OrderDate &gt;= #1-1-95#;&quot;<br> Set rst = db.OpenRecordset(SQL)<br>------------------------<br>To set a form to something just as easy<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' set record source to Subform <br>&nbsp;&nbsp;&nbsp;&nbsp;SQL = &quot;SELECT * FROM Orders WHERE OrderDate &gt;= #1-1-95#;&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;Me![YOURsubform].Form.RecordSource = SQL<br><br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[CharlesDFW]

Many thanks, Doug. I always prefer to use my own SQL string, too. Makes it a lot easier on me.<br><br>But the documentation says that a compiled querydef runs much faster because it uses the database statistics to perform Rushmore optimization on the querydef.<br><br>I guess my question is, is it still going to be faster than entering a new SQL string and opening a recordset if the querydef has a parameter? Setting the parameter in code to prevent asking the user for it is a lot more trouble than just generating the SQL string, but I'm always trying to eke out a bit more speed.<br><br>Thanks.<br><br>Charles<br><br><br>

 

[Halifax]

It depends on which version of Access you're using<br>In pre-97, it's true that saved, compiled querydefs were faster, because they were precompiled, and the Jet had built an execution plan.&nbsp;&nbsp;This however, is true if the dataset that the query was tested on was a similar size to the dataset size in the live database.&nbsp;&nbsp;A query developed, compiled and saved using 10 records as a testbed, will not have an effective execution plan if it is to be run against a table of 100,000 records.<br><br>Since Access97, when your SQL is imbedded in your form or report, Access creates and saves a system querydef (named.... i think... something like &quot;~sq+name of form/report&quot;.) This is what is compiled and used as the basis for the query execution plan. So wether you use a saved query or embedded SQL is a much less important consideration. Better to concentrate on ensuring your criteria fields are indexed, and whether you put criteris on the child (no) or the parent (yes) side of the join.<br>