Optimizing conditional query in sybase

Question

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.

You have not mentioned any indexes on the tables. If you have them, please include the type (clustered/non-clustered, unique, etc) of index, and the rows included in the index. Can you also specify what version of SAP Sybase ASE you are running — Mike Gardner, Apr 11 '14 at 01:28
@MichaelGardner Hello again! I have used clustered index on the primary key columns. I have an index on the Year and EmployeeID combination. I am using Sybase ASE 11/12/15 TDS 5.5. Is this what you are asking about? Thanks in advance for the help! — user3358472, Apr 12 '14 at 23:30

score 0 · Answer 1 · answered Apr 16 '14 at 04:10

It's been a while since I worked with Sybase, and I am not too sure of their syntax support for SQL-99 queries. However, assuming Sybase has at least some support for SQL-99 join syntax, the following should work:

Create view dbo.employees(Year, EmployeeID)
as 
select e.employeeId, e.Year, e.attribute1, e.attribute2, ... e.attributeN,
       ifnull(te.employeeId, e.employeeId, te.employeeId) as testEmployeeId,
       ifnull(te.employeeId, e.Year, te.Year) as testYear,
       ifnull(te.employeeId, e.attribute1, te.attribute1) as testAttribute1,
       ifnull(te.employeeId, e.attribute2, te.attribute2) as testAttribute2,
               ....
       ifnull(te.employeeId, e.attributeN, te.attributeN) as testAttributeN
from  employee e
left join testemployees te on (e.employeeId = te.employeeId and e.Year = te.Year)

Be aware that this is somewhat different from the semantics that you defined. I am guessing that this is actually a bit closer to what you want in any case.

Optimizing conditional query in sybase

1 Answers1