SQL Server – Understanding Logical Query Processing (LQP)

“TSQL is 2 faced!” – cried the app team member

“Have you been watching Batman?”

Recent conversation with application folks made me think and I decided to break it down on Understanding Logical Query Processing (LQP).

  • Logical – is the conceptual understanding of the statement and what will be the result set
  • Physical – is what is processed by the Engine; ensuring that end results stick to the logical concept

This will be my attempt to take a swing at (on a high level) how a query is visualized by the Engine and in turn visualized by a DBA. Going back and forth my engineering friends from UT Arlington on how they would perceive “Get me a bottle of water from the table”, the end result “Go to table, get water bottle, get it to me.” Same concept gets applied when a TSQL is sent across.

For instance, TSQL follows the English language pattern
[crayon class=”lang:tsql decode:true”]
SELECT emplId, fName, lName, phone
FROM dbo.Employee
[/crayon]
But LQP interprets as

FROM dbo.Employee
SELECT emplId, fName, lName, phone

TSQL dictates the following pattern:

  1. Select
  2. From
  3. Where
  4. Group By
  5. Having
  6. Ordered By

LQP dictates the following pattern:

  1. From
  2. Where
  3. Group By
  4. Having
  5. Select
  6. Ordered By

Let’s take a step down further:

  1. FROM – This is where you indicate the table(s) you want to query
  2. WHERE – There is where the FILTER process starts. Rows are filtered based on the predicate. Note: References to COLUMN ALIAS will error out as WHERE is evaluated prior to SELECT.
  3. GROUP BY – This defines a group for each distinct value to be clubbed. If a reference is made to a value that is not part of the GROUP BY it needs to be part of the aggregate family (SUM, MAX)
  4. HAVING – Another filter to further refine your result set. Since this is after GROUP BY, HAVING evaluates per group and filters groups as whole.
  5. SELECT – Finally the SELECT gets evaluated. It makes sure that it evaluates the list and display results based on that. If a DISTINCT is applied, it takes one more step and removes duplicates from the result set.
  6. ORDERED BY – Presentation is everything. If ORDERED BY clause is used, it will present the result set accordingly. Also note ORDER BY can be done on COLUMN ALIAS since this is done after the SELECT clause.

With this in mind, it will help newbies to tackle pages of codes when troubleshooting.

~ Adios

Khan

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |

Follow me on Twitter

Avatar

About Arsalan Khan

People know me as 'Khan' ... nope not Shah Rukh's movie but Star Trek II - Wrath of Khan and since than 'Khan' it has been. Born and raised in Dubai before moving to US for my masters. Been blessed with solid 8+ yrs with SQL (and counting) and currently working for the biggest publishing house as Sr. Database Administrator. I have had an opportunity to speak about tips & tricks to write efficient tsql for Quest International Users Group (PeopleSoft). Love playing TT, badminton and thoroughly enjoy watching cricket when not occupied with my loving daughter. Expertise in DR, Performance Tuning, Troubleshooting and Problem Solving. With that being said I have finally decided to roll my sleeves up and give back to the community bit by bit. Finally.. as my wonderful wife puts it ... "if you don't have a smile, I will give you one of mine ~ Rabia Khan" ~Cheers

View all posts by Arsalan Khan →

Leave a Reply

Your email address will not be published. Required fields are marked *