Speed Issue

groupy

Registered User.
Local time
Today, 09:24
Joined
Dec 17, 2001
Messages
33
Running query (speed issue)

Hi,

I have the following question, which way of running query is in general faster or best to use:

--------------------------------------------------
DoCmd.SetWarnings False
DoCmd.RunSQL ("[SQL STATEMENT]")
DoCmd.SetWarnings True
--------------------------------------------------

OR

--------------------------------------------------
Set db = CurrentDb
mySQL = "[SQL STATEMENT]"
Set qry = db.CreateQueryDef("", mySQL)
qry.Execute
qry.Close
db.Close
--------------------------------------------------

The second question is: Are they both working when I transfer my back-end to SQL Server 2000?

My third question is: Is DLookup the fastest way to look for a record in a table or are there other and faster ways to do it?

Thanks a lot for your answers.
 
The fastest way to get a query to run is by using a stored querydef. This way the SQL for the query is precompiled and, thus, runs faster then an SQL statement generated and executed at runtime. Instead of the options below why not just run the query like this:
...................
Docmd.setwarnings false
Docmd.openquery "YourQueryName"
Docmd.setwarnings true
...................

If you still wish to know which of the two solutions you provided below is faster I will leave that up to others to answer (my gut tells me the second option but I truthfully do not know) But, as I said above, the stored querydef will definitley be the fastest of the three.

Second question: Yes they will both work in SQL Server 2000

Third Question: No, DLookUp is not the fastest method (sometimes ) :D Basically it really depends on the size of the dataset your working with. The fastest means to find a specific record is to create a recordset and manipulate the data in the recordset however, If your dealing with smaller datasets then DLookUp won't be that much slower and if your new to coding it is easier then creating a recordset so therre is some things to think about there too...

HTH,
Kev
 
Thanks for you're reply.

About the first question, when I will use the following code:
...................
Docmd.setwarnings false
Docmd.openquery "YourQueryName"
Docmd.setwarnings true
...................

Can I replace the "YourQueryName" with a Stored Procedure when I transfer my back-end to SQL Server. Or are Stored Procedures working on a other way.

About the DLookup. I like DLookup because it is easy to use. Can you give me a example how to use a recordsset on the same way as DLookup but on the sortest possible way?

Thanks a lot for your answers and maybe future answers.
 
Groupy:

Stored procedures work different so you can't just replace the name of your query with the name of our stored procedure as they require a connection string to run. Basically a sproc (sproc = stored procedure) requires the following steps:

- connect to BE through connection string
- create ADODB command object
- define what your connecting to run 'your case a sproc
- pass parameters to sproc
- either return recordset or run sproc
- close connection string
- set recordset/object = nothing

for the full code, including code on how to create the connection string check this previous posts:
http://www.access-programmers.co.uk...ge=15&highlight=stored procedure&pagenumber=1

Recordset:

Here are the fundamentals to creating a recordset:

Dim rst As New ADODB.Recordset
Dim StrSQL As String
Set rst = New ADODB.Recordset
strSQL = "SELECT * From YourTableName"
rst.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic
'now you manipulate your recordset (do something with it!)
with rst.
.AddNew
.Find
etc....
rst.close
set rst = nothing

Thats the basics to creating and using a recordset. The SQL String replaces your DLookUp statement and then you can edit, add, manipulate, etc... the recordset just as you would a variable in a DLookUp statement only its more effecient as the SQL string is a generally faster process to run.

Also - for more help on these subjects try using the search function on this forum as there a numerous posts on both of these subjects that may give you additional insight.

HTH,
Kev
 
Last edited:

Users who are viewing this thread

Back
Top Bottom