speed sql vs recordset

[WindAndWaves]

I was wondering if anyone knows what is faster: sql or recordset updates???
And if there are any other differences. At the moment I use docmd.runsql
throughout my database.


---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.688 / Virus Database: 449 - Release Date: 18/05/2004

[Tom van Stiphout]

On Fri, 21 May 2004 12:17:35 +1200, "WindAndWaves" <ac****@ngaru.com>
wrote:

Do it both ways, and time the differences. The GetTickCount API call
is good for measuring time.

-Tom.

I was wondering if anyone knows what is faster: sql or recordset updates???
And if there are any other differences. At the moment I use docmd.runsql
throughout my database.


---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.688 / Virus Database: 449 - Release Date: 18/05/2004

[Rob]

Using a SQL update is ABSOLUTELY faster!

Tom van Stiphout <to*****@no.spam.cox.net> wrote in message news:<u6********************************@4ax.com>. ..

On Fri, 21 May 2004 12:17:35 +1200, "WindAndWaves" <ac****@ngaru.com>
wrote:

Do it both ways, and time the differences. The GetTickCount API call
is good for measuring time.

-Tom.

I was wondering if anyone knows what is faster: sql or recordset updates???
And if there are any other differences. At the moment I use docmd.runsql
throughout my database.


---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.688 / Virus Database: 449 - Release Date: 18/05/2004

[WindAndWaves]

thanks for that Rob - SQL seems to be a very fast tool and it is actually
very powerful in its simplicity

Great, I made the right choice!

