This may be asking a bit much given the sizes of the files.  The
following query is run where both tables are indexed on all of the
fields involved (each field individually), with each table containing
about 1M records, about 300M each.

I'm wondering if there are any obvious things I should check to speed it up?

TIA


DELETE FROM IMPORT JOIN MLIST ON
  MLIST."Last Name" = IMPORT."Last Name" AND
  MLIST."First Name" = IMPORT."First Name" AND
  MLIST."Middle Name" = IMPORT."Middle Name" AND
  MLIST."Street Name" = IMPORT."Street Name" AND
  MLIST."House No" = IMPORT."House No" AND
  MLIST."Apt No" = IMPORT."Apt No"
WHERE
  MLIST."Last Name" = IMPORT."Last Name" AND
  MLIST."First Name" = IMPORT."First Name" AND
  MLIST."Middle Name" = IMPORT."Middle Name" AND
  MLIST."Street Name" = IMPORT."Street Name" AND
  MLIST."House No" = IMPORT."House No" AND
  MLIST."Apt No" = IMPORT."Apt No";

Do you actually need the where clause ? I think the join should do it all
for you. Dont know if it will make it any faster though.


"David Ray" <david@timecalc.com> wrote in message
news:AE40D121-B102-47B3-913E-BE32F29FA293@news.elevatesoft.com...
> This may be asking a bit much given the sizes of the files.  The following
> query is run where both tables are indexed on all of the fields involved
> (each field individually), with each table containing about 1M records,
> about 300M each.
>
> I'm wondering if there are any obvious things I should check to speed it
> up?
>
> TIA
>
>
> DELETE FROM IMPORT JOIN MLIST ON
>   MLIST."Last Name" = IMPORT."Last Name" AND
>   MLIST."First Name" = IMPORT."First Name" AND
>   MLIST."Middle Name" = IMPORT."Middle Name" AND
>   MLIST."Street Name" = IMPORT."Street Name" AND
>   MLIST."House No" = IMPORT."House No" AND
>   MLIST."Apt No" = IMPORT."Apt No"
> WHERE
>   MLIST."Last Name" = IMPORT."Last Name" AND
>   MLIST."First Name" = IMPORT."First Name" AND
>   MLIST."Middle Name" = IMPORT."Middle Name" AND
>   MLIST."Street Name" = IMPORT."Street Name" AND
>   MLIST."House No" = IMPORT."House No" AND
>   MLIST."Apt No" = IMPORT."Apt No";

"David Ray" <david@timecalc.com> wrote in message
news:AE40D121-B102-47B3-913E-BE32F29FA293@news.elevatesoft.com...
> This may be asking a bit much given the sizes of the files.  The following
> query is run where both tables are indexed on all of the fields involved
> (each field individually), with each table containing about 1M records,
> about 300M each.
>
> I'm wondering if there are any obvious things I should check to speed it
> up?
>

Try getting rid of all the joins except for the first, or maybe the first
two (if you're patient enough, get rid of joins one at a time starting from
the lowest, and try it out). Past a certain misterious point, it is much
better to let it scan all the matching first names using the WHERE clause
than use indexes.

Robert

> TIA
>
>
> DELETE FROM IMPORT JOIN MLIST ON
>   MLIST."Last Name" = IMPORT."Last Name" AND
>   MLIST."First Name" = IMPORT."First Name" AND
>   MLIST."Middle Name" = IMPORT."Middle Name" AND
>   MLIST."Street Name" = IMPORT."Street Name" AND
>   MLIST."House No" = IMPORT."House No" AND
>   MLIST."Apt No" = IMPORT."Apt No"
> WHERE
>   MLIST."Last Name" = IMPORT."Last Name" AND
>   MLIST."First Name" = IMPORT."First Name" AND
>   MLIST."Middle Name" = IMPORT."Middle Name" AND
>   MLIST."Street Name" = IMPORT."Street Name" AND
>   MLIST."House No" = IMPORT."House No" AND
>   MLIST."Apt No" = IMPORT."Apt No";

David,

<< I'm wondering if there are any obvious things I should check to speed it
up? >>

What does the query plan say about the execution of the query ?

--
Tim Young
Elevate Software
www.elevatesoft.com