February 14, 2018 at 3:10 am
Slow Loading (apporox 2 min)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[ZZTEST]
@MODE AS INT = 0,
@LOCATIONCODE AS INT = 0,
@BILLNO AS VARCHAR(30) = ''
AS
BEGIN
IF @MODE = 9
BEGIN
SELECT MAS.NETBILLAMOUNT TOTALAMOUNT, MAS.SYSTEMNAME SYSTEM FROM SALESMAS (NOLOCK) MAS
INNER JOIN SALESDET (NOLOCK) DET ON DET.ENTRYID = MAS.ENTRYID
WHERE
((ISNULL(@LOCATIONCODE,0) = 0 AND 1= 1) OR (ISNULL(@LOCATIONCODE, 0) <>0 AND MAS.LOCATIONCODE =@LOCATIONCODE )) AND
((ISNULL(@BILLNO, '') = '' AND 1= 1) OR (ISNULL(@BILLNO, '') <> '' AND MAS.ENTRYNO =@BILLNO ))
ORDER BY PM.NAME
END
END
Fast Loading (approx 2 sec)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[ZZTEST]
@MODE AS INT = 0,
@LOCATIONCODE AS INT = 0,
@BILLNO AS VARCHAR(30) = ''
AS
BEGIN
IF @MODE = 9
BEGIN
SELECT MAS.NETBILLAMOUNT TOTALAMOUNT, MAS.SYSTEMNAME SYSTEM FROM SALESMAS (NOLOCK) MAS
INNER JOIN SALESDET (NOLOCK) DET ON DET.ENTRYID = MAS.ENTRYID
WHERE MAS.LOCATIONCODE = @LOCATIONCODE AND MAS.ENTRYNO = @BILLNO
ORDER BY PM.NAME
END
END
Both are same, but applying where condition is different. Second one is loading fast, first one is loading very slow.
I need to use where condition based on the variable so first one is what I needed, but its slowing down. I don't know what is the problem. can anyone help me to find out.
February 14, 2018 at 3:59 am
You have a classic Catch_all type query.
GilaMonster as a great blog post about this.
This type of code is true for every record in the table/index, thereby causing SQL to do full scans, rather than seeks to find the data.ISNULL(@LOCATIONCODE,0) = 0 AND 1= 1
February 14, 2018 at 4:24 am
DesNorton - Wednesday, February 14, 2018 3:59 AMYou have a classic Catch_all type query.
GilaMonster as a great blog post about this.
A very old blog post about it. (there's an updated one scheduled for a few weeks time)
Try the Simple Talk article for a better explanation: https://www.simple-talk.com/sql/t-sql-programming/how-to-confuse-the-sql-server-query-optimizer/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply