Solved

Optimizing query ,sybase

Posted on 2012-04-04
5
492 Views
Last Modified: 2012-04-12
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
0
Comment
Question by:rbhargaw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 14

Expert Comment

by:Jan_Franek
ID: 37809822
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
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 500 total points
ID: 37811946
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
 

Author Comment

by:rbhargaw
ID: 37812442
Thanks Joe, I will remove the derived table,  how to deal with the case statement?
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 37814706
I don't think you can change the CASE, but I don't think you need to, CASE isn't expensive.
0
 

Author Closing Comment

by:rbhargaw
ID: 37838700
Not enough joins in the table and hence working on that. The answers points to right direction
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question