[WD22] Delete Query Speed

Hello,

1st post on the new forum, thanks Guenter for setting it up

I have a simple delete query:

DELETE FROM
ORDER_TABLE
WHERE
ORDER_TABLE.saledate2 < {Paramsaledate2}


the table has around 150k records which would be removed my the above, my problem is how long its taking to remove these records it been running for hours and only about 30K records have gone so far, this cant be normal? I reindexed the datafile first, i have a few other datafiles i have to run similar queries on and cant be having a repeat of this!

thanks

ISO

check the links to this file in your analisis, do you have Referential integrity to delete records in other files?

Regards

Paulo Oliveira



Edited 1 time(s). Last edit at 09/18/2018 12:31PM by pao.

Hi Paulo,

Thanks for your reply, there are no links.

The query has been running 6 hours

Number of deleted records: 77,914

Hi,

What is the data file indexed on? Does the date have an index? It might be doing a table-scan for each delete opertation...

Regards,
Darren.

Hi Darren

The file is indexed on saledate2 which is used in the WHERE part of the query.

Would this slow it down?

Hi,

I suppose the couse could be any number of things, like;
- System resources such as memory.
- Is it C/S or Classic?
- Network speed/traffic.

...and the Performance Profiler might not be any use either in this circumstance.

Have you carried out any investigations ...like how long does it take to do a sinlge Delete? How long does it take to do a Delete within a date range? Can you use the HDelete statement instead? Just some ideas.

Personally, I only use SQL for SELECTing; so for ALL inserts, updates and deletes, I use H commands.

Regards,
Darren.

Hi Darren

Its C/S, Its also the same with Hdelete.

I have to run the queries on the Clients Server, maybe its just a case of my local system setup, i ll see how it goes on theirs, thanks for your help and advice, i ll let you know how it goes.

Thanks

ISO

ISO,

I gave it a try on one of my databases.
Deleted 80k records from a table with +1M records in about 90 seconds, based on the key field ot the table
Deleted 20k records from a table with +1M records in about 20 seconds, based on a NON indexed field
Deleted 140k records from the SAME table with +1M records took about 10 MINUTES, also based on that same NON indexed field

I also saw the memory consumption went to the maximum, during that last action. Maximum is 2Gb in my case as defined as the cache-value for this database. You can control that using the HFSQL control centre. Makes sense, if there is no more memory to work with, it slows down things, switching data in and out memory

So maybe the answer is to increase the cache size of you database. but that may still be not sufficient with these amount of records to be deleted.
If not you can try to delete in parts. Let's say per "saledate2". I guess that will be less records to delete within one transaction. Maybe the memory consumption and therefore the speed will better match your server.

I did also this btw on my local laptop having a SSD drive.

Arie

Hi ISO,

Since you are on C/S check from the HFSQL control center to see if it is running in a Transaction ...
Also if this code/functionality is important to you, it is worth doing the following:
- move your data to a Classic installation
- run the same code against your classic data
If you see a HUGE difference in time, something is WRONG with your C/S Engine.

But, I don't think the "times" you are reporting are normal ..


Regards
Steven Sitas

ISO,
How do you KNOW the number of records that have been deleted at any one time? Are you using a counter on screen that decrements for every record deleted? Or a progress bar? Trace?
From prior tests I have found that anytime you DISPLAY something on the screen it adds time to the procedure. LOTS of time. If you are displaying something, don't. Comment out the code and maybe display a toast that says "Deleting 151,246 records... please stand by" and then display another toast that says "Dirty deleting done".
HTH, Art.

Hi

its definitely something to do with my setup i ran the same queries on the Clients Server and it was done in minutes similar times to Arie! My system has a much higher spec than the clients server, i did notice the cache of the indexes on my server was set to 30%, other than that cant see any difference.

Art, i was just refreshing the datafile in the control center to see the number of records.
Steven, No transaction.

not sure whats going on, think ill try installing version 22

thanks everyone for your help

cheers

ISO

Hello ISO,

I am having a similar issue with an update query. It is only 178 records when I run it from a remote office it will take 20 - 30 minutes if I login thru remote desktop and run it inside the network it takes 23ms. My guess the firewall is the cause.

As far as connection speed it is very fast on all ends. I also tried the same test from 3 different locations so my issue seams to be at the main office where the server resides.

DW