Geek asks and answers

Columns Included for Nonclustered Index

Hello
. I have a table with a lot of records (11 million). Entries have an external identifier, date, and some other fields.
I have a query that I often run in my application. The query looks something like this:


if( SELECT * FROM myTable WHERE Date=@Date AND ForeignID=@ForeignID != 0 )    
     UPDATE myTable SET ....... WHERE Date=@Date AND ForeignID=@ForeignID
else
     INSERT INTO myTable(......) VALUES(.....)

I want to add "Date" as a non-clustered index. Then, if I add the "ForeignID" column as the included column for this index, will it help the query run faster?
Thank.

+3
source share
4 answers

I agree with @gbn that you need an index for both Date and ForeignID, not "Include Column".

:

CREATE NONCLUSTERED INDEX [IDX1] ON [myTable] ([Date], [ForeignID])

" *" . "EXISTS"

+3

ForeignID , , ForeignID .

Date ForeignID.

:

create unique index IX_MyTable_Date_ForeignID on MyTable(Date, ForeignID)

, MERGE . , SQL .

+1

Date, ForeignID, Date. ( WHERE), , INCLUDE

Also, your test..update..else..insert template does not scale. See this for other ways: Select / Insert Upsert version: is there a design template for high concurrency?

+1
source

Yes, I would expect this to speed up this request significantly (do you need to make a choice *?)

but this will slow down the insertion into the table, since it has a different index for writing - it will obviously also increase the db size.

It might be worth adding that this often starts proc very slowly.

0
source

More articles:


All Articles