Looking for SQL Optimization Interview Questions

views:

157

answers:

+4 Q:

Looking for SQL Optimization Interview Questions

I'm on a project where I was asked to take a quick peek at some reporting SQL (in a SQL Server 2K5 environment) and was surprised at what I found: 4 to 5 levels of subquerys, distinct clauses, unions, and NoLock hints (which were needed because the SQL was running so long it was blocking standard processing) - all in the same set!.

Because I (foolishly :) mentioned that I thought the SQL was inefficient I've been labeled the "expert" and have been tasked with creating a test for a couple of interviewees to do that will assess their SQL optimizing abilities. I'm hoping someone can point me to some URLS, or maybe provide a list that I can use to help weed out the good from the bad.

I would give them a Query plan (via EXPLAIN, or whatever your flavor of SQL uses as a keyword) and see if they can decipher what it means, what the weak points are, and how to improve the query.

Look at MySQL's Explain Documentation for help using MySQL's explain and what it means.

Dan D. 2010-07-11 03:16:30

Thanks @Dan D. I hadn't even thought of utilizing an "Execution Plan"; my focus was more along drafting some poorly written SQL (much like the reporting SQL that started this) and saying "What wrong with this?". I like your idea and will do some digging (ultimately I guess I'll still need that bad SQL to generate an Execution Plan).

BKMWpg 2010-07-11 03:37:07

+5 A:

Since you mentioned the SQL Server 2005 environment:

More SQL Server interview questions than you possibly could have imagined:

The classic set. Most interviewees will probably have studied these...maybe a good way to gauge who has prepared.

Another classic

Questions from one of the original Stack Overflow DBAs

8kb 2010-07-11 03:35:43

Uh ... WOW - you've provided me with a LOT of reading, thanks!

BKMWpg 2010-07-11 03:40:50

Yeah, maybe too much to sift through. The MSSQL Tips questions are broken down and there is a Performance Tuning set. Brent's post is definitely worth reading. I think the idea of showing them what they'll need to refactor is excellent. You can get a sense of their skill and they'll get an idea of what they'll be working on.

8kb 2010-07-11 03:44:53

+1: Only thing I'd add is to consider googling "TSQL Challenges" for code testing. I wouldn't expect perfect answers, but a candidate who knows these challenges is someone who is definitely interested in SQL Server.

OMG Ponies 2010-07-11 03:56:12

Thank's for you comments; one thing I failed to mention was that ... uhm ... the set of questions is due on Monday :$ (hey, nothing like a little dead-line pressure to get the blood flowing!). @8kb thanks for your detailed list, and again for pairing it back to something I can cram into a weekend :P (I've been recently following Brent's blog [one of his posts were the primary motivator for creating a Stack Overflow acct]). @OMG Ponies - good call on the TSQL Challenges, if for nothing else then only to confirm that my original assumption of tonnes of distincts with unions just ain't cool. :)

BKMWpg 2010-07-11 04:06:47

+1 for the great links (even if I haven't touched SQL Server in years) and especially for that last link to Brent Ozar's blog (even if it's mostly unrelated to the question).

George Marian 2010-07-11 04:30:24

check this link that is very good questions and answers

http://blog.sqlauthority.com/sql-server-interview-questions-and-answers/

KuldipMCA 2010-07-11 05:43:24

Thanks @KuldipMCA, you're the 2nd person to suggest Pinal Dave - I'll definitely take some time to review his questions.

BKMWpg 2010-07-11 14:14:08

ansaurus

tags:

views:

answers:

Looking for SQL Optimization Interview Questions

related questions