May 29, 2012 at 2:58 am
I thought there is a concept of "short-circuiting" in sql server. Try the following.
SELECT 1 WHERE (1 = 1) OR (1 / 0 = 0)even if there is a division by zero still it works, because the second part never gets evaluated. Thats how probably all programming languages work to improve the performance.
based on this understanding I thought the second part will only be evaluated if we don't have a match for the first part.
I don't need to try the statement you gave as I know exact result even without running it.
It has nothing to do with "short-circuiting" concept. Try:
SELECT 1 WHERE (1 / 0 = 0) OR (1 = 1)
Based on your understanding it should return error, but it doesn't The first part is never evaluated!
Also would like to mention that is definitely not all programming languages implement "short-circuiting" concept to improve the performance. As you can see from above example SQL goes even futher with that: it implements "check the simplest thing first" concept 😀
Actually, my example may not always work. It is all depend which road the sql optimiser will take.
Some time you can see "short-circuiting" effect even where not expected, some time it doesn't work at all. For example: there is possibility to have "division by zero" error in the following query:
SELECT Col1/Col2 FROM Table WHERE Col2 <> 0.
SQL Server optimiser may try to calculated everything first before applying the filter and that will result into error!
And again, all the above has nothing to do how JOIN's work. You need to read BoL to understand what the JOIN's do ion SQL, there is no much point to guess...
May 29, 2012 at 3:13 am
Thanks for the reply Eugene. It was quite informative. Now I can say, I understand the joins some what better. Thanks.
Any ways, if we go back to the original question of implementing the priority join and I have 4-5 OR conditions in it, then how I can do that in a simpler way?
Regards,
Pravasis
May 29, 2012 at 3:22 am
Could you please specify all conditions you have
May 29, 2012 at 3:26 am
Eugene Elutin (5/29/2012)
Could you please specify all conditions you have
expanding the same sql
select s.id, s.name1,s.name2,s.name3,s.name4,d.[desc]
from dbo.Test_Join_Source s
inner join dbo.Test_Join_Destination d
on (s.name1=d.name1 or s.name2=d.name2 or s.name3=d.name3 or s.name4=d.name4)
Regards,
Pravasis
May 29, 2012 at 3:31 am
and what is the priority of your rules? which record from destination table you want to select first?
the one with minimum id where any of the names match or the one where most of names matches?
May 29, 2012 at 3:46 am
Eugene Elutin (5/29/2012)
and what is the priority of your rules? which record from destination table you want to select first?the one with minimum id where any of the names match or the one where most of names matches?
It should start joining from name1 to name4. If it finds a match for any of names then it should pick the desc for the first available record in destination table and go to the next record in the source table.
Sounds like cursor or may be a function.
Regards,
Pravasis
May 29, 2012 at 4:22 am
Pravasis (5/29/2012)
Eugene Elutin (5/29/2012)
and what is the priority of your rules? which record from destination table you want to select first?the one with minimum id where any of the names match or the one where most of names matches?
It should start joining from name1 to name4. If it finds a match for any of names then it should pick the desc for the first available record in destination table and go to the next record in the source table.
Sounds like cursor or may be a function.
How to determine "the first available record in destination table"? Is it the one with minimal ID?
It's definitely not a candidate for cursor or function. It can be easily done in SET-BASED operation (and that is exactly T-SQL language was designed for)
You can try the following (record with minimal ID's will be picked up from destination table)
select s.id, s.name1,s.name2,
(select top 1 d.[desc]
from dbo.Test_Join_Destination2 d
where (d.name1 = s.name1)
or (d.name2 = s.name2)
or (d.name3 = s.name3)
or (d.name4 = s.name4)
order by d.id) as [desc]
from dbo.Test_Join_Source2 s
May 29, 2012 at 4:41 am
Great, thats is probably the best solution where in we can limit the number of records as that of source table and get the desired result. Thanks Eugene.
I guess this would evaluate record-by-record, is not it?
If it is, is there any way we can do it in SETS.
Any ways, thanks.
Regards,
Pravasis
May 29, 2012 at 5:03 am
It is done in SETS, using correlated sub-query.
There is another way:
;with allrules
as
(
select s.id, s.name1,s.name2,d.[DESC]
,d.id did
from dbo.Test_Join_Source2 s
join dbo.Test_Join_Destination2 d
on (s.name1=d.name1)
or (s.name2 = d.name2)
or (d.name3 = s.name3)
or (d.name4 = s.name4)
)
, mrule
AS
(
select id, MIN(did) as mdid
from allrules
group by id
)
select a.id, a.name1,a.name2, a.[DESC]
from allrules a
join mrule m on m.id = a.id and m.mdid = a.did
Run both to see difference in performance.
May 29, 2012 at 5:06 am
or a bit smaller:
;with allrules
as
(
select s.id, min (d.id) mdid
from dbo.Test_Join_Source2 s
join dbo.Test_Join_Destination2 d
on (s.name1=d.name1)
or (s.name2 = d.name2)
or (d.name3 = s.name3)
or (d.name4 = s.name4)
group by s.id
)
select s.id, s.name1, s.name2, d.[DESC]
from dbo.Test_Join_Source2 s
join allrules a
on a.id = s.id
join dbo.Test_Join_Destination2 d
on d.id = a.mdid
May 29, 2012 at 6:32 am
Thanks you all.
That was really helpful.Appreciate it.
Regards,
Pravasis
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply