asked on

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.

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

there is no attachment

avivap

ASKER

Hm...that's odd!
I do remember attaching my file!

Here it is again.
SQL---7-14-14.docx

PortletPaul

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.

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

>>"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?

avivap

ASKER

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!

PortletPaul

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