Searching a Table - If a result is found, don't search the next table

Question

I have a bit of a strange question in terms of a query I'm trying to produce.

Basically I need to look in about 14 different databases to see if a specific Contract number exists. If it does, I need it to return a Yes, but the only thing is, the query takes around 30 minutes each time because it's checking every DB for the same values (the contract number will only exist in one DB), and is only going to get higher as the days go on.

I'd like to change this (if possible) so that if a result is found in Table1, it doesn't need to search in Table2 or Table3 etc for that result. I don't know if this is possible, which is why I thought I'd ask the experts! ;)

Currently my query is:

select Id 
INTO #tmpContracts
from Contracts
where CreatedOn >= '2017-06-01'

select  Id,
        CASE WHEN EXISTS (select ch.CH_CODE from DimL1.dbo.CST_COSTHEADER ch where CAST(c.Id as varchar(255)) = CAST(ch.CH_CODE as varchar(255)) ) THEN 'Yes'
             WHEN EXISTS (select ch.CH_CODE from DimL2.dbo.CST_COSTHEADER ch where CAST(c.Id as varchar(255)) = CAST(ch.CH_CODE as varchar(255)) ) THEN 'Yes'
             WHEN EXISTS (select ch.CH_CODE from DimL3.dbo.CST_COSTHEADER ch where CAST(c.Id as varchar(255)) = CAST(ch.CH_CODE as varchar(255)) ) THEN 'Yes'
             WHEN EXISTS (select ch.CH_CODE from DimL4.dbo.CST_COSTHEADER ch where CAST(c.Id as varchar(255)) = CAST(ch.CH_CODE as varchar(255)) ) THEN 'Yes'
             WHEN EXISTS (select ch.CH_CODE from DimL5.dbo.CST_COSTHEADER ch where CAST(c.Id as varchar(255)) = CAST(ch.CH_CODE as varchar(255)) ) THEN 'Yes'
             WHEN EXISTS (select ch.CH_CODE from DimL6.dbo.CST_COSTHEADER ch where CAST(c.Id as varchar(255)) = CAST(ch.CH_CODE as varchar(255)) ) THEN 'Yes'
             WHEN EXISTS (select ch.CH_CODE from DimL7.dbo.CST_COSTHEADER ch where CAST(c.Id as varchar(255)) = CAST(ch.CH_CODE as varchar(255)) ) THEN 'Yes'
             WHEN EXISTS (select ch.CH_CODE from DimL8.dbo.CST_COSTHEADER ch where CAST(c.Id as varchar(255)) = CAST(ch.CH_CODE as varchar(255)) ) THEN 'Yes'
             WHEN EXISTS (select ch.CH_CODE from DimL9.dbo.CST_COSTHEADER ch where CAST(c.Id as varchar(255)) = CAST(ch.CH_CODE as varchar(255)) ) THEN 'Yes'
             WHEN EXISTS (select ch.CH_CODE from DimL10.dbo.CST_COSTHEADER ch where CAST(c.Id as varchar(255)) = CAST(ch.CH_CODE as varchar(255)) ) THEN 'Yes'
             WHEN EXISTS (select ch.CH_CODE from DimL11.dbo.CST_COSTHEADER ch where CAST(c.Id as varchar(255)) = CAST(ch.CH_CODE as varchar(255)) ) THEN 'Yes'
             WHEN EXISTS (select ch.CH_CODE from DimL12.dbo.CST_COSTHEADER ch where CAST(c.Id as varchar(255)) = CAST(ch.CH_CODE as varchar(255)) ) THEN 'Yes'
             WHEN EXISTS (select ch.CH_CODE from DimL13.dbo.CST_COSTHEADER ch where CAST(c.Id as varchar(255)) = CAST(ch.CH_CODE as varchar(255)) ) THEN 'Yes'
             WHEN EXISTS (select ch.CH_CODE from DimL14.dbo.CST_COSTHEADER ch where CAST(c.Id as varchar(255)) = CAST(ch.CH_CODE as varchar(255)) ) THEN 'Yes'
        ELSE 'No' END 'ProjectCodeExists'
from #tmpContracts c

drop table tmpContracts

Is this possible to do in SQL? If so, this would hugely decrease the execution time.

Thanks for any replies in advance :)

Answer 1

Subqueries from your query run for each rows. I think it uses a lot of time and resources.

Try to use loop and do update for group of rows which not found earlier.

select
  Id,
  0 ProjectCodeExists
into #tmpContracts
from Contracts
where CreatedOn >= '2017-06-01'


declare @dbNum int=1
declare @query varchar(max)

