An interviewer asked me this question:
Tables are created with appropriate normalization rules, However the database is performing slow. [Ie.: The select, insert statements are taking time to do his operation.] What are areas we need to look to improve the database performance.
Obviously this is a vague question. What sorts of things might be wrong with a database that is running slowly, even when normalized?
I would talk about how there are many things which can be done to improve performance. The first thing is always to investigate if the correct indexes are in place. Of particular concern in a normalized database is making sure FKs are indexed. Likely this would fix many performance issues.
Other things to look at would be rewriting the SQL code to use more performant techniques such as getting rid of cursors and correlated subqueries and making where clauses sargable. You would want to review the worst performing queries individually. You would also want to review queries that are frequently run (especially if multiple users run them simultaneously) as a small change in those could multiply through the system. If your worst queries are coming from an ORM, they might need to be rewritten as stored procs so they can be performance tuned.
You might also want to make sure you have a performance problem. What you might have is really a blocking problem where performant code is being blocked by other processes and has to wait.
Then you would look at hardware, if you have underpowered hardware and network connections, likely no other change is going to fix that.
In a large enterprise system, you might consider data partitioning.
Denormalization is a technique to improve performance but it is the last thing you would want to consider. First, you have the risk to the data of changing the structure that drastically. Converting the data to the new structure is something that can go very badly wrong if a mistake is made and it is more time-consuming to make this type of structural change than any of the other possible performance improvements. It would also be irresponsible to denormalize without creating triggers to make sure the data stays in synch as it is changed in the denormalized tables. This may mean selects are imporved but action queries are slower, so performance may not be imporved as much as you think. It is also a concern that in denormalizing, you may be making the tables significantly wider and that can affect performance negatively if you have wide tables.
Sounds to me that your interviewer was not looking for a data scientist answer but was simply looking to make sure you understand that "normalization" != "performance". So I'll keep this answer at the level that I'm guessing he wanted.
Normalization means minimizing redundancy in stored data. Instead you setup relationships (often with foreign constraints) between multiple tables. However, while normalization might lead to a smaller amount of stored data, often it creates performance problems because now many queries end up joining multiple tables. Same thing with adding data where you might now have to update multiple tables at once.
Often, speed gains could be achieved by de-normalizing the data. You are storing more and there might be duplicates, but when it comes to running most frequently used queries, all your data would now be in one table. Getting results from one table is usually much easier on the hardware than having to join multiple tables
Making INSERT statements run faster is a bit of an arcane art. But that's probably not the focus. The point of a database isn't putting data into it; it's getting it back out in interesting and useful ways. So the main things to focus on are SELECT statements.
The first thing I would look at is checking the query plans on slow queries. See if you have any table scans that are taking up a significant percentage of your time. A table scan is when the database engine has to examine every row individually to see if it meets a WHERE criterion. If you find one of these, you can make the query run faster by indexing the table on the appropriate WHERE criteria. This can take search times from O(N) down to O(log N) or even O(1).
Some databases will make it easy on you: their query plan analyzer will point out that you're missing an index and suggest what you ought to create.
Also, check out the joins on your query. Make sure they aren't using too broad of joining criteria, and be careful that you're not using left outer joins when a full join would work. Both of these issues can cause a poorly-written query to produce too many rows and take longer to run.
If you don't have missing indices or bad joins, a more advanced trick is denormalization: setting up columns on tables that duplicate data that can be found in other tables, to allow you to avoid joins or aggregates that can be expensive. This has to be done carefully, though, with triggers so that the data remains in sync, and is best done only if you know what you're doing and if there aren't any better alternatives available.
Specifically, in the Query Execution Plan look for actions that are table scans instead of index seeks. It is a hint that you might want to add an index to say a column that represents foreign key (they don't get created automatically)
Other options would be to put your data files on different physical disks. Using RAID for your partitions might work as well. At the very least, you want to separate log files from that data files...so that writing to the log does not impact the write time to the data file.
More advanced scenarios include clustering and sharding to allow the load for searches to be spread across multiple nodes.
