SQL (short for Structured Query Language) is a domain-specific computer language designed specifically for interacting with relational database systems. It is typically extended with procedural languages like Transact-SQL (for Microsoft SQL Server) or PL/SQL (for Oracle) that provide the ability to write long and complex stored procedures, functions, DDL scripts, etc. However, it is not a general purpose programming language like Python, Java or C++.
There are many intricacies to the use of SQL, as well as the procedural languages that are typically packaged with them. The way in which tables are joined, the manner in which data manipulation steps are performed, as well as the existence of proper indexes on each table are critical in terms of performance. Writing your SQL one way might sift through millions of rows in a fraction of a second. Written another way, it could take minutes or hours.
I had one project supporting the bond trading desk at Bank of America, during which I rewrote some existing SQL code which literally reduced its average run time from 5 minutes to 5 seconds. That’s the kind of difference you can get when you properly analyze the code and have extensive experience working with it. The client was very happy.
Although there are many tricks to the trade, one of the most common is finding ways to reduce the amount of data worked with up front, or in multiple steps along the way, using temporary tables or Common Table Expressions. You want to slice and filter as quickly as possible, using appropriate indexes, before applying your final mathematical calculations. The last thing you want is to have to scan an entire table of millions of rows.
Whether you’re writing new SQL code from scratch, or you need to review and improve your existing code, I can definitely help out. I’ve been working with the ins and outs of SQL for over 25 years.
Transact SQL Programming
A snippet of code from a stored procedure that analyzes a given home against all the other homes in the same town, using many different combinations of factors. If the given home comes near the top of any of these lists, that analysis is used as supporting documentation in a tax grievance case. This work was done for Taxtrim.com, which I own.