SQL Slowdown?

I am not good enough at this yet to know where to look so any guidance would be appreciated.

I set up a project for our analytical group to enter results directly into tables within the SQL database. As of this point there are 8 indexed tables with about 20 columns each and at this point only 10 rows of data. The user uses a tabbed navigation bar to access the appropriate table for entry. Simple enough - however they complained that after opening 2 or three tables the system slowed down to a crawl. I increased project memory to 64 MB minimum and 1 GB maximum and for a day they said all was good (random event). Today they are again complaining that the system slows down after opening a few of the tables.

I tried replicating the problem but was unable to. Where should I be looking to diagnose this?

We also have 46 historical transaction groups running, each writing on average 50 records every minute - fairly light load. Plus, we have 3 easy charts displaying trends.

First, what flavor of SQL are you running?

Second, what does the Database Connection status screen show?

We are using MS SQL 2008 R2 running on a Windows server 2008 32 bit PC

Throughput is 1.6 queries/sec
Total queries is 3,036,729
Avg. duration is 0.0 sec

When they say that it slows down, do they mean that the data starts taking a long time to come in, or that it becomes slow/difficult to navigate, use the screens, etc?

What I’m getting at is: is it clear that it’s a database slowdown? Or is perhaps something in the display side slowing down? If you can catch it while its slow, take a look at the database status page while logged in, and see if you can catch any running queries. If you enable live display on the panel, you might see some queries pop in and take some time.

Basically, I would look at anything that was polling, and make sure they operations they’re polling on aren’t too heavy. With tabs, usually all of the contents are running/polling, even when not displayed- and this can catch people off guard.

Another thing to look out for (though it’s a bit hard to track down) is bound history queries firing with extra big time ranges (like, starting at 0) when the window first opens. This would likely show up under the running queries, though, as it would probably take some time to run.

If the problem seems more on the display side, take a look at the CPU and memory usage over all. If the system is low on memory in general, it might be swapping to disk, which can make things a bit sluggish.

Hope this helps a bit,

I have not seen the slow down but based on the description given to me…
After filling in a number of tables, the data entry step will slow down. As you know from working with the table, whenever the user presses enter, there is a cell edit event. Mine is a bit more complicated in that I do an Information_Schema query to determine the data type being written and then use that info plus some bounds info that I have in a dataset to determine if the data is acceptable. If the data is acceptable it is written to the table if not, it is rejected.

I am wondering if the Information_Schema query could be loading it down. As I have been working on this I found I had one other Information_Schema query that was running on a relative poll for each table. I have fixed that but I have no data yet to tell me if that helped.

I have been watching the Database Connections status screen on the gateway and see nothing unusual except every so often (once every couple minutes) it will flash up the text of an update query. Should I be able to see the text of a query under Active Queries? I am assuming the transaction history uses an update query to write data and that is what I am seeing.

Looking at the Gateway status page, it is using 1% of the cpu and 167 mb of ram. I am still using Java 1.6.0_26, is that okay?

My console has some errors with regards to one PLC, something in regards to Key equals 0: length=378. I will do a search on that.

The Event script I use:

[i]table = event.source.parent.TableName
worktable = event.source
pk = event.source.parent.PrimaryKey
colName = event.source.data.getColumnName(event.column)
data = event.source.data
att_tabledata = event.source.Attributes
att_rowcount = att_tabledata.rowCount

determining the data type of the field being edited.

query = “Select DATA_TYPE FROM [Ignition].[INFORMATION_SCHEMA].[COLUMNS] Where TABLE_NAME = ‘%s’ AND COLUMN_NAME = ‘%s’”% (table, colName)
datatype = system.db.runScalarQuery(query)

we don’t have limits for strings and if the value is -1000 we jump to below

if datatype <> ‘varchar’ and event.newValue <> -1000:
for x in range(att_rowcount):
rowName = att_tabledata.getValueAt(x,“Variable”)
if rowName == colName:
# aquire the values needed for boundary checking
minVal = att_tabledata.getValueAt(x,“Minimum”)
maxVal = att_tabledata.getValueAt(x,“Maximum”)
unit = att_tabledata.getValueAt(x,“Unit”)

# if the entered value is outside the boundaries reject the entry and popup a message
if event.newValue < minVal or event.newValue > maxVal:
	newVal = event.oldValue
	system.gui.messageBox("Valid range is %g to %g (%s)"% (minVal, maxVal, unit))
	system.db.refresh(worktable, "data")
	newVal = event.newValue

if the value is -1000, set the field back to Null

