Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Statement evaluation precedence Expand / Collapse
Author
Message
Posted Friday, May 7, 2010 2:29 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, October 16, 2014 5:21 AM
Points: 754, Visits: 152
There is another link on this topic that may be of interest:

http://www.bennadel.com/blog/70-SQL-Query-Order-of-Operations.htm
Post #917711
Posted Friday, May 7, 2010 3:36 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, January 2, 2014 9:57 AM
Points: 554, Visits: 863
Thanks For the Good Question :)
Post #917744
Posted Friday, May 7, 2010 3:43 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 3, 2014 3:41 AM
Points: 333, Visits: 129
An excellent question, one which should etched in DB developers' heads IMO. All too often I've heard folks complain that their query plan isn't what they wanted and blamed SQL Server for getting it wrong when actually they've not understood the processes that go on once they hit Execute...
Post #917748
Posted Friday, May 7, 2010 3:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:18 PM
Points: 6,048, Visits: 8,330
I have to disagree with the comments so far. I do not consider this a very good question.

The processing order as described in the correct answer is the logical order of evaluation. What order a Relational Database Management System uses internally is completely implementation dependant; any internal processing order is valid as long as the results are the same as when the logical processing order had been used. This is where the query optimizer comes in - it considers countless various processing orders and access methods to find the one with the lowest estimated cost.

I would have liked the question (a lot!) if the question had asked for the logical processing order. But the question specificallly asked aboout the sequence internally used, and that threw me off completely. I did answer correctly, but only because, after reading the answer options, I was able to work out what the intention of the question was.

Thanks for taking the effort to submit a question, Tom. I know how much work goes into it, and I appreciate the effort. I hope my harsh comments won't keep you from submitting more questions.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #917753
Posted Friday, May 7, 2010 4:19 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 7:48 AM
Points: 1,221, Visits: 1,433
Hugo Kornelis (5/7/2010)
I have to disagree with the comments so far. I do not consider this a very good question.

The processing order as described in the correct answer is the logical order of evaluation. What order a Relational Database Management System uses internally is completely implementation dependant; any internal processing order is valid as long as the results are the same as when the logical processing order had been used. This is where the query optimizer comes in - it considers countless various processing orders and access methods to find the one with the lowest estimated cost.

I would have liked the question (a lot!) if the question had asked for the logical processing order. But the question specificallly asked aboout the sequence internally used, and that threw me off completely. I did answer correctly, but only because, after reading the answer options, I was able to work out what the intention of the question was.

Thanks for taking the effort to submit a question, Tom. I know how much work goes into it, and I appreciate the effort. I hope my harsh comments won't keep you from submitting more questions.


I attach my test script, where I was attempting to confirm the validity of my 10-year-old slip of paper, by looking at actual execution plans, and matching up the parts of T-SQL to the plan.
I avoided using 'logical' because the execution plan seemed to back up the sequence of evaluation. The plans show data being moved from right to left, joined, filtered, sorted, etc. This is my explanation for the use of internal sequence of evaluation.

Perhaps these plans only represent the logical way a query is handled, and don't really reflect the true sequence of processing? If so then can we ever truely know whats going on.

USE TEMPDB;
GO

CREATE TABLE Items (
pKey INT NOT NULL,
Created DateTime,
ID char(20),
Region INT,
fItemType INT );
CREATE TABLE ItemTypes (
pKey INT NOT NULL,
Description varchar(50),
CODE char(20) );

INSERT INTO ItemTypes
SELECT 1, 'Purchase Order', 'ORDER' UNION ALL
SELECT 2, 'Invoice Order', 'INVOICE';

INSERT INTO Items
SELECT 1, GetDate(), '381203', 1, 1 UNION ALL
SELECT 7, DATEADD(day,-1,GetDate()), '371203', 3, 1 UNION ALL
SELECT 8, DATEADD(day,-1,GetDate()), '371203', 3, 1 UNION ALL
SELECT 2, DATEADD(day,-2,GetDate()), '391203', 1, 1 UNION ALL
SELECT 3, DATEADD(hour,-1,GetDate()), '383203', 2, 2 UNION ALL
SELECT 4, DATEADD(day,-1,GetDate()), '385203', 2, 2 UNION ALL
SELECT 5, DATEADD(year,-1,GetDate()), '394203', 3, 2 UNION ALL
SELECT 6, DATEADD(month,-1,GetDate()), '340203', 3, 2 ;

