SQL Delete freezing

[sal88]

Hi all

I have a delete SQL command that freezes FM for around 40 seconds, before succesfully executing the command. The table in question has 30,000 records. The command is:

"DELETE FROM Originals WHERE Case_IDF IS NULL AND LOWER (Receive_Subject) LIKE LOWER ('%" & Settings::Case Filter Spark & "%')"

Have I done anything wrong here?

[Kris M]

This is a fairly processing intensive SQL call as you are asking to transform 2 cols of 30,000 rows with LOWER and filtering the result with IS NULL and LIKE.

I would shift the LOWER to FM by making Originals::Receive_Subject and Settings::Case Filter Spark calculations that produce lower as a result.

PS SQL does not always like cols with spaces in the names so i would change Settings::Case Filter Spark to Settings::Case_Filter_Spark.

Edited February 9, 2016 by Kris M

[sal88]

Thanks Kris I will try that.