February 13, 2008 at 11:36 pm
While I trying to run the below query i got the error message
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'with'.
Msg 319, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Query is
with CTE1 (C1) as
(
Select C1 from T1
where C1 in (select val from SelField) and C2 = '1'
),
with CTE2 (IDK) as
(
select IDK,Count(*) as cnt from T2 group by IDK having Count(*) > 1
)
Select mx.IDK,mx.C4 from T3 mx INNER JOIN CTE1 CT on mx.C4 = CTE.C1
INNER JOIN CTE2 C on mx.IDK = CTE2.IDK
Is it possible to create the two common table expression ?
Kumar
February 14, 2008 at 2:47 am
Get rid of the second WITH.
February 14, 2008 at 2:57 am
Yes, thanks - I made the syntax error.
Yes i recitfied, and run the query but aim is to reduce the Query cost in "Key Lookup" area.
even though i got the same cost in key lookup area in execution plan. is anything apart while selecting the above query ?
thanks & regards
Saravanakumar.R
February 14, 2008 at 3:12 am
Maybe you could try variations on:
SELECT T3.IDK, T3.C4
FROM T3
WHERE EXISTS
    (
        SELECT *
        FROM T1
        WHERE T1.C1 = T3.IDK
            AND EXISTS
            (
                SELECT *
                FROM SelField S
                WHERE S.val = T1.C1
            )
    )
    AND EXISTS
    (
        SELECT T2.IDK
        FROM T2
        WHERE T2.IDK = T3.IDK
        GROUP BY T2.IDK
        HAVING COUNT(*) > 1    
    )
February 14, 2008 at 3:27 am
Create a covering index to avoid the lookup. If you post some sample data and what indices you currently have we'd be able to offer some more targeted advice.
February 14, 2008 at 11:24 pm
I have some around 64 Lakh rows and 10 columns
i compared the both the function
Using CTE I got the result on 18 Sec. but there is the difference while reading the table
Using Exist function i got the same ie., 17 Sec.
Using CTE,
Table 'mxCosmosDestination'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'mxDocument'. Scan count 10, logical reads 408733, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Using Exist function
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'mxCosmosDestination'. Scan count 2, logical reads 2116, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'mxDocument'. Scan count 2, logical reads 408604, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Should i go the Exits function Query / CTE Query. Kindly advice me. or comment if anything else.
thanks for your advance help.
regards
Kumar
February 14, 2008 at 11:25 pm
If i make the covering index also i got the same.
thanks & regards
Saravanakumar.R
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply