Monday, January 07, 2008
« Removing Duplicate Rows In T-SQL | Main | I Just Finished My First VB.NET Project ... »
Another Interview Question

One question I ask when interviewing is what types of T-SQL statements are the worst for performance, I am looking for answers like the below (not in any order):

  • Cursors
  • IN clauses
  • Large INNER JOINs

What this tells me about the person is that they are conscience of the code impact they have on the server.  Someone that doesn't know the answer will write anything that works just to get their job done -- which means that I will later have to clean-up their stuff.

{6230289B-5BEE-409e-932A-2F01FA407A92}

T-SQL | Wayne
Monday, January 07, 2008 9:52:08 AM (Pacific Standard Time, UTC-08:00)  #    Disclaimer  |  Comments [1]  |  Related posts:
Shutting Down a Running COM+ Instance
Azure Storage - CloudDrive SDK
varchar(max) and varbinary(max) Questions and Answers
Mutual Exclusive CollapsiblePanelExtender
For Best Performance Avoid SELECT * Select Statements and never use COUNT(*)
Query SQL Server 2008 Total Memory Usage
Sunday, January 06, 2008 5:53:24 PM (Pacific Standard Time, UTC-08:00)
If you were to interview me I would answer in the following manner regarding your bullet points:

1. Cursors: Could never justify using a cursor and would probably expect to write a 3-page research paper with relevant code samples to justify my using of a cursor.

2. IN Clauses: I have recently found some tests online stating that these can be sluggish when dealing with larger datasets. If I had to use similar functionality I would choose EXISTS / NOT EXISTS over IN / NOT IN as they perform faster, especially over larger sets of data.*

3. Large INNER JOINS: not a big fan! I'd much rather break up large & complex JOINS into more manageable views. I'd fine-tune the views and then create one larger view consisting of several smaller views. You can re-use these smaller views to create other subsets of data to your liking.

Am I hired? :-)

* I read somewhere online (forget where but will try to re-find it) that EXISTS / NOT EXISTS is only quicker and more efficient than IN / NOT IN when the results set consists of 12 rows or more. To me this seemed odd but it could've just been that the difference in performance is trivial only up to > 12 rows of data??
Will Asrari
Name
E-mail
Home page

Comment (HTML not allowed)  

Enter the code shown (prevents robots):
Live Comment Preview