May 26, 2011 at 7:07 am
Hi , i am currently facing trouble in query which cause of slow response,
i can not change structure in query in Actual Scenario(All other ways does not work for me),i have tried to simulate situation here.
Below is Table and Data Generation script:
[Code]
CREATE TABLE Industry ( IndustyID INT )
CREATE TABLE IndustryCriteria
(
IndustryID INT ,
CriteriaID INT ,--Belongs to criteria (leather,oil,etc)
SegmentID INT --if industry belongs to leather criteria the have multiple segment i.e. Shoe,Purse,Wallet)
)
CREATE TABLE CriSegmentAllocated
(
CriteriaID INT ,
SegmentID INT ,
PriceRange VARCHAR(100)
)
DECLARE @flag INT = 1
WHILE @flag <= 100
BEGIN
INSERT INTO dbo.Industry
( IndustyID )
VALUES ( @flag )
SET @flag = @flag + 1
END
SET @flag = 1
DECLARE @in_flag1 INT= 1
DECLARE @in_flag2 INT= 1
WHILE @flag <=20
BEGIN
SET @in_flag1 = 1
WHILE @in_flag1 <= 12
BEGIN
SET @in_flag2 = 1
WHILE @in_flag2 <= 14
BEGIN
INSERT INTO dbo.IndustryCriteria
( IndustryID, CriteriaID, SegmentID )
VALUES ( @flag, @in_flag1, @in_flag2 )
SET @in_flag2 = @in_flag2 + 1
END
SET @in_flag1 = @in_flag1 + 1
END
SET @flag = @flag + 1
END
INSERT INTO dbo.CriSegmentAllocated
( CriteriaID, SegmentID, PriceRange )
VALUES ( 1, 1, '<100' ),
( 1, 2, '100-200' )
[/code]
now i have following query of which execution plan i trouble to understand.
DECLARE @Criteria1_exists AS BIT = 1
SELECT COUNT(IndustyID)
FROM dbo.Industry
WHERE ( @Criteria1_exists = 0
OR IndustyID NOT IN (
SELECT IndustyID
FROM dbo.IndustryCriteria AS CI
INNER JOIN dbo.CriSegmentAllocated AS CD ON [CD].SegmentID = ci.SegmentID
WHERE CD.CriteriaID=1
AND CI.CriteriaID = 1 )
)
if you see attached image(I have attached Execution plan as well) i have marked in table scan portion of CriSegmentAllocated .which shows Actual row count 200 which passed to next step.In fact when table has only 2 row then how it can pass 200 rows?
if you remove "@Criteria1_exists = 0
OR" from where clause from above query then Actual rows passed to next step is perfect.i want to know why it returning wrong number of row.
in Actual scenario i have so millions of rows so there is huge difference,i have also created proper index but same problem.I can neither remove " "@Criteria1_exists = 0
OR" from query i need it for business logic.
May 26, 2011 at 7:14 am
Looks like a good place for dynamic sql and sp_executesql.
Have you tried that?
-- Gianluca Sartori
May 26, 2011 at 7:20 am
Gianluca Sartori (5/26/2011)
Looks like a good place for dynamic sql and sp_executesql.Have you tried that?
yes,it takes more time.
May 26, 2011 at 7:39 am
I find it hard to believe.
Have you tried this:
DECLARE @sql nvarchar(max)
SET @sql = N'
SELECT COUNT(IndustyID)
FROM dbo.Industry
'
IF @criteria1 = 1
SET @sql = @sql + N'
WHERE (IndustyID NOT IN (SELECT IndustyID
FROM dbo.IndustryCriteria AS CI
INNER JOIN dbo.CriSegmentAllocated AS CD
ON CD.SegmentID = ci.SegmentID
WHERE CD.CriteriaID = 1
AND CI.CriteriaID = 1))
'
EXEC sp_executesql @sql
-- Gianluca Sartori
May 26, 2011 at 9:01 am
Gianluca Sartori (5/26/2011)
I find it hard to believe.Have you tried this:
DECLARE @sql nvarchar(max)
SET @sql = N'
SELECT COUNT(IndustyID)
FROM dbo.Industry
'
IF @criteria1 = 1
SET @sql = @sql + N'
WHERE (IndustyID NOT IN (SELECT IndustyID
FROM dbo.IndustryCriteria AS CI
INNER JOIN dbo.CriSegmentAllocated AS CD
ON CD.SegmentID = ci.SegmentID
WHERE CD.CriteriaID = 1
AND CI.CriteriaID = 1))
'
EXEC sp_executesql @sql
Yes i tried that way but as performance affects.that is the problem.
May 26, 2011 at 9:04 am
That's a good read for you at the moment.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply