SQL Optimization Tips/Questions

Below are some of the tips for SQL query optimizations in the form of question/answers.

1. Which of the following query is the most optimized?
a. SELECT column_name FROM table_name WHERE LOWER(column_name) = ‘name’.
b. SELECT column_name FROM table_name WHERE column_name = ‘NAME’ or column_name = ‘name’.
c. SELECT * FROM table_name WHERE LOWER(column_name) = ‘name’
d. SELECT * FROM table_name WHERE column_name = ‘NAME’ or column_name = ‘name’.

Answer: B.
Reason: We should specify the columns in the Select queries and avoid functions like UPPER, LOWER etc as far as possible.

2. Which of the following query generally prevents (but not always) the query optimizer from using an index to perform a search?
a. SELECT member_number, first_name, last_name FROM members WHERE firstname like ‘m%’
b. SELECT member_number, first_name, last_name FROM members WHERE dateofbirth < DATEADD(yy,-21,GETDATE())
c. SELECT member_number, first_name, last_name FROM members WHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21
d. All of these

Answer: C
Reason: Column name is mixed within a function. Hence index cannot be used by optimizer.

3. When we use “NOT IN” our SQL queries, the query optimizer uses which technique to perform the activity?
a. Indexing
b. Clustered Indexed scan
c. Nested table scan
d. None of these

Answer: C

4. Which of the following is the best way of inserting a binary image into database?
a. Use Insert statement
b. Use Stored procedure
c. Both give same performance
d. None of these

Answer: B
Reason: The reason for this is because the application must first convert the binary value into a character string (which doubles its size, thus increasing network traffic and taking more time) before it can be sent to the server. And when the server receives the character string, it then has to convert it back to the binary format (taking even more time).

5. SELECT INTO option locks the system tables. True or false?
Answer: True

6. Using which among “Derived table” and “Temporary table” can we reduce I/O and boost our application’s performance?
Answer: Derived table
Reason: A derived table is the result of using a SELECT statement in the FROM clause of an existing SELECT statement. By using derived tables instead of temporary tables, we can reduce I/O and boost our application’s performance.

7. Which of the following query is the best one in performance to verify the existence of a record in a table:
a. SELECT COUNT(*) FROM table_name WHERE column_name = ‘xxx’
b. IF EXISTS (SELECT * FROM table_name WHERE column_name = ‘xxx’)
c. Both give same performance

Answer: B
Reason: don’t use SELECT COUNT(*) in your Transact-SQL code to identify it, which is very inefficient and wastes server resources.

8. Which of the following is NOT recommended for UPDATE queries in order to reduce the amount of resources required to perform the query:
a. Try not to change the value of a column that is also the primary key.
b. Try to update a column that has a reference in the WHERE clause to the column being updated whenever possible
c. Try to avoid updating heavily indexed columns.
d. When updating VARCHAR columns, try to replace the contents with contents of the same length.

Answer: B
Reason: All points except “B” are recommended points for UPDATE queries. Instead we should try not to update a column that has a reference in the WHERE clause to the column being updated.

9. Omitting of which clause (if possible) can decrease the possibility that a sort operation will occur:
d. Both A and B

Answer: D

10. Which of the following query will be better in performance?
a. SELECT * FROM Orders WHERE OrderID*3 = 33000
b. SELECT * FROM Orders WHERE OrderID = 33000/3
c. Both are same in terms of performance as well.

Answer: B
Reason: We should avoid computation on columns as far as possible and hence we will get an index scan instead of a seek.

kick it on DotNetKicks.com

Shout it

pimp it

Anky Goyal

I am MCPD certified in .NET and working on .NET technologies for past 3yrs. I am very passionate about the new features which get introduced in the technology. I am here to share the new features i get to know in the world of Microsoft. Follow me on twitter @ankygoyal [email protected]Ankit Goyal

More Posts - Website

  • Pingback: DotNetShoutout()

  • Pingback: PimpThisBlog.com()

  • Pingback: Tweets that mention SQL Optimization Tips/Questions | TechArtifact -- Topsy.com()

  • Martin Smith

    1a and 1b are not equivalent. 1b will not return data for Name or any other mixed case ‘NaMe’ .

    • Hello Martin,
      Yes you are correct. But queries were written to stress the fact that we should avoid using functions and “*” in queries and not logic

  • Markus Wollny

    I was irritated by the fact that you don’t actually say which RDBMS you’re talking about – I guess it’s Microsoft SQL Server, the ‘Transact-SQL’ mention really gave it away. On more general terms:

    1b might actually be the best in case your RDBMS supports functional indexes and you have set such a functional index on lower(column_name). This is an option with PostgreSQL and Oracle, though not (yet) for MySQL, don’t know about other RDBMS support for functional indexes – Microsoft SQL Server may not be among the lucky lot here.

    As to 2c: Again, if you have functional indexes available, such a mixing of column_name and function may not prevent you from using indexes, though in this case it surely will, GetDate() being a volatile function (unlike lower()).

  • For question 1, this would depend on the collation setting on the server etc. If the system was set up to be case sensitive and you were performing a user search you would need to use the upper or lower or handle this issue in another tier of the system (not recommended … coupling)

  • Wonderful goods from you, man. I have understand your stuff previous to and you are just extremely magnificent. I actually like what you’ve acquired here, really like what you are saying and the way in which you say it. You make it enjoyable and you still care for to keep it sensible. I cant wait to read far more from you. This is actually a wonderful website.

  • Great blog here! Also your site loads up very fast! What web host are you the usage of? Can I get your affiliate link on your host? I desire my site loaded up as fast as yours lol

  • Thank you, I have recently been looking for information approximately this topic for ages and yours is the greatest I’ve found out so far. But, what in regards to the bottom line? Are you certain in regards to the supply?