SELECT Query Taking Lot of time to execute

Question

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_WEIGHT incoming.IC_RATE  incoming.IC_CONSIGNMENTRECDATETIME 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 ..

Puneet Kankar · Answer

Hi Ridhi 1. Avoid subquery preferred joins. 2. Create clustered nonclustered indexing on table

Mani Kandan · Answer

Use JOINs rather than sub-queries to get data from other tables (eg Courier Service Name etc). Beyond that look at the query plan via SQL Management Studio and maybe build indexes on the columns included in WHERE queries however that may have a knock-on effect with insert performance. Tuning queries is a massive topic on its own lots of articles out there if you google. And What database ? sql server ? oracle ? anything else ? if sqlserver include actual Execution plan and estimated execution plan - if any index missing sql server will display command to create missing index case and cast really needed ? should be done in client (c# vb ...) database is for retrieving data not for calcing subquery maybe slower then joining

Ramesh Singh · Answer

@Riddhi it looks like a Cartesian join . verify your result set. is it returning correct result? use joins amongs mulitple tables replace sub-queries with joins and check index on column used in where clause. it will help to increase performance.

Riddhi Valecha · Answer

Hello all.. Thank you so much ... but to be frank I am a bit weak in Join Queries... Query Details - 1. (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' - Column - IC_RECEIVERS_SR_ID has numeric values and string values. Requirement - Where the value is a number (243 etc) then check in table SenderReceiver and take name from that table where ID = 234 - Hence I used ISNUMERIC Method. 2. (select courier.CSP_NAME from COURIERSERVICEPROVIDER courier where courier.CSP_ID = incoming.IC_CSP_ID) as 'Courier Service Name' - CourierServiceProvider - is the master table - (CSP_ID - Primary Key ; IC_CSP_ID - foreign key in transaction table). 3. (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' - Two columns Site and Subhub are from one single table. Difference - EQ_CODe EQ_DESC EQ_Level_1 EQ_LEVEL_2 Flag 100 Hydrabad 0 0 1 101 Abids 100 1 1 102 Gachibowli 100 1 1 103 Manikonda 100 1 1 104 Uppal 100 1 1 Site - Code = 100 (sent from front-end ; and EQ_Level_1 = 0 and EQ_Level_2 = 0 Site - Hydrabad (100) ; Subbub - EQ_Level_1 = 1 and EQ_Level_2 = 1 -------------- Client Statistics - Time Statistics Client processing time 31 31.0000 Total execution time 291301 291301.0000 Wait time on server replies 291270 291270.0000 ------ How do I tune this query?

Akshay Phadke · Answer

@Riddhi As your above query there are lot of subqueries used.. So it is going to take lot of time. As Run the query which you are using above .Kindly right click on the query and select query execution plan. In that it will highlight what changes you have to make in that query . For example you have to create a non clustered indexes on any table and so on . This will gradually tune your query .