0

I have a code which basically implements the below logic

select something
from few tables
where not exists (select query with some logic, say L1)

union all

select query with logic L1

As you can see, the select query with logic L1 is being run twice. It is quite a big logic and could take some time to run. Is there anyway to re-format the code to make the execution better?

pallupz
  • 793
  • 3
  • 9
  • 25
  • Sure. Run that query and store it in a [temp table](http://stackoverflow.com/a/64891/703644) – TheNorthWes May 02 '16 at 14:41
  • CTE qill be more efficient, imo – Sébastien Pertus May 02 '16 at 14:42
  • @AdmiralAdama Can I do that with just DML access? – pallupz May 02 '16 at 14:44
  • 1
    I would note that the compiler can do a LOT of analysis to determine the best approach for your query, and it may come up with a plan that is very different that how the query actually _looks_ in SQL. Just reorganizing it does not _necessarily_ mean that the plan will be better. I would run it, see if it is _actually_ too slow for your needs, and if it is, look at the _query_ plan to see where all the time is being spent, then attack _that_. – D Stanley May 02 '16 at 14:46
  • The structure of your query doesn't really make sense to me. In a `union all`, each subquery needs to return the same thing. Can you try to express the logic you intend in (simple) terms? – Gordon Linoff May 02 '16 at 14:48
  • @GordonLinoff Both queries have a custom field - let's say Status. If an ID does not exist in Logic L1, that should give Status as "Not in Logic L1". If it is there, that ID should not show up at the first query. Instead, it should show up with Status "Present in Logic L1" – pallupz May 02 '16 at 14:55
  • 1
    It's rather difficult to make any suggestions without seeing an actual query, a query plan, etc. It's much easier to help when there's a minimal example with sample data, expected output, and for performance issues a query plan. http://stackoverflow.com/help/how-to-ask – Tom H May 02 '16 at 15:07

2 Answers2

1

you can use a CTE. https://msdn.microsoft.com/en-us/library/ms175972.aspx For instance, Something like that;

;With L1 as  (Select query with some logic, say L1)
Select Something 
from few tables
where not exists (select * from L1)
union all
Select * from L1
Sébastien Pertus
  • 655
  • 4
  • 11
0

Why not put it into a single statement? Not sure what the structure is but here is the idea:

Select 
Something1
,Something2
,Something3
,Case When L1 Is Not Null 'Case1'
            Else 'Case2'
        End AS Status
FROM Some Table or view
craig.white
  • 401
  • 2
  • 8