Stored Query vs SELECT statements

[wvandenberg]

I would like to ask for some advice about the performance of stored queries vs. SELECT statements and when/where to use each.

Which of the following scenarios would be faster/better/more efficient/preferred by real programmers (unlike myself)

    strSQL = "SELECT DISTINCT ANALYTE, AnalyteName, pkAnalyteID, ReportingUnitsDescription " & _
            "FROM tblImportALS, qryAllAnalytes " & _
            "WHERE AnalyteName Like ANALYTE " & _
            "And ReportingUnitsDescription Like UNITS"
    Set rst = db.OpenRecordset(strSQL)

vs. (assuming the SQL of Qry1 is the same as strSQL)

    Set rst = db.OpenRecordset("Qry1")

Also, is it faster/better/more efficient/preferred by real programmers (unlike myself) to use a stored query as the control source for a combo box rather than a Select statement?

Any comments or advice is appreciated.

Wendy

 

[dhookom]

IMHO, the performance will be not be much different unless the recordset is quite large. I expect the saved query will "compile" to provide greater performance.

Regarding the Row Source for a combo or list box (or even a form's Record Source). If you create a query with SQL of:

SELECT msysObjects.*
FROM msysObjects
WHERE (((msysObjects.Name) Like "~sq_*"));

You will see that most sql statements are actually saved queries.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]