Search the Community
Showing results for tags 'infocentre'.
Found 1 result
-
Infocentre Access History - 140 million rows!
jstill posted a question in General Queries and Feedback
Hi all - been a while since I last posted but have a bit of an issue and I wondered if anyone else has come across a solution. At the moment it's looking like an expensive custom build. In short the access history is huge - we made the jump from IC to ICP a few months ago so we would no longer need a 12 hour full refresh and could just do the routine 'data refresh' rather than full bulk transfer. In a nutshell: · Our access history has over 141 million records in it. · A data refresh (not full bulk export) in ICP started at 9:15 am does not finish the same day. · A data refresh done at 03:00 am does complete, but takes 19.5 hours. · A few days ago I was able to see the BAH file (access hist) was being processed at 8:50am, which is one of the last files processed in the refresh – it looks like this took 12 hours plus to process. · During the refresh, queries get locked and there are delays for info going into ICP – I have seen delays of over 450 seconds shown on the monitor. · Obviously the full bulk refresh takes a lot longer due to this (as it did with original Infocenter). · For the meantime, I’ve changed the data refresh to only run on a Sunday so it doesn’t impact the business. The fix would be to remove a lot of the older records from the access history. As I understand it, the tables in ICP replicate the structure found in OpenGI. As such all we really want is a once off process that deletes records from OpenGI’s equivalent of icp_braccesshist. Getting rid of records prior to 01/01/2015 would approximately halve the data we have, but I’d be inclined to get rid of everything prior to 01/01/2017, which would lose over 90 million records. In short we just need the OpenGI equivalent of the below. delete from icp_braccesshist where [#date] < '20170101' Has anyone else had this issue and/or found a solution? I don't want to disabled the access hist export as we use the data for a few things. Kind regards James