Sql选择查询对于获取数据非常慢 [英] Sql select query very slow for fetching data

问题描述

这里粗体代码是缓慢提取的主要问题

选择distinct isnull（V.VendorItemId， 0 ） as  VendorItemId，isnull（V.VendorItemNumber，I.Ord_guide） as  VendorItemNumber，
 isnull（V.VendorItemDescription，I.Pkg_desc） as  VendorItemDescription，
  Id =（选择最小（k.Id）来自 dbo.InvoiceImportDataPrice k 其中​​ K.Ord_guide = I.Ord_guide和K.Price = I.Price 
和k.Pkg_desc = I.Pkg_desc和k.Store_name = I.Store_name和k.InvoiceImportHistoryId =  128 ），
 I.InvoiceImportHistoryId，I.Price，isnull（V.CurrentPrice， 0 ） as  CurrentPrice，ISNULL（ISNULL（V.CurrentPrice， 0 ） -  ISNULL（I.Price） ， 0 ）， 0 ）AS differencePrice，
 I.Invoice_No，I.Sold_to，I .Store_name，I.Deliv_date，
（CASE WHEN isnull（I.Qty_ship，'  0'）= '  0'然后'   -  0'  else  I.Qty_ship END）AS Qty_ship，I.Ship_um，
（CASE WHEN isnull（I.Qty_ord，'  0'）=  '  0'然后'   -  0'   else  I.Qty_ord END）AS Qty_ord，I.Item_no，I.Weight，
（CASE WHEN isnull（I.Amount，'  0'）= '  0'  THEN '   -  0'  else  I.Amount END）AS Amount，I.Pkg_desc，I.Tax_amount，I.Ord_guide 
，（ select  count（*）FROM dbo .InvoiceImportDataPrice IP 其中 IP.Pkg_desc = V.VendorItemDescription 
和IP.Item_no = V.VendorItemNumber和IP.Price = V.CurrentPrice）
  as  PriceDiscrepancyCount  
 来自 dbo.InvoiceImportDataPrice I 
 left  join  dbo.VendorItems V WITH（NOLOCK）on V.VendorItemNumber = I.Ord_guide 
  where （I .InvoiceImportHistoryId =  128 和VendorItemDescription！= ' 销售税'）或
（I.InvoiceImportHistoryId =  128 和isnull（V.VendorItemDescription，' '）= ' '）

我尝试过：



当我关闭两个代码然后数据快速获取但我需要两个代码。记录的总数是2833

解决方案

这里可能有很多不同的东西。

• 整理

• 没有涵盖子查询的索引

• 加入在大型nvarchar列上根本没有推荐

• 统计

你的名字它..

here Bold code is main issue for slow fetching

Select distinct isnull(V.VendorItemId,0) as VendorItemId  , isnull(V.VendorItemNumber,I.Ord_guide) as VendorItemNumber,
isnull(V.VendorItemDescription,I.Pkg_desc) as VendorItemDescription,
Id=(select Min(k.Id) from dbo.InvoiceImportDataPrice k where K.Ord_guide=I.Ord_guide and K.Price=I.Price
and k.Pkg_desc=I.Pkg_desc and k.Store_name=I.Store_name and k.InvoiceImportHistoryId = 128),
I.InvoiceImportHistoryId,I.Price,isnull(V.CurrentPrice,0) as CurrentPrice,ISNULL(ISNULL(V.CurrentPrice, 0) - ISNULL(I.Price, 0), 0) AS differencePrice,
I.Invoice_No, I.Sold_to, I.Store_name, I.Deliv_date,
(  CASE WHEN isnull(I.Qty_ship,'0')='0' THEN '-0' else I.Qty_ship   END ) AS Qty_ship, I.Ship_um, 
(  CASE WHEN isnull(I.Qty_ord,'0')='0' THEN '-0'  else I.Qty_ord   END ) AS Qty_ord,  I.Item_no, I.Weight, 
(  CASE WHEN isnull(I.Amount,'0')='0' THEN '-0'  else I.Amount   END ) AS Amount, I.Pkg_desc, I.Tax_amount, I.Ord_guide
,(select count(*) FROM dbo.InvoiceImportDataPrice IP where IP.Pkg_desc=V.VendorItemDescription
and IP.Item_no= V.VendorItemNumber and  IP.Price = V.CurrentPrice)
as PriceDiscrepancyCount
from dbo.InvoiceImportDataPrice  I
left join dbo.VendorItems V WITH (NOLOCK) on V.VendorItemNumber = I.Ord_guide
where (I.InvoiceImportHistoryId = 128 and VendorItemDescription != '"Sales Tax"') or
(I.InvoiceImportHistoryId = 128 and isnull(V.VendorItemDescription,'')='')

What I have tried:

when i off both code then data fetch fast but i need both code. total no of records is 2833

解决方案

There can be a ton of different things wrong here.

• Collation

• No covering Indexes on the subqueries

• Joining on large nvarchar columns are not recommented at all

• Statistics

you name it..

这篇关于Sql选择查询对于获取数据非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！