SQream Platform
GPU Powered Data & Analytics Acceleration
Enterprise (Private Deployment) SQL on GPU for Large & Complex Queries
Public Cloud (GCP, AWS) GPU Powered Data Lakehouse
No Code Data Solution for Small & Medium Business
By Arnon Shimoni
I first used SQL for an online blogging platform I was building in Perl back in 1999. Moving from a flat-file to MySQL was truly a lifechanging experience. The (then) four-year-old DBMS had a nice little set of features and optimizations that I could easily apply and get a decent performance boost.
Call me a masochist, but I quite like spending a few hours or days determining why a query is slow – because the reward of solving it and speeding it up by an order of magnitude is amazing.
The landscape has changed quite a bit since however, but the challenges mostly remain. Some common optimizations can speed up a query by 10x, but overusing optimizations can actually slow SQL down, or cause big problems when you scale to more data or more substantial workloads.
In the rest of this post, I’ll detail some common tasks and ideas for solving (or working around) common SQL issues.
Here are some things to try when your SQL queries are slow, without changing your servers or instances:
Most DBMSs handle data very efficiently — much more efficiently than a client application:
With very big queries, strain can come from transmitting result sets to the client. Do you really need all the data? If not, reduce the result set size in the DBMS:
SELECT *
LIMIT
Large queries can be hard to work out. Breaking queries into small bits and using temporary tables can make the query much more understandable.
Vendor-written functions often outperform SQL functions chained together.
Some examples:
LIKE '%foo%'
ISPREFIXOF(x,'foo')
ILIKE
Views are virtual tables that are created from a query. A view is often materialized when you run a query that accesses them.
In many DBMSs, indexes speed up your query, letting the DBMS know where to look for data. Apply indices selectively – focus on columns with high cardinality or columns you use often.
Too many indices decrease write performance (and take up lots of space):
Each DBMS is different, so be sure to apply best practices for the DBMS you use.
However, there are some things that will always be true:
There’s only so much you can tweak and tune. When you can’t (or won’t) tune any further, consider updating your DBMS.
New SQL DBMSs such as SQream DB:
If you’d like more information about what to do when you’ve outgrown your current DBMS, give SQream DB a try.