query speed / table design

[Jim]

I have two HUGE tables that don't have keys but are indexed (Yes
(Duplicates OK)) on the fields I use for the join and select. There's
no keys because these are imported tables and I'm only doing reports off
the tables. No maintenance or updates will be done to the them. If I
run queries against the individual tables they scream (go fast). If I
join the two and query, they run forever. Its like they're doing a
table scan - looking for each record.

The two tables are in separate db's. I'm running A2K on Win XP Pro.

Screams:
SELECT clmhdr.chclno, clmhdr.chwkno, clmhdr.chinfr
FROM clmhdr
WHERE (((clmhdr.chclno)=4) AND ((clmhdr.chwkno)=1));

Screams:
SELECT clmdet.cdclno, clmdet.cdwkno, clmdet.cdlnno
FROM clmdet
WHERE (((clmdet.cdclno)=4) AND ((clmdet.cdwkno)=1));

Dies:
SELECT clmhdr.chclno, clmhdr.chwkno, clmhdr.chinfr, clmdet.cdlnno
FROM clmhdr INNER JOIN clmdet ON (clmhdr.chwkno = clmdet.cdwkno) AND
(clmhdr.chclno = clmdet.cdclno)
WHERE (((clmhdr.chclno)=4) AND ((clmhdr.chwkno)=1));


TIA
Jim

 

[Lynn Trapp]

Is there some reason that you need the two tables in separate databases?
Have you tried removing the WHERE clause from your problem query?

 

[Jim]

The tables are too big to put all in one db.

I can take the WHERE clause out but I don't want to see all the rows.

 

[John Spencer]

I suspect that the problem is that the two tables are in separate databases.
If you can experiment, perhaps you can import both tables into one database
and see if this improves performance. Another thing you might try is to
join your two screaming queries together instead of joining the tables. You
might even be able to do this all in one query. See UNTESTED sample query
below.

SELECT A.*, B.cdlnno
(SELECT clmhdr.chclno, clmhdr.chwkno, clmhdr.chinfr, chwkno
FROM clmhdr
WHERE clmhdr.chclno=4 AND clmhdr.chwkno=1) as A
INNER JOIN
(SELECT clmdet.cdclno, clmdet.cdwkno, clmdet.cdlnno, cdwkno
FROM clmdet
WHERE clmdet.cdclno=4 AND clmdet.cdwkno=1) as B
ON A.chwkno = B.cdwkno AND A.chclno = B.cdclno

SInce HUGE can mean different things to different people. What is the size
of the two databases?

 

[Jim]

The clmhdr table in a db by itself is 455,640kb. The clmdet table was
too big to put into one db so I have 3: 1,574,736kb, 907,864kb, and
382,992kb. I could combine the last two but I'd like to get past this
problem first.

I'm querying the clmhdr against the 907,864kb table.

 

[Jim]

John,

That did it!

Here is the TESTED version:

SELECT A.*, B.cdlnno
FROM (SELECT clmhdr.chclno, clmhdr.chwkno, clmhdr.chinfr
FROM clmhdr
WHERE clmhdr.chclno=4 AND clmhdr.chwkno=1) as A
INNER JOIN
(SELECT clmdet.cdclno, clmdet.cdwkno, clmdet.cdlnno
FROM clmdet
WHERE clmdet.cdclno=4 AND clmdet.cdwkno=1) as B
ON A.chwkno = B.cdwkno AND A.chclno = B.cdclno;

I'm still working on it and I'll let you know how it turns out.

thanks
Jim

 

[Dale Fye]

JIm,

YOu might want to look into downloading SQL Server 2005 Express from the
Microsoft download sight. It has a 4GB limit for each db, so you could get
both of these tables into a single db, then link to that database with
Access. This would work if your two tables are at the upper end of their
limit of growth, but if they are going to continue to grow, this might not
be the best solution either.

Dale