Re: query optimization

In article <871578594.31196_at_dejanews.com>,   Brenda Muller <muller.brenda_at_primestar.tci.com> wrote:
>
> In article <33F23ACA.1359F04B_at_ucdavis.edu>,
> kakluge_at_ucdavis.edu wrote:
> >
> > I have one table (A) with ~30,000 rows in it and a second table (B) with
> > ~85,000 rows in it. Each table contains an ID column, and each table in
> > indexed on that ID column (these are not primary key type ID's; there
> > are duplicates, so the indexes are not unique). I want to delete from
> > table A all rows where the ID does not exist in table B. When I'm done,
> > I've deleted ~22,000 rows (>70% of the rows) from table A. The problem
> > is the query takes most of the day to run, so I'd like to optimize it.
> > Because this is a batch deletion I want best throughput rather than best
> > response time. As far as I know we don't generate analysis statistics,
> > so I believe this means we're u
>

I don't know what happened to my other post. It somehow got lost during the DejaNews post process. What I basically said was the following:

It would be helpful if you posted the query that you're having problems with. Generally speaking, you should use an existence join ("exists, "not exists") rather than "not in", and you might want to consider creating a new table with the values that exist in both table A and table B, and then replacing table A with the new table, rather than deleting stuff from table A. This will definitely be faster if it's an option, and will maintain your table structure better over a period of time.

Brenda

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet