Quickly update a large amount of rows in a table, without blocking inserts on referencing tables, in SQL Server 2008 -


context: have system acts web ui legacy accounting system. legacy system sends me large text file, several times day, can update contract table in database (the file can have new contracts, or updated values existing contracts). table has around 2m rows , 150 columns. can't have downtime during these updates, since happen during day , there's 40 logged users in given time.

my system's users can't update contract table, can insert records in tables reference contract table (foreign keys contract table's id column).

to update contract table first load text file staging table, using bulk insert, , use merge statement create or update rows, in batches of 100k records. , here's problem - during merge statement, because i'm using read commited snapshot isolation, users can keep viewing data, can't insert - transactions timeout because contract table locked.

question: know of way update large amount of rows, while enforcing data integrity , without blocking inserts on referencing tables?

i've thought few workarounds, i'm hoping there's better way:

  1. drop foreign keys. - i'd enforce data consistency, don't sound solution.
  2. decrease batch size on merge statement transaction fast enough not cause timeouts on other transactions. - have tried this, sync process becomes slow; has mentioned above, receive update files , it's vital updated data available shortly after.
  3. create intermediate table, single contractid column , have other tables reference table, instead of contract table. allow me update faster while keeping decent integrity. - guess work, sounds convoluted.

update: ended dropping foreign keys. since system has been in production time , logs don't ever show foreign key constraint violations, i'm pretty sure no inconsistent data created. commented.


Comments

Post a Comment

Popular posts from this blog

linux - xterm copying to CLIPBOARD using copy-selection causes automatic updating of CLIPBOARD upon mouse selection -

i've tried various ways coerce xterm (versions 285 , 292) copy selection clipboard clipboard whenever press ctrl-shift-c. promising way far has been, in ~/.xresources, put this: xterm*vt100.translations: #override \ ctrl shift <keypress> c: copy-selection(clipboard) \n\ ctrl shift <keypress> v: insert-selection(clipboard) ctrl-shift-v works perfectly, copying has nuance... if restart xterm, highlighting text puts things in primary clipboard; expected, proper, default behavior. if hit ctrl-shift-c, copies current selection clipboard clipboard. the bug, however, if highlight text after first time pressed ctrl-shift-c, you'll see highlighting copies both primary and clipboard. cannot figure out how stop xterm updating clipboard upon selection... , doesn't make sense me in first place. i said, @ specific point in time, copy selection clipboard... yet starts updating automatically upon selection after doing once... anyone have workaround...
Read more

c++ - qgraphicsview horizontal scrolling always has a vertical delta -

Image
i've subclassed qgraphicsview custom canvas used in cad application. i've reimplemented qgraphicsview::wheelevent check keyboard modifiers control key and, if control key pressed, zoom. i'm trying implement horizontal scroll when user holds shift , uses wheel. the problem i'm having horizontal scrolling always scrolls 0.279. not huge problem, hugely annoying , points else being wrong. so, here questions: is right way implement horizontal scrolling? if not, is? how eliminate delta of 0.279? thanks in advance. code , sample output below void myview::zoom(int delta) { double factor = pow(1.2, delta/abs(delta)); this->scale(factor, factor); } void myview::scrollhorizontal(int level) { qpointf center = maptoscene(viewport()->rect().center()); qdebug() << "center: " << center.x() << ", " << center.y(); centeron(qpointf(center.x() - level, center.y())); } void myview::wheelevent(qwheeleve...
Read more