What causes slow database performance and how can you optimize it?

There is no concept of Slow Database. Basically it is the code/application/process which we are running against behaving as not expected. i.e Either the query is taking longer time or may be the System resources are spiking abnormally (like CPU, IO,N/W etc...) or might be the queries are getting suspended/blocked once they enter into the system etc. So we need to look the system from the scratch. Generally we will look at 1. OS performance 2. SQL & N/W Metrics 4. SQL server & Database level configuration parameters. 6. Table level properties like right indexes and updated statistics along with well tested Production code Once we have everything above in place and further we need to go for Query Tuning if issue still exists.

In my experience, choosing the right database for the problem to solve, takes care of a lot of problems right away. After that, the next critical part is, how you design the database itself. For me, a well defined SQL database should have: 1. Normalized Data (No duplicate data) 2. Well defined/consistent & non-fishable Primary key & column data types should be relevant. 3. Related tables having Foreign/Unique Key. 4. Storing data (say marks of each subjects for a student) 5. Not storing calculatable information (Do not store total or percentage when you have already stored marks of a student. These can be calculated. 6. Define good Indexes. 7. Ensure ACID NoSql: 1. Keep design flexible. 2. Data Duplication is ok. 3. Design around BASE.

Back in 2007, I had written a stored procedure (took me three days, Sql Server 2000 days) which involved talking to about 20+ tables & creating few more dynamic tables & removing them after. The requirement was complicated & was reflected via the stored procedure. If I do that today, probably, I'll do it very differently. Here are some simple tips to write efficient queries. 1. Create Indexes for columns which frequently appear within the "where" clause. 2. Select only the data you need. Not every column. 3. Create views where ever necessary. 4. Use "Explain" to understand what the query is performing & tweak accordingly. 5. Use Stored Procedures (SP) VS Native SQL Statements. SPs are optimized. 6. Use joins & avoid nested queries

Fragmentation in tables can also impact read and write performance. Regular index maintenance should be performed. A quite uncommon one is a slow network latency, which does affect the communication between databases and server / clients. Consider using content delivery networks for faster performance. Also consider Partitioning large tables and update statistics

ORM framework is actually a helpful tool to make Developer's job more easier and faster. But as a DBA, I don't really like it. The thing is, they generate SQL code in inefficient and disgusting ways. I faced some queries that require the Server to grant it 20Gb Ram but actually request approx. 40Mb Ram to be executed... It's a big waste and I cannot rewrite the entire code without understand the business (approx. 3,000 rows). Actually, we can limit the maximum memory each query can require, but you know...

In my experience, ORMs have helped to simplify and speed up database interaction. ORMs are tools that help developers to write code that interacts with databases in an object-oriented way. They handle the logic of mapping objects to relational tables, validate constraints, and manage transactions.

Here are 4 tips: 1. Never have a database(db) call inside a loop. For Reading, Use Maps/related types in the application code or for writing, use bulk updates. 2. Ensure the data to be stored is sanitized. 3. Use Redis or a similar memory cache to store/read data which is frequently read but not frequently updated. When such data is updated, bust the cache. 4. Always keep an eye for connection leaks/related DB warnings. Close the connection after its usage.

Continuous monitoring and keeping a baseline of database infrastructure resource utilisation and sql query performance is very important.

One common pitfall in database performance is the over-reliance on ORMs. While ORMs simplify interactions with the database, they can inadvertently lead to inefficient queries if not taken care of. For instance, fetching an entire row when only a single column is needed results in unnecessary data retrieval, slowing down performance. Database design is an intricate art. Excessive reliance on joins, if not carefully managed, can also significantly hamper performance. I've learned that understanding the nuances of both ORMs and database architecture is key to optimizing database efficiency.

Change or Failure causes database performance issues. The failure could be at infrastructure level in terms of the server, SAN or networks or software level in terms of database bugs or app level in terms of SQL plans drift, increase volumes or new code base, stale metadata (stats). Key is resilient infrastructure, testing environments (to proof any change or fix), controlled deployments, patched s/w, data housekeeping including archiving and up to date stats. Lastly having adequate tooling and support staff to quickly identify an issue is key.

Some tips to get better at optimizing SQL queries: - Make sure you select only the columns you need - Inspect generated queries by the ORM - When designing a new table, set indexes on columns you will frequently filter on - If you will always filter with a combination of columns, create a composite index - When a query is randomly slow, monitor the db server and check the execution plan of the expensive query. Compare it to the fast execution of the same query. - Last but not least, ask for the help of more experienced devs when looking into a performance issue. For NoSQL dbs there could be more database type specific features that you need to research to improve your optimization skills.