SQL optimization

Ignition
1

I intermittently (25% of the time) receive the following error when executing a SQL query, the remaining time the desired results are achieved. I am trying to set the sort order for a new set of records after they have been inserted. I assume the error is the result of the unoptimized SQL query:

[code]sql = “SELECT count(*) FROM tblOMS WHERE SortOrder IS NULL AND WCID = ‘%s’” %(wcid)
count = system.db.runScalarQuery(sql, “AX_MES”)

sql = “WHILE ? > 0 BEGIN UPDATE TOP (1) tblOMS SET SortOrder=(SELECT ISNULL(MAX(SortOrder),0) + 1 FROM tblOMS WHERE WCID = ?) WHERE WCID=? AND SortOrder IS NULL SET ?=?-1 END”
system.db.runPrepUpdate(sql, [count, wcid, wcid, count, count])[/code]

Traceback (most recent call last):

File “event:actionPerformed”, line 43, in

java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(WHILE ? > 0 BEGIN UPDATE TOP (1) tblOMS SET SortOrder=(SELECT ISNULL(MAX(SortOrder),0) + 1 FROM tblOMS WHERE WCID = ?) WHERE WCID=? AND SortOrder IS NULL SET ?=?-1 END, [1, 11, 11, 1, 1], , , false, false)

caused by Exception: Error executing system.db.runPrepUpdate(WHILE ? > 0 BEGIN UPDATE TOP (1) tblOMS SET SortOrder=(SELECT ISNULL(MAX(SortOrder),0) + 1 FROM tblOMS WHERE WCID = ?) WHERE WCID=? AND SortOrder IS NULL SET ?=?-1 END, [1, 11, 11, 1, 1], , , false, false)
caused by GatewayException: Read timed out
caused by SocketTimeoutException: Read timed out

Ignition v7.6.4 (b2013112117)
Java: Sun Microsystems Inc. 1.6.0_31

2

Looks like an infinite loop to me. Try this instead:

sql = """DECLARE @mycount INT
SET @mycount = ?
WHILE @mycount > 0
BEGIN
  UPDATE TOP (1) tblOMS SET SortOrder=(SELECT ISNULL(MAX(SortOrder),0) + 1 FROM tblOMS WHERE WCID = ?) WHERE WCID=? AND SortOrder IS NULL
  SET @mycount = @mycount - 1
END"""
system.db.runPrepUpdate(sql, [count, wcid, wcid])