How do I improve permormance the select query ?

Question

1.00/5 (1 vote)

See more:

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

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

What I have tried:

SQL

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

Posted 3-Jul-17 23:00pm

Member 12955507

Updated 3-Jul-17 23:10pm

Add a Solution

Comments

F-ES Sitecore 4-Jul-17 5:11am

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.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

mike14021 · Answer 1 · 2017-07-03T23:10:00

wath 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