Intersystems Cache - Write Performance

In previous blog posts I have mentioned that Cache has good write performance, in fact in certain circumstances it is faster than many other databases. However, it is rarely the case that optimising performance is not desirable. Every one loves a performance boost, it is the most universally loved product change. The closest I have ever experienced to a complaint about a performance boost is "its so fast I didn't believe it had worked".

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.

The Test

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. 

Conclusion

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. 


Comments

Post a Comment

Popular posts from this blog

IE9 Intranet compatibility mode in Intranet websites

So you have written an HTML5 site on your local intranet with some lovely CCS3 and run it up in Firefox and you feel smug, all your HTML and CSS are perfectly formed, but you run it up in IE9 and all the CSS3 goodness has gone away leaving your lack luster IE7 version of your site.   Why is IE9 running in IE7 compatibility mode? IE9 has a hidden setting that forces it to run in compatibility mode when it encounters any intranet websites. Microsoft have detailed this behaviour in a Blog about what they call Smart compatibility mode . You can easily switch off the compatibility mode for specific machines using the internet tools mentioned in the article above, but most of the time developers do not have the luxury of applying corporate wide settings of this nature. Avoiding Smart compatibility with X-UA-Compatible Luckily there is a single line of HTML you can that you can use to override this behaviour: <meta http-equiv="X-UA-Compatible" content="IE=edge,c
Read more

User Interface Usability Checklist Part 2

So you made it through part 1 of the usability check list, how about part 2? 21. Poor Label Alignment If you have been through design 101 you have probably heard of the wonderful acronym for the basic principles of design, C.R.A.P. This of course stands for Contrast, Repetition, Alignment and Proximity. Proximity is very important when identifying which label belongs to which input. Each different form of alignment has its advantages and disadvantages. Left Alignment The label appearing on the left of a box is a common practice. This often helps with vertical space and keeping all of the elements of a form on a single page, which can be beneficial in aiding the user in form completion and making them aware of all of the possible options they can, or made need to provide information for. It does however have a few disadvantages. Long labels cause short label's input boxes to be far away from the end of the label, this damages that basic principle of proximity. In extreme
Read more

Procedural VS Object Oriented

What is the correct way to write code? If you have worked with PHP long enough you may well have come across the website  PHP The Right Way . This website is dedicated to helping developers learn the industry best practices. Interestingly another site appeared afterwards called  PHP The Wrong Way . In essence this website is a rebuttal to the many of the recommendations made in PHP The Right Way. With any sphere of learning it is important to keep an open mind, to listen and critically evaluate the received wisdom and determine if it is indeed wisdom or opinion built on shaky ground. There is one part of the argument I would like to focus on and this is the argument for and against " Always use object-oriented Programming ". While PHP The Right Way does not appear to explicitly state that you should always use an object oriented approach it is implicit from the examples and the recommendations to use design patterns. While there are programming approaches available othe
Read more