Slow running query when using case in SQL server

Question

0.00/5 (No votes)

See more:

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

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'

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

Posted 17-Jan-17 22:13pm

Rahul_Pandit

Updated 17-Jan-17 22:59pm

v2

Add a Solution

Comments

F-ES Sitecore 18-Jan-17 4:26am

I'm no SQL expert so can't give specifics but you need to try and at least get rid of the sub-queries. Think of a single SELECT you can do that gets all the data you need in that union sub-query in a single hit and make that a CTE or table variable then rather than doing an EXISTS you can do a JOIN on that table. That way you generate the data once and SQL is fast at JOINs, whereas at the moment you are running that subquery for every result and that is slow.

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)

Ashwin. Shetty · Answer 1 · 2017-01-17T22:34:00

Solution 1

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[^]

Posted 17-Jan-17 22:34pm

Ashwin. Shetty

Comments

Rahul_Pandit 18-Jan-17 5:00am

Thanks for reply..I have improved question with the reason why cant I use table variable.