Sitemap

Amicuk Programming Answers

Suggestions For Handling Bulk Updates Without Blocking Local User Updates

-0001-11-30   Views:0

Advertisement

Hi, This is a request for general implementation suggestions. We have a CRM database that is used by a call center application to allow reps to update customer info during business hours.  Outside of business hours we receive data feeds from another

Hi,
This is a request for general implementation suggestions.
We have a CRM database that is used by a call center application to allow reps to update customer info during business hours.  Outside of business hours we receive data feeds from another source that are bulk uploaded into the database to refresh the
data. This has been working fine for now, but we are expanding the use of the app to offices in other countries and are beginning to encounter more blocking during the bulk upload because now the app is being used outside our local business hours because of
the time difference.
It seems this would be a common problem, but I haven't been able to identify a good source of information on methods to overcome this. 
What suggestions do people have to complete bulk loads while still allowing updates by local users?
Ideas I have been considering include duplicating the database and performing merge replication, using service broker to queue updates during the bulk load, using snapshot isolation or isolation levels with row versioning....
Any ideas would be greatly appreciated.
Thanks,
Reinis

The replay answer
Advertisement
I have considered trying to break the update into chunks, but my fear as you said is it will take a lot longer.
Quite a few years ago, I rewrote a process in our system to make it a set-based update for better performance. But I heard as late as today from our customer with the biggest volumes, that they are still running the old process which updates one by one,
because when they do all at once the blocks other operations. (Which admittedly is due to other shortcomings in the system.)
Anyway, I would recommend you to look into that, and particularly make the chunks size configurable. Maybe you are able to find a sweet spot where blocking is not a problem, but the chunks are till big enough to be efficient.
Both of those resolutions are feasible and I certainly know there will be significant changes we may have to undertake.  I was just thinking that this must be a really common issue now with the global reach of data and there must be people who have
handled it in different ways.
It's not really a simple problem, and the solution is likely to depend on the current architecure you have. What fits in one shop, may not fit in another. And most of all, one solution may be a lot less costly to implement than another.
Erland Sommarskog, SQL Server MVP, [email protected]

Go to See the other 5 answers

Suggestions For Handling Bulk Updates Without Blocking Local User Updates

Category:DefaultRelease time:-0001-11-30Views:130

Hi, This is a request for general implementation suggestions. We have a CRM database that is used by a call center application to allow reps to update customer info during business hours.  Outside of business hours we receive data feeds from another[More]

Doubt on bulk update

Category:DefaultRelease time:-0001-11-30Views:130

Hi, We have the below code block to do one of our bulk updates. Its taking way too long to finish. its 10g 10.2.0.4 version. can anyone pls suggest any other alternatives to make this code run faster ? DECLARE CURSOR s_cur IS SELECT /*+ PARALLEL(item[More]

No Data Found Exception in bulk updates

Category:DefaultRelease time:2015-10-11Views:130

I am trying to catch no data found exception in bulk updates when it does not find a record to update in the forall loop. OPEN casualty; LOOP FETCH casulaty BULK COLLECT INTO v_cas,v_adj,v_nbr LIMIT 10000; FORALL i IN 1..v_cas.count UPDATE tpl_casual[More]

Bulk Update query

Category:DefaultRelease time:-0001-11-30Views:130

Hello Friends, Can some one suggest me is that any possible bulk update query which consumes less timings? Table - MyTable id - PK. orderid - Order Id. Subid - Sub Id for an Order. lineitem - LineItemId. ProducId - Product Id. Now i want to update th[More]

Best practice: bulk update (inverse of REF CURSOR SELECT)??

Category:DefaultRelease time:-0001-11-30Views:130

To move data from the database to the application, there are REF CURSORS. However, there is no easy way to move updates/inserts from a dataset back to the database. Could someone provide some guidelines or simple examples of how to do bulk updates (a[More]

BULK UPDATE

Category:DefaultRelease time:-0001-11-30Views:130

Hi, I am trying to do Update in batches, but with very little success. I did some reading and tried using BULK COLLECT and FORALL to accomplish the task. I am not sure where I messed up but only one row is being updated. Also I am not sure if there i[More]

Is there a bulk update statement in 11g?

Category:DefaultRelease time:-0001-11-30Views:130

Hi everyone, This is a pretty simple request but I cannot seem to find any documentation for it (at least on the 11g DB version). I have a simple update statement: UPDATE W_AP_XACT_FS SET DATASOURCE_NUM_ID = 5; COMMIT;It probably seems strange why I[More]

Can I update a reference in a JPQL bulk update?

Category:DefaultRelease time:-0001-11-30Views:130

I have an InvoiceHeaders entity which has a many to one reference to an InvoiceStatus table with a small number of rows. What I need to do is to bulk-update a set of invoices, changing their status. I tried UPDATE InvoiceHeaders i SET i.status = (SEL[More]

Sophos Firewall blocking local debugging on VS 2013

Category:DefaultRelease time:-0001-11-30Views:130

Sophos Firewall is blocking local debugging on VS 2013. Unfortunately the firewall log isn't saying much about what is being blocked. The article below says uninstalling and reinstalling would work but that doesn't seem to be the right solution.  htt[More]

How can I do a bulk update?

Category:DefaultRelease time:2015-10-11Views:130

Hi In our BC4J application I need to perform a "bulk update", that is, I need to iterate over all the rows of a View and set one attribute on each one. Tracing the SQL session, I note that the BC4J framework is doing a select for update where pk[More]

SMC not getting suggested patch info from Update Manager in Solaris 10

Category:DefaultRelease time:2015-10-11Views:130

Hi All, why SMC not getting suggested patch info from Update Manager in Solaris 10?? Thank you in advance.Hi All, why SMC not getting suggested patch info from Update Manager in Solaris 10?? Thank you in advance.Read other 15 answers[More]

Hot
Hello everybody, I was thinking about purchasing Web Premium CS4 and was wondering about the license. Currently I'm running on a Windows 7 desktop and was considering purchasing a Mac laptop. Would I be able to use the same key on the desktop and lap [More]
Hey when I try to log on to the computer using a domain account, I get "User Profile Service failed the logon. User profile cannot be loaded" it allows me to login using the local account. I have restored the computer as far back as it can go, I [More]
Hi Folks, I am trying to use alv grid list display in function module with at user-command event to call specific transaction in my report. But its not working,its going to short dump. when i am doing this fucntionality in report its working fine but [More]
I have been regulary getting an 'out of memory' error when i choose to use my bespoke preflight profile. I have 4gig of ram and run Indesign CS5 on OS 10.6.8. Does anyone know a work around? As soon as I select from the basic default profile, i get t [More]
How do you restore the events folder in fcpx? Now the 'project' is only the sequence and not the project any more, if your events drive gets corrupt or stops working you'll need a way to recapture the files into the events folder exactly the same way [More]
when i go to download apps form my ipod touch, it requires a password to a username i've never seen before. These are legit apps, and i have no idea why and it won't let me change the usename, only allows me to type in a password.Go to Settings>iTune [More]
This only happens with full installation of software. I used the web site to update everything. Maybe I did it out of order. I don't know, but I've tried 3 times. Same results. I like to use all the full functions of the hardware & software, but I ha [More]
Requirement : I had created multiple projects in an application, what i want to do is to refer the common Skin and Template created across the project. I found the way where i can locate the resource bundle across projects, in Project Properties. Is [More]
I need to access internal information of a certain object that I created from servlet. when I do this <h1><c:out value='${content}' /></h1> this one returns toString() value of the object. But I also need to access Object.getName(), Obje [More]
I just upgraded to Snow Leopard and now none of the interface text will display in Final Cut Express. (The top menus are OK, but no file or sequence titles, effects names, etc, within Final Cut project windows or the like.) Immediately after the upgr [More]