SELECT Query Taking Lot of time to execute

Riddhi Valecha

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 ..

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ramesh Singh9y ago
@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.
0
Riddhi Valecha9y ago
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 (2,4,3, etc), then check in table SenderReceiver and take name from that table where ID = 2,3,4 - 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?
0
Puneet Kankar9y ago
Hi Ridhi,

1. Avoid subquery preferred joins.
2. Create clustered nonclustered indexing on table
0
Akshay Phadke9y ago
@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 .
0
Abhilash J A9y ago
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
0

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