Any way I was working on improving query performance on a server when I noticed some intermittent slow down. I traced the performance issue to a task which performs a huge burst of write operations. The write operations were so heavy that the rest of the server was experiencing slow down.
Obviously the code is under review to see if the amount of data being written is necessary, but I was curious about optimising the write operation itself. With read operations I have noticed that dynamic SQL is slower than compiled SQL which is in turn slower than direct global access, in certain circumstances this performance difference can be 10-100 times difference. I wanted to determine if this is the case with write operations as well.
I thought a simple test should be sufficient, just write to the database 10,000 times 3 small strings.
Test 1 Dynamic SQL
For better code reuse and readability it is sometimes superior to use dynamic sql, however, there is often quite a performance penalty. I assumed this would be the slowest method and it did not disappoint. My laptop managed to complete the operation in 2.173 seconds.
Test 2 Compiled SQL
If possible use compiled SQL over the dynamic variety, not only is the syntax highlighting of benefit, the performance gain is substantial. The test ran through in 0.238 seconds, nearly a 10 times performance improvement and the code is just as readable. The only downside of compiled SQL is that occasionally in a production environment I have found that queries can stop performing correctly and you need to clear the cached queries, this is relatively uncommon though.
Test 3 Direct Global Write
Unsurprisingly writing globals was dramatically faster at 0.042 seconds. Writing directly to globals has many issues in terms of code re-use and clarity, additionally it requires a lot of extra code around it to make sure the data matches the requirements for the fields, and its not exactly ACID compliant without substantial work. That being said if the performance is insufficient for the task and hardware upgrade is out of the question it can become necessary to use global access.
Avoid writing dynamic SQL if you can, compiled SQL will really boost the performance and without any real cost. If you have to move lots of data about really quickly and are happy to deal with the limitations then globals can really help get that application performing well.