在SQL Server中使用大小写时运行缓慢的查询 [英] Slow running query when using case in SQL server

问题描述

你好朋友，



我在查询下运行 -

hello friends,

I am running below query-

select case WHEN (
						
	    select count(1)from( select count(1) as cnt
								from WCF_INDENT wi LEFT OUTER JOIN INDENT_INVOICE_DETAILS iid  ON  wi.WCF_NO=iid.WCF_NO and wi.ITEM_CODE=iid.ITEM_CODE
								--where wi.WCF_NO='WC/63912'
								group by wi.ITEM_CODE,wi.QTY having SUM(ISNULL(iid.QTY,0))<wi.QTY
								UNION
								select count(1) as cnt
								from VOR_ORDER_DETAILS vod LEFT OUTER JOIN INDENT_INVOICE_DETAILS iid ON vod.ITEM_CODE=iid.ITEM_CODE and vod.VOR_NO=iid.WCF_NO
								where vod.VOR_NO='WC/63912'
								group by iid.ITEM_CODE,vod.QTY
								having SUM(ISNULL(iid.QTY,0))<vod.QTY 
						     )bb
						)>0  
				
	then 'Partial Dispatch'  else 'Full Dispatch' end

但是它的在子句运行1秒内连续运行甚至查询。

我缺少什么？



有什么办法，因为我将使用此查询在WCF_NO的基础上更新表。所以我不能将内部查询放入临时表或任何其他对象。



以下是更新查询

but its running continously even query within when clause runs in 1 seconds.
what am I missing?

Is there any way because i will use this query to update a table on the basis of WCF_NO. So i cant put inner query into temp table or any other object.

Following is the update query

update A set A.Dispatch_Status=
                              (select distinct case WHEN EXISTS ( select count(1) as cnt
                                                                  from WCF_INDENT wi
                                                                  LEFT OUTER JOIN INDENT_INVOICE_DETAILS iid  ON  wi.WCF_NO=iid.WCF_NO and wi.ITEM_CODE=iid.ITEM_CODE
                                                                  where wi.WCF_NO=A.WCF_NO --83184
                                                                  group by wi.ITEM_CODE,wi.QTY having SUM(ISNULL(iid.QTY,0))<wi.QTY
                                                                  UNION
                                                                  select count(1)
                                                                  from VOR_ORDER_DETAILS vod
                                                                  LEFT OUTER JOIN INDENT_INVOICE_DETAILS iid ON vod.ITEM_CODE=iid.ITEM_CODE and vod.VOR_NO=iid.WCF_NO
                                                                  where vod.VOR_NO=A.WCF_NO--21051
                                                                  group by iid.ITEM_CODE,vod.QTY
                                                                  having SUM(ISNULL(iid.QTY,0))<vod.QTY
                                                              )
                                      then 'Partial Dispatch'
                                      else 'Full Dispatch' end as AA
                                      from INDENT_INVOICE_DETAILS
                              )
      FRom #T3 A WHERE FSR_REQD='1'

在此我我在WCF_No的基础上更新＃T3所以我在这种情况下不能使用表格变量。



我尝试过：



我试过了 -

IN this I am updating #T3 on the basis of WCF_No So i cant use table variable in this case.

What I have tried:

I tried--

select case WHEN EXISTS (	select count(1) as cnt
										from WCF_INDENT wi LEFT OUTER JOIN INDENT_INVOICE_DETAILS iid  ON  wi.WCF_NO=iid.WCF_NO and wi.ITEM_CODE=iid.ITEM_CODE
										--where wi.WCF_NO='WC/63912' --83184
										group by wi.ITEM_CODE,wi.QTY having SUM(ISNULL(iid.QTY,0))<wi.QTY
										UNION
										select count(1)
										from VOR_ORDER_DETAILS vod LEFT OUTER JOIN INDENT_INVOICE_DETAILS iid ON vod.ITEM_CODE=iid.ITEM_CODE and vod.VOR_NO=iid.WCF_NO
										where vod.VOR_NO='WC/63912'--21051
										group by iid.ITEM_CODE,vod.QTY
										having SUM(ISNULL(iid.QTY,0))<vod.QTY
									)  
				then 'y'  else 'n' end as AA

推荐答案

没有数据类型信息就很难判断田野和田野的离子在不知道每个表中的数据大小的情况下，如果temp是一个问题并且单个执行查询需要一秒钟，您可以想到表变量。在表变量中插入数据并在其上运行案例。下面的链接应该给表变量一些想法



T-中的表变量SQL [ ^ ]

It will be difficult to judge without data type information for fields & without knowing data size in each table, If temp is a problem and individual execution of query take a sec you can think of Table Variable. Insert data in Table Variable and run case on top of it. Below link should give some Idea on table variable

Table Variables In T-SQL[^]

这篇关于在SQL Server中使用大小写时运行缓慢的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！