January 29, 2013 at 8:50 am
Straight-forward...at least I though so...I made certain all stats for this were updated, yet still shows an index scan? Note: certain keywords have hyphens in them to avoid my proxy server from catching "certain" SQL statements đŸ˜‰C-REATE TABLE [dbo].[etl_TEST](
[TranId] [decimal](19, 0) NOT NULL,
CmtTranType varchar(8) NULL,
[TxnFlag] [char](5) NULL,
[tranref] [decimal](19, 0) NULL,
[FeesType] [varchar](1) NULL,
CONSTRAINT [PK_etl_TEST] PRIMARY KEY CLUSTERED
(
[TranId] ASC
)ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO dbo.etl_TEST
SELECT 134567, 'CLCH', NULL, NULL, 'M' UNION ALL
SELECT 429209, 'CLCH', NULL, NULL, 'M' UNION ALL
SELECT 557849, 'CLCH', NULL, NULL, 'M' UNION ALL
SELECT 334518, 'CLCH', NULL, NULL, 'M' UNION ALL
SELECT 222784, 'CLCH', NULL, NULL, 'M' UNION ALL
SELECT 497859, 'CLCH', NULL, NULL, 'M'
C-REATE INDEX idx_TranID ON dbo.etl_TEST (TranID) INCLUDE (CmtTranType,TxnFlag,tranref,FeesType)
SELECT
Tranid,
cmttrantype,
txnflag,
tranref,
FeesType
FROM dbo.etl_TEST
WHERE TranId IS NOT NULL
D-ROP TABLE dbo.etl_TEST
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 29, 2013 at 8:59 am
Cause SQL is smart enough to know a PK column is never NULL so it need to get all records... hence a SCAN.
Pedro
January 29, 2013 at 9:01 am
And another thing that might happen...
If the table is small sometimes a scan is faster than a seek, so SQL chooses the scan...
but in your case SQL just knows the column is never null so it does a full scan to get the data required...
January 29, 2013 at 9:06 am
I see your point, but this table has nearly 700,000 rows, and in the execution plan it's using the non-clustered index (not the clustered).
If I drop the constraint altogether, it still results in a scan (at least in the test with the small subset of rows)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 29, 2013 at 9:14 am
The scan is because of the obvious... no point trying to get a SEEK on a PK column with the IS NOT NULL filter... Always a scan on that...
It probably uses the NC index cause its smaller than the Clustered one hence making less reads, hence faster...
Turn the statistics IO ON an use the index hint:
SET STATISTICS IO ON
SELECT
Tranid,
cmttrantype,
txnflag,
tranref,
FeesType
FROM dbo.etl_TEST WITH(INDEX(PK_etl_TEST))
WHERE TranId IS NOT NULL
SELECT
Tranid,
cmttrantype,
txnflag,
tranref,
FeesType
FROM dbo.etl_TEST WITH(INDEX(idx_TranID))
WHERE TranId IS NOT NULL
SET STATISTICS IO OFF
compare the output of the IO...
Pedro
January 29, 2013 at 9:17 am
Your code:
SELECT
Tranid,
cmttrantype,
txnflag,
tranref,
FeesType
FROM dbo.etl_TEST
WHERE TranId IS NOT NULL
Look close with eye at the WHERE clause. You are asking for all records where TranId is not null, therefore the scan of the index.
January 29, 2013 at 9:17 am
an index seek would only occur if it was looking for one value, or a range of values that were consecutive; otherwise it has to scan the index, right?
so WHERE PK IS NOT NULL can't use an index to narrow down the number of rows...it's got to grab the whole table, right?
Lowell
January 29, 2013 at 9:18 am
A small test with 10.000 rows and SQL has 73 logical reads with the PK and 52 with the NC index.
Pedro
January 29, 2013 at 12:19 pm
As Pedro mentioned, size of table is important to seek vs. scan, but as is order of fields in the index and continuity. There are a number of rules about seek vs. scan. First, I'd start here, with this blog from Gail:
http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/
Next, there's the tipping point calculations, you'll want to look up Kimberly Tripp for that one.
Finally, there's equijoin seeking vs. range seeking, or inequality predicates. Once again I turn to Gail's blog for an already well laid out discussion on the topic:
http://sqlinthewild.co.za/index.php/2009/02/06/index-columns-selectivity-and-inequality-predicates/
Column order can also trip you up. IS NOT NULL is a form of range predicate, in my experience to date.
In general, expect scans except for very tight selectivity in your where clause.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 29, 2013 at 1:05 pm
Thanks for the replies, I'll dig a tad further to see where it goes đŸ˜‰
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply