December 3, 2019 at 3:02 am
There is a SQL statement to select data from 2 SQL Server tables, and the the collation of these 2 servers is different. and I want to know below, thanks!
December 3, 2019 at 11:00 am
Sometimes it can be better to create a table variable (or temp table) and fetch your data into it, then use that in your join.. not always
I've started using sql replication.. I can put a copy of my data anywhere and not have to use linked servers because the data is local..
have a play with replication... it might help - ps - in replication settings there are options about keeping collation settings... it might help
MVDBA
December 3, 2019 at 12:23 pm
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 3, 2019 at 3:57 pm
<li style="list-style-type: none;">
- OPENQUERY is your friend. Linked servers and letting SQL Server figure out if, maybe, this time, it pushes predicates to the second server to filter the data there before attempting to move it over the wire can be extremely unreliable. OPENQUERY can force the pass through to the secondary server. However, they're a pain to use.
<li style="list-style-type: none;">
- Nothing will let that stay in place and get you good performance. So, you need to look at doing something like Mike suggested. Move to a temporary table or have a second copy of the data in another database (maybe a local one). I'm not sure I agree with the use of replication (not a fan), but something along those lines is a possibility (not arguing with Mike, I just find replication to be so fragile, I have a hard time recommending it).
correct - it is fragile. and very frustrating - but with the correct usage and a DBA checking up on it all the time it can be very useful. ours breaks occasionally and all I have to do is "reinitialise subscription"
do you think there is a market for a new feature ? or am i missing a trick here?
MVDBA
December 3, 2019 at 4:24 pm
correct - it is fragile. and very frustrating - but with the correct usage and a DBA checking up on it all the time it can be very useful. ours breaks occasionally and all I have to do is "reinitialise subscription"
do you think there is a market for a new feature ? or am i missing a trick here?
Honestly, I feel better setting up my own data migrations for small stuff rather than rely on replication. If I'm going big, whole databases, Availability Groups for the win. However, no, you're not missing anything. There really is no in-between service between roll your own and AG except Replication. I just find it too big a pain. That's just me. I know it's all over the place. However, Microsoft has ZERO plans to show it love, so it's always going to be a bit of a wreck.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 3, 2019 at 5:13 pm
We combine OPENQUERY against a LinkedServer and it does appear to help performance quite a bit (especially when pointing at AS400s or IBM PowerSystems). I have no measured stats on it though but the observable difference was substantial.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 4, 2019 at 4:14 am
Thanks Mike?Grant and Jeff for you kind help !
I tested it with linked server and openquery and found it there was no any improvement in the performance. but I found another question maybe I will post a new post about them, thanks !
December 4, 2019 at 8:50 am
Grant Fritchey wrote:
<li style="list-style-type: none;">
<li style="list-style-type: none;">
<li style="list-style-type: none;">
<li style="list-style-type: none;">
- OPENQUERY is your friend. Linked servers and letting SQL Server figure out if, maybe, this time, it pushes predicates to the second server to filter the data there before attempting to move it over the wire can be extremely unreliable. OPENQUERY can force the pass through to the secondary server. However, they're a pain to use.
<li style="list-style-type: none;">
<li style="list-style-type: none;">
<li style="list-style-type: none;">
<li style="list-style-type: none;">
- Nothing will let that stay in place and get you good performance. So, you need to look at doing something like Mike suggested. Move to a temporary table or have a second copy of the data in another database (maybe a local one). I'm not sure I agree with the use of replication (not a fan), but something along those lines is a possibility (not arguing with Mike, I just find replication to be so fragile, I have a hard time recommending it).
correct - it is fragile. and very frustrating - but with the correct usage and a DBA checking up on it all the time it can be very useful. ours breaks occasionally and all I have to do is "reinitialise subscription"
do you think there is a market for a new feature ? or am i missing a trick here?
my reason for using replication is that we only want 3 fields out of the 15 used - and i want them as close to realtime as i can get. I could write my own job, but a few clicks on the replication tab was easier. And Tracer tokens are gods gift to any DBA who uses replication 🙂
MVDBA
December 4, 2019 at 4:49 pm
We combine OPENQUERY against a LinkedServer and it does appear to help performance quite a bit (especially when pointing at AS400s or IBM PowerSystems). I have no measured stats on it though but the observable difference was substantial.
I'll also state the the correct indexes DO need to be in-place on the far side of the OPENQUERY.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 4, 2019 at 7:37 pm
2. ... [H]ow to address this problem ?
That depends on the specific details in this situation.
2A. How many rows are in the remote table?
2B. How much data do you need from the remote table?
2C. How much data volume is a table of matching criteria from the local table? For example, if the tables are joined on id, what is the total volume of id data that would need pushed to the remote table to allow all matching selection to be done on the remote server?
More generally, SQL will need to move the remote data locally to join to it. Thus, if the total amount of remote data is trivial -- given your system's capacity to transmit the data -- just copy all the remote data locally, then join to it there. If the total remote data is (very) large, and the list of matching is (relatively) small, then copy the id list to the remote instance to reduce the data selected from there.
Often the data volumes will fall in between those extremes. In those cases oftens it's best to create a covering index on the remote table for this specific selection. Now, in general, I'm not a fan of the too-common approach of creating gazillions of covering indexes, one for (almost) every query (*), but for remote usage, covering indexes can be especially performant.
(*) because in the vast majority of cases, focusing on setting up the best clustering of the table yields much better overall performance and lower resource usage than scads of covering indexes on a by-default identity clustering of the table.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 5, 2019 at 5:44 am
Thanks everyone for you good suggestions, thanks...!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply