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
Hi There Can I use an icloud email address as the primary email address for an Apple ID?Whenever you create a new icloud email it is an Apple ID, but if you previously had an Apple ID you cannot use the new email to replace the old one. You have to a [More]
Hello, my iPod touch is not working right. It says its connected but if I select App Store, safari and other apps that require wifi pops out a note saying either"error loading" or "cannot connect to iTunes Store" and would exit the app [More]
I had to do a file recovery on a hard drive with a program called TestDisk. It recovered 30GB+ of files but it recovered data by ignoring the partition/partition table. Because of this it uses its own naming structure during the recovery. The names o [More]
We are attempting to print barcode labels from XML Publisher. This question is not about fonts but rather label size. In our Word template, we selected a label size under Page Setup. But we have an odd requirement to publish that document as a PDF fo [More]
Hi, I tried using the HP uninstall program to remove the HP printer drivers and related software, but I still see them in my hard drive. I see HP files in my Library/Preferences and Library/Printers folder. I will remove them by hand. Are there other [More]
I have got a problem during syncing my photos from Aperture to my iPhone. And later iTunes wrote aperture library is not available. What can I do now? Thanks. AronAre you sure that it isn't how you are viewing them on the iPad? Open Photos on the iPa [More]
I'm purchasing a macbook pro and have internet capability on my existing G5. What do I need to purchase in order to use the internet simultaneously with both computers? TIAOk, so I was having the same problems as everyone has been complaining about, [More]
I've a table contains the following columns and the data create table test_test2 (do_id_tp varchar2(32),do_id number ,do_up_tp varchar2(32),do_up_id number) insert into test_test2 values('A_S',100170,'A_S',100001); insert into test_test2 values('P_G' [More]
Hello, I installed crystal report server 2008 and Iu2019m missing the directory and classes in "com.crystaldecisions.sdk.framework" on the webreporting.jar package. Does anyone know why? Or does anyone know where I can get the complete webreport [More]
Hi!!!!!!!! First of all i wanna say that this same topic is published in "Java Development Forum". I use JBuilder as IDE and i developed an applet that uses XYLayout. xylauyout is included in jbcl package. The import sentence is like this: " [More]