Query optimization

I have a view comprised of the following select statement.
SELECT
x.Key
FROM dbo.Table_X AS x
INNER JOIN
dbo.Table_Z AS z ON z.FKID = x.PVUID AND z.Key = x.Key
INNER JOIN
dbo.Table_Z AS abc ON abc.FKID = x.PVUID AND abc.Key = x.Key
AND abc.MyBit = 1
I have the following indexes for Table_Z:
Clustered: (Key, PFUID)
Nonclustered: (FKID, Key)
I have the following indexes for Table_X:
Clustered: (Key, PVUID)
Unique Nonclustered: (PVUID, Key)
Note: It does appear I have overlapping/duplicate indexes for Table_X. Unless
that factors in to the following, please disregard. Let's move on.
Table_Z has 98 rows.
Table_X has 84 rows.
All the fields used in the above select statement are data type int, except
field MyBit, which is data type bit.
In running the select statement (of which the view is comprised) I obtain the
following statistics:
Table 'Table_Z'. Scan count 76, logical reads 80, physical reads 0, read-
ahead reads 0.
Table 'Table_X'. Scan count 75, logical reads 75, physical reads 0, read-
ahead reads 0.
CPU time = 0 ms
Table_Z gets is the outer input using a Clustered Index Scan, Actual rows = 75, Estimated rows = 74.25
Table_X is the inner input using an Index Seek, Actual rows = 75, Estimated = 1
The two combine into a Nested Loop with 75 rows returned.
Followed by another Index Seek on Table_Z, Actual rows = 98, Estimated rows = 1.31
This results in another Nested Loop, with 98 rows returned.
I was hoping to optimize the select statement so I added a where clause:
SELECT
x.Key
FROM dbo.Table_X AS x
INNER JOIN
dbo.Table_Z AS z ON z.FKID = x.PVUID AND z.Key = x.Key
INNER JOIN
dbo.Table_Z AS abc ON abc.FKID = x.PVUID AND abc.Key = x.Key
AND abc.MyBit = 1
WHERE x.Key > 0
This resulted with the following:
Table 'Table_Z'. Scan count 159, logical reads 291, physical reads 3, read-
ahead reads 4.
Table 'Table_X'. Scan count 1, logical reads 4, physical reads 1, read-ahead
reads 3.
CPU time = 0 ms
Table_X gets is the outer input using a Clustered Index Seek, Actual rows = 84, Estimated rows = 83.93
Table_Z is the inner input using an Clustered Index Seek, Actual rows = 75,
Estimated = 1
The two combine into a Nested Loop with 75 rows returned.
Followed by another Index Seek on Table_Z, Actual rows = 98, Estimated rows = 1.31
This results in another Nested Loop, with 98 rows returned.
The where clause greatly reduced the scans on logical reads on Table_X, but
greatly increased the scans and logical reads on Table_Z. I tried adding an
additional filter of "AND z.Key > 0" but this had no affect. The statistics
and Explain Plan remained the same.
Is their any way to simultaneously reduce the scans and reads on Table_Z to
coincide with the reduction on Table_X while still returning the 98 rows?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1What's the difference between the two inner joins on table z? It's not
apparent to me why you'd join table_z twice. Can you strip the second join?
-T
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6065e661ba0a5@.uwe...
>I have a view comprised of the following select statement.
> SELECT
> x.Key
> FROM dbo.Table_X AS x
> INNER JOIN
> dbo.Table_Z AS z ON z.FKID = x.PVUID AND z.Key = x.Key
> INNER JOIN
> dbo.Table_Z AS abc ON abc.FKID = x.PVUID AND abc.Key = x.Key
> AND abc.MyBit = 1
> I have the following indexes for Table_Z:
> Clustered: (Key, PFUID)
> Nonclustered: (FKID, Key)
>
> I have the following indexes for Table_X:
> Clustered: (Key, PVUID)
> Unique Nonclustered: (PVUID, Key)
> Note: It does appear I have overlapping/duplicate indexes for Table_X.
> Unless
> that factors in to the following, please disregard. Let's move on.
> Table_Z has 98 rows.
> Table_X has 84 rows.
> All the fields used in the above select statement are data type int,
> except
> field MyBit, which is data type bit.
> In running the select statement (of which the view is comprised) I obtain
> the
> following statistics:
> Table 'Table_Z'. Scan count 76, logical reads 80, physical reads 0, read-
> ahead reads 0.
> Table 'Table_X'. Scan count 75, logical reads 75, physical reads 0, read-
> ahead reads 0.
> CPU time = 0 ms
> Table_Z gets is the outer input using a Clustered Index Scan, Actual rows
> => 75, Estimated rows = 74.25
> Table_X is the inner input using an Index Seek, Actual rows = 75,
> Estimated => 1
> The two combine into a Nested Loop with 75 rows returned.
> Followed by another Index Seek on Table_Z, Actual rows = 98, Estimated
> rows => 1.31
> This results in another Nested Loop, with 98 rows returned.
>
> I was hoping to optimize the select statement so I added a where clause:
> SELECT
> x.Key
> FROM dbo.Table_X AS x
> INNER JOIN
> dbo.Table_Z AS z ON z.FKID = x.PVUID AND z.Key = x.Key
> INNER JOIN
> dbo.Table_Z AS abc ON abc.FKID = x.PVUID AND abc.Key = x.Key
> AND abc.MyBit = 1
> WHERE x.Key > 0
> This resulted with the following:
> Table 'Table_Z'. Scan count 159, logical reads 291, physical reads 3,
> read-
> ahead reads 4.
> Table 'Table_X'. Scan count 1, logical reads 4, physical reads 1,
> read-ahead
> reads 3.
> CPU time = 0 ms
> Table_X gets is the outer input using a Clustered Index Seek, Actual rows
> => 84, Estimated rows = 83.93
> Table_Z is the inner input using an Clustered Index Seek, Actual rows => 75,
> Estimated = 1
> The two combine into a Nested Loop with 75 rows returned.
> Followed by another Index Seek on Table_Z, Actual rows = 98, Estimated
> rows => 1.31
> This results in another Nested Loop, with 98 rows returned.
> The where clause greatly reduced the scans on logical reads on Table_X,
> but
> greatly increased the scans and logical reads on Table_Z. I tried adding
> an
> additional filter of "AND z.Key > 0" but this had no affect. The
> statistics
> and Explain Plan remained the same.
> Is their any way to simultaneously reduce the scans and reads on Table_Z
> to
> coincide with the reduction on Table_X while still returning the 98 rows?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1