sql - Join table index performance improvements concerns -

sql - Join table index performance improvements concerns -

to start with, have 3 tables, primary key , other data. these tables joined in bring together table ~70 1000000 rows: table_1, table_2, table_3.

there primary key in bring together table across table_3_id, table_1_id, table_2_id (in order). there non-clustered index on table_1_id, table_2_id, table_3_id (in order) fill index of 95.

the info filtered table_1_id (i have preset of ~100 of these ids) , (through join) property table_3 (so uses table_3_id). then, table_1_id , table_2_id values returned. done in 1 query in entity framework.

this query:

var items = datacontext.tablesjoin.asnotracking() .join(datacontext.table_3.asnotracking(), x => x.table_3_id, x => x.id, (combi, scan) => new { combi, scan }) .where(x => possibleids.contains(x.combi.table_1_id) && otherids.contains(x.scan.other_id)) .select(x => new { firstid = x.combi.table_1_id, secondid = x.combi.deviceinformationdevices_id }) tolist();

because configuration running on sql server express, i'm running space problems (10gb max). info 2gb, primary key , index total of 5gb. because there more info in database, i'm interested in reducing size of index while retaining performance.

after looking @ everything, had concerns used. because of of bring together i'm not exclusively sure how useful include table_3_id in non-clustered index. removing column index saves around 1gb of space.

initially, had table clustered index (to safe space) because table has quite amount of inserts (1000 / hour) slow because of disk access had swap 10gb of info around. help if fill factor set lower (like 70) around this? of course, mean more wasted space if save lot on index might worth it?

this table used lot , performance index needed. running without index takes few minutes execute, whereas index done within 2 seconds.

execution plan xml: http://pastebin.com/raw.php?i=tfuxgyrk

you don't need primary key uniqueness. nci provides uniqueness. can rid of 1 of indexes. should save space.

you can save space other index uses making clustered. notices performance problems due to, apparently, randomly located inserts. plausible. consider changing column order of index inserts happen in 1 or few places. way pages affected tend cached. working set required dml low.

the dml perf problems not due page splits. these cause cpu load , fragmentation. perf problems because random pages must read disk.

1000 inserts per hr not lot. consider accumulating writes delta table little , cached. move rows on main table in background process. way dml latency off critical path. selects either need tolerate staleness or union all delta table.

sql sql-server entity-framework join indexing