Optimizing query ,sybase

Is there a better way to right this query which increase optimization?

select table1.id 'MCN'
      ,table1.LastName+', '+table1.FirstName 'Name'
      ,PTList.noteDate 'LastDocumented'
      ,table1.patientid
      ,(
      select DisplayValue from table3
            where selectionvalueid = table1.gender
       ) 'Gender'
       
     ,table1.dateofbirth
     
     ,PTLIST.notepadid
     
     ,PTLIST.note
 from
       
       (
           select distinct table2.patientid, notepadid, noteDate, note
         
           from  table2
           
           inner join table5
              on table5.PatientId = table2.patientid
             
           inner join table4
              on table4.TxEventId = table5.TxEventId
             
           where closedDate is null
             and noteType = (
                               select selectionvalueid from  table3
                              where code = 'NoteType_Pending'
                             )
                             
             and    
             
             case --Calculate coordinator id based on whether or not the transplant has occurred
                     when table4.TxDate is null
                     then table4.PreTxCoordinator
                            else table4.PostTxCoordinator
                        end  =   {0}       --Parameter              
        )
        PTList
rbhargawFounderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jan FranekCommented:
Can you post structure of your tables, indexes and number of rows in tables ?

And it seems like your query is not complete - you use table1 in the first part, but there is no table1 in FROM clause.
0
Joe WoodhousePrincipal ConsultantCommented:
I am suspicious of the distinct in the derived table. Why is it there? Why use a derived table at all? You don't join to it in any way. Flatten everything in it to the outermost query. If you need the distinct to eliminate duplicates, you haven't put enough joins and/or WHERE clauses in.

Likewise I don't see any reason why your correlated subquery on table3 couldn't be written as a regular join.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rbhargawFounderAuthor Commented:
Thanks Joe, I will remove the derived table,  how to deal with the case statement?
0
Joe WoodhousePrincipal ConsultantCommented:
I don't think you can change the CASE, but I don't think you need to, CASE isn't expensive.
0
rbhargawFounderAuthor Commented:
Not enough joins in the table and hence working on that. The answers points to right direction
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.