May 24, 2012 at 1:08 pm
This is the problem in where clause
AND ISNULL(CHILD.ISDELETED,0) = 0
Also why to use left join when you are using the where clause....
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:10 pm
Gullimeel (5/24/2012)
This is the problem in where clauseAND ISNULL(CHILD.ISDELETED,0) = 0
'splain, Lucy...
May 24, 2012 at 1:12 pm
CptCrusty1 (5/24/2012)
Gullimeel (5/24/2012)
This is the problem in where clauseAND ISNULL(CHILD.ISDELETED,0) = 0
'splain, Lucy...
Same thing I was going to ask. In the view this column will contain either a null or a 0. Since the view is on the right side of a LEFT OUTER JOIN, this column will again have a null (from the view or lack of a matching row) or a 0.
May 24, 2012 at 1:13 pm
Something else.... The ISDELETED predicates are irrelevent in that all the tables are repointed to their corresponding view. Thus the ISDELETED is already in the statement.
May 24, 2012 at 1:15 pm
CptCrusty1 (5/24/2012)
Something else.... The ISDELETED predicates are irrelevent in that all the tables are repointed to their corresponding view. Thus the ISDELETED is already in the statement.
My question at this point then is why are they still in the WHERE clause if they are irrelevent. Makes checking them again a waste of time.
May 24, 2012 at 1:18 pm
Gullimeel (5/24/2012)
Also why to use left join when you are using the where clause....
Normally, the query uses the table... the ISDELETED column shows us that the record is no longer valid. We don't throw it away. A user can make changes to the record, but what actually happens is the old record is Marked as deleted, and a new record is inserted. The value of the joined column will remain the same, but if the ISDELETED flag isn't eliminated in the WHERE clause, you'll get 2 results.
May 24, 2012 at 1:19 pm
Becuase I was switching back and forth between the view and the table. For the production version, once this annoying issue is resolved, the Where clause predicates referencing the ISDELETED field will go away. They aren't necessary.
May 24, 2012 at 1:20 pm
suppose one of your record has isdeleted = 1
when table will be joined... suppose joining column is presnet in mani table as well in this table. Then isdeleted will be 1 in where clause and the isnull(isdeleted,0) = 0 will retrun false...
so no record is shown..
On the other hand..
when you join with view... then join will have the extra condition isnull(ideleted,0) = 0 as isdeleted is 1 this condition will be false..Thus join condition is false..Thus isdeleted in where clause have a null value which means where caluse will be true..
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
select * from a left join myvwab b on a.id = b.id where ISNULL(isdeleted,0) = 0
go
you can see above is reason why you are getting worng results
be carful with left joins ...
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:29 pm
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
select * from a left join myvwab b on a.id = b.id where ISNULL(isdeleted,0) = 0
go
you can see above is reason why you are getting worng results
be carful with left joins ...
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:29 pm
Gullimeel (5/24/2012)
suppose one of your record has isdeleted = 1when table will be joined... then it is decided by the joining column.Suppsoe it exists in main table as well.. Then isdeleted will be 1 in where clause and the isnull(isdeleted,0) = 0 will retrun false...
so no record is shown..
On the other hand..
when you join with view... then join will have the extar copndidtion isnull(ideleted,0) = 0 as isdeleted is 1 this condition will be false..Thus isdeleted in where clause have a null value which means where caluse will be true..
Okay, lost. The view will not have any records where ISDELETED = 1. This query is having all table references changed to use views built over the tables. These views filter out all the ISDELETED = 1 records.
OP, if ISDELETED can only have null, 0, or 1; why not just use ISDELETED <> 1?
May 24, 2012 at 1:31 pm
Okay, lost. The view will not have any records where ISDELETED = 1. This query is having all table references changed to use views built over the tables. These views filter out all the ISDELETED = 1 records.
After reading that explaination..even I am lost..I created a small query and that will help in understanding... Whenevr you have issues related to not matching the data left join sually is the culprit 🙂
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:36 pm
Gullimeel (5/24/2012)
Okay, lost. The view will not have any records where ISDELETED = 1. This query is having all table references changed to use views built over the tables. These views filter out all the ISDELETED = 1 records.
After reading that esplianation ..even I am lost..I created a small query and that will help in understanding... Whenevr you have issues related to not matching the data left join sually is the culprit 🙂
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?
May 24, 2012 at 1:40 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.
My google-fu is broken. I have sought out help from others to help explain this better than I.
May 24, 2012 at 1:43 pm
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..
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:47 pm
The ISNULL(ISDELETED,0)=0 condition in the where clause is eliminated when the view is used, I don't even run it.
When I switch to the view, I comment out the where clause.
When I switch to the table, I uncomment the where clause.
Viewing 15 posts - 16 through 30 (of 67 total)
You must be logged in to reply to this topic. Login to reply