Hi All,
I am building a dashboard for a warehouse so the management can view what is going on in their warehouses - which are big. The problem for them is that they are in the midst of a migration project from an old Warehouse Management System (WMS) to a new one. I solve that problem as QV sits over both databases and allows a consolidated view.
I have just got to the stage (with many thanks to this wonderfully helpful community) where I now have to interrogate a transaction history table - and I have to do this twice. Once in the older system and once in the new system. Old system = old slow hardware with more users and more transactions currently. New system = new super fast system (with fewer WMS records).
The transaction history folder basically holds every transaction to every SKU in the warehouse. Even though I am only interested in this year's data and restrict the SQL select to older than '01/01/2011', I am coming up against a problem. I retrieve around 75,000 records from the new system in less than 5 seconds (roughly) and then I retrieve 900,000+ records from the old system in 2.5 hours.
My thoughts were that if I only have to interrogate the live systems for the last month's data, could I read in and save the year's data apart from the last 30 days into a QVD file. I would still have to open and read in these records whenever the application was refreshed, but as the data now resides in QVD files, I should avoid waiting for the DBMS to service my request and there will be less load on the DB and certainly no contention for the older data. Right? The data never changes once it is older than about a week.
Is this the right approach to this problem? Then once a month I re-run the separate QV application to refresh the data in the QVD file(s) and update my script in the main application to only read in "live records" newer than the latest QVD file creation date from the DBMS.
Hope this makes sense. And if anyone can send a link to QVD best practises - if that sort of thing exists - much appreciated. Is there any way to schedule and automate the process of saving the data every week for data older than 30 days?
Regards
Ian
