make this quicker ? 2

[foxup]

Hi,

I have a very simple query:

DELETE FROM REMTRAN WHERE remtran.custno NOT in (sele custno FROM client)

Just some quick details:

REMTRAN has 2.5 million records
CLIENT has 56,000

Geez, it's slow, and quicker way????

Please help.


Thanks,
FOXUP!

 

[newtofoxpro]

Your logic can also be used with DELETE FOR !seek(remtran.custno,'client') and will take the same time."

I agree with this.

blank & Recycle : Is one of the way. I should decide where I should use this and take maximum benefits.

DELE & PACK OR DELE & NOT PACK : Is another way. I should decide where I should use this and take maximum benefits.

this is the flexibility & beauty of the foxpro.

 

[Olaf Doschke]

I agree, newtofoxpro,

You can always decide what has the maximum benefits, yes.

One thing bothers me a little: You indirectly suggest recycling is only possible, when using BLANK. You can also recycle deleted records, if not packing, via RECALL. AND you have the advantage they don't get in the way while they are in the deleted sate.

In a case you reuse the balnked records in the next moment, yes, that makes sense. It's a waste of time to delete, recall and then overwrite, that's really true. But it's surely not the general case.

Bye, Olaf.

 

[foxup]

WoW!!

Thanks for all the input guys. It's really very much appreciated. OK, so I see I've hit a flaw of the SQL engine. The rushmore optimisation fails on the SQL delete.

OK, so will this:

SELECT Client
SET ORDER TO CustNo
SELECT RemTran
SET RELATION TO CustNo INTO Client
DELETE FOR EOF("Client")


be teh same equivailancy?? Please let me know.


Thanks,
FOXUP!

 

[Mike Lewis]

DELETE FOR EOF("Client") is not SQL Delete. It's native (also called xBase) Delete.

In any case, I wouldn't expect to see Rushmore involved with this particular expression, as it's not based on a field in a table (I could be wrong about that).

If I've understood this discussion correctly, it's the SET RELATION that's important from the Rushmore point of view.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Assuming your index tag names are matching the field names, yes, that would be one equicvalent way to deleting.

Regarding the flaw, it's reproducable to me using the tables as is, but not an fresh copied and reindexed data, so it's hard to hand out code creating a repro. My guess is that it all depends on the index. An index can be correct and still not work best for seeking values, that's because of the nature of an index being a tree, updates causing a less ideal but still not wrong index, than creating it freshly in one run. That's also a reason to reindex once in a while.

As the select sql was fast in any case, there must be some flaw anyway, in how delete-sql makes different use of the index. You could maybe profit from the select-sql being fast, doing something like selecting all the IDs of the records, which should be deleted and then deleting them by seeking to these IDs or via inner joining to this result. But you'd end up with an even more complex and maintainance unfreindly select.

I'd actually keep your original delete as a comment, for docuemting how this xbase code can be done with sql. That'll make it easier to understand and migrate to some sql backend later.

Bye, Olaf.

 

[foxup]

Amazing !!

This code is soooooooooooooooo much quicker. Night and Day!

Thanks all!


FOXUP!!

 

[TamarGranor]

Just seeing all this now. Seems to me that the issue with SQL-DELETE isn't an optimization flaw. It's that DELETE requires actually writing to disk for each record to be deleted. That is, my suspicion is that figuring out which records to delete is fast, but marking them all for deletion is slow.

Tamar

 

[Olaf Doschke]

Tamar,

you may be on the right track, but I actually used a method of inspection that showed the delete scans through all records of the larger table in a constant slow speed:

DELETE ... WHERE sqldebug() AND ...

With an sqldebug function like

Function sqldebug()
insert into curTiming values (seconds(), recno())
Return .T.

The cursor curTiming is prepared to hold a double float and an int of course. There is no peak or slowness on the set of records to be deleted.

And this was just one of many tests. You can also SET STEP ON in the function and inspect what foxpro does during the Delete sql operation, what aliases are open etc.

I am still puzzled by this. As copying the same data to new tables made the same delete fast, it could be some index issue. but that doesn't explain, why the select was fast anyway, even on the somehow flawed, worn, old cdx.

It is reproducable to me, but I can't hand out that data to anyone, it's userspecific data.

Bye, Olaf.