while @dbNum<=14
begin

  if exists(select * from #tmpContracts where ProjectCodeExists=0)
  begin
    set @query='update c set c.ProjectCodeExists=1
      from DimL'+cast(@dbNum as varchar(2))+'.dbo.CST_COSTHEADER ch
      join #tmpContracts c on CAST(c.Id as varchar(255)) = CAST(ch.CH_CODE as varchar(255))
      where c.ProjectCodeExists=0'

    print @query

    exec(@query)
  end

  set @dbNum=@dbNum+1
end

select *
from #tmpContracts

drop table #tmpContracts

My script does the following

if exists(select * from #tmpContracts where ProjectCodeExists=0)
  update c set c.ProjectCodeExists=1
  from DimL1.dbo.CST_COSTHEADER ch
  join #tmpContracts c on CAST(c.Id as varchar(255)) = CAST(ch.CH_CODE as varchar(255))
  where c.ProjectCodeExists=0

if exists(select * from #tmpContracts where ProjectCodeExists=0)
  update c set c.ProjectCodeExists=1
  from DimL2.dbo.CST_COSTHEADER ch
  join #tmpContracts c on CAST(c.Id as varchar(255)) = CAST(ch.CH_CODE as varchar(255))
  where c.ProjectCodeExists=0

if exists(select * from #tmpContracts where ProjectCodeExists=0)
  update c set c.ProjectCodeExists=1
  from DimL3.dbo.CST_COSTHEADER ch
  join #tmpContracts c on CAST(c.Id as varchar(255)) = CAST(ch.CH_CODE as varchar(255))
  where c.ProjectCodeExists=0

...

if exists(select * from #tmpContracts where ProjectCodeExists=0)
  update c set c.ProjectCodeExists=1
  from DimL14.dbo.CST_COSTHEADER ch
  join #tmpContracts c on CAST(c.Id as varchar(255)) = CAST(ch.CH_CODE as varchar(255))
  where c.ProjectCodeExists=0

For example. If #tmpContracts contains 100 rows then subqueries from your variant of query can run 1400 (100*14) times.

In my variant uses only 14 queries to other DBs.

Answer 2

I think this modification will boost your query:

select  Id,
    CASE WHEN EXISTS (select ch.CH_CODE from DimL1.dbo.CST_COSTHEADER ch where CAST(c.Id as varchar(255)) = CAST(ch.CH_CODE as varchar(255)) ) THEN 'Yes' ELSE
    CASE WHEN EXISTS (select ch.CH_CODE from DimL2.dbo.CST_COSTHEADER ch where CAST(c.Id as varchar(255)) = CAST(ch.CH_CODE as varchar(255)) ) THEN 'Yes' ELSE
    CASE WHEN EXISTS (select ch.CH_CODE from DimL3.dbo.CST_COSTHEADER ch where CAST(c.Id as varchar(255)) = CAST(ch.CH_CODE as varchar(255)) ) THEN 'Yes' ELSE
    CASE WHEN EXISTS (select ch.CH_CODE from DimL4.dbo.CST_COSTHEADER ch where CAST(c.Id as varchar(255)) = CAST(ch.CH_CODE as varchar(255)) ) THEN 'Yes' ELSE
    CASE WHEN EXISTS (select ch.CH_CODE from DimL5.dbo.CST_COSTHEADER ch where CAST(c.Id as varchar(255)) = CAST(ch.CH_CODE as varchar(255)) ) THEN 'Yes' ELSE
    CASE WHEN EXISTS (select ch.CH_CODE from DimL6.dbo.CST_COSTHEADER ch where CAST(c.Id as varchar(255)) = CAST(ch.CH_CODE as varchar(255)) ) THEN 'Yes' ELSE
    CASE WHEN EXISTS (select ch.CH_CODE from DimL7.dbo.CST_COSTHEADER ch where CAST(c.Id as varchar(255)) = CAST(ch.CH_CODE as varchar(255)) ) THEN 'Yes' ELSE
    CASE WHEN EXISTS (select ch.CH_CODE from DimL8.dbo.CST_COSTHEADER ch where CAST(c.Id as varchar(255)) = CAST(ch.CH_CODE as varchar(255)) ) THEN 'Yes' ELSE
    CASE WHEN EXISTS (select ch.CH_CODE from DimL9.dbo.CST_COSTHEADER ch where CAST(c.Id as varchar(255)) = CAST(ch.CH_CODE as varchar(255)) ) THEN 'Yes' ELSE
    CASE WHEN EXISTS (select ch.CH_CODE from DimL10.dbo.CST_COSTHEADER ch where CAST(c.Id as varchar(255)) = CAST(ch.CH_CODE as varchar(255)) ) THEN 'Yes' ELSE
    CASE WHEN EXISTS (select ch.CH_CODE from DimL11.dbo.CST_COSTHEADER ch where CAST(c.Id as varchar(255)) = CAST(ch.CH_CODE as varchar(255)) ) THEN 'Yes' ELSE
    CASE WHEN EXISTS (select ch.CH_CODE from DimL12.dbo.CST_COSTHEADER ch where CAST(c.Id as varchar(255)) = CAST(ch.CH_CODE as varchar(255)) ) THEN 'Yes' ELSE
    CASE WHEN EXISTS (select ch.CH_CODE from DimL13.dbo.CST_COSTHEADER ch where CAST(c.Id as varchar(255)) = CAST(ch.CH_CODE as varchar(255)) ) THEN 'Yes' ELSE
    CASE WHEN EXISTS (select ch.CH_CODE from DimL14.dbo.CST_COSTHEADER ch where CAST(c.Id as varchar(255)) = CAST(ch.CH_CODE as varchar(255)) ) THEN 'Yes' ELSE
    'No' END END END END END END END END END END END END END END 'ProjectCodeExists'
from #tmpContracts c

I just nested all cases. It should stop evaluating once any of cases is satisfied.