Database Discussion Boards

Database

Same Execution Plans / Different Actual Number of Rows [modified]

Meysam Mahfouzi24-Jun-09 4:27

Meysam Mahfouzi

24-Jun-09 4:27

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) itemNet

query 2:

SELECT TOP 10 *
FROM news
CROSS APPLY (SELECT TOP 1 NetworkID FROM ItemNetwork WHERE ItemID = news.ID AND ItemType = 0) itemNet

ItemNetwork table has 4 columns:

[ID] [bigint] IDENTITY(1,1) NOT NULL,
[ItemID] [bigint] NOT NULL,
[ItemType] [tinyint] NOT NULL,
[NetworkID] [int] NOT NULL

I 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 News table is 1576612 rows, in Network table 1820 rows and in ItemNetwork table 42164 rows

modified on Thursday, June 25, 2009 3:19 AM