Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2014
    Posts
    4

    Unanswered: Optimizing the query with conditional cases

    Hello guys,

    I would like to get help on optimizing my query below.

    I want to have a view that exposes employee data across years. This view has data from employee table which has employeeID, year and employee demographs as columns. I also have a table called testemployees which has EmployeeID and Year. This is a subset of employee table which might or might not have data. What I am trying to accomplish is:

    If there is data in testEmployees table my view should fetch employee details only for employees in testEmployees and if there is not data in testEmployees I should have my view have all the data in employee table.

    My employee table is really very huge, and even though the query below works it takes a lot of time to fetch this data. Any pointers on how I can improve this query will be greatly appreciated.

    Create view dbo.employees(Year, EmployeeID)
    as
    select * from
    employee e, testemployees te
    where e.Year = case when((select count(1) from testemployees)>0) then te.Year else e.Year
    and e.employeeID = case when((select count(1) from testemployees)> 0) then te.employeeID else e.employeeID

  2. #2
    Join Date
    Apr 2014
    Posts
    4

    Conditional Queries: Help optimizing query

    Hello guys,

    I would like to get help on optimizing my query below.

    I want to have a view that exposes employee data across years. This view has data from employee table which has employeeID, year and employee demographs as columns. I also have a table called testemployees which has EmployeeID and Year. This is a subset of employee table which might or might not have data. What I am trying to accomplish is:

    If there is data in testEmployees table my view should fetch employee details only for employees in testEmployees and if there is not data in testEmployees I should have my view have all the data in employee table.

    My employee table is really very huge, and even though the query below works it takes a lot of time to fetch this data. Any pointers on how I can improve this query will be greatly appreciated.

    Create view dbo.employees(Year, EmployeeID)
    as
    select * from
    employee e, testemployees te
    where e.Year = case when((select count(1) from testemployees)>0) then te.Year else e.Year
    and e.employeeID = case when((select count(1) from testemployees)> 0) then te.employeeID else e.employeeID


    Let me know your thoughts on how to optimize the query. Would using any other kind of joins help?

    Thanks in advance.

  3. #3
    Join Date
    Apr 2014
    Posts
    4

    Conditional Queries: Help optimizing query

    Hello guys,

    I would like to get help on optimizing my query below.

    I want to have a view that exposes employee data across years. This view has data from employee table which has employeeID, year and employee demographs as columns. I also have a table called testemployees which has EmployeeID and Year. This is a subset of employee table which might or might not have data. What I am trying to accomplish is:

    If there is data in testEmployees table my view should fetch employee details only for employees in testEmployees and if there is not data in testEmployees I should have my view have all the data in employee table.

    My employee table is really very huge, and even though the query below works it takes a lot of time to fetch this data. Any pointers on how I can improve this query will be greatly appreciated.

    Create view dbo.employees(Year, EmployeeID)
    as
    select * from
    employee e, testemployees te
    where e.Year = case when((select count(1) from testemployees)>0) then te.Year else e.Year
    and e.employeeID = case when((select count(1) from testemployees)> 0) then te.employeeID else e.employeeID


    Let me know your thoughts on how to optimize the query. Would using any other kind of joins help?

    Thanks in advance.

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    If you use any function on an indexed column then you disqualify that column from using an index

    But looks like you want a left join e.g. untested
    select *
    from employee e
    left join testemployees te
    on e.Year = te.Year
    and e.employeeID = te.employeeID
    Last edited by pdreyer; 05-14-14 at 07:33.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •