I have been working on a new web site for monitoring and managing computers. Each management agent links up with other agents in the local network and the web site gets a constant streaming flow of computer state information. So, this flow of information about where each computer is on the network, what it's power state, etc.  being stored in a SQL database on the web server. You can then log in and see what is going on. In my lab in Oregon, I have been working on adding more computers to my mesh and testing the scalability of the entire system. I now have 20 computers in my lab and more at home and at other friends houses.

With all the computers on the mesh, the web site gets a lot of data and optimizing SQL input and retrieval is a high priority. As I got over 30 computers in the mesh, I started getting SQL failures. Some of the requests started deadlocking and causing lots of problems. Well, first off, I need to overall the way data is being stored in the database. Right now, the 30 computers are generating about 40 SQL stored procedure execution per second. This is massive and not needed. I will be fixing this in the next few days and weeks. Still, before I fix the underlying problem, I figured I would use this opportunity to learn and optimize the SQL queries I am already doing. Assuming I keep the 40 executions per seconds, what can I do to speed things up?

• First, I have a few queried that use JOIN, and the relation tables did not have indexes on them. Found how to create an index and boom! These queries where significantly faster.


• Second, I had a few stored procedures that needed to get data from just one row of a select call.  Since I did not know how to do this, I used an example using a database cursor. This is overkill, you can do that same with "SELECT @name = name FROM table...". This seems to have speed up things.


• Third, last night I found the magical "SET NOCOUNT ON" which speeds up the queries by not returning the "rows affected". From a few web site I have seen, this alone is a significant speed boost. I added this at the top of all my stored procedures.

After this and a few other tweaks, I got the database to keep up very well. Below is a screen shot of a page from the web site. I use two queries to the database to get all nodes and all links between nodes. I then create a text file that I feed to Graphviz to generate a PNG (or SNG when supported). Rendering this graph is pretty hard work for the database when about 40 SQL updates occur every second. You see my Oregon lab in the big sphere, then my house, another lab in Oregon and a lab in Paris in the smaller groups.

Ylian
opentools.homeip.net