May 25, 2012 at 10:19 am
After fixing your view, and looking a bit closer at the data in this sample code, your problem may also be in the how you are joining the data together.
If you look at the following you will find that these queries return the same results:
SELECT T1.ID, T1.CHILD_ID, T2.ID
FROM DBO.table_1 T1 WITH (NOLOCK)
LEFT OUTER JOIN DBO.table_2 T2 WITH (NOLOCK)
ON T1.CHILD_ID = T2.ID AND
ISNULL(T1.ISDELETED,0)=0
AND ISNULL(T2.ISDELETED,0)=0
--View driven...
SELECT T1.ID, T1.CHILD_ID, T2.ID
FROM DBO.V_table_1 T1
LEFT OUTER JOIN DBO.v_table_2 T2
ON T1.CHILD_ID = T2.ID
GO
May 25, 2012 at 10:36 am
You asked about NOLOCK, here is one link. I'll keep looking for others.
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
May 25, 2012 at 10:39 am
Lynn Pettis (5/25/2012)
Slight problem with your views (or at least the code you posted). Both views are based on table1.
in the famous words of Homer Simpson...
D'OH! Ok... let me fix that stupid error...
grrrrrr....
May 25, 2012 at 10:45 am
Lynn Pettis (5/25/2012)
You asked about NOLOCK, here is one link. I'll keep looking for others.http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
I posted Gail's favorite yesterday in case you missed it.
http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 25, 2012 at 10:49 am
Sean Lange (5/25/2012)
Lynn Pettis (5/25/2012)
You asked about NOLOCK, here is one link. I'll keep looking for others.http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
I posted Gail's favorite yesterday in case you missed it.
http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
Yes, I think I missed that, thank you for reposting it again.
May 25, 2012 at 10:51 am
Lynn, Did you look at the video link I posted?
May 25, 2012 at 10:53 am
CptCrusty1 (5/25/2012)
Lynn, Did you look at the video link I posted?
Must have missed that one, too.
May 25, 2012 at 11:00 am
Here ya go....
Since YODA is your avetar... you might like this....
May 25, 2012 at 11:02 am
Gents....
It is with a sad and heavy heart that I admit I screwed up my view... LOL.. I fixed it and they give the same result now. However, that being said, the original problem still exists, it's still not giving the same result.
I enlisted the help of our dba who immediatley indicated some issue with the way SQL Server is handling the view vs. the table in the Execution sequence... He thinks there might be a deeper issue related to our version of SQL Server... So far, all agree, the 2 queries should be returning the same results. The DDL script (with the corrected view) is proof of that.
I don't think I'll get a solution out of him before he runs off for Labor day.. BUTT...
Anyone know of some wonky behavior in SQL Server 2k8 that might cause this? What about a table issue?
Crusty
May 25, 2012 at 11:07 am
I don't think I'll get a solution out of him before he runs off for Labor day.. BUTT...
Well I hope it doesn't take that long...Labor Day is not until September. This is Memorial Day weekend. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 25, 2012 at 11:09 am
OMG
I am so not on my game these last couple days... Do men get MENapause?
May 25, 2012 at 11:11 am
CptCrusty1 (5/25/2012)
OMGI am so not on my game these last couple days... Do men get MENapause?
Well we do get MENtal pause. I have often found that this is the result of too little alcohol.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 25, 2012 at 11:31 am
After fixing your view, and looking a bit closer at the data in this sample code, your problem may also be in the how you are joining the data together.
If you look at the following you will find that these queries return the same results:
SELECT T1.ID, T1.CHILD_ID, T2.ID
FROM DBO.table_1 T1 WITH (NOLOCK)
LEFT OUTER JOIN DBO.table_2 T2 WITH (NOLOCK)
ON T1.CHILD_ID = T2.ID AND
ISNULL(T1.ISDELETED,0)=0
AND ISNULL(T2.ISDELETED,0)=0
--View driven...
SELECT T1.ID, T1.CHILD_ID, T2.ID
FROM DBO.V_table_1 T1
LEFT OUTER JOIN DBO.v_table_2 T2
ON T1.CHILD_ID = T2.ID
GO
This is waht i was saying since yesterday that put the where clause condition in join condirtion to make both work the same way.
Gents....
It is with a sad and heavy heart that I admit I screwed up my view... LOL.. I fixed it and they give the same result now. However, that being said, the original problem still exists, it's still not giving the same result.
I enlisted the help of our dba who immediatley indicated some issue with the way SQL Server is handling the view vs. the table in the Execution sequence... He thinks there might be a deeper issue related to our version of SQL Server... So far, all agree, the 2 queries should be returning the same results. The DDL script (with the corrected view) is proof of that.
I don't think I'll get a solution out of him before he runs off for Labor day.. BUTT...
Anyone know of some wonky behavior in SQL Server 2k8 that might cause this? What about a table issue?
THis is not issue with server it is issue with query. Query using the table is not equivalent to query using views and thus you will not get the same results.
If you want same results. Put all your where clause conditions in the corresponding join clause after on and then you will get the same results.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
May 25, 2012 at 11:48 am
OK... dba says something else is going on, so I'm going to believe him.. BUT.. he also said there could be an issue with the execution sequence which is what this is... now the part I don't understand...
Why are these two returning different results.....
--Table Driven version.
SELECT T1.ID, T1.CHILD_ID, T2.ID
FROM DBO.table_1 T1 WITH (NOLOCK)
LEFT OUTER JOIN DBO.table_2 T2 WITH (NOLOCK)
ON T1.CHILD_ID = T2.ID
WHERE ISNULL(T1.ISDELETED,0)=0
AND ISNULL(T2.ISDELETED,0)=0
SELECT T1.ID, T1.CHILD_ID, T2.ID
FROM DBO.table_1 T1 WITH (NOLOCK)
LEFT OUTER JOIN DBO.table_2 T2 WITH (NOLOCK)
ON T1.CHILD_ID = T2.ID AND
ISNULL(T1.ISDELETED,0)=0
AND ISNULL(T2.ISDELETED,0)=0
May 25, 2012 at 11:56 am
Correction, not the plan, but the Query Execution....Logical Query Processing Plan...
Viewing 15 posts - 46 through 60 (of 67 total)
You must be logged in to reply to this topic. Login to reply