indexing - SQL Server - Query performs Index Scan instead of Seek -

-

i'm in process of indexing content of cms lucene, have extended sql server database schema add "isindexed" bit column, lucene indexer can find piece of content hasn't been indexed.

i added index content table lookups isindexed column should go faster. database looks like:

create table content (     documentid bigint,     categoryid bigint,     title nvarchar(255),     authoruserid bigint,     body nvarchar(max),     isindexed bit ) create table users (     userid bigint,     username nvarchar(20) )

the following indexes exist:

content (     pk_content (clustered) : documentid asc     ix_categoryid (non-unique, non-clustered) : categoryid asc     ix_authoruserid (non-unique, non-clustered) : authoruserid asc     ix_indexed_asc (non-unique, non-clustered) : isindexed asc, documentid asc     ix_indexed_desc (non-unique, non-clustered) : isindexed desc, documentid asc )  users (     pk_users (clustered) : userid )

this query used find nonindexed content:

select     top 1     content.documentid,     content.categoryid,     content.title,     content.authoruserid,     content.body     users.username     content     inner join users on content.authoruserid = users.userid     isindexed = 0

however when run actual execution plan reports clustered index scan pk_content combined clustered index seek pk_users. query takes 300ms execute.

when modify query remove users.username field , users inner-join, query takes 60ms run , there no clustered index scan pk_content, clustered index seek pk_content.

i tried before , after adding descending index content.isindexed column, added content.documentid ix_indexed indexes, made no difference.

what doing wrong? i've made necessary indexes (and some). content table has hundreds of thousands of rows, users table, can't see why optimiser choose scan.

an index on such low selectivity column (only 2 values 0 , 1) going ignored, see the tipping point. 1 option move leftmost key in clustered index, , make primary key constraint on documentid non-clustered index:

create table content (     documentid bigint,     categoryid bigint,     title nvarchar(255),     authoruserid bigint,     body nvarchar(max),     isindexed bit,     constraint pk_documentid primary key nonclustered (documentid) )  create unique clustered index cdxcontent on content (isindexed, documentid);

another option create filtered covering index:

create unique index nonindexedcontent on content (documentid)   include (categoryid, title, authoruserid, body)   isindexed = 0;

this second option duplicate lot of content possibly. personally, go first option.


Comments

Post a Comment

Popular posts from this blog

c# - SVN Error : "svnadmin: E205000: Too many arguments" -

-
i trying reposotries using c# code process svncommand = null; var psi = new processstartinfo("svnadmin"); psi.redirectstandardoutput = true; psi.redirectstandarderror = true; psi.useshellexecute = false; psi.arguments= @"dump c:\repositories\myrepo > c:\temp\myrepodumpfile.dump"; using (svncommand = process.start(psi)) { var myoutput = svncommand.standardoutput; var myerror = svncommand.standarderror; debug.write("output :" + environment.newline + environment.newline + myoutput.readtoend() + environment.newline + "error :" + environment.newline + environment.newline + myerror.readtoend()); svncommand.close(); } when use dump command commandline svnadmin dump c:\repositories\myrepo > c:\temp\myrepodumpfile.dump it works fine when try ...
Read more

c# - Copy ObservableCollection to another ObservableCollection -

-
how copy observablecollection item observablecollection without reference of first collection? here observablecollection item value changes affecting both collections. code private observablecollection<ratemodel> _allmetalrate = new observablecollection<ratemodel>(); private observablecollection<ratemodel> _metalrateondate = new observablecollection<ratemodel>(); public observablecollection<ratemodel> allmetalrate { { return this._allmetalrate; } set { this._allmetalrate = value; notifypropertychanged("metalrate"); } } public observablecollection<ratemodel> metalrateondate { { return this._metalrateondate; } set { this._metalrateondate = value; notifypropertychanged("metalrateondate"); } } foreach (var item in metalrateondate) allmetalrate.add(item); what causing , how can solve it? you need clone object referenced item before it's a...
Read more

All overlapping substrings matching a java regex -

-
is there api method returns (possibly overlapping) substrings match regular expression? for example, have text string: string t = 04/31 412-555-1235; , , have pattern: pattern p = new pattern("\\d\\d+"); matches strings of 2 or more characters. the matches are: 04, 31, 412, 555, 1235. how overlapping matches? i want code return: 04, 31, 41, 412, 12, 55, 555, 55, 12, 123, 1235, 23, 235, 35. theoretically should possible -- there obvious o(n^2) algorithm enumerates , checks substrings against pattern. edit rather enumerating substrings, safer use region(int start, int end) method in matcher . checking pattern against separate, extracted substring might change result of match (e.g. if there non-capturing group or word boundary check @ start/end of pattern). edit 2 actually, it's unclear whether region() expect zero-width matches. specification vague, , experiments yield disappointing results. for example: string line = "xx90xx"; strin...
Read more