How Do You Optimize SQL to Get Away from Disk Contention, to Reduce the I/O Activity?

Problem scenario
Let's say you want to rearrange the columns a of SQL table because a special order would make subsequent SQL commands easier.  Or you just want to avoid disk contention on the underlying storage device.  How do you optimize SQL to do less input/output activity on the disk while being able to rearrange the order of columns?

There are other ways such as avoiding "select *", changing the frequency that indexes are updated, normalizing tables or partitioning tables.  For this answer we recommend using a view.  A view can accomplish the rearrangement of columns and put the results of a SELECT statement into memory as opposed to leaving a table on the disk.  This does take more RAM.  Here is an example of a view based on a table named "goodTable":

create view continualView AS select a.column3, a.column1, a.column2 * from goodTable a

Now continualView can be queried like goodTable.  You would want to avoid views if your environment was memory constrained.  But if there was ample memory and there I/O activity on the storage device (e.g., NAS, SAN, or hard disk of the server) was a bottleneck for the performance of your SQL database, then a view may help.

Leave a comment

Your email address will not be published. Required fields are marked *