Thank you for replying.
I shall try to get all information together you asked for.
Target (large table) definition: 10 columns, int, int, datetime, datetime, varchar(1024), varchar(512), bigint, bigint, bigint, bigint
unique non-clustered index on 2 of the bigints columns (combined into one index, ascending)
Input table is a temporary table which is loaded via bulk insert operation on a 64MB csv style file.
this is quite quick 1-3 seconds and results in about 500.000 rows in the temp table.
Definition: int, varchar(128), varchar(512), varchar(512), varchar(32), char(32), bigint, bigint
after bulk insert, primary key of the 2 bigints (combined) is created to aid with the following merge. Thats also done in a second or 2.
The following merge statement is then executed to merge the temp table into the large table:
merge FullURLs as target
using #tmpimport as source
on source.URLExLo = target.URLEExLo and source.URLExHi = target.URLEExHi
when matched then
update set SeenLast = CURRENT_TIMESTAMP, target.TimesSeen = target.TimesSeen+source.TimesSeen
when not matched then
insert ( TimesSeen, SeenFirst, SeenLast, URL, [File], URLEExLo, URLEExHi, DomainExLo, DomainExHi )
VALUES( 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, source.FullURL, source.[File], source.URLExLo, source.URLExHi, fnMD5ExLo( fnMD5Ex(Domain) ), fnMD5ExHi( fnMD5Ex(Domain) ) )
output $action into #tempresult
URLExLo and URLExHi are the 2 bigints I mentioned earlier which I index on (combined).
The execution plan is also attached, showing that the majority of the time (98%) is spend on the Index Seek and RID lookups. 15 minutes (!!) for the batch I collected
the attached exec plan for, which resulted in 0 inserts, and +472.000 row updates within the merge. (no inserts coz I ran the same batch already earlier).
So its slow either way, on insert and on updates and becomes slower and slower the large the table gets.
You can skipp all the other statements in the exec plan, its only the merge which takes teh whole focus.
Is that all the information you require to make a better judgement?
Thanks so much for looking into this with me - Im very much looking fwd to your pointers!