quicker query method?

[Justin Koivisto]

In PHP, I do the following (pseudo code for clarity)...

Query: SELECT distinct(web_co ntacts.zip) FROM web_contacts WHERE
web_contacts.zi p <> ''
Query: SELECT DISTINCT(contac ts.zip) FROM contacts WHERE contacts.zip <> '';

Take all those results put them into an array:
$zips[$zipCode] = 0;

The zipCode is used as the index to avoid duplicates between tables...

Then I retrieve the counts of these zips:

$zips[$zipCode] = Query: SELECT COUNT(web_conta cts.zip) FROM
web_contacts WHERE web_contacts.zi p = $zipCode
$zips[$zipCode] += Query: SELECT Count(contacts. zip) FROM contacts WHERE
contacts.zip = $zipCode
$zips=arsort($z ips)

So this now gives me an array that has all the different zip codes from
2 tables as indicies, and how many total times each of these zips showed
up as the element's value ordered by most hits to least hits. From that,
you can print a "Top X Zips" report by walking the array and printing
each key and value pair until you've reached the X number you want to
report on.

Great, no problems there... However - It's never simple is it? - I now
need to do the same thing in a Microsoft Access database. I tried this:

[zip1]
SELECT distinct(web_co ntacts.zip) FROM web_contacts WHERE
web_contacts.zi p <> '' UNION ALL SELECT DISTINCT(contac ts.zip) FROM
contacts WHERE contacts.zip <> '';

[zip2]
SELECT DISTINCT (zip1.zip) AS zip FROM zip1;

[zip3]
SELECT zip2.zip as zipCode, (SELECT COUNT(web_conta cts.zip) FROM
web_contacts WHERE web_contacts.zi p = zip2.zip) + (SELECT
Count(contacts. zip) FROM contacts WHERE contacts.zip = zip2.zip) AS
zipCount FROM zip2

[zip4]
SELECT TOP 50 zip3.zipCode, zip3.zipCount FROM zip3 ORDER BY zip3.zipCount;

By the time I get to executing the zip3 query for testing, it takes a
few seconds to get the first part of the data, but takes nearly 4
minutes to retrieve the whole set of 6000 records...

When I run zip4, it chugs along for 12 minutes of nothing, then up pops
an error stating that the "Object invalid or no longer set."

I then changed zip3 to a make table query, and changed zip4 to select
from that table instead. This seems to work, but zip3 still takes about
4 minutes to complete. Is there a more efficient way to handle this?

There are about 8900 records in contacts.
There are about 12500 records in web_contacts.
zip1 returns 7400 results in about 2 seconds.
zip2 returns 6000 records in about 2 seconds.

The database will only grow, and it looks like the rate is on average
500 records/week...

Thanks in Advance!

--
Justin Koivisto - spam@koivi.com

http://www.koivi.com

[Salad]

Re: quicker query method?

Justin Koivisto wrote:
[color=blue]
> Great, no problems there... However - It's never simple is it? - I now
> need to do the same thing in a Microsoft Access database. I tried this:
>
> [zip1]
> SELECT distinct(web_co ntacts.zip) FROM web_contacts WHERE
> web_contacts.zi p <> '' UNION ALL SELECT DISTINCT(contac ts.zip) FROM
> contacts WHERE contacts.zip <> '';[/color]

Change this to a totals query. Drag the zip into 2 columns. Set to a
Totals query (inverted M on the menu). Set the first column to GroupBy,
and Count on the second zip code. Do this for each table and then Union
it like above witht the same filters.

SELECT zip, Count([Zip] As ZipCnt FROM web_contacts WHERE
zip <> '' Group By Zip UNION ALL
SELECT zip, Count([Zip] As ZipCnt FROM contacts WHERE
zip <> '' Group By Zip
[color=blue]
> [zip2]
> SELECT DISTINCT (zip1.zip) AS zip FROM zip1;[/color]

Change this to a totals query. This time drag both zips, the first
group by, the second sum.

Select zip, Sum(ZipCnt) As ZipSum From Zip1 Group By Zip
[color=blue]
>
> [zip3]
> SELECT zip2.zip as zipCode, (SELECT COUNT(web_conta cts.zip) FROM
> web_contacts WHERE web_contacts.zi p = zip2.zip) + (SELECT
> Count(contacts. zip) FROM contacts WHERE contacts.zip = zip2.zip) AS
> zipCount FROM zip2[/color]

get rid of this one.
[color=blue]
>
> [zip4]
> SELECT TOP 50 zip3.zipCode, zip3.zipCount FROM zip3 ORDER BY zip3.zipCount;
>[/color]

Use the results from Zip2's new Totals query. Using SubSelects is a
very slow way of doing things.

SELECT TOP 50 zipCode, zipSum FROM zip2 ORDER BY zipSum

[Justin Koivisto]

Re: quicker query method?

Salad wrote:[color=blue]
> Justin Koivisto wrote:
>[color=green]
>> Great, no problems there... However - It's never simple is it? - I now
>> need to do the same thing in a Microsoft Access database. I tried this:
>>
>> [zip1]
>> SELECT distinct(web_co ntacts.zip) FROM web_contacts WHERE
>> web_contacts.zi p <> '' UNION ALL SELECT DISTINCT(contac ts.zip) FROM
>> contacts WHERE contacts.zip <> '';[/color]
>
>
> Change this to a totals query. Drag the zip into 2 columns. Set to a
> Totals query (inverted M on the menu). Set the first column to GroupBy,
> and Count on the second zip code. Do this for each table and then Union
> it like above witht the same filters.
>
> SELECT zip, Count([Zip] As ZipCnt FROM web_contacts WHERE
> zip <> '' Group By Zip UNION ALL
> SELECT zip, Count([Zip] As ZipCnt FROM contacts WHERE
> zip <> '' Group By Zip
>[color=green]
> > [zip2][/color]
>[color=green]
>> SELECT DISTINCT (zip1.zip) AS zip FROM zip1;[/color]
>
>
> Change this to a totals query. This time drag both zips, the first
> group by, the second sum.
>
> Select zip, Sum(ZipCnt) As ZipSum From Zip1 Group By Zip
>[color=green]
>>
>> [zip3]
>> SELECT zip2.zip as zipCode, (SELECT COUNT(web_conta cts.zip) FROM
>> web_contacts WHERE web_contacts.zi p = zip2.zip) + (SELECT
>> Count(contacts. zip) FROM contacts WHERE contacts.zip = zip2.zip) AS
>> zipCount FROM zip2[/color]
>
>
> get rid of this one.
>[color=green]
>>
>> [zip4]
>> SELECT TOP 50 zip3.zipCode, zip3.zipCount FROM zip3 ORDER BY
>> zip3.zipCount;
>>[/color]
>
> Use the results from Zip2's new Totals query. Using SubSelects is a
> very slow way of doing things.
>
> SELECT TOP 50 zipCode, zipSum FROM zip2 ORDER BY zipSum[/color]

Thank you! GROUB BY is the key I needed... I really need to read up on
that again so I can remember it!

--
Justin Koivisto - spam@koivi.com

http://www.koivi.com