[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 421
  • Last Modified:

SQL Server - need to include or exclude a join to a table based on a variable value

I have a long query with many tables in the FROM clause.
One of the tables has 100,000+ rows, which makes the whole query take a long time to run.
If a variable is set to TRUE, then that table is not needed at all in the FROM clause.
If the variable is set to FALSE, then a different variable is used to filter the table.
When I tried to add a case statement in the WHERE clause, it still took way too long to run!

I need help to conditionally add that table to the WHERE clause based on the variable being set.
In other words, if the variable is set to TRUE, I don't need that table at all, if the variable is set to FALSE, I need to add a statement to the WHERE clause.
See attached file.
0
avivap
Asked:
avivap
  • 5
  • 2
2 Solutions
 
Scott PletcherSenior DBACommented:
Not many details, but I think this will do it:

FROM mainTable mt
LEFT OUTER JOIN joinTable jt ON
    (@variable = 'True' OR jt.<column> = mt.<column>)
0
 
PortletPaulCommented:
there is no attachment
0
 
avivapAuthor Commented:
Hm...that's odd!
I do remember attaching my file!

Here it is again.
SQL---7-14-14.docx
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
PortletPaulCommented:
It is preferrable to simply use a code block such as this.
declare @vUID int,@vYear varchar(4), @vAdmin varchar(1)

Select @vYear = ''

Select @vAdmin = '1'
 
 
select
distinct sr.LAST_NAME as 'lastname', sr.FIRST_NAME as 'firstname', sr.SSN AS 'empid', 
 sr.acnt_id as 'srid', xaccourse.ActivityTitle as 'class', 
 sr.email_address as 'email',mimxname.OptionText as 'terminal', 
 'enrolled' = CASE WHEN rc.enrolled = 1 Then 'Yes' Else 'No' END
 
 
 FROM acnts aa 
inner join records sr on 
 aa.records_id = sr.id 
 inner join enrolment enr on enr.fk_acnt_id = sr.id 
 inner join classes rc on enr.id = rc.id 
 Join dataXref mimx ON mimx.OptionText = sr.fk_id and mimx.TemplateID = 
 200 and mimx.CategoryID = 750 
 Join dataXref2 mimxname ON mimxname.InstanceID 
 = mimx.InstanceID and mimxname.CategoryID = 750 
 --join dataItem mgrterm on mgrterm.itemid = 5 
 --and mgrterm.instanceid = @vUID 
  
 JOIN schedule cs ON cs.Schedule_ID = enr.fk_schedule_id 
 left outer JOIN dataItem mimi ON mimi.CatID = 397 
 AND mimi.Text = enr.fk_schedule_id 
 left outer JOIN dataItem mimicourse ON  mimicourse.Text = fk_class_id 
 and mimicourse.templateid = 215 AND mimicourse.CategoryID = 750 
 left outer JOIN ActivityMain xam ON xam.ActivityID = mimicourse.instanceid 
 left outer JOIN ActivityContent xacCourse ON xacCourse.ID = xam.ID  
 
   WHERE 
   --this is where I would like to add the condition
   --and remove the JOIN from the FROM clause above – the one that’s commented
   --out now.  IF @vADMIN is equal to 1, then there is no need to include the mgrterm   
   --table and to filter by @vUID
   --if the @vAdmin is equal to 0 – then it needs to filter the mgrterm table by the  
   --value in @vUID 
   
    
   DatePart(yyyy,Convert (datetime,CONVERT(varchar(10),rc.created,110))) = case when @vYear is NULL 
   then DatePart(yyyy,Convert (datetime,CONVERT(varchar(10),rc.created,110))) when @vYear = '' 
   then DatePart(yyyy,Convert (datetime,CONVERT(varchar(10),rc.created,110))) else @vYear end  

Open in new window

it enables review of your question and the code together.
0
 
PortletPaulCommented:
I think it can be done this way. The join becomes a left join and is only executed when needed. It becomes the equivalent to an inner join by referencing in it the where clause
DECLARE @vUID int
      , @vYear varchar(4)
      , @vAdmin varchar(1)

SELECT
      @vYear = ''

SELECT
      @vAdmin = '1'


SELECT DISTINCT
      sr.LAST_NAME            AS 'lastname'
    , sr.FIRST_NAME           AS 'firstname'
    , sr.SSN                  AS 'empid'
    , sr.acnt_id              AS 'srid'
    , xaccourse.ActivityTitle AS 'class'
    , sr.email_address        AS 'email'
    , mimxname.OptionText     AS 'terminal'
    , 'enrolled' =            CASE WHEN rc.enrolled = 1 THEN 'Yes' ELSE 'No' END


FROM acnts aa
      INNER JOIN records sr
                  ON aa.records_id = sr.id
      INNER JOIN enrolment enr
                  ON enr.fk_acnt_id = sr.id
      INNER JOIN classes rc
                  ON enr.id = rc.id
      INNER JOIN dataXref mimx
                  ON mimx.OptionText = sr.fk_id
                        AND mimx.TemplateID =
                        200
                        AND mimx.CategoryID = 750
      INNER JOIN dataXref2 mimxname
                  ON mimxname.InstanceID
                        = mimx.InstanceID
                        AND mimxname.CategoryID = 750
      LEFT JOIN dataItem mgrterm
                  ON @vAdmin = 0
                        AND mgrterm.itemid = 5
                        AND mgrterm.instanceid = @vUID

      INNER JOIN schedule cs
                  ON cs.Schedule_ID = enr.fk_schedule_id
      LEFT OUTER JOIN dataItem mimi
                  ON mimi.CatID = 397
                        AND mimi.Text = enr.fk_schedule_id
      LEFT OUTER JOIN dataItem mimicourse
                  ON mimicourse.Text = fk_class_id
                        AND mimicourse.templateid = 215
                        AND mimicourse.CategoryID = 750
      LEFT OUTER JOIN ActivityMain xam
                  ON xam.ActivityID = mimicourse.instanceid
      LEFT OUTER JOIN ActivityContent xacCourse
                  ON xacCourse.ID = xam.ID

WHERE (
        (@vYear IS NULL or @vYear = '')
       OR
        (rc.created >= dateadd(year,convert(int,nullif(@vYear,'')) - 1900,0) AND rc.created < dateadd(year,convert(int,nullif(@vYear,'')) - 1899,0)
      )
AND (
       (@vAdmin = 0 AND mgrterm.instanceid = @vUID)
      OR
        @vAdmin = 1
    )
;

Open in new window

Please note I have changed the way you perform the date filter. Please don't apply functions to data unless absolutely required (which isn't true here).

Try the following, you will see it calculates Jan 01 @vYear and Jan 01 @vYear+1 so you can get a full year of data without using any functions on rc.created
declare @vYear varchar(4) = '2014'

select
  dateadd(year,convert(int,@vYear) - 1900,0) 
, dateadd(year,convert(int,@vYear) - 1899,0)
;
|                       COLUMN_0 |                       COLUMN_1 |
|--------------------------------|--------------------------------|
| January, 01 2014 00:00:00+0000 | January, 01 2015 00:00:00+0000 

Open in new window

0
 
PortletPaulCommented:
>>"One of the tables has 100,000+ rows, which makes the whole query take a long time to run."

Using DISTINCT over that entire query could be the largest case of slowness (or at least one of the biggest causes).

which one of those tables is 100,000+ ?
can it be summarised (using group by or distinct) before it is used in the main query?
0
 
avivapAuthor Commented:
Thank you both.
I learned a lot from both solutions.
I took parts of each of your solutions, and used it in my SQL Script.

Thank you PortletPaul for that date calculation - I'm sure I'll be using it often!
0
 
PortletPaulCommented:
regarding that date calculation just be careful if the parameter is varchar (if varchar and it is '' then you have a problem)   It would be better if the parameter as int.

thanks for the split.
Paul
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now