British firm TotallySQL has launched a range of tools that extend the T-SQL scripting language with new functions, aggregates and types for advanced processing within the database layer.
Charles Southey, managing director of the Oxford-based company, says: “Carrying out processing within the database has a number of advantages over just storing the data there. Firstly, the data doesn’t have to be shuttled backwards and forwards across the network anything like as much – which can mean a massive improvement in application performance and a drastic reduction in network load. Secondly, it enables the application to take advantage of the awesome processing efficiency of the MS SQL database engine, which can process millions of data rows incredibly quickly – it can seem instantaneous.”
Southey says that he often sees what he describes as poorly architected applications that pull entire datasets off a database a row at a time for processing on the client. He maintains that all that is really needed is a handful of values as the final result.
"That leads to slow, unresponsive applications – which are also unreliable as operations time-out – and very frustrated users," he says. "Tuning or adding more power to the database in these situations can only help to a limited extent because the real bottleneck is in architecture itself. Instead, by moving more of the logic into the database and eliminating the pointless chatter and data loading across the network, performance improvements of several orders of magnitude can be achieved.”
There are eight TotallySQL.com utilities, ranging from SQLEngineering to the free tool SQLTuple. Where his products help, says Southey, is by enabling richer business and data processing logic to be built into scripts and stored procedures that reside on the database, while keeping the syntax in those scripts clean, elegant and easy to maintain. They're fast, too.
"There’s no observable difference in performance compared with the built-in native functions," he says. "Where there’s complex numeric processing to be done, such as in some of our mathematical functions, we’ve researched extensively for the fastest and most reliable algorithms available, and taken great care to deal properly with the limits of machine precision.”
As well as the new off-the-shelf products that cover areas such as maths, statistics, financial analysis, engineering, text-processing and date/time arithmetic, TotallySQL.com provides bespoke function libraries to order. The company also offers its Assembly Manager tool for users of CLR-based functions.