Jeff Moden (12/28/2014)
bantrim (12/28/2014)
Jeff,Thank you for your reply. I haven't tested the queryout option against the incremental query, but I guess I had assumed that I would run into the same issue as I did in the original query. Certainly, if I could get bcp's speed (or close to) in the query, I would be a happy guy.
Oh, and StartDttm doesn't have an index on it.
As for replication...well, it's kind of a long story. We have our EMR actually hosted by another medical organization...one with a very underfunded IS department. I've worked my way down many options, including replication, and my requests for getting anything setup on their end has failed. Actually, I know that TouchWorks updates the OLAP dB by exporting the transaction log from the OLTP environment each day, so the easiest option , I figured, would be to do an initial load and then get a copy of said log each night. No dice...
Thanks for the suggestions! I'll run a comparison with queryout and report back.
I don't know if the QueryOut option will actually help against the linked server. Any way you could "go direct" with the BCP? And is there any way that you could "push" an index onto the column so it stands a chance of performance?
I'm not 100% in the actual arrangement, but I do know that, through connecting via an IP address I was given, I can execute directly against their server, which is what I am using with BCP. Unfortunately, my requests at having any indices built have been denied as well. Our permissions are locked down to the extent that I can't even see execution plans.
I don't want to make this too confusing, but another reason why I began to lean towards the BCP full-table pull is because, through many days of querying, I have found that the datetime fields (i.e. CreateDttm, UpdateDttm, etc.) in the tables aren't 100%, in that they don't always get updated/populated when a change/insert occurs. Beyond this, since we are "sharing" this EMR with another medical facility, all of our data is mashed together, but we only are allowed to pull data for patients who have been to one of our facilities. So, if a patient goes to their facilities for years, and then goes to ours one day, I will need to pull that patient's entire history for all tables.
All of these things combined are starting to make me think maybe I just need to find a way to make the full refresh approach work.