May 24, 2012 at 1:48 pm
Gullimeel (5/24/2012)
When the query was access the tables directly, they needed to select only the records where ISDELETED was NULL or 0, correct? Therefor the isnull(ISDELETED,0) = 0 for each table in the query.
Enter the views which will only have those records where ISDELETED is null or 0. No more need for the ISNULL(ISDELETED,0) = 0 for each table, right? There will be NO records in any of the views where ISDELETED = 1.
Still lost?
Logically they are doing an inner join by introducing the column form right table in where clause which actually is wrong.
There will be NO records in any of the views where ISDELETED = 1.
This is culprit.This makes the isdeleted in where clause a null value. And where clause is true when either value is 0 or null.. But in case of table this value will be 1 and thus where clause is false and no records..
You are missing the point. The OP has stated that all the tables in the query are being replaced with views built over the tables. These views ELIMINATE all records where ISDELETED = 1.
This means that there will be NO records in the result set of the query where ISDELETED = 1 for any of the tables and, as the OP stated, all the ISNULL(ISDELETED,0) = 0 references will be eliminated.
There will be no direct table access, so how is an ISDELETED = 1 going to cause a problem?
May 24, 2012 at 1:53 pm
Then for the record he is having issue has isdeleted as 1..
with view it will return row..but with table.. just no row...
Simple thing is that when he is using the isnull(isdeleted,0) in where clause he is using inner join..with view he is using pure left join...Thus these two things are not equivalent.
So the resultset will be different unless all his record are unique in table and has value of either 0 or null.
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 24, 2012 at 1:54 pm
Lynn Pettis (5/24/2012)
CptCrusty1 (5/24/2012)
Lynn Pettis (5/24/2012)
Actually, you didn't eliminate the NOLOCKS hint, you just hide them in the view.What is with the hint any way? You do know the issues with using the hint, correct?
Whatchoo talken 'bout Willis?
The database masters tell me that there are issues with NOT using the NOLOCKS... It's there cuz I was told to use it.. Actually, I didn't write this one.
My google-fu is broken. I have sought out help from others to help explain this better than I.
I can help Lynn...
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 24, 2012 at 1:55 pm
I'm getting really lost here.
I don't use the ISDELETED predicates in the where clause when I use the view. It's in the view so I don't need to.
May 24, 2012 at 1:56 pm
CptCrusty1 (5/24/2012)
Lynn Pettis (5/24/2012)
Actually, you didn't eliminate the NOLOCKS hint, you just hide them in the view.What is with the hint any way? You do know the issues with using the hint, correct?
Whatchoo talken 'bout Willis?
The database masters tell me that there are issues with NOT using the NOLOCKS... It's there cuz I was told to use it.. Actually, I didn't write this one.
Tell your "masters" to read the article I posted. NOLOCK is by no means a go fast pill. It is a chance to introduce duplicate, missing data and several other incredibly quirky and difficult to debug issues. Talk to your masters and suggest isolation instead.
_______________________________________________________________
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 24, 2012 at 2:00 pm
I'm getting really lost here.
I don't use the ISDELETED predicates in the where clause when I use the view. It's in the view so I don't need to.
these two are not equivalent.. Ifyou use table use isdeledt as part of join rather than whereclause otherwise you are comapring the output of left join with inner join...
drop table a
go
drop table b
go
drop view myvwab
go
create table a ( id int)
go
create table b (id int,isdeleted int null)
go
insert into a select 1
go
insert into a select 2
go
insert into b select 1,1
go
create view myvwab
as
select * from b where ISNULL(isdeleted,0) = 0
go
select * from b where ISNULL(isdeleted,0) = 0
select * from myvwab
go
select * from a left join b on a.id = b.id where ISNULL(isdeleted,0) = 0 ---inner not a left
select * from a left join myvwab b on a.id = b.id --it is left
select * from a left join b on a.id = b.id and ISNULL(isdeleted,0) = 0 -- nowit is left
go
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 24, 2012 at 2:05 pm
HOLY Recursive Statement Batman!!
I just noticed that the Joins don't have the NOLOCK hint, but the views all DO have the no lock hint.... so by switching to the View, I'm adding the NOLOCK hint when it wasn't in the original query with the table.....
May 24, 2012 at 2:15 pm
HOLY Recursive Statement Batman!!
I just noticed that the Joins don't have the NOLOCK hint, but the views all DO have the no lock hint.... so by switching to the View, I'm adding the NOLOCK hint when it wasn't in the original query with the table.....
I am not sure that is the root cause here. Thiugh it could create issue by reading the uncommitted records.So if you do not need to read uncommitted code.Do not use view.
But your issue is that by using view you are using left join and with table you were using inner join.Thus you have to fix this as well.Otherwise you can have wrong output.
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 24, 2012 at 2:20 pm
I'm not going to say your wrong. But I will say that I think the problem has been twisted and trounced that it's not even the same problem anymore....
Select *
frm TABLE with (NOLOCK)
where ISNULL(ISDELETED,0)=0
If that query gives me 10 records.
Then
CREATE VIEW v_TABLE
as
Select *
frm TABLE with (NOLOCK)
where ISNULL(ISDELETED,0)=0
Select *
from v_Table
Should also give me 10 records, right?
May 24, 2012 at 2:22 pm
CptCrusty1 (5/24/2012)
HOLY Recursive Statement Batman!!I just noticed that the Joins don't have the NOLOCK hint, but the views all DO have the no lock hint.... so by switching to the View, I'm adding the NOLOCK hint when it wasn't in the original query with the table.....
Ummm, I thought I had mentioned that a efw posts back....
May 24, 2012 at 2:24 pm
CptCrusty1 (5/24/2012)
I'm not going to say your wrong. But I will say that I think the problem has been twisted and trounced that it's not even the same problem anymore....Select *
frm TABLE with (NOLOCK)
where ISNULL(ISDELETED,0)=0
If that query gives me 10 records.
Then
CREATE VIEW v_TABLE
as
Select *
frm TABLE with (NOLOCK)
where ISNULL(ISDELETED,0)=0
Select *
from v_Table
Should also give me 10 records, right?
Possibly. Please remember that both the direct query and view have the possibility of reading uncommitted rows from the table. But technically, yes.
May 24, 2012 at 2:25 pm
Yes... you did.. but honestly, the posts were coming in so quickly, I coudldn't keep up...
I tweaked the query to take that into account with no change...
26666 records vs. 26672... Same 6. I'm eliminating 90% of the code now so I can focus on the tables that the joins are using and see if I can narrow it down to specific columns, values... etc.
May 24, 2012 at 10:01 pm
I'm not going to say your wrong. But I will say that I think the problem has been twisted and trounced that it's not even the same problem anymore....
Select *
frm TABLE with (NOLOCK)
where ISNULL(ISDELETED,0)=0
If that query gives me 10 records.
Then
CREATE VIEW v_TABLE
as
Select *
frm TABLE with (NOLOCK)
where ISNULL(ISDELETED,0)=0
Select *
from v_Table
Should also give me 10 records, right?
These both are same and but at times they might not give you same result..It all depends what is being changed and at what time in the table.
26666 records vs. 26672... Same 6. I'm eliminating 90% of the code now so I can focus on the tables that the joins are using and see if I can narrow it down to specific columns, values... etc.
If nolock were issue you would have never gotten the difference of 6 records (that too same records) everytime unless same 6 records were getting changed when you ran the query with view which i doubt is the case here.
If the diff in your resultset is same everytime.Then issue is relate dot left join in this case.
If the difference in resultset is different everytime and sometime results are same then nolock is causing the issue.
The same issue could happen with other tables/views as well in you query .To make them consistent do following.
1) For query using tables remove the where clause and put those condition in their respective left join.
2) Use the no lock consistently with botha tbale and view or do not use it at all depending on your requirement.
Can you run a simple query
Select *
frm TABLE with (NOLOCK)
where ISNULL(ISDELETED,0) <> 0
The query is nested too much in left join.. Thus for record which are not coming...try to check which tables have the data and which one doesnt have..
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 9:32 am
SWEET!! OK.. Now we're getting somewhere...
Here's what I did. I put together some DDL so that I can see if the issue duplicates... and it does. It dupliates on an entirley manufactured dataset unrelated to the tables and views I'm currently using... take a look and tell me what you think.
BEGIN
create table dbo.table_1 (
IDINT not null,
CHILD_IDINT not null,
ISDELETEDINT null);
insert into dbo.table_1 values(1,10,0);
insert into dbo.table_1 values(2,20,0);
insert into dbo.table_1 values(3,30,NULL);
insert into dbo.table_1 values(4,40,NULL);
insert into dbo.table_1 values(5,50,NULL);
END;
select *
from dbo.table_1;
BEGIN
create table dbo.table_2 (
IDINT not null,
ISDELETEDINT null);
insert into dbo.table_2 values(10,1);
insert into dbo.table_2 values(20,1);
insert into dbo.table_2 values(30,1);
insert into dbo.table_2 values(40,1);
insert into dbo.table_2 values(50,1);
END;
select *
from dbo.table_2;
create view dbo.v_table_1 as
select *
from dbo.table_1 with (nolock)
where ISNULL(isdeleted,0)=0;
create view dbo.v_table_2 as
select *
from dbo.table_1 with (nolock)
where ISNULL(isdeleted,0)=0;
-------------------
-------------------
--- MONEY SHOT ---
-------------------
-------------------
--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
--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
The results of the Table driven version should give me 5 records from the table on the left with 5 NULL values for the column coming from the table on the right side of the join, but I get nothing.
The view gives me the answer I'm looking for. Are you able to duplicate this issue using the script I provided? I'm beginning to think I'm missing something fundamental. In all the years I've been writing code, I think I should be able to figure this out, but I'm going to loose my mind until this makes sense to me.
Thanks
Crusty.
May 25, 2012 at 10:12 am
Slight problem with your views (or at least the code you posted). Both views are based on table1.
Viewing 15 posts - 31 through 45 (of 67 total)
You must be logged in to reply to this topic. Login to reply