What's faster - Query or Execute SQL 1

[hwkranger]

Which is faster?

MySQL = "DELETE [ProductivityRpt-Monthly].* FROM [ProductivityRpt-Monthly];"
CurrentDb().Execute MySQL

or

DoCmd.OpenQuery "Prod-Daily-Delete", acNormal, acEdit

Please don't provide opinions, provide substantial proof. Even miliseconds count! I'm all about performance.

*Note I use CurrentDB() and not an indexed referenced DBEngine, due to the fact that people can have more than 1 database open, and this could cause problems.

Thankyou,


Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[jsteph]

If you were all about performance, you'd be using a querydef.

A querydef is compiled, either of the above are not.
--jsteph

 

[jsteph]

...I take that back...the .OpenQuery is a compiled query, but it's slower because it goes through the docmd object, which, I don't have statistics, but I know there is another layer there.
--jsteph

 

[hwkranger]

Perhaps I'm seeing this from the wrong angle, but:

DoCmd.OpenQuery "Prod-Daily-Delete", acNormal, acEdit

is a delete query, which has a querydef.

so from what your saying the following would be faster? (straight from the access help documents)

Sub NewQuery()
Dim dbs As Database, qdf As QueryDef, strSQL As String

' Return reference to current database.
Set dbs = CurrentDb
strSQL = "SELECT * FROM Employees WHERE [HireDate] >= #1-1-93#"
' Create new query.
Set qdf = dbs.CreateQueryDef("RecentHires", strSQL)
DoCmd.OpenQuery qdf.Name
Set dbs = Nothing
End Sub

Honestly, I don't understand why this would be faster.

Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[hwkranger]

Ok,

so what's the fastest way to execute a query?

Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[jsteph]

No....
Your query that already exists:
Prod-Daily-Delete

...has a querydef.

sub Test()
dim db as database,qd as querydef
set db = currentdb
set qd = db.querydefs("Prod-Daily-Delete&quot
'(by the way, if milliseconds count, adjust your naming convention--using non-standard naming slows down a bit because access has to parse a bracketed name. ie one with dashes, reserved words, spaces, etc)

qd.execute

end sub

Now, you see more code there than you do in docmd.openquery..., don't you?

Just because that's one line of code (same with comparing Dlookup() to opening a recordset, etc), don't fall into the trap of thinking it's faster. Do you think it's magic or something? Do you think that one line of code can just skip all the other stuff I did in the test function?

No. The docmd is a wrapper--that means it's an object that calls it's own functions that have many, many lines of code. Just because you see that, and Dlookup, etc as one line, doesn't make it faster.
--jsteph

 

[hwkranger]

I didn't think that it was a magic way of being faster because it's shorter. I try to never use docmd I know that every function has a library that is uses that has tons of code.

The naming convention i follow. I inherited this POS from someone else.

Thankyou very much for your indepth explanation!


Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.