WHERE...AND issues (1 Viewer)

[vbaInet]

As promised, folks.

Table - local table containing ~100k records with all the fields listed below
Query - based on table + showing all fields
Order By On Load - No (Table and Query)
Timer - high res timer with a precision of ~100 microseconds

Field1 - AutoNumber, Indexed
Field2 - Text, Indexed
Field3 - Number
Field4 - Date

Test Cases:
1. CompileQ: Opening a recordset with a query name.

CurrentDb.OpenRecordset("QueryName", dbOpenSnapshot)

2. CodeQ: Opening a recordset with a SELECT statement of the table.

CurrentDb.OpenRecordset("SELECT * FROM TableName", dbOpenSnapshot)

3. QuerydefQ: Opening a recordset from a querydef.

CurrentDb.Querydefs("QueryName").OpenRecordset(dbOpenSnapshot)

Scenario 1: No sort or filter applied, dbOpenSnapshot (as above)

CompiledQ:      1       0.0019
CompiledQ:      2       0.0029
CompiledQ:      3       0.002
CompiledQ:      4       0.0031
CompiledQ:      5       0.0028
CompiledQ:      6       0.0026
CompiledQ:      7       0.0028
CompiledQ:      8       0.0019
CompiledQ:      9       0.0029
CompiledQ:      10      0.0022
Average:		0.0025

CodeQ:          1       0.0017
CodeQ:          2       0.0019
CodeQ:          3       0.0017
CodeQ:          4       0.0014
CodeQ:          5       0.002
CodeQ:          6       0.0017
CodeQ:          7       0.0019
CodeQ:          8       0.0017
CodeQ:          9       0.0018
CodeQ:          10      0.0019
Average:		0.0018

QuerydefQ:      1       0.0035
QuerydefQ:      2       0.004
QuerydefQ:      3       0.0033
QuerydefQ:      4       0.0037
QuerydefQ:      5       0.0034
QuerydefQ:      6       0.0052
QuerydefQ:      7       0.0035
QuerydefQ:      8       0.0033
QuerydefQ:      9       0.0035
QuerydefQ:      10      0.0033
Average:		0.0036

The following scenarios are results that have been averaged out. Take note of of the recordset type.

Scenario 2: Field2 (Text, Indexed) ASC, Field4 (Date) ASC, dbOpenSnapshot
CompiledQ:              0.3721
CodeQ:                  0.4085
QuerydefQ:              0.3736

Scenario 3: No sort or filter applied, dbOpenDynaset
CompiledQ:              0.0028
CodeQ:                  0.0018
QuerydefQ:              0.0038

Scenario 4: Field2 (Text, Indexed) ASC, Field4 (Date) ASC, dbOpenDynaset
CompiledQ:              0.2762
CodeQ:                  0.2890
QuerydefQ:              0.2550

I would imagine that there will be performance differences if the same tests are performed on linked tables and it would also vary on the source of the linked table.

 

[max1]

same (ish) problem, different day

Hi again, an annoyingly similar problem to my original post. I hope yous will help me again!

I receive a error message to the effect that I have a syntax error (missing operator) in the 'WHERE' clause.

I have tried to set out the query in the approved style.

Not sure how many more parentheses i can remove

Sub EnterDrillReconData(STPName As String)

Dim db As DAO.Database
Dim strSQL100 As String

Set db = CurrentDb


strSQL100 = "INSERT INTO tbltmpDrillRecon ( StopeName, VulcanHoleID, DesignOrSurvey, GoodData, Depth, IDDrillRecon, DrillPurpose, DrillDate, DrillDiameter, DrillDepth, DrillOperator, DrillRig, DrillShift, Breakthrough, Dump, Comments ) " & _
            "SELECT tblDesignStope.StopeName, tblHolesDesignAndSurvey.VulcanHoleID, tblHolesDesignAndSurvey.DesignOrSurvey, tblHolesDesignAndSurvey.GoodData, tblHoleCoordinates.Depth, tblDrillRecon.IDDrillRecon, tblDrillRecon.DrillPurpose, tblDrillRecon.DrillDate, tblDrillRecon.DrillDiameter, tblDrillRecon.DrillDepth, tblDrillRecon.DrillOperator, tblDrillRecon.DrillRig, tblDrillRecon.DrillShift, tblDrillRecon.Breakthrough, tblDrillRecon.Dump, tblDrillRecon.Comments " & _
            "FROM ((tblDesignStope " & _
            "INNER JOIN tblHolesDesignAndSurvey ON tblDesignStope.IDStope = tblHolesDesignAndSurvey.IDStope) " & _
            "LEFT JOIN tblDrillRecon ON tblHolesDesignAndSurvey.IDHole = tblDrillRecon.IDHole) " & _
            "INNER JOIN tblHoleCoordinates ON tblHolesDesignAndSurvey.IDHole = tblHoleCoordinates.IDHole " & _
            "WHERE tblDesignStope.StopeName=" & STPName & " " & _
            "AND tblHolesDesignAndSurvey.DesignOrSurvey='DESIGN'"

Debug.Print strSQL100

db.Execute (strSQL100)

DoCmd.OpenForm "frmDrillRecon"

End Sub

 

[JHB]

I think it is because of the missing '

"WHERE tblDesignStope.StopeName='" & STPName & "' " & _

 

[namliam]

Agree with JHB and Kudos for trying to make and keep your code readable!

Though, why you would create a table just before opening a form (that is probably based on the just created table) is beyond me ....

 

[max1]

JB - you suggest the additional inverted commas and yet I have used the same structure without the additional inverted commas previously. The only difference being this query has an additional left join.

namliam - I create a table because I don't think additional records can be added to data viewed through a query. Hence I query existing records into temporary table - edit some records, add others - query back into permanent tables.

 

[Galaxiom]

you suggest the additional inverted commas and yet I have used the same structure without the additional inverted commas previously.

Quote marks are string delimiters. Your other queries probably were against numeric values.

 

[max1]

Thank you Galaxiom. You have hit the nail on the head. Once more a very simple solution. I am now a little less ignorant

 

[JHB]

.. you suggest the additional inverted commas and yet I have used the same structure without the additional inverted commas previously. The only difference being this query has an additional left join.
..

Why didn't you just try it, then you would have discovered that the answer was correct?

 

[max1]

Did that comment add anything constructive JB?

I think not

 

[Brianwarnock]

Of course it did, it suggested that you try things rather than wait for somebody to confirm what will happen, thus saving time.

Some object to trial and error learning but it is amazing how instructive it can be, of course I assume you are working on test data that can be easily reconstructed.

Brian