Efficient data import?
We work with a lot of real estate, and while rebuilding the data import process, I ran into an interesting problem.
First, the way our system works (loosely speaking), we run a Coldfusion process once a day that pulls in the data provided by the IDX vendor via FTP. They push data towards us. Whatever they send us, we get.
This has proven to be quite volatile over the years.
I am rebuilding it from PHP to the RETS standard, which uses SOAP methods to retrieve data that is already much better than ours.
When it comes to "updating" existing data, my initial thought was to only request updated data. There is a box for "Modified" that says when the last update was updated and the code I have will grab any updates updated in the last 6 hours (give me a window if anything goes wrong) ...
However, I see that many real estate developers are proposing to create "batch" processes that go through all listings regardless of the status update that is constantly running.
Is this the best way to do it? Or am I fine, just grabbing the data that I know I need? It doesn't make much sense to me to do more processing than necessary. Thoughts?
source to share
If you can trust MODIFIED, you come up well enough and faster than parsing the entire file. If you are looping through the whole find, trying to find what has changed or not, it will be more difficult.
There will be a slight performance improvement if you can upload the file directly to the database and import it using the built-in file import functions. For example MySQL has this: http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html I'm sure other databases like SQL Server and Oracle have mechanisms like this. This saves you programming language overhead and uses database resources.
source to share
If you are lucky enough to have a last updated date, then it is certainly more efficient to limit the number of rows you update or import. Just make sure the date is actually updated by the data source when records change, as not all databases are well designed and don't always do what you expect.
And whatever you do, don't process line by line, this is the most inefficient way to do things. I can do a batch insert of a million records faster than I can process a dataset of 100,000 records one row at a time. But there are standard ways to do the type if you insert the one you are talking about and an updated 100 updated records will be faster than a batch insert of 50,000 records.
source to share