February 28, 2008 at 4:57 am
Hi,
I had scenario were my Sp hanged at one particular line. Using profiler I was able to find out the query.
I use IF EXISTS to know the existence of number
If Exists(SELECT ReceiptNo FROM A with (nolock) WHERE Receipt_No = @Last_Receipt_No And ID in(Select ID from B with (NOLOCK) where Companyid= @companyid)
begin
End
As a solution to this my senior modify the query like this and it worked.
SELECT @recCount = Count('X') FROM A with (nolock) WHERE Receipt_No = @Last_Receipt_No And ID in(Select ID from B with (NOLOCK) where Companyid= @companyid)
if @recCount > 0
begin
End
Do you find any reason why the previous query didn't work and current query worked?
thanks in advance for you help
February 28, 2008 at 5:07 am
It looks like the first one should work.
Personally, I'd have done it as a join instead of having the subquery on the company number, but it looks like it should work.
If Exists is almost always faster than Select Count, too.
In the first one, change the "Select Receipt_No" to "Select 1", and see if that makes it work.
Here's what I recommend trying:
If Exists
(SELECT 1
FROM A with (nolock)
INNER JOIN B with (nolock)
on A.ID = B.ID
WHERE A.Receipt_No = @Last_Receipt_No
and B.Companyid= @companyid)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 28, 2008 at 7:17 am
Thanks for your reply, So Count will lead to performance issue. Right?
if yes Then the modification made my senior is not the solution.
February 28, 2008 at 7:36 am
Your first syntax is also missing a parenthesis if I am not badly mistaken. If anything is dragging it down - it's the use of the subquery in the IN. Have you considered rewriting THAT as a join or using EXISTS?
I'd give GSquared's syntax a whirl.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 28, 2008 at 12:51 pm
I planning to give a try with join Query.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply