Anyone knew operator *=?
-
please explain the usage, thanks.
it can not be found in BOL, even impossible to search the internet for information. But I do see code using it.
-
I think it is left outer join. I may be wrong
Kindest Regards,Amit Lohia
-
Yes, this is a non-ANSI version of a left outer join.
=* would be right outer join.
-
thanks, guys. I guessed so.
However, in my case, the performance difference is huge -- when I use Left join to replace it, an immediate execution becomes 6 minutes!
Interesting...
-
Look at the where clause. Is there a clause for the left-joined table?
like this...
select blah
from table1 t1, table2 t2
where
t1.pk *= t2.fk
and
t2.val1 = @value
If so, try this:
select blah
from table1 t1
Left outer join table2 t2
on
t1.pk = t2.fk
and
t2.val1 = @value
-
Yes, Pam. I did so.
Looks like SQL server has a 'short-cut' optimization for '*=' than Left outer join, or there is something we don't know?
-
John.
Could you post your actual query? Maybe something else is wrong that can be fixed to return your performance.
-
I doubt it.
What happens if you re-run the left outer join query? It could be a statistics / query optimization issue. What do the two query plans look like?
-
sorry, I'm not going to post my actual query, Jeff.
In my query, there are 2 tables, 1 is from linked server, no other special.
And Pam, re-run won't get better performance. And execution plans are almost same, but Left join has smaller data-set in each step...
Results are same.This article might got some points:*= sometime will ignore some selection criteria
http://www.databasejournal.com/features/mssql/article.php/1438001
Perhaps just keep in mind that we have one more option when tuning performance.
-
Take note that Microsoft has said that they may not always support the use of =* and *=.
-SQLBill
-
You really might want to rethink whether to post that query or not, even if you need to change column names for some reason. I'm guessing you have something in your WHERE clause causing the problem, such as testing if something IS Null. With the old style syntax, the WHERE clause is tested before the join, whereas in the new syntax, the JOIN logic happens first. This can result in vastly different results under certain circumstances, and that likely means that you're not getting the actual data you should be getting.
Are you sure you don't want to post your query, or at least the WHERE clause?
-
Well if you are not going to post the information requested then you are on your own to try and fix the problem. No one here will be able to provide concrete solution based upon theoretcial table layouts and select statements.
I've written many queries and when properly written, LEFT OUTER JOIN always outperforms *= syntax, which as others have stated is non-standard and may not always be supported.
-
Link server may be the cause for slow performance
Kindest Regards,Amit Lohia
-
*= is deprecated because of ambiguity the operator can cause in some queries. Avoid *= operator, as it is not ANSI standard and will probably be dropped in the future.
-
Agreed.
MS has for many years now recommended that old legacy join syntaxes be replaced and rewritten in ANSI style. I wouldn't be too surprised if in the next version of SQL Server, legacy joins just won't work anymore. (or the next after that
) The point is, better to bite now and do the changes necessary when there is calm and quiet time for it, than wait to the last minute./Kenneth
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply