by Toan T
Writing SQL queries is a simple task that everyone can do but how to write efficient query that does not affect performance is another issue. As a matter of fact, many developers from every platforms are struggling and seemingly stuck in using simple DO WHILE loops and many of them often have their own methods for writing codes that they are too reluctant to think outside the box. For effective query writing, there are seven tips that the article recommended using.
1. Don’t use UPDATE instead of CASE – use CASE when wanting to insert new data into a table because UPDATE is logged so it has to be written twice for every single write to the table. CASE just simply test every row that meet the condition and will just write it to the table once.
2. Don’t blindly reuse code – This is a very common issue because it hard not to use someone else’s code because you know it will pull up the data you need. the problem is that it will quite often pulls much more data than it was needed which end up causing the system to use more resource.
3. Do pull on the number of columns you need – This is issue is similar to issue no. 2 but it is more specific to columns.
4. Don’t double dip – This occur when there are two query statements that get written to pull up data that has two conditions from a table.
5. Do known when to use temp tables – Temp table is a temporary table that developers use to store certain data extracted from one table and then use it to join with data from another table. This method is sometimes mandatory and there is no way to avoid it, but knowing when to use it will greatly help decrease the processing power required to join large tables.
6. Do pre-stage data – Joining tables ahead of time and persisting them into a table when needing to do a report will help save resources.
7. Do delete and update in batches – Don’t delete or update large amounts of data from large table but rather doing it in smaller batches will help save time and less likely to cause problem.
I find this article to be very helpful because as we are learning to write SQL, we tend to follow what the instructions tell us to do and we don’t really know the what each query does in term of how they affect the performance of the database which the mistakes can be forgiven. But when writing queries at a enterprise level, there will be a lot of pressure and stress involved if we make mistake so knowing how to do it correctly by avoiding unnecessary mistakes will help save time and resource system resource usage.
McCown, S. (2010). 7 performance tips for faster SQL queries. InfoWorld.Com, , n/a. Retrieved from http://search.proquest.com/docview/223176224?accountid=10357