Monday 16 August 2010
Some thoughts on the dark art of hardcore SQL and why there might just be an easier way of doing things.
I first got introduced to SQL in 1989 when the company I worked for embarked on the development of a new system using what was then a fairly early version of Oracle.
I was lucky enough to be sent on a couple of courses to learn about Structured Query Language (SQL) and an Oracle Forms course in Reading.
Being relatively new to commercial IT (I was previously a free lance games writer in my teens), and initially being trained to code in COBOL and IDMSX, the concepts of SQL felt like a breath of fresh air over the old style methods to navigate around databases.
One of the things I instantly took a liking to was how easy SQL was and yet how devious you needed to be to do anything difficult. SQL brought out the maverick in me. If someone was to say “you can’t get it to do that”, I’d think “yes I can” and go and write some convoluted, unfathomable code that would do exactly that.
I took pride in writing queries that would take over a minute to print due to the huge number of DECODE statements, CORRELATED SUB QUERIES, OUTER JOINS, UNIONS, WHERE EXISTS etc…, etc… the 50 to 60 tables in the FROM clause, imponderably large WHERE clauses and SORTS and GROUP BY statements.
I strongly believed that the bigger the SQL the better. Perhaps it was a “man thing”. More likely it was a “me thing”.
I was well on my way to becoming a SQL Ninja… a master of my craft.
Someone who would quietly stand to one side as other developers toiled and scratched their heads then leap into action seconds before it was too late to write some mystifying code that returned the results they needed in minutes.
So what does this tell you about SQL?
It’s powerful, very powerful.
If your data is stored in a database that supports SQL and in particular, supports the extensions that were built into Oracle’s implementation of SQL, if you knew what you were doing you could produce any report, any extract. It might take a while but it could be done.
So where’s the rub?
SQL Ninjas are few and far between. SQL Ninjas are being left behind by some new methods of accessing data that does not require the same level of “geekiness” you need to extract data out of complex database schemas and marts.
Someone invented the search engine – A new super fast data access tool that allows children to search for data as soon as they have learned the basics of writing.
What could be easier than typing in the key words you are interested in and then see the results appear in front of your eyes in a matter of milliseconds? True it doesn’t have the richness of syntax or ability to join data together from multiple sources but the fundamentals are brilliant and it’s so fast!
Being a SQL Ninja and becoming a big fan of search engines, I wondered what would happen if you combined the flexibility of SQL and the sheer speed and simplicity of search technology together.
What if you could come up with a tool that could give you the flexibility of SQL with regards to the kind of outputs you could produce but used the power and speed of search technology to filter down the information you wanted to display?
So we wrote CXAIR.
A tool that allows your child to generate the type of outputs a SQL Ninja would be proud of. Step aside Ninja’s, your time is up!
CXAIR – “probably the best BI tool in the world.”