Dec 08

My links of the week – December 8, 2013


Here is choice of links for this week.

  • SQL Server:
    • Phil Factor’s Primary Key Primer for SQL Server is an excellent primer on choosing primary keys for SQL Server databases. It also includes links to several related and relevant resources.
    • Brent Ozar’s The Secrets of TempDB is a very interesting video on TempDB, one of SQL Server’s system databases. The video covers multiple aspects related to TempDB, presenting its purpose, how SQL Server uses it, how it should be setup and how it can affect SQL Server performance. The post also includes a link to multiple valuable TempDB related resources.
    • Jez Schultz Borland’s Filtered Indexes vs. Table Partitioning explains some of differences between filtered indexes and table partitioning and suggests use cases for both. It also includes links to additional resources on both subjects.
    • Thomas LaRock’s Doing it Right: Performance Monitoring and Troubleshooting compares tracing and polling as strategies for collection performance metrics in terms of advantages, disadvantages and risks and advises a strategy that includes both, while stating his preference for polling for regular monitoring.
    • Brent Ozar’s How to Build a SQL Server Support Matrix presents his version of a SQL Server support matrix and, in a very interesting post, explains how it can be used and some of the advantages that it can bring about.
    • Glenn Berry’s General Database Server Build and Deployment Instructions provides a useful build checklist for setting up new SQL Server instances.
    • Jack Li’s How Simple Parameterization works addresses query parameterization, explaining how SQL Server versions since 2005 includes two modes of parameterization (simple and forced), how SQL Server behaves in each and how it can be determined whether a given query is being parameterized or not.
    • Karen Lopez’s 10 Tips for the Minimalist DBA presents a set of 10 tips about concerns and skills needed for anyone in a DBA role.
    • Bill Karwin’s How to Design Indexes, Really, is a very good presentation on index design that, although addressing MySQL indexes, can also be of valued for anyone designing indexes for  other database management systems.

That’s it for this week. Thanks for reading.

Dec 06

Data Tables, an unexpected browser stress test and a surprising winner

The venerable HTML table, although less used than it once was, has still its place, especially when showing, well, tabular data. Recently, whenever there is has been a need has to use tables with column sorting, searching or having tables with fixed headers or columns, Data Tables has been of great use. Data Tables is an excellent, flexible and versatile jQuery plug-in, authored by Allan Jardine of SpryMedia, that I can strongly recommend to anyone needing to use tables in a web app.


An example of the use of Data Tables in the CFOlytics financial management web app.

While working on a recent web project with Data Tables, we found ourselves in a situation where a table with around 900 hundred rows had to be displayed and, in our first try, paging was not an option – the plug-in allows the use of both vertical and horizontal scrollbars, enabling an experience that is somewhat similar to Excel, so this high number of rows wasn’t really an initial concern. Having used the plug-in in similar circumstances before (although not quite with as many rows), we weren’t expecting any unpleasant surprises, in terms of browser behavior.

The first test with Chrome confirmed a previous experience – data download wasn’t a problem, time wise, but getting the browser to display the table was another matter altogether. Chrome took its time to display the data, probably 3 to 4 times the amount of time needed to get the data from the server using Ajax. IE was next in line for testing and we started with IE 11 on Windows 8. Surprisingly, or maybe not, 3 out 4 times, IE couldn’t handle the table without crashing. IE 10 on Windows 7 was even worse. Not only did it crash as well, but when it didn’t, the table didn’t display correctly. The good surprise was left to the end. When we tried the page with Firefox, the page display correctly, but performance was clearly better than Chrome or IE. With Firefox, the response time, both in terms of download and display was perfectly acceptable.

These results were a bit surprising. I definitely didn’t expect to see Firefox beating Chrome in Javascript performance in a such a clear way, but it did. Firefox lost a bit of its luster to Chrome but, at least to me, this specific situation made me rethink the way I looked at Firefox. As to IE, it seems that no matter how much Microsoft tries to advertise as a fast, reliable browser, every day use shows it still lacks the robustness its rivals show – even considering this is a very specific situation and we didn’t mean this to be a test in browser performance. However, my own experience helping users on the Windows Secrets Lounge, shows that IE still lags behind its major competitors in reliability.

As to the situation that brought about this unexpected browser performance test, we solved the problems that prevented us from using Data Tables paging features, so the use of paging ensures a good behavior from all modern browsers while displaying the table in question.