Same Execution Plans / Different Actual Number of Rows [modified]
-
I have got two queries both of which generate the same execution plan: query 1:
SELECT TOP 10 *
FROM news
CROSS APPLY (SELECT TOP 1 NetworkID FROM ItemNetwork WHERE ItemID = news.ID) itemNetquery 2:
SELECT TOP 10 *
FROM news
CROSS APPLY (SELECT TOP 1 NetworkID FROM ItemNetwork WHERE ItemID = news.ID AND ItemType = 0) itemNetItemNetwork table has 4 columns:
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[ItemID] [bigint] NOT NULL,
[ItemType] [tinyint] NOT NULL,
[NetworkID] [int] NOT NULLI have also created a non-clustered index on ItemNetwork table:
CREATE NONCLUSTERED INDEX [IX_ItemNetwork_ItemID_ItemType__NetworkID] ON ItemNetwork
(
[ItemID] ASC,
[ItemType] ASC
)
INCLUDE ( [NetworkID])The first query takes one second to execute, while it takes 2 minutes for the second one to execute. The execution plan for both queries is the same. You can see the execution plan for the first query here[^] and for the second query here[^]. The only difference that can be seen between the two execution plans is the amount of data that comes out of news table. For the second query, we see a very big arrow coming out of news table. That is because the actual number of rows coming out of this table is 1534672 rows while for the first query, this number is 877 rows. For both queries, the estimated number of rows is 10 (because of top 10 clause). Look at the actual number of rows for both queries here[^] and here[^]. The only difference between the two queries is this condition:
ItemType = 0
I also updated the statistics for all the tables involved, but it didn't make any difference. Could somebody please tell me how I can make the second query execute as fast as the first one? p.s. the total number of rows in
Newstable is 1576612 rows, inNetworktable 1820 rows and inItemNetworktable 42164 rowsmodified on Thursday, June 25, 2009 3:19 AM
-
I have got two queries both of which generate the same execution plan: query 1:
SELECT TOP 10 *
FROM news
CROSS APPLY (SELECT TOP 1 NetworkID FROM ItemNetwork WHERE ItemID = news.ID) itemNetquery 2:
SELECT TOP 10 *
FROM news
CROSS APPLY (SELECT TOP 1 NetworkID FROM ItemNetwork WHERE ItemID = news.ID AND ItemType = 0) itemNetItemNetwork table has 4 columns:
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[ItemID] [bigint] NOT NULL,
[ItemType] [tinyint] NOT NULL,
[NetworkID] [int] NOT NULLI have also created a non-clustered index on ItemNetwork table:
CREATE NONCLUSTERED INDEX [IX_ItemNetwork_ItemID_ItemType__NetworkID] ON ItemNetwork
(
[ItemID] ASC,
[ItemType] ASC
)
INCLUDE ( [NetworkID])The first query takes one second to execute, while it takes 2 minutes for the second one to execute. The execution plan for both queries is the same. You can see the execution plan for the first query here[^] and for the second query here[^]. The only difference that can be seen between the two execution plans is the amount of data that comes out of news table. For the second query, we see a very big arrow coming out of news table. That is because the actual number of rows coming out of this table is 1534672 rows while for the first query, this number is 877 rows. For both queries, the estimated number of rows is 10 (because of top 10 clause). Look at the actual number of rows for both queries here[^] and here[^]. The only difference between the two queries is this condition:
ItemType = 0
I also updated the statistics for all the tables involved, but it didn't make any difference. Could somebody please tell me how I can make the second query execute as fast as the first one? p.s. the total number of rows in
Newstable is 1576612 rows, inNetworktable 1820 rows and inItemNetworktable 42164 rowsmodified on Thursday, June 25, 2009 3:19 AM
Is it possible that the index could be fragmented? Not that it should make such a significant difference but might be worth checking.
-
Is it possible that the index could be fragmented? Not that it should make such a significant difference but might be worth checking.
I looked at it. The fragmentation is 0%
