How to speed up the query speed between different server tables

  • 892717952

    SSC-Addicted

    Points: 450

    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!

    1. how to speed up the query speed while the SQL statement need to access tables on 2 SQL Server server ?
    2. The 2 servers' collation is different,  so we need to use collate word while joining 2 tables, even if we have created index on the join fields, then the index won't be used because of the collate word.  if so, how to address this problem ?
  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21197

    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

  • Grant Fritchey

    SSC Guru

    Points: 395653

    1. 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.
    2. 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).

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21197

    Grant Fritchey wrote:

     

      <li style="list-style-type: none;">

    1. 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;">

    1. 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

  • Grant Fritchey

    SSC Guru

    Points: 395653

    MVDBA (Mike Vessey) wrote:

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Jeff Moden

    SSC Guru

    Points: 995144

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • 892717952

    SSC-Addicted

    Points: 450

    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 !

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21197

    MVDBA (Mike Vessey) wrote:

    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;">

    1. 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;">

    1. 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

  • Jeff Moden

    SSC Guru

    Points: 995144

    Jeff Moden wrote:

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • ScottPletcher

    SSC Guru

    Points: 98214

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • 892717952

    SSC-Addicted

    Points: 450

    Thanks everyone for you good suggestions, thanks...!

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply