SELECT Query Taking Lot of time to execute

Riddhi Valecha

Riddhi Valecha

Jul 4 2017 4:42 AM
Dear Friends,

I am stuck up with Database Query Performance once again..

One Select Query - is taking a lot of time to execute - 7minutes for 30records.

Query -
select distinct incoming.IC_NO,
(case when ISNUMERIC(incoming.IC_RECEIVERS_SR_ID) <> 1 then incoming.IC_RECEIVERS_SR_ID when ISNUMERIC(incoming.IC_RECEIVERS_SR_ID) = 1
then (select sr.SR_NAME from SENDERRECEIVER sr where sr.SR_ID = cast( incoming.IC_RECEIVERS_SR_ID as numeric) )end) as 'IC_RECEIVERS_SR_ID',
(select courier.CSP_NAME from COURIERSERVICEPROVIDER courier where courier.CSP_ID = incoming.IC_CSP_ID) as 'Courier Service Name',
equip.EQ_DESC as 'Site',
(select REPLACE(t.EQ_DESC,'–','') from EQUIPMENT t where t.EQ_L1 = equip.eq_code and t.EQ_LEVEL = 2 and incoming.IC_SUBHUBLOC = t.EQ_CODE) as 'Subhurb',
incoming.IC_ReceiverCity as 'Receiver City',
incoming.IC_RECEIVERPINCODE as 'Receiver Pincode',
incoming.IC_AIRWAYBILLNO as 'Airway Bill No',
incoming.IC_AMOUNT as 'Amount', incoming.IC_CREATEDBY as 'Created By',
incoming.IC_COURIERTYPE as 'Courier Type'
from INCOMINGCONSIGNMENT incoming , equipment equip,
equipment subhurb where 1=1 and IC_CONSIGNMENTRECDATETIME between '03 Jul 2017' and '03 Jul 2017'

Please guide ..

Answers (5)