🚀 go-pugleaf

The problem is that the subquery has to scan the entire table
sequentially without using an index and the delete has to scan the 3500
rows returned by the subquery for each row in the table you are
deleting from.

One thing that will help is to use a stored procedure with a FOR SELECT
to replace the subquery and the DELETE in the DO clause of the FOR
SELECT. Make sure WipNo is indexed.

For even better performance convert the inqDate column to type DATE or
add a shadow column that stores the date as type DATE and update the
shadow value automatically with a trigger.

-- 
Bill (TeamB)
TeamB cannot answer questions received via email

Jarek Bialik wrote:

> Hi Bill, thanks for your response,
> 
>  Unfortunately that "date" column is actually stored as Varchar(10),
> so   it needs a type cast. The Select will return about ~3500 rows.
> 
>  So does the subquery execute for every row in the delete query?
>  Shouldnt the select sub query execute only once?
> 
> Thanks
> Jarek Bialik
> Britec Computer Consultanting
> "Bill Todd" <no@no.com> wrote in message 
> news:418c1939$1@newsgroups.borland.com...
> > Why do you need to cast the value from the inqDate column? What is
> > its data type?
> > 
> > How many rows does the SELECT return?
> > 
> > -- 
> > Bill (TeamB)
> > TeamB cannot answer questions received via email