count in a query

[x-rays]

Hello all,

I have a simple select statement:

SELECT Count(*)
FROM Table1 a where exists (Select 1 from Table2 b where a.Country =
b.Country and a.Factory = b.Factory and a.Code = b.Code and a.Type =
b.Type);

the problem I'm facing is that this query takes too much time to run.
Table1 has 44500 rows and Table2 685000 rows.
Any ideas how to reconstruct the statement to get results?

I tried these too with no luck:

SELECT Count(*)
FROM Table1 a where Code in (Select b.Code from Table2 b where
a.Country = b.Country and a.Factory = b.Factory and a.Code = b.Code and
a.Type = b.Type);

SELECT Count(*)
FROM Table1 a
INNER JOIN Table2 b
On a.Country = b.Country and a.Factory = b.Factory and a.Code = b.Code
and a.Type = b.Type);

Thanks in advance!

 

[Michael Gramelspacher]

Did you try:

SELECT Count(*)
FROM Table1 a, Table2 b
WHERE a.Country = b.Country
AND a.Factory = b.Factory
AND a.Code = b.Code
AND a.Type = b.Type;

 

[x-rays]

I tried the statement you mentioned, but it's just keep processing &
processing & processing.......

any other ideas?

 

[Gary Walter]

I tried the statement you mentioned, but it's just keep processing &
processing & processing.......

is that what you meant earlier by "no luck"?

is it within your power to set indexes?

is this a heterogeneous join, i.e., are
both tables Access tables or is one table
in Access and other on SQL?

just curious, did you try separate group by's
and see how long do they take?

SELECT
Count(*),
Country,
Factory,
Code,
Type
FROM
table1
GROUP BY
Country,
Factory,
Code,
Type;

 

[John Spencer]

The last option should be the most efficient, although what you posted would
give a syntax error with the extra ")" at the end.. If it is too slow, then
you should look at setting indexes for the fields involved in the join -
Country, Factory, Code, and Type.

Beyond that I see no way to speed up the query.

SELECT Count(*)
FROM Table1 a
INNER JOIN Table2 b
On a.Country = b.Country and a.Factory = b.Factory and a.Code = b.Code
and a.Type = b.Type

 

[x-rays]

Hello Gary and thanks for your reply,

The fields in where clause are the primary key of Table1 and works as
index as well, in Table2 can't index these fields because values are
not unique. Both tables are Access Tables and the select statement you
mention runs in less a second.

PS: I see I have to suffer before upgrade to SQL Server

 

[x-rays]

Also, I used Distinct in table2 but time did not got better

 

[John Spencer]

You can set indexes on the individual fields. The indexes for the
individual fields do not have to be unique.

You are limited to 32 (?) indexes on any one table (including any that
Access generates for relationships you have specified between tables).

 

[Gary Walter]

you can index fields in Table2
(dups okay) but I suppose it
could be "expensive."

A man's gotta do what a man's gotta do.

I don't see anyway else to speed it up
at the moment. Work calls but I will try
to think further when have time.

 

[x-rays]

Hi Gary and John,

I created the index allowing duplicates that Gary suggested and guess
what, it runs in 3 secs tops.

Thank you very much for dealing with my issue guys.