如何改善选择查询的permormance？ [英] How do I improve permormance the select query ?

问题描述

我再次坚持使用数据库查询性能..



一个选择查询 - 需要花费大量时间来执行 - 对于30个记录，需要7分钟。 />


我尝试了什么：

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:

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

推荐答案

wath数据库？ sql server？ oracle？其他什么？



如果是sqlserver，包括实际的执行计划和估计的执行计划 - 如果任何索引缺失，sql server将显示命令创建缺失的索引



案件和演员真的需要吗？应该在客户端完成（c＃，vb ...）数据库用于检索数据，而不是用于调用



子查询可能比加入

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

这篇关于如何改善选择查询的permormance？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！