what is faster sql or movenext?

[Danny]

I am trying to process a database and my code does so much that it takes a
whle to go through the database.
most of it is sql queries, updates and such.

For about 6000 records, it takes over a minute to process,maybe 2.5 to 3
minutes.
How can I speed it up?
I did create an index on some of the search fields, but didnt seem to help.

I have a 2.4 mhz machine!!

is sql faster or just going through each record and working on the fields
one at a time faster.

thanks again

[Lyle Fairfield]

Re: what is faster sql or movenext?

"Danny" <dannywork5@hot mail.com> wrote in
news:Ch52c.1536 1$UF1.2107282@n ews4.srv.hcvlny .cv.net:
[color=blue]
> I am trying to process a database and my code does so much that it takes
> a whle to go through the database.
> most of it is sql queries, updates and such.
>
> For about 6000 records, it takes over a minute to process,maybe 2.5 to 3
> minutes.
> How can I speed it up?
> I did create an index on some of the search fields, but didnt seem to
> help.
>
> I have a 2.4 mhz machine!!
>
> is sql faster or just going through each record and working on the
> fields one at a time faster.[/color]

SQL is faster 90% of the time. In very intensive data manipulation, an
experienced programmer may be able to speed things up with temporary
tables and indexes and vba. If you tell us what "my code does so much" is
doing, we might be able to make some suggestions.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[R.A.Balder]

Re: what is faster sql or movenext?

You say: "did create some indexes of search fields". Do your tables have
unique key indexes ? If not, everything will be slow.
Also: I use indexes on all fields used for relations, criterias and search.
Whatch out for relations: if you by mistake create many to many relations in
a query, processes will be very slow.The "one" side of a relations should be
a unique index field.
So all of the stuff to do for your database design is important. No code
can cure a bad database design.

Brgds
Rolfern

"Danny" <dannywork5@hot mail.com> wrote in message
news:Ch52c.1536 1$UF1.2107282@n ews4.srv.hcvlny .cv.net...[color=blue]
> I am trying to process a database and my code does so much that it takes a
> whle to go through the database.
> most of it is sql queries, updates and such.
>
> For about 6000 records, it takes over a minute to process,maybe 2.5 to 3
> minutes.
> How can I speed it up?
> I did create an index on some of the search fields, but didnt seem to[/color]
help.[color=blue]
>
> I have a 2.4 mhz machine!!
>
> is sql faster or just going through each record and working on the fields
> one at a time faster.
>
> thanks again
>
>[/color]

[Rich P]

Re: what is faster sql or movenext?

I have seen this kind of performance before for a fast machine. A 2.4
gig machine should be able to process 6000 records in a few milli
seconds. The mdb I had to deal with that had the same problem had some
union queries that were base on other queries that were based on other
queries that were based on other queries....

and each of these "other" queries had like 10 joins per query of which
some of the joins were many to many. So what was taking time was that
Access was doing a lot of guessing work. I give Access credit for being
a champion of usually guessing correctly, but the price is performance.

If you have a lot of queries that are based on other queries, or queries
with several joins you may want to follow the suggestion of one of the
other repliers to your post to use temp tables. Populate the temp
tables to reduce joins. Joins are a major villain to performance.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Mike MacSween]

Re: what is faster sql or movenext?

"Rich P" <rpng123@aol.co m> wrote in message
news:404916f1$0 $198$75868355@n ews.frii.net...
[color=blue]
> tables to reduce joins. Joins are a major villain to performance.[/color]

Particularly outer joins, I found the other day. I had a query which ran
like a slug crawling over treacle. A drunk slug. In a coma. When I looked at
the query I found an (unnecessary, as it turned out) outer join. When I
changed that to an inner join the whole thing was instant.

Mike

[JIMMIE WHITAKER]

Re: what is faster sql or movenext?

USE ARRAYS, nothing is faster.

"Mike MacSween" <mike.macsween. damnthespam@bti nternet.com> wrote in message
news:4049861e$0 $63623$5a6aecb4 @news.aaisp.net .uk...[color=blue]
> "Rich P" <rpng123@aol.co m> wrote in message
> news:404916f1$0 $198$75868355@n ews.frii.net...
>[color=green]
> > tables to reduce joins. Joins are a major villain to performance.[/color]
>
> Particularly outer joins, I found the other day. I had a query which ran
> like a slug crawling over treacle. A drunk slug. In a coma. When I looked[/color]
at[color=blue]
> the query I found an (unnecessary, as it turned out) outer join. When I
> changed that to an inner join the whole thing was instant.
>
> Mike
>
>[/color]

[Larry Linson]

Re: what is faster sql or movenext?


"JIMMIE WHITAKER" wrote
[color=blue]
> USE ARRAYS, nothing is faster.[/color]

Unfortunately, arrays are not permanent storage in a database system, so
they still have to be read from and written to disk -- and, in a database
system, that would probably be in a table rather than a file. If not, then
you lose the capability to query the data, select on criteria, sort as it is
retrieved, etc..

I'm afraid your advice here doesn't make much sense to me.

Larry Linson
Microsoft Access MVP

[Andrew]

Re: what is faster sql or movenext?

Hi Danny,

If you can achieve what you need to do using SQL, you will probably find it
is much quicker. Check that you're using indexes sensibly (any field that's
used in a join or in search criteria should be indexed).

However, if what you're doing is too tricky to manage in SQL, then by all
means use VBA. What I would suggest though, is if it's an entirely
Access/Jet application (i.e. the data is in an Access database, as well as
the processing taking place through Access) then use DAO, not ADO. If your
app is Access 97, then DAO is the default anyhow, but if it's A2K or higher,
then you'll have to set a reference (VBA editor, Tools, References then find
Microsoft DAO x.xx Object Library - use the highest one.) Then explicitly
declare your recordset as DAO.Recordset in code.

I had one application where using ADO to update about 600 record took 15
seconds! I changed to DAO and it took less than 1/2 a second. This is a
fairly extreme example, but it's worth knowing that DAO is usually much
faster than ADO within Access.

(If your data is in one of the big fellas like SQL Server or Oracle, then
try and use a stored procedure or user-defined function to do the work - the
server will be much, much more powerful than Access!)

HTH

Andrew

"Danny" <dannywork5@hot mail.com> wrote in message
news:Ch52c.1536 1$UF1.2107282@n ews4.srv.hcvlny .cv.net...[color=blue]
> I am trying to process a database and my code does so much that it takes a
> whle to go through the database.
> most of it is sql queries, updates and such.
>
> For about 6000 records, it takes over a minute to process,maybe 2.5 to 3
> minutes.
> How can I speed it up?
> I did create an index on some of the search fields, but didnt seem to[/color]
help.[color=blue]
>
> I have a 2.4 mhz machine!!
>
> is sql faster or just going through each record and working on the fields
> one at a time faster.
>
> thanks again
>
>[/color]