Professional Web Applications Themes

long running distributed query - Microsoft SQL / MS SQL Server

Hello: When I run the following distributed query, it takes several minutes to complete as opposed to running the same query on the remote server, where this query runs in a second. -- query start select Account_ID, sum(case portfolio_num when 1 then Income_Commission_Amt else 0 end) as Capital, sum(case portfolio_num when 2 then Income_Commission_Amt else 0 end) as Income from <remote_server>.<db_name>.dbo.FINANCIAL_TRAN where account_id='123456789' and entry_dt between '2002-07-01' and '2003-06-30' and isnull(Income_Commission_Amt,0)<>0 group by account_id -- query end From the execution plan of this query, it displays the Remote Query operator to be sending over 3 million rows to the Filter ...

  1. #1

    Default long running distributed query

    Hello:

    When I run the following distributed query, it takes
    several minutes to complete as opposed to running the same
    query on the remote server, where this query runs in a
    second.

    -- query start
    select Account_ID,
    sum(case portfolio_num when 1 then
    Income_Commission_Amt else 0 end) as Capital,
    sum(case portfolio_num when 2 then
    Income_Commission_Amt else 0 end) as Income
    from <remote_server>.<db_name>.dbo.FINANCIAL_TRAN where
    account_id='123456789' and
    entry_dt between '2002-07-01' and '2003-06-30' and
    isnull(Income_Commission_Amt,0)<>0
    group by account_id
    -- query end

    From the execution plan of this query, it displays the
    Remote Query operator to be sending over 3 million rows to
    the Filter operator. This is probably why my query runs
    slowly when executed as a distributed query.

    Any ideas as to how I can improve this query to have run
    more efficiently.

    Your responses are highly appreciated. Thanks.
    Rob Guest

  2. #2

    Default Re: long running distributed query

    Try using OPENQUERY for this query. See Books Online for more information
    and examples.
    --
    HTH,
    Vyas, MVP (SQL Server)
    http://vyaskn.tripod.com/
    What hardware is your SQL Server running on?
    http://vyaskn.tripod.com/poll.htm


    "Rob" <com> wrote in message
    news:0bf501c356a9$6d15c1b0$gbl...
    Hello:

    When I run the following distributed query, it takes
    several minutes to complete as opposed to running the same
    query on the remote server, where this query runs in a
    second.

    -- query start
    select Account_ID,
    sum(case portfolio_num when 1 then
    Income_Commission_Amt else 0 end) as Capital,
    sum(case portfolio_num when 2 then
    Income_Commission_Amt else 0 end) as Income
    from <remote_server>.<db_name>.dbo.FINANCIAL_TRAN where
    account_id='123456789' and
    entry_dt between '2002-07-01' and '2003-06-30' and
    isnull(Income_Commission_Amt,0)<>0
    group by account_id
    -- query end

    From the execution plan of this query, it displays the
    Remote Query operator to be sending over 3 million rows to
    the Filter operator. This is probably why my query runs
    slowly when executed as a distributed query.

    Any ideas as to how I can improve this query to have run
    more efficiently.

    Your responses are highly appreciated. Thanks.


    Narayana Guest

  3. #3

    Default Re: long running distributed query

    Thanks... that helped.
    Rob Guest

  4. #4

    Default Re: long running distributed query

    Actually, OPENQUERY has a limitation where you cannot pass
    any arguments. Is there another function that I can use to
    be able to pass parameters as well (essentially, running
    distributed parameterized queries). Do you know if this is
    possible using the OPENROWSET or any other function(s)?

    Thanks.
    Rob Guest

  5. #5

    Default Re: long running distributed query

    Yeah, you can't pass parameters unless you use dynamic SQL, which I try to
    stay away from.

    Another option you could consider is, to create a stored procedure on the
    remote server, that accepts the required parameters and just call the stored
    procedure using the linked server directly using 4 part naming convention.
    --
    HTH,
    Vyas, MVP (SQL Server)
    http://vyaskn.tripod.com/
    What hardware is your SQL Server running on?
    http://vyaskn.tripod.com/poll.htm




    "Rob" <com> wrote in message
    news:009601c356c2$7416eed0$gbl...
    Actually, OPENQUERY has a limitation where you cannot pass
    any arguments. Is there another function that I can use to
    be able to pass parameters as well (essentially, running
    distributed parameterized queries). Do you know if this is
    possible using the OPENROWSET or any other function(s)?

    Thanks.


    Narayana Guest

Similar Threads

  1. CFMX7 Standard running distributed mode (cluster)
    By Monolith in forum Coldfusion Server Administration
    Replies: 1
    Last Post: October 16th, 03:35 PM
  2. scheduled task running to long.
    By nedflanders in forum Macromedia ColdFusion
    Replies: 2
    Last Post: February 17th, 02:30 PM
  3. Long-running performance (MVCC, Vacuum, etc.) - Any fix?
    By Eric Brown in forum PostgreSQL / PGSQL
    Replies: 3
    Last Post: December 28th, 05:50 PM
  4. ADO client disconnects after running a long query
    By Gary in forum ASP Components
    Replies: 3
    Last Post: August 12th, 07:32 PM
  5. Killing long running queries
    By Meenal Dhody in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 2nd, 09:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139