November 17, 2011 at 12:53 pm
Hi, i have a requiremetn where i have to compare list of values with values in other server.
(we are using linked server to achieve that)
so, is that good solution to get all related field values that i need to compare with my local list in a single call and keep them in memory(list) and compare my local list values to see if the local values are valid or not...
or
is that good way to make calls to other server(linked) per each value i have in my local list.. (they might be in 100's)
what i am thinking is that instead of making called per each row to other server i think getttin all at single call and keep them in memory and checking them in code is a good thing to do ?
please advise me.
November 17, 2011 at 1:06 pm
as with anything in SQL "it depends."
How often will you be doing this comparison? Daily, Hourly, Every millisecond, as part of another process, etc...?
How many records are you talking about comparing (pulling over from the linked server)
Are the values from the linked server static, or how often do they change?
There are a bunch of factors to account for here so I'll submit my answers with the caveat of it depends.
If it's an infrequent need or the set to compare to is small then it probably doesn't matter.
If it's frequent or costly(large result set) then I would say pull all values at once.
If the values are fairly static, then pull all at once.
If the values change frequently, then you will probably need to do them one at a time to avoid stale (out of date) data
But again, it really depends!
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 17, 2011 at 1:14 pm
How often will you be doing this comparison? Daily, Hourly, Every millisecond, as part of another process, etc...?
may be 7-8 times a day
How many records are you talking about comparing (pulling over from the linked server)
Are the values from the linked server static, or how often do they change?
yes they are static they dont change in minuits (while the data is pulled/compared)
There are a bunch of factors to account for here so I'll submit my answers with the caveat of it depends.
If it's an infrequent need or the set to compare to is small then it probably doesn't matter.
the values that we need to compare from linked server are in thousands(more than 20.000)
my local values will be 100's
If it's frequent or costly(large result set) then I would say pull all values at once.
If the values are fairly static, then pull all at once.
If the values change frequently, then you will probably need to do them one at a time to avoid stale (out of date) data
i mean my single pull i was talking about is everytime this compare needs to be done.. lets say on button click.. (i am not pulling them and keeping them for days.. they are being pulled once the button is pushed)
so next time they need to push the button i am getting them again etc..[/i]
November 17, 2011 at 1:41 pm
whole (11/17/2011)
How often will you be doing this comparison? Daily, Hourly, Every millisecond, as part of another process, etc...?may be 7-8 times a day
How many records are you talking about comparing (pulling over from the linked server)
Are the values from the linked server static, or how often do they change?
yes they are static they dont change in minuits (while the data is pulled/compared)
There are a bunch of factors to account for here so I'll submit my answers with the caveat of it depends.
If it's an infrequent need or the set to compare to is small then it probably doesn't matter.
the values that we need to compare from linked server are in thousands(more than 20.000)
my local values will be 100's
If it's frequent or costly(large result set) then I would say pull all values at once.
If the values are fairly static, then pull all at once.
If the values change frequently, then you will probably need to do them one at a time to avoid stale (out of date) data
i mean my single pull i was talking about is everytime this compare needs to be done.. lets say on button click.. (i am not pulling them and keeping them for days.. they are being pulled once the button is pushed)
so next time they need to push the button i am getting them again etc..[/i]
Then you should probably pull all 200k into a temp table in your procedure, then do your compare. In general you want to limit the time the linked server connection is used.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 17, 2011 at 2:07 pm
If you pull all of them into a table variable, it will be faster than a temp table, because it avoid the distributed transaction coordinator. That will often give you more of a performance boost than the costs associated with table variable execution plan limits.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply