SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


help with this query !!!


help with this query !!!

Author
Message
curious_sqldba
curious_sqldba
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7566 Visits: 3696
Most of my cost is around the filtering which is done at the bottom of the code. My goal is to do the filtering when the table is joined so that we join on smaller set, can someone please assist me with this. More details are in the code.





DECLARE @CurrentDateTime DATETIME

SET @CurrentDateTime = Getdate()

DECLARE @Nth AS BIT
DECLARE @Aus INT
DECLARE @Ante NVARCHAR(255)
DECLARE @Atid INT
DECLARE @Adid INT
DECLARE @Brid INT
DECLARE @Tid INT
DECLARE @TeID INT
DECLARE @Rkey NVARCHAR(20)

SET @Rkey= '123456'

--Get Exception Info
SELECT @Tid = Tid
FROM Synonym_Tes
WHERE [ID] = @TeID

SELECT TOP(1)@Atid = Tid
FROM Synonym_Tasks
WHERE [Name] = 'Azn'

SET @Nth = 0

-- SET @Adid= 64
SELECT @Adid= brt.Dsid,
@Brid = Id
FROM Synonym_Brt brt
WHERE brt.Rtn = 'Part 1'

---- Is an auth needed?
IF EXISTS (SELECT 1
FROM tempdb..sysobjects
WHERE id = Object_id('tempdb..#Fds'))
DROP TABLE #Fds

IF EXISTS (SELECT 1
FROM tempdb..sysobjects
WHERE id = Object_id('tempdb..#Rls'))
DROP TABLE #Rls

IF EXISTS (SELECT 1
FROM tempdb..sysobjects
WHERE id = Object_id('tempdb..#Mtcs'))
DROP TABLE #Mtcs



CREATE TABLE #Sid
(
ID INT
)


CREATE TABLE #Fds
(
Dsid INT,
Rid INT,
Fnme VARCHAR(100),
Fve VARCHAR(255)
)

CREATE TABLE #Rls
(
Dsid INT,
Brid INT,
Fnme VARCHAR(100),
Fve VARCHAR(MAX),
FCprs VARCHAR(30),
Fopr VARCHAR(10)
)

CREATE TABLE #Mtcs
(
Dsid INT,
Rid INT,
Brid INT
)

DECLARE @CidINT
DECLARE @flag INT
DECLARE @Count INT

SET @flag = 1

SELECT @Count = Count(Rid)
FROM Cgs
WHERE Rid = @Rkey

WHILE ( @flag <= @Count )
BEGIN
SELECT @Cid= Id
FROM Cgs
WHERE Rid = @Rkey
AND Cordr = @flag


INSERT INTO #Fds
(Dsid,
Rid,
Fnme,
Fve)
SELECT @AuthDsid,
Rid,
Fnme,
Fve
FROM vw_FdsAzn
WHERE Rid= @Cid

SET @flag = @flag + 1
END

INSERT INTO #Rls
(Dsid,
Brid,
Fnme,
Fve,
FCprs,
Fopr)
SELECT @AuthDsid,
br.Id,
brc.Fnme,
Fve= CASE
WHEN brc.FCprs IN ( 'INLE', 'NOT-BLE' ) THEN [dbo].[my_function](brc.Fve, brc.FCprs)
ELSE brc.Fve
END,
brc.FCprs,
dsf.Fopr
FROM Synonym_BRls br
JOIN Synonym_Brc brc
ON brc.Brid= br.Id
JOIN Synonym_Brt brt
ON brt.Id = br.Brid
JOIN Synonym_DataSourceFds dsf
ON dsf.Fnme= brc.Fnme
AND dsf.Dsid = brt.Dsid
WHERE br.Atve= 1
AND br.Brid = @Brid





DECLARE @dlm CHAR

SET @dlm = ','

SELECT Rid,
Dsid,
Brid,
Fnme,
CompareFrom,
FCprs,
Fopr,
CompareTo,
Passed
INTO #FCprss
FROM (SELECT r.Brid,
r.Fnme,
r.FveAS CompareTo,
r.FCprs,
r.Fopr,
f.FveAS CompareFrom,
f.Rid,
f.Dsid,
CASE
WHEN ( ( r.FCprs = 'EQS'
AND ( f.Fve= r.Fve) )
OR ( r.FCprs = 'DOEL'
AND r.Fopr<> 'OR'
AND ( f.Fve<> r.Fve) )
OR ( r.FCprs = 'COS'
AND ( f.FveLIKE '%' + r.Fve+ '%' ) )
OR ( r.FCprs = 'DON'
AND r.Fopr<> 'OR'
AND ( f.FveNOT LIKE '%' + r.Fve+ '%' ) )
OR ( r.FCprs = 'BETH'
AND ( f.FveLIKE r.Fve+ '%' ) )
OR ( r.FCprs = 'DOTH'
AND r.Fopr<> 'OR'
AND ( f.FveNOT LIKE r.Fve+ '%' ) )
OR ( r.FCprs = 'ENH'
AND ( f.FveLIKE '%' + r.Fve) )
OR ( r.FCprs = 'DOTH'
AND r.Fopr<> 'OR'
AND ( f.FveNOT LIKE '%' + r.Fve) )
OR ( r.FCprs = 'ISNULL'
AND f.FveIS NULL )
OR ( r.FCprs = 'DOAL'
AND r.Fopr= 'OR'
AND ( Isnull(f.Fve, '') <> r.Fve) )
OR ( r.FCprs = 'DOEIN'
AND r.Fopr= 'OR'
AND ( Isnull(f.Fve, '') NOT LIKE '%' + r.Fve+ '%' ) )
OR ( r.FCprs = 'DOESITH'
AND r.Fopr= 'OR'
AND ( Isnull(f.Fve, '') NOT LIKE r.Fve+ '%' ) )
OR ( r.FCprs = 'DOETH'
AND r.Fopr= 'OR'
AND ( Isnull(f.Fve, '') NOT LIKE '%' + r.Fve) )
OR ( r.FCprs = 'ISNUMERIC'
AND Isnumeric(f.Fve) = 1 )
OR ( r.FCprs = 'BETWEEN'
AND f.FveBETWEEN Ltrim(Substring(r.Fve, 0, Charindex(',', r.Fve))) AND Ltrim(Substring(r.Fve, Charindex(',', r.Fve) + 1, 255)) )
OR ( r.FCprs = 'NOTBETWEEN'
AND f.FveNOT BETWEEN Ltrim(Substring(r.Fve, 0, Charindex(',', r.Fve))) AND Ltrim(Substring(r.Fve, Charindex(',', r.Fve) + 1, 255)) )
OR ( r.FCprs = 'NOTIN'
AND r.Fopr<> 'OR'
AND t.ID IS NULL
AND f.FveIS NOT NULL )
OR ( r.FCprs = 'NOTIN'
AND r.Fopr= 'OR'
AND t.ID IS NULL )
OR ( r.FCprs = 'IN'
AND t.ID IS NOT NULL )
OR ( r.FCprs = 'ntble'
AND r.Fopr<> 'OR'
AND t.ID IS NULL
AND f.FveIS NOT NULL )
OR ( r.FCprs = 'ntble'
AND r.Fopr= 'OR'
AND t.ID IS NULL )
OR ( r.FCprs = 'INTABLE'
AND t.ID IS NOT NULL ) ) THEN 1
ELSE 0
END AS Passed
FROM #Rls r
LEFT JOIN #Fds f
ON r.Fnme= f.Fnme
LEFT JOIN #Sid t
ON r.FCprs IN ( 'IN', 'NOTIN', 'INTABLE', 'ntble' )
AND t.ID <= Len(@dlm + r.Fve+ @dlm)
AND Substring(@dlm + r.Fve+ @dlm, t.ID - 1, 1) = @dlm -- i need to move this on the top where there is join on # Sid table.
AND Ltrim(Rtrim(Substring(@dlm + r.Fve+ @dlm, t.ID, Charindex(@dlm, @dlm + r.Fve+ @dlm, t.ID) - t.ID))) = f.Fve) a


curious_sqldba
curious_sqldba
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7566 Visits: 3696
please move this to appropriate forum..
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search