# SQL optimization

**URL:** <https://forum.inductiveautomation.com/t/sql-optimization/8145>\
**Category:** Ignition\
**Created:** [March 12, 2014, 4:34pm UTC](https://forum.inductiveautomation.com/t/sql-optimization/8145 "2014-03-12T16:34:48Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Tim](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tim/32/3361_2.png) [@Tim](https://forum.inductiveautomation.com/u/Tim)\
**Post date:** [March 12, 2014, 4:34pm UTC](https://forum.inductiveautomation.com/t/sql-optimization/8145/1 "2014-03-12T16:34:48Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![adamaustin](https://avatars.discourse-cdn.com/v4/letter/a/5f8ce5/32.png) [@adamaustin](https://forum.inductiveautomation.com/u/adamaustin)\
**Post date:** [March 12, 2014, 5:09pm UTC](https://forum.inductiveautomation.com/t/sql-optimization/8145/2 "2014-03-12T17:09:31Z")

</div>

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

```auto
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])
```