Complex SQL for Beginners: Real-time fiscal calendar reporting using date-based data sources with grouping and pivoting ~~~ And a lot of helpful hints along the way

Why should you care about complex SQL or database performance?

Database access is usually the slowest part of online transactions. And can be really slow, unless it is done right. In particular, Service-Oriented Architecture (SOA) has exponentially increased database access time. In the old, “green screen” environments, database and application times were about 50% each with well-tuned database access. SOA application times can easily be 10+ times more than the internal database times.

When applications run on the same hardware as the database, they use a very fast direct connection to the database. When applications run on separate hardware from the database, as in SOA, their database requests are routed across the network using TCP/IP. The network time getting to the database and back using TCP/IP can be 100 to 1,000+ times longer than using direct access or the internal time the SQL runs. This adds up very quickly, so SOA basically requires the total number of SQLs run in a transaction to be minimized. That means doing as much work as possible in each SQL and doing “set processing” (discussed later), instead of iterative row by row processing.

This requires complex SQL. And, complex SQL has to be well-written with indexes defined to support them, or they can run very, very slowly.

A case in point. A large department store chain was converting to a new application and one very important program ran for 8 hours in the conversion for the first group of stores. This was the smallest of 13 store chains they owned. The conversion for the second chain was backed out a month later, after this program ran for 24 hours. It was a major failure. One simple change to one SQL made it run in 2 hours, instead of more than 24 hours. One more simple change made it run in 5 minutes, instead of 2 hours. That’s the impact SQL can have on an application. And the company using it.

Performance of SQL is so important, SQL should be learned from a performance aspect first. Insert, Select, Update and Delete are simple concepts. Learning how the database processes them can help avoid serious performance issues later. The entire purpose of applications is to capture and maintain data. Applications need to do that as efficiently as practical, to minimize the cost of capturing and maintaining their data.