Interview question: Tell me about sql Optimization?
Alright alirght!!! I know you never expected SQL optimisation on desicrunch.com website. But you know it has been observed that more than 3/4 th of Desi contractors deal with database questions during interview.
If you are Database ACE, then leave this page and read some other interesting articles like Private Pilot License or Mortgage for H1b or How to keep consulting contract extended also coming soon "How to use Matrimonial websites"
Quick 25 points to sound like a sql expert.
1) Avoid repeated scan of big tables, instead try to use views which can cut down number of rows.
2) Avoid too much of : Normalisation, excess usage of cursor & temp tables, recompilation
of stored procedure, complicated joins, Indexing, Order by. [once again, point says "avoid
too much use of following"]
3) Stored procedures send flag value(sometimes know as done_proc flag) after execution of
every statement in it. this value travels on network. Avoid it if you dont need it by using
"SET NOCOUNT ON"
4) Always try to use some where clause in SELECT, avoid bringing whole table data.
5) For SQL Server users, use the SQL Server Profiler to create Trace Wizard.
6) "Union All" combines data from two tables. "Union" combines data from two tables and
then finds distinct rows. If duplicate rows doesnt matter in your query, use "union All"
over "Union", this will make ur query faster.
7) Avoid using "DISTINCT" whenever possible. It takes substantial processing.
8) Avoid "SELECT *", instead write name of columns in your query.
9) If you are MS SQL 2005 user, "Top" keyword can be used with DML(Delete, Insert, Update)
statements.
10) In "Where" clause "=" is more optimised compared to "<>" .
11) If using "LIKE" statement, use preceding character, use 'm%' instead of '%m' , this
will try to use index internally.
12) If you need summation, always use trigger, trigger has some overhead, but will help you
avoid summation after every transaction.
13) If your app needs to insert binary data into image column, use stored procedure instead
of insert statement, because insert statement has to convert binary to string, becomes
double in size, then once it reaches sql, again it has get re-converted to binary type from
string.
14) Avoid temp table, instead use Derived tables.
15) If you are going to insert same value again & again in table, use default table value.
16) Stored procedures are good, cos their execution plan stays in cache. Every sql statement you run needs get validated within sql engine, with stored procedure this validation thing happens only first time and remains ready to use for all other calls.
17) Avoid naming your procedure starting with sp_ for eg : sp_FindEmployees . Every procedure with this name, server first tries to search in master database.
18) Validate all parameters in start of procedure.
19) Avoid change in execution plan of stored procedure (for eg: change in where clause..everytime query is run) this doesnt give cache benefit. Instead deletgate call to different stored procedures using if then statements within stored procedure.
20) "Exec database_name.dbo.myProcedure" is better than "Exec myProcedure" i.e use fully qualified names.
21) Identify which queries runs frequently and is resource intensive, make Indexes only for them.
22) Static tables with master data (tables which dont changed thru DML statement much) can be indexed.
23) SOUNDEX in where clause helps to escape Index.
24) This one is popular question. how to avoid cursors ? Cursors usually involves row by row update, instead try to use SET operation.
for eg:
Age between 30 and 40 -- 5000 hike
Age between 40 and 55 -- 7000 hike
Age between 55 and 65 -- 9000 hike
UPDATE tbl_emp SET salary =
CASE WHEN AGE BETWEEN 30 AND 40 THEN salary + 5000
WHEN AGE BETWEEN 40 AND 55 THEN salary + 7000
WHEN AGE BETWEEN 55 AND 65 THEN salary + 10000
END
25) Another situation where cursors get used often, is when we want to call stored procedure, depending on value of one particular column. Use "While" loop to avoid it.
Bonus Point: Best way to use this article is, keep it open in front of you while giving telephonic interview and wait for interviewer to ask you "How to optimise SQL".