-- Test Query - get the actual execution plan for this
-- remember to read from right to left
SELECT DISTINCT TOP(3) I.ID, COUNT(I.Region) AS RCount, T.Description, I.Created, T.CODE
FROM dbo.Items AS I
JOIN dbo.ItemTypes AS T ON I.fItemType = T.pKey
WHERE I.Created > '2009-12-10'
AND T.Description like '%Order'
GROUP BY T.CODE, I.ID, I.Region, T.Description, I.Created, T.CODE
HAVING COUNT(I.Region) <= 1
ORDER BY I.Created DESC, T.CODE


ALTER TABLE dbo.Items
ADD PRIMARY KEY CLUSTERED (pKey) ;
ALTER TABLE dbo.ItemTypes
ADD PRIMARY KEY CLUSTERED (pKey);
ALTER TABLE dbo.Items
WITH CHECK ADD FOREIGN KEY (fItemType) REFERENCES dbo.ItemTypes(pKey);

-- try again with clustered & foreign keys (doesn't make a difference to the processing order
SELECT DISTINCT TOP(3) I.ID, COUNT(I.Region) AS RCount, T.Description, I.Created, T.CODE
FROM dbo.Items AS I
JOIN dbo.ItemTypes AS T ON I.fItemType = T.pKey
WHERE I.Created > '2009-12-10'
AND T.Description like '%Order'
GROUP BY T.CODE, I.ID, I.Region, T.Description, I.Created, T.CODE
HAVING COUNT(I.Region) <= 1
ORDER BY I.Created DESC, T.CODE


-- clean up
DROP TABLE TempDb.dbo.Items
DROP TABLE TempDB.dbo.ItemTypes


Post #917770
Posted Friday, May 7, 2010 4:34 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
Tom Brown (5/7/2010)
Thanks Paul for the tip on Itzik's book - I'll be visiting Amazon soon I think (or should I wait for the R2 edition?)

AFAIK he's not releasing an R2 update. It is an excellent book - very dense, very technical in places, but if you love that sort of thing...you'll love it




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #917780
Posted Friday, May 7, 2010 4:38 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
Hugo Kornelis (5/7/2010)
I would have liked the question (a lot!) if the question had asked for the logical processing order. But the question specificallly asked aboout the sequence internally used, and that threw me off completely.

Hmmm it seems my brain substituted the word 'internally' for 'logically' without conscious intervention - I had to go back and re-read the question to see that you are right about this. You have a good point.

I still think it's a great question though




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #917783
Posted Friday, May 7, 2010 4:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:18 PM
Points: 6,048, Visits: 8,330
Tom Brown (5/7/2010)
I attach my test script, where I was attempting to confirm the validity of my 10-year-old slip of paper, by looking at actual execution plans, and matching up the parts of T-SQL to the plan.
I avoided using 'logical' because the execution plan seemed to back up the sequence of evaluation. The plans show data being moved from right to left, joined, filtered, sorted, etc. This is my explanation for the use of internal sequence of evaluation.

Perhaps these plans only represent the logical way a query is handled, and don't really reflect the true sequence of processing? If so then can we ever truely know whats going on.


Hi Tom,

The execution plan represents the actual processing order, you looked at the right thing. But you made two mistakes. One is not looking good enough, the other one is assuming that, if it holds for this example, it always holds.

Run the example again. Check the execution plans. This time, hover your mouse over any of the two table scan operators in the first query, or any of the two clustered index scan operators in the second query. Check what is listed as "Predicate". You will see that the WHERE clause has been pushed up; the Query Optimizer has decided to filter rows based on the WHERE clause because that reduces the query cost without affecting the results.

Of course, there might well be situations where the actual internally used order matches the logical processing order exactly. If there are no cheaper alternatives, that is what SQL Server will do. My point is that this will not always happen (and, in fact, almost never happens).



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #917792
Posted Friday, May 7, 2010 4:56 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 7:48 AM
Points: 1,221, Visits: 1,433
Thanks Hugo.

I'd never noticed those 'Predicate' parts of the plan before. Just goes to show, no matter how closely you investigate something, there is always more to learn.
Post #917797
Posted Friday, May 7, 2010 5:30 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 6:55 AM
Points: 880, Visits: 876
The key for me getting this right was starting at the end. I knew it had to end DISTINCT, ORDER BY, COUNT -- fortunately there was only one answer with that ending!

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown
Post #917816
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse