Efficiency Question - Dlookup vs ADO cmd execution

  • Thread starter Thread starter Chris Strug
  • Start date Start date
C

Chris Strug

Hi,

Pretty much as the title says, I've been updating an app that my company
uses and have noticed that in a lot of places it uses the VBA DLookUp
command to retrieve information (for example, detecting if a record exists).
Presumably all these DLookUps are a leftover from before the project was
updated from an MDB.

I was wondering, is it more efficient to retrieve information like this by
executing a SELECT statement embedded in an ADO Command object.

Certainly from a portability point of view this would seem to be the way
forward (should I ever want to migrate the front end to a stand-alone VB
app) but I was wondering about it from a performance point of view - is the
ADO command object more efficient than using a the DLookUp function?

Any and all advice is gratefully received.

Thanks

Chris.
 
Most of the work is done on the SQL-Server side; so usually there will be no
real difference between these two. You might gain a little performance when
using a stored procedure or a precompiled Select statement but unless you
are on a very stressed SQL-Server, you shouldn't mind about these little
differences most of the time. To make sure, you can take a look with the
SQL-Server Profiler.

However, this doesn't mean that there are no possibility of achieving a
greater performance and you should always take a deep look at the design on
a regular basis. For example, if an index is missing for a critical field,
then both the DLookUp and the Select statement will resolve to a lengthy
table scan on the SQL-Server.

For the future, classical VB is dimmed in favor of .NET, so there is no
point of changing these DLookUp or to migrate to a stand-alone VB
application if your concern is portability.
 
Sylvain Lafontaine said:
Most of the work is done on the SQL-Server side; so usually there will be
no real difference between these two. You might gain a little performance
when using a stored procedure or a precompiled Select statement but unless
you are on a very stressed SQL-Server, you shouldn't mind about these
little differences most of the time. To make sure, you can take a look
with the SQL-Server Profiler.

However, this doesn't mean that there are no possibility of achieving a
greater performance and you should always take a deep look at the design
on a regular basis. For example, if an index is missing for a critical
field, then both the DLookUp and the Select statement will resolve to a
lengthy table scan on the SQL-Server.

For the future, classical VB is dimmed in favor of .NET, so there is no
point of changing these DLookUp or to migrate to a stand-alone VB
application if your concern is portability.
Click to expand...

Thanks for that - though I would have thought that changing them to ADO
command objects would certainly have made migration to a VB.net client
easier...?

Anyway, thanks for taking time to help.

Regards

Chris.
 
Absolutely not: the difference beetwen ADO and ADO.NET is so big that
migrating them first to ADO objects won't make things easier at all. Doing
this will only be a pure waste of time.
 
Sylvain Lafontaine said:
Absolutely not: the difference beetwen ADO and ADO.NET is so big that
migrating them first to ADO objects won't make things easier at all.
Doing this will only be a pure waste of time.
Click to expand...

Thanks for the advice. Looks like I've some reading to do! :)

Thanks again

Chris.
 
I read somewhere that using DLookup with ADP/MS SQL was not recommended
because it returned unpredictable results. While I have not witnessed this
behavior myself, I'm trying to do all of my look-ups using ADO just to be
safe.

Has anyone else heard anything about this issue?

Mark
 
Back
Top