Howdy all.

Doing some testing on a query that is part of a form to purge old records...

Seems the sql query below takes approx 20 mins to delete 473 records. In the
test form, I hardcoded the table name and association number (~strAssn'). I
also used a tcursor pointed at the table (UnitMsgs) and switched to the
secondary index of UnitNumFK thinking this might help... Checking via
TaskManager, the cpu is pegged at 99%.

DELETE FROM ~tblName
WHERE UnitNumFK IN (SELECT U.UnitNumPK
FROM
:HaiData:UnitMast U
WHERE
U.AssnNumFK = '~strAssn')

The Pdox qbe, on the other hand, deleted the 473 records in approx 15 secs.

Query

~strTblName | UnitNumFK |
Delete | _t1 |

:HAIData:UNITMAST | UnitNumPK | AssnNumFK |

EndQuery

I stayed with the QBE while all other queries were SQL. Could the problem be
related to the UnitMsgs table having a 3 part index (UnitNumFK (A6), MsgDate
(Date), MsgNum (autoincrement)?

Appreciate your comments/suggestions. As this form may later get included
into the SQL Svr conversion project, it'll be a mute point but currently its
got me puzzled...

Thanks,
Rey

Rey wrote:

3 part index or 3 part primary key?

The LocalSQL DELETE WHERE IN (..) syntax is a little unwieldy and doesn't
offer the processing shortcuts of other platforms. I am a little startled
by an 80x difference between QBE and SQL. How many total records in each
table, and how many unique UnitNumFKs were deleted?

I wouldn't think selecting an index on a TCursor would help.

--
Larry DiGiovanni
Digico, Inc.
IT Consulting and Staffing Solutions
www.digicoinc.com
Check out www.thedbcommunity.com for Paradox resources.

Howdy Larry.
Thanks fore replying.

Should have stated that UnitMsgs table has a composite key composed of
UnitNumFK, MsgDate and MsgNum.

UnitMsgs table had 45,674 records. For this test, the deleted table has 537
records; when I set its primary key to UnitNumFK, the KeyViol table contains
331 records.

Rey

Rey wrote:

Deleting 537 rows from a 45674 row table via a subselect takes 20 minutes?
It would make an interesting filemon comparison between QBE and SQL.

--
Larry DiGiovanni
Digico, Inc.
IT Consulting and Staffing Solutions
www.digicoinc.com
Check out www.thedbcommunity.com for Paradox resources.