Sql选择查询对于获取数据非常慢 [英] Sql select query very slow for fetching data
本文介绍了Sql选择查询对于获取数据非常慢的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这里粗体代码是缓慢提取的主要问题
选择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屋!
查看全文