Queries running too SLOOOW!

[Guest]

I'm running a query where I'm copying records that match on table A and table B and inserting them into table C

Now Table A is about 50,000 records but Table B is a monster at 3.2 million records...Table C is blank for moment

My problem. Well, I've run it twice and it seems my computer slooows down to a crawl. First time I let it go for about 90 mins and it's currently been going for about an hour. I exited out of it first time and made some modifications to data to see if that could speed things up and doesn't seem to be doing it

I did check my SQL on limited data and that's not problem

Now my computer is pretty high end with what I think is sufficient memory 512K and I closed out of unecessary programs

Is there some kind of limitation to the number of records one can query efficiently in ACCESS? (2003

Thanks!

 

[gandalf]

You might notice a boost in speed if you place the proper
indexes.

Like
Select T1.Lastname,T2.Address FROM
NAMES T1 INNER JOIN ADDRESSES T2
ON T1.PersonID = T2.PersonID

->Place index on Names.PersonID & T2.PersonID
This way Access only needs to pull PersonID for
comparision and not the full record.

if possible place an index also on T1.LastName,T2.Address
(doesn't need an extra roundtrip to retrieve
LastName,Address after a match)

What is the actual SQL-string
and from where is your data coming from?
((local network,broadband), (Access,SQL
Server,Oracle,MySQL...))?

2.If all the data is on a remote server, try to process
the query there instead of on the local pc
(using pass-through queries, stored procedures or views)

-----Original Message-----
I'm running a query where I'm copying records that match

on table A and table B and inserting them into table C.

Now Table A is about 50,000 records but Table B is a

monster at 3.2 million records...Table C is blank for
moment.

My problem. Well, I've run it twice and it seems my

computer slooows down to a crawl. First time I let it go
for about 90 mins and it's currently been going for about
an hour. I exited out of it first time and made some
modifications to data to see if that could speed things up
and doesn't seem to be doing it.

I did check my SQL on limited data and that's not problem.

Now my computer is pretty high end with what I think is

sufficient memory 512K and I closed out of unecessary
programs.

Is there some kind of limitation to the number of records

one can query efficiently in ACCESS? (2003)

 

[Guest]

MY SQL string is

INSERT INTO Table
SELECT * FROM Table
WHERE [TableA.areacode] & [TableA.phone
IN (SELECT TableB.areacode & TableB.phone FROM TableB

That is the copy string that I am running to first move the records which I will later delet from Table A

Is their a more efficient string to do what I'm trying to do? I'm still not getting anywhere with this string...it seems like a starts off and then my computer just crawls

My Data is coming from an Access database on the hard drive. The original information is in text comma delimited format - could that be it?

Thanks!

 

[gandalf]

It should be running smootly when everything comes from
your harddrive.

Each program has its SQL optimizer, the way you write SQL
can give it addition hints for processing.

1)With this format, the optimizer can directly conclude)
that it will need the areacodes & phones fields

INSERT INTO TableC
SELECT TableA.* FROM TableA
INNER JOIN TableB ON
([TableA].[areacode] = [TableB].areacode
AND [TableA].[phone] = [TableB].phone )

assumming B the newer data

A INNER JOIN B= must be exact the same in A & B
candidates for updating

A LEFT JOIN B= must be ... in A and/or B (fill gaps
(missing in B) with NULL)
like the ones that are no longer in use
test on b.areacode IS NULL

A RIGHT JOIN B = must be ... in B and/or A
the new ones to add to A

*Avoid concatination in WHERE/JOIN/HAVING clauses
Using each field seperately instead of concatenating
allows the optimizer to use indexes (like bookmarks)
on areacode and phone
(perhaps a index with both areacode & phone fields)
Huge performance boost on large tables

2) Indexes for frequent queries
Everything that you need to glue together in a Join or
Where clause is a good candidate
Especially for a large amount of records
Add no more fields than needed

3)When in doubt try the analysis-tools in the menu extra

4)Prefer joins above where's
5)Prefer where's above having
6)Try to act on collections of records instead of
processing each record one at a time

*JET SQL differs a bit from ANSI-SQL
try the help in case my querie fails.

-----Original Message-----
MY SQL string is:

INSERT INTO TableC
SELECT * FROM TableA
WHERE [TableA.areacode] & [TableA.phone]
IN (SELECT TableB.areacode & TableB.phone FROM TableB)

That is the copy string that I am running to first move

the records which I will later delet from Table A.

Is their a more efficient string to do what I'm trying to

do? I'm still not getting anywhere with this string...it
seems like a starts off and then my computer just crawls.

My Data is coming from an Access database on the hard

drive. The original information is in text comma delimited
format - could that be it??