elif event.newValue == -1000 or event.newValue == ‘-1000’:
newVal = None
system.db.refresh(worktable, “data”)
newVal = event.newValue

write the data to the database

Handle the case where there are multiple primary keys

pks = [x.strip() for x in pk.split(",")]
for key in pks:
where += key + "=? AND "

remove trailing AND

where = where[:-5]

query = “UPDATE %s SET %s=? WHERE %s”% (table, colName, where)

params = [newVal]
for key in pks:
params.append(event.source.data.getValueAt(event.row, key))

system.db.runPrepStmt(query, params)
system.db.refresh(worktable, “data”)[/i]

If you load up SQL Management Studio, there is a performance dashboard that will show you things like long running queries and frequently ran queries… I’d start there.

I did not find the performance dashboard you spoke of but I did run the profiler while the user was entering data. I than ran that profile data through the ‘Database Engine Tuning Advisor’ and it found nothing wrong. It only made minor recommendations.

I have made a lot of improvements to this project in regards to not running SQL queries frivolously, but I still am getting slow down complaints.

From your description, it is not clear if the slowdown is taking place when more than one client is entering data simultaneously. Locking/blocking or spinlocking issues can slow down the db with symptoms as you describe. Check this link out: msdn.microsoft.com/en-us/library/ms175518.aspx

Yup, this is what I was talking about. Sorry for any confusion.

I found what you were referring to and what I think ‘gbuehler’ was referring to as performance monitor. I went through the activity monitor and did not find any locking or blocking issues or anything indicating a problem.

The biggest consumer of resources seems to be Ignition housekeeping deleting old files.

The data tables being used by this project are only being used by one person, so I don’t expect locking to be a problem.

The Ignition gateway is using JAVA 1.6_26, would it matter if the user is using a different version, 1.6_31?

Have you tried wrapping this code in system.util.invokeAsynchronous()?

As this appears to be a client side issue, is there a possibility that multiple window instances of the same window are opening as you navigate from tab to tab? Are you checking to see if an instance of window is already open when navigating?

No I haven’t tried system.util.invokeAsynchronous(). What is that supposed to do?

As I navigate from tab to tab, the windows are swapped out. I’ve checked from the windows menu and only one window is open at a time in addition to the tab strip.

I think part of the problem has something to do with designer. One of the windows in designer was a test window that I had developed early on in the project that had some pretty aggressive queries. When I opened that window it started running those queries and even when I closed the window and deleted it, the Information Schema query did not stop. (They were showing up in the SQL Activity Monitor.) I have been leaving designer open on this project for days since I have been trying to figure out what is wrong and I believe that these queries have been causing the problem. However, after shutting down designer and reopening it, I just saw one of those phantom queries. I think I need to clear my cache.


Sorry for asking about invokeAsynchronous. Of course I didn’t look in the manual :blush: . I may give it a try.

I did try out invokeAsynchronous but it didn’t help.

I did get the following piece of information from the user:
“We are still experiencing the slow down about the third tab of data we
enter. We have found that if we open the task manager while ignition is
still running when it slows down, it will speed back up. We don’t have
to close anything else, we just open the task manager…I’m not sure
what is going on and if they are really related, but it seems to be a
repeatable occurrence.”

Any ideas???

The task manager thing seems very hard to believe.

You really need to narrow down exactly what becomes slow before we can proceed. Here are some ideas:

  1. If you edit your database connection configuration in Ignition, you’ll see a parameter called the “Slow Query Log Threshold”. Make this smaller, something like 1 second (1,000). That way the Gateway will log a warning to the Gateway’s log console if any query takes more than a second. This can help you find queries that are not performing well.

  2. Time your script. You can check and see exactly how long your script is taking to run, like so:

[code]from java.lang import System
startTime = System.currentTimeMillis()

… do your stuff here …

endTime = System.currentTimeMillis()
print “The cell edit event took %d millis” % (endTime-startTime)[/code]

And then look in the client’s console to find the amount of time (in milliseconds) that the script took. If the script is fast, then the “slowdown” is due to something else.

I have made the changes you suggested.

How do I look into the clients console?

The console in the client is under Help > Diagnostics from the file menu. From there select the console tab.

I am remotely administering this system so it is not so easy for me to check the users console via the method you specified. Is there another way to check the console - perhaps a log file?

Couple dumb questions from me:

Is the Ignition gateway installed on the same machine as the db server or another?
If another, How are they connected to each other?

For all machines involved, are they physical? virtual?

If SQL and Ignition are on the same machine, Is SQL set to consume as much ram as it likes?