8 Replies Latest reply: Jul 23, 2012 12:18 PM by 6363 RSS

    Help for a query

    lxiscas
      Hi, guys:

      I may have a silly question to ask: I have a query which works fine for the first time when I tested it in SQL developer, but takes very long time (more than 2 minutes) to return result with exactly same query and same parameters for the second time unless I reload SQL developer, could anyone give me a hint on this so I can improve my query?
                select distinct  so.offender_id as "Offender_ID",  so.first_name||' '|| so.middle_name||' '||so.last_name as "Offender_Name", 
                nvl2(sl.ADDRESS_LATITUDE, to_char(sl.ADDRESS_LATITUDE)||','||to_char(sl.address_longitude),'') as "Address_Geocoding",
                nvl2(sl.physical_address_latitude,to_char(sl.physical_address_latitude) ||','||to_char(sl.physical_address_Longitude),'') as "Physical_Geocoding"
                from sor_location sl, sor_offender so, sor_offense sof, registration_offender_xref rox, sor_last_locn_v sllv
                where rox.offender_id=so.offender_id
                and sllv.offender_id=so.offender_id
                and sl.location_id=sllv.location_id
                and sof.offender_id=so.offender_id
                and rox.status not in ('Merged')
                and rox.reg_type_id=1
                and upper(rox.status)='ACTIVE'
                and nvl(rox.admin_validated, to_date(1,'J'))>=nvl(rox.entry_date, to_date(1,'J'))
                and sl.ADDRESS_LATITUDE <=to_number(:P_f_Resident_Latitude)+0.02*to_number(:P_n_Radius) and sl.ADDRESS_LATITUDE>= to_number(:P_f_Resident_Latitude)-0.02*to_number(:P_n_Radius)
                and sl.address_longitude >=to_number(:P_f_Resident_Longitude)-0.02*to_number(:P_n_Radius) and  sl.address_longitude<=to_number(:P_f_Resident_Longitude)+0.02*to_number(:P_n_Radius)
                and sor_google_map_service.Calculate_Distance(:P_f_Resident_Latitude, :P_f_Resident_Longitude, sl.ADDRESS_LATITUDE, sl.address_longitude)<=:P_n_Radius
               union
                select distinct  so.offender_id as "Offender_ID",  so.first_name||' '|| so.middle_name||' '||so.last_name as "Offender_Name", 
                nvl2(sl.ADDRESS_LATITUDE, to_char(sl.ADDRESS_LATITUDE)||','||to_char(sl.address_longitude),'') as "Address_Geocoding",
                nvl2(sl.physical_address_latitude,to_char(sl.physical_address_latitude) ||','||to_char(sl.physical_address_Longitude),'') as "Physical_Geocoding"
                from sor_location sl, sor_offender so, sor_offense sof, registration_offender_xref rox, sor_last_locn_v sllv
                where rox.offender_id=so.offender_id
                and sllv.offender_id=so.offender_id
                and sl.location_id=sllv.location_id
                and sof.offender_id=so.offender_id
                and rox.status not in ('Merged')
                and rox.reg_type_id=1
                and upper(rox.status)='ACTIVE'
                and nvl(rox.admin_validated, to_date(1,'J'))>=nvl(rox.entry_date, to_date(1,'J'))
                and sl.physical_address_latitude <=to_number(:P_f_Resident_Latitude)+0.02*to_number(:P_n_Radius) and sl.physical_address_latitude>= to_number(:P_f_Resident_Latitude)-0.02*to_number(:P_n_Radius)
                and sl.physical_address_Longitude >=to_number(:P_f_Resident_Longitude)-0.02*to_number(:P_n_Radius) and  sl.physical_address_Longitude<=to_number(:P_f_Resident_Longitude)+0.02*to_number(:P_n_Radius)
                and sor_google_map_service.Calculate_Distance(:P_f_Resident_Latitude, :P_f_Resident_Longitude, sl.physical_address_latitude, sl.physical_address_Longitude)<=:P_n_Radius;
         
      I use the following parameter to test:
      :P_n_Radius 3
      :P_f_Resident_Latitude 35.5113030
      :P_f_Resident_Longitude -97.5543081

      Thanks a lot!

      Sam
        • 1. Re: Help for a query
          N_i_R_v_A_n_A
          use UNION ALL, always better
          • 2. Re: Help for a query
            lxiscas
            Hi, N_i_R_v_A_n_A :

            After many trying, I just found a solution though I am still confused about the cause. I wish you or anyone could teach me on this. I just updated the query back to original style as
             select distinct  so.offender_id as "Offender_ID",  so.first_name||' '|| so.middle_name||' '||so.last_name as "Offender_Name", 
                      replace(replace(nvl2(sl.address1, sl.address1||' '||sl.address2 ||' '||sl.city ||' '||sl.county||' '||(select sc3.description from sor_code sc3 where sc3.code_id=sl.state)||' '||sl.zip, 'No Known Address'),'#') ,',') as "Address",
                      replace(replace(nvl2(sl.physical_address1,sl.physical_address1||' '||sl.physical_city ||' '||sl.physical_county||' '||(select sc4.description from sor_code sc4 where sc4.code_id=sl.physical_state)||' '||sl.physical_zip, 'No Known Address'),'#') ,',')  as "Physical_Address",
                      nvl2(sl.ADDRESS_LATITUDE, to_char(sl.ADDRESS_LATITUDE)||','||to_char(sl.address_longitude),'') as "Address_Geocoding",
                      nvl2(sl.physical_address_latitude,to_char(sl.physical_address_latitude) ||','||to_char(sl.physical_address_Longitude),'') as "Physical_Geocoding"
                      from sor_location sl, sor_offender so, sor_offense sof, registration_offender_xref rox, sor_last_locn_v sllv
                      where rox.offender_id=so.offender_id
                      **and sllv.offender_id(+)=so.offender_id**
                      **and sl.location_id(+)=sllv.location_id**
                      and sof.offender_id=so.offender_id
                      and rox.status not in ('Merged')
                      and rox.reg_type_id=1
                      and upper(rox.status)='ACTIVE'
                      and nvl(rox.admin_validated, to_date(1,'J'))>=nvl(rox.entry_date, to_date(1,'J'))
                      and sl.ADDRESS_LATITUDE <=to_number(:P_f_Resident_Latitude)+0.02*to_number(:P_n_Radius) and sl.ADDRESS_LATITUDE>= to_number(:P_f_Resident_Latitude)-0.02*to_number(:P_n_Radius)
                      and sl.address_longitude >=to_number(:P_f_Resident_Longitude)-0.02*to_number(:P_n_Radius) and  sl.address_longitude<=to_number(:P_f_Resident_Longitude)+0.02*to_number(:P_n_Radius)
                      and sor_google_map_service.Calculate_Distance(:P_f_Resident_Latitude, :P_f_Resident_Longitude, sl.ADDRESS_LATITUDE, sl.address_longitude)<=:P_n_Radius
                      union
                      select distinct  so.offender_id as "Offender_ID",  so.first_name||' '|| so.middle_name||' '||so.last_name as "Offender_Name", 
                      replace(replace(nvl2(sl.address1, sl.address1||' '||sl.address2 ||' '||sl.city ||' '||sl.county||' '||(select sc3.description from sor_code sc3 where sc3.code_id=sl.state)||' '||sl.zip, 'No Known Address'),'#') ,',') as "Address",
                      replace(replace(nvl2(sl.physical_address1,sl.physical_address1||' '||sl.physical_city ||' '||sl.physical_county||' '||(select sc4.description from sor_code sc4 where sc4.code_id=sl.physical_state)||' '||sl.physical_zip, 'No Known Address'),'#') ,',')  as "Physical_Address",
                      nvl2(sl.ADDRESS_LATITUDE, to_char(sl.ADDRESS_LATITUDE)||','||to_char(sl.address_longitude),'') as "Address_Geocoding",
                      nvl2(sl.physical_address_latitude,to_char(sl.physical_address_latitude) ||','||to_char(sl.physical_address_Longitude),'') as "Physical_Geocoding"
                      from sor_location sl, sor_offender so, sor_offense sof, registration_offender_xref rox, sor_last_locn_v sllv
                      where rox.offender_id=so.offender_id
                      **and sllv.offender_id(+)=so.offender_id**
                      **and sl.location_id(+)=sllv.location_id**
                      and sof.offender_id=so.offender_id
                      and rox.status not in ('Merged')
                      and rox.reg_type_id=1
                      and upper(rox.status)='ACTIVE'
                      and nvl(rox.admin_validated, to_date(1,'J'))>=nvl(rox.entry_date, to_date(1,'J'))
                      and sl.physical_address_latitude <=to_number(:P_f_Resident_Latitude)+0.02*to_number(:P_n_Radius) and sl.physical_address_latitude>= to_number(:P_f_Resident_Latitude)-0.02*to_number(:P_n_Radius)
                      and sl.physical_address_Longitude >=to_number(:P_f_Resident_Longitude)-0.02*to_number(:P_n_Radius) and  sl.physical_address_Longitude<=to_number(:P_f_Resident_Longitude)+0.02*to_number(:P_n_Radius)
                      and sor_google_map_service.Calculate_Distance(:P_f_Resident_Latitude, :P_f_Resident_Longitude, sl.physical_address_latitude, sl.physical_address_Longitude)<=:P_n_Radius;
                
            All I change is just to use outer join instead of inner join, why it performs much better?

            Thanks.

            Sam
            • 3. Re: Help for a query
              SomeoneElse
              use UNION ALL, always better
              Whoa, always?
              • 4. Re: Help for a query
                926577
                it mutually exclusive conditions
                and rox.status not in ('Merged')
                and upper(rox.status)='ACTIVE'
                you can remove
                and rox.status not in ('Merged')
                • 5. Re: Help for a query
                  946222
                  You are using DISTINCT together with UNION. If you are using UNION you already have an implicit DISTINCT in your query, so drop those explicit DISTINCTs to avoid unnecessary sorts. In summary:

                  UNION = UNION ALL + DISTINCT

                  Also, just like our colleague said, if you know that the data won't repeat on both sides of the UNION, you can use an UNION ALL instead and get rid of every sort on your query, which you give you a good speed up.
                  • 6. Re: Help for a query
                    lxiscas
                    Alkaron:

                    Thanks, I already noticed it:), It is from old code 10 years ago written for government, and I am gonna leave it there for a while until I tested other parts to make sure I can remove it without any problem. My assumption is, since I only query offenders whose location is close by a resident location, surely I do not have to consider outer join as rows from sor_location table (offender's location) should not be empty when join with sor_offender table on offender_id. why inner looks dangling forever, but outer join performs much better?


                    Sam
                    • 7. Re: Help for a query
                      lxiscas
                      Paulo Petruzalek:

                      Thanks for your advice, I remove distinct, but the query looks dangling again sometimes. It is strange. It looks related to null value test, But I cannot figure out the cause. Thanks for reminding.

                      Sam.
                      • 8. Re: Help for a query
                        6363
                        N_i_R_v_A_n_A wrote:

                        use UNION ALL, always better
                        How do you mean better? It returns different results. Surely which results are needed is based on a requirement, not whether you prefer UNION ALL to UNION

                        Re: DBA interview questions

                        If you mean a query that returns in less than a second is too fast and you want to make it take say six seconds instead, then yes, UNION ALL might be better if you have a need to slow down a query.