June 2, 2011 at 1:36 am
SELECT
(
SELECT COUNT(*)
FROM
Table (nolock) T
WHERE
T.Column1=F.Column1 And
T.Column2=F.Column2 AND
.........................
--Some 10 columns joined
) AS Count,*
FROM
Table F (NOLOCK)
WHERE
F.Column1=X, F.Column2=Y,
ORDER BY
Column1
Is it possible to write this query in a better way to improve the performance ?
June 2, 2011 at 2:00 am
If I read it correctly, then u will always get the same count if just use one table with where condition as T.Col1 will always equal to F.col1.. so on and so forth...
Can u not remove self join and get count(*) with where condition?
June 2, 2011 at 2:10 am
It depends.
Depending on the data structure the following code might return the same result:
SELECT sub.Count,F.*
FROM
Table F
INNER JOIN
(
SELECT COUNT(*), Column1, Column2
FROM
Table (nolock) T
WHERE
T.Column1= x And
T.Column2= y AND
.........................
--Some 10 columns joined
GROUP BY Column1, Column2
) sub
ON WHERE sub.Column1=F.Column1 And
sub.Column2=F.Column2
ORDER BY
Column1
If it doesn't please provide table def and sample data in a ready to use format as described in the first link in my signature. Also, please include the expected result based on your sample.
As a side note: joins on "some 10 columns" usually indicate a bad table design (missing primary and/or foreign key). If you post the table defs, we might be able to help resolving this issue as well.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply