How I build my SQL strings

[disruptivehair]

Where CITYPE is NOT NULL

Is CITYPE an INDEX? And it allows NULL's? I'm not so sure how NULL's are handled in INDEX SCANS - I'm not sure I have ever indexed a column that allows null's...

CI_Type is not indexed.

I did find this:
how many rows in that table?

In the table where all the data is actually held there are 528,643 rows.

[szlamany]

So a table scan is being done for that query.

Is the PRIMARY KEY a CLUSTERED INDEX?

[disruptivehair]

So a table scan is being done for that query.

It doesn't say a table scan is being done. I hate computers sometimes. I mean what am I, psychic?

Is the PRIMARY KEY a CLUSTERED INDEX?

Yes.

[szlamany]

...what am I, psychic?

I am

Actually, since you told me that the WHERE column was not an INDEX...

but I can see that 87% of the time was spent on something appearing to be named "PPEKBaseData.PK..."

that means you have a CLUSTERED PRIMARY KEY.

So your DATA LEAVES are in physically stored in with your PRIMARY INDEX LEAVES. You have no DATA section - so the PRIMARY KEY section was used.

It has no relationship with CI_Type so it was "table scanned"...

I'm guessing based on the number of rows+size of row data returned that the LAZY spooler was used to store the working resultset on disk - not enough memory space...

[szlamany]

[edit]Change it to:

Select * From dbo.vwfSpecialIssued
	Where PRIMARYKEYCOLUMN is in (Select PRIMARYKEYCOLUMN
					From dbo.vwfSpecialIssued
					Where CI_Type is not null)

and tell me if performance changes

[disruptivehair]

[edit]Change it to:

Select * From dbo.vwfSpecialIssued
	Where PRIMARYKEYCOLUMN is in (Select PRIMARYKEYCOLUMN
					From dbo.vwfSpecialIssued
					Where CI_Type is not null)

and tell me if performance changes

Nope, no change.

This:

Select * From dbo.vwfeSpecialIssued
	Where Pol_no in (Select Pol_no
			From dbo.vwfeSpecialIssued
			Where CI_Type is not null)

and this:

SELECT * FROM dbo.vwFESpecialIssued
WHERE CI_Type IS NOT NULL

took exactly the same amount of time to run though their execution plans look different. The one you wrote contains a clustered index seek and an index scan as well as three clustered index scans; the simple one below just has three clustered index scans.

[szlamany]

You did see my post #19 - right?

At any rate - I was trying to avoid the spool to disk by using the subquery to generate a "primary key list" - being smaller than SELECT *...

So that the final resultset would be served by an index scan.

But "IN (subquery)" is a stinky construct.

What's the goal here anyway? There is no way to really avoid a table scan when the WHERE clause is on some un-keyed column of data. Do you have the option of temporarily adding an INDEX for ci_type to see the difference - or do you need DBA blessing for that