-2

Original Code:

select A.* ,isnull(c.name,'')
into #temp 
from A join B on A.id =B.id
left join c on a.id=c.id

Optimized Code:

Select a.* ,
'' as name -- creating a dummy column which can be updated later
into #temp 
from  A join B on A.id =B.id

update #temp
set name =c.name
from #temp a
join c on a.id=c.id

Is it correct?

philipxy
  • 14,867
  • 6
  • 39
  • 83
dp1212
  • 69
  • 2
  • 8
  • 5
    Why do you think the latter is more optimised? – Thom A Jul 04 '22 at 13:57
  • could see a performance improvement as left join was taking time ...logically is it correct is what I doubt??.wanted to check if my understanding is correct... – dp1212 Jul 04 '22 at 14:15
  • 2
    I would suggest that the latter statement is slower, due to the need to perform to DML statements against the table `#temp`; if the `LEFT JOIN` is slow, that suggests a lack of indexing. Or, perhaps you want an `CROSS APPLY` (the queries aren't equivalent with a 1 to many relationship). – Thom A Jul 04 '22 at 14:16
  • Actually due to some reason indexing cannot be adopted ...could you please help me with the alternative exists statement ... pls – dp1212 Jul 04 '22 at 14:19
  • 1
    I wouldn't like to guess, with no sample data or expected results. Though, again, I would suggest that is the query is slow, then the problem is the indexing; start there. – Thom A Jul 04 '22 at 14:20
  • 3
    [How to Optimize Queries in a Database - The Basics](https://stackoverflow.com/q/3191623/3404097) [What are your most common sql optimizations?](https://stackoverflow.com/q/1332778/3404097) [When and why are database joins expensive?](https://stackoverflow.com/q/173726/3404097) [Re SQL "performance".](https://stackoverflow.com/a/24196511/3404097) [Tips for asking a good SQL question](https://meta.stackoverflow.com/q/271055/3404097) [Asking query performance questions](https://dba.meta.stackexchange.com/q/3034/43932) – philipxy Jul 04 '22 at 14:31
  • 1
    Are you doing this because you have heard that "left joins are slow"? That little tidbit of "received wisdom" is much too naive, and is likely to lead you down the wrong path rather than helping you to really understand optimization. Ignore it. – allmhuran Jul 04 '22 at 16:12

1 Answers1

1

Is it correct?

If every a.Id matches at most one c.Id, then yes, otherwise the first INSERT will insert more rows than the second.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67