Query takes ages to run...

[Nicolae Fieraru]

Hi All,

I have a Table1 with ID, FirstName, Surname, Address1, Address2, PostCode,
Suburb, Purchase.
The table list all purchases made by all customers.
A customer can have multiple purchases and there will be multiple records
for the same customer. The address details might not be the same (for
example Suburb might be W Gosford in one record and West Gosford in another
record)
I want to build a query which shows all the customers from the table, only
once. A customer can be considered unique if he has distinct Surname and
Address1.

I need the following fields: ID, FirstName, Surname, Address1, Address2,
Postcode, Suburb

The query is required for mailing purposes, a customer should receive only
one letter, no matter of how many purchases he made.

I created this query, which works fine for tables with a few thousand
records. But in the original table, with over 200,000 records, the query
seems to take a long time (runs longer than 30 minutes and actually I've
never seen the finished result). On a sample table with about 20,000 records
it took over 5 minutes to run.
The query actually displays records, but it doesn't show the maximum number
of records until finishes.

SELECT a.*
FROM Table1 AS a
WHERE a.ID =
(
SELECT min(b.ID)
FROM Table1 AS b
WHERE b.Address1 = a.Address1 AND b.FirstName = a.FirstName
);

Is there a better performing query than this one? I am aware that the design
of the database is not good at all, but it isn't in my power to do changes
in it (at least not now).
Regards,
Nicolae

[Salad]

Nicolae Fieraru wrote:

Hi All,

I have a Table1 with ID, FirstName, Surname, Address1, Address2, PostCode,
Suburb, Purchase.
The table list all purchases made by all customers.
A customer can have multiple purchases and there will be multiple records
for the same customer. The address details might not be the same (for
example Suburb might be W Gosford in one record and West Gosford in another
record)
I want to build a query which shows all the customers from the table, only
once. A customer can be considered unique if he has distinct Surname and
Address1.

I need the following fields: ID, FirstName, Surname, Address1, Address2,
Postcode, Suburb

The query is required for mailing purposes, a customer should receive only
one letter, no matter of how many purchases he made.

I created this query, which works fine for tables with a few thousand
records. But in the original table, with over 200,000 records, the query
seems to take a long time (runs longer than 30 minutes and actually I've
never seen the finished result). On a sample table with about 20,000 records
it took over 5 minutes to run.
The query actually displays records, but it doesn't show the maximum number
of records until finishes.

SELECT a.*
FROM Table1 AS a
WHERE a.ID =
(
SELECT min(b.ID)
FROM Table1 AS b
WHERE b.Address1 = a.Address1 AND b.FirstName = a.FirstName
);

Is there a better performing query than this one? I am aware that the design
of the database is not good at all, but it isn't in my power to do changes
in it (at least not now).
Regards,
Nicolae

Subqueries are nortoriously slow. If your Where was
WHere ID IN ("1","2","3")
it would be extrememly fast.

My suggestion is to create a second query. Select the ID, firstname.
and Address and group on them as a totals query.

Next, link that query to your current query and get rid of the subquery.

Run your current query to this new query method and verify the record
count is the same.

You might want to create an index on the first name and Address1 prior
to running the code to speed things up even more.