In Cluse in Query

[rishi_375]

Hi,
I have total 2.5 billon rows in the 'Units' table and 1 billion rows in the 'Sales' table. when i am use below query then access is hang..

strquery = "select count(*) from Sales where bym in (SELECT distinct BYM FROM Units)

What is problem?

It is problem with In clause or what?
Thanks.

 

[a.sinatra]

Have you tried splitting it up...

Count(Sales), Count(Units)
________
YAMAHA ARTIST SPECIFICATIONS

 

[rishi_375]

hi,
I don't want to split this two table.
If possible with split how to do it please explain me more detail.
Thanks.

 

[Pat Hartman]

A billion is an impressive number of rows. Are you sure you don't mean million? In any event, Jet does not always properly optimize coorelated subselects. Use a join instead.

strquery = "select count(*) from Sales Inner Join Units On Sales.bym = Units.BYM;