One more little question: why are the domain aggregate functions (e.g.
dlookup) so slow and are there alternatives (I used them all the time.

CHeers

Nicolaas
"Rob" <si****@ensco.com> wrote in message
news:bd*************************@posting.google.co m...

Using a SQL update is ABSOLUTELY faster!

Tom van Stiphout <to*****@no.spam.cox.net> wrote in message

news:<u6********************************@4ax.com>. ..

On Fri, 21 May 2004 12:17:35 +1200, "WindAndWaves" <ac****@ngaru.com>
wrote:

Do it both ways, and time the differences. The GetTickCount API call
is good for measuring time.

-Tom.

I was wondering if anyone knows what is faster: sql or recordset updates???And if there are any other differences. At the moment I use docmd.runsqlthroughout my database.


---
Please immediately let us know (by phone or return email) if (a) this emailcontains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.688 / Virus Database: 449 - Release Date: 18/05/2004

---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.688 / Virus Database: 449 - Release Date: 18/05/2004

[Larry Linson]

It used to be that people said that domain aggregate functions did not take
advantage of indexes, but others have said that is not the case. As far as I
know, Microsoft has not made any official comment on that issue.

Back when people thought the domain aggregate functions didn't use the
indexes, I saw a number of substitute functions that people had written on
the assumption that they would be faster, using DAO/SQL. I never saw one of
those actually timed, so don't know if they WERE faster.

My use of domain aggregate functions has generally been such that
performance was a minor issue, if an issue at all. Occasional use in code is
where they shine.

Larry Linson
Microsoft Access MVP
"WindAndWaves" <ac****@ngaru.com> wrote in message
news:Fb*******************@news.xtra.co.nz...

thanks for that Rob - SQL seems to be a very fast tool and it is actually
very powerful in its simplicity

Great, I made the right choice!

One more little question: why are the domain aggregate functions (e.g.
dlookup) so slow and are there alternatives (I used them all the time.

CHeers

Nicolaas
"Rob" <si****@ensco.com> wrote in message
news:bd*************************@posting.google.co m...

Using a SQL update is ABSOLUTELY faster!

Tom van Stiphout <to*****@no.spam.cox.net> wrote in message news:<u6********************************@4ax.com>. ..

On Fri, 21 May 2004 12:17:35 +1200, "WindAndWaves" <ac****@ngaru.com>
wrote:

Do it both ways, and time the differences. The GetTickCount API call
is good for measuring time.

-Tom.
>I was wondering if anyone knows what is faster: sql or recordset updates??? >And if there are any other differences. At the moment I use docmd.runsql >throughout my database.
>
>
>
>
>---
>Please immediately let us know (by phone or return email) if (a) this email >contains a virus
> (b) you are not the intended recipient
> (c) you consider this email to be spam.
>We have done our utmost to make sure that
>none of the above are applicable. THANK YOU
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.688 / Virus Database: 449 - Release Date: 18/05/2004
>

---
Please immediately let us know (by phone or return email) if (a) this

email contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.688 / Virus Database: 449 - Release Date: 18/05/2004

[almish]

I'd caution you about making a presumption that SQL is always faster.
For almost all standard queries this is undoubtable true but sometimes
complex queries can be done faster in code and measuring it, as Tom
points out, would be justified where query performance is lacking.

'---------------
' John Mishefske
'---------------

si****@ensco.com (Rob) wrote in message news:<bd*************************@posting.google.c om>...

Using a SQL update is ABSOLUTELY faster!
Tom van Stiphout <to*****@no.spam.cox.net> wrote in message news:<u6********************************@4ax.com>. ..

On Fri, 21 May 2004 12:17:35 +1200, "WindAndWaves" <ac****@ngaru.com>
wrote:

Do it both ways, and time the differences. The GetTickCount API call
is good for measuring time.

-Tom.

[Larry Linson]

I once worked in an organization which had a very knowledgeable and thorough
"quality manager" who reviewed all proposals and deliverables. He noted that
"always", "every", "none", and "never" are very strong statements that we
usually would be well-advised to avoid. <GRIN>

But, I agree: in most instances, you get advantage of the optimization built
into the database engine by using SQL or Queries, while you are "on your
own" in code. But, you may "luck out" sometimes and do code that beats
particular SQL.

Larry Linson
Microsoft Access MVP

"almish" <mi****@execpc.com> wrote in message
news:95**************************@posting.google.c om...

I'd caution you about making a presumption that SQL is always faster.
For almost all standard queries this is undoubtable true but sometimes
complex queries can be done faster in code and measuring it, as Tom
points out, would be justified where query performance is lacking.

'---------------
' John Mishefske
'---------------

si****@ensco.com (Rob) wrote in message

news:<bd*************************@posting.google.c om>...

Using a SQL update is ABSOLUTELY faster!
Tom van Stiphout <to*****@no.spam.cox.net> wrote in message news:<u6********************************@4ax.com>. ..

On Fri, 21 May 2004 12:17:35 +1200, "WindAndWaves" <ac****@ngaru.com>
wrote:

Do it both ways, and time the differences. The GetTickCount API call
is good for measuring time.

-Tom.

[Tony Toews]

"Larry Linson" <bo*****@localhost.not> wrote:

I once worked in an organization which had a very knowledgeable and thorough
"quality manager" who reviewed all proposals and deliverables. He noted that
"always", "every", "none", and "never" are very strong statements that we
usually would be well-advised to avoid. <GRIN>

Yup, I've had to change my posts occasionally due to this as other folks have pointed
out that I was completely wrong. <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Tony Toews]

"WindAndWaves" <ac****@ngaru.com> wrote:

And if there are any other differences. At the moment I use docmd.runsql
throughout my database.

I prefer, if DAO, to use Currentdb.Execute strSQL,dbfailonerror command instead of
docmd.runsql. For ADO use CurrentProject.Connection.Execute strCommand,
lngRecordsAffected, adCmdText

If you're going to use docmd.setwarnings make very sure you put the True statement in
any error handling code as well. Otherwise wierd things may happen later on
especially while you are working on the app. For example you will no longer get the
"Do you wish to save your changes" message if you close an object. This may mean
that unwanted changes, deletions or additions will be saved to your MDB.

Also performance can be significantly different between the two methods. One posting
stated currentdb.execute took two seconds while docmd.runsql took eight seconds. As
always YMMV.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm