🚀 go-pugleaf

Thanks Bill ,
  I'll try the S.P. approach,  then lesson here I guess is use proper 
indexing and referencing
  with cascading deletes in future database design!

Jarek Bialik
Britec Computer Consulting


"Bill Todd" <no@no.com> wrote in message 
news:418fdbc7@newsgroups.borland.com...
> 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