Database developers have it tough. Whether they use SQL Server, Oracle, DB2, MySQL, PostgreSQL, or SQLite, the challenges are similar. It’s too easy to write queries that perform badly, that waste system resources, or that don’t take advantage of database features designed to make life easier.
Here are seven common traps to avoid when writing database applications.
7 SQL mistakes to avoid
Blindly reusing queries
Nesting views
Running large, multi-table operations in a single transaction
Clustering on GUIDs or other “volatile” columns
Counting rows to check if data exists
Using triggers
Doing negative searches
Blindly reusing queries
An SQL query is typically tailored to retrieve the data needed for a specific job. If you repurpose a query that fits most of your use case, it may work outwardly, but it could also supply too much data. This takes a toll on performance and resources, some of which won’t manifest until you hit scale. Always examine queries you intend to repurpose and trim them to fit the new use case.
To read this article in full, please click here
InfoWorld
