Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2007
    Posts
    6

    Angry Unanswered: Distributed Query Performance is ID-dependent?

    We have an interesting performance issue with a distributed query. When run by a system-administrator account, the remote computer returns the requested row set. It does the same thing for small row counts (<=7) for user accounts. On larger row counts, however, the user account returns the ENTIRE rowset from the remote table, and performs the restrict operation locally. Performance-wise, this results in a difference between 11 seconds and 12 MINUTES. I'm assuming it's some sort of security issue in DTC, but the remote server is on Windows Server 2000, and there's no security button for DTC under Component Services.

    Suggestions?

    The query in question is:
    EXEC ("INSERT INTO #XML (vin, ws_xml, lang_id)
    SELECT slo.vin,
    br.xml,
    br.lang_id
    FROM #SALE_LINEUP_ORDER slo
    INNER JOIN RemoteServer.mydatabase.dbo.build_record br ON slo.vin = br.vin
    INNER JOIN #LANGUAGE_IDS li ON br.lang_id = li.lang_id")

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I suggest you run this as a stored procedure, which will ensure that the entire operation takes place on the server.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2007
    Posts
    6
    Unfortunately, it's a cross-database join, so a stored procedure doesn't work. This worked FINE until we had a corruption of DTC two weeks ago, and had to uninstall and re-install DTC on the remote server.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by kriscook2
    Unfortunately, it's a cross-database join, so a stored procedure doesn't work.
    is that true with fully qualified names and deferred name resolution in sps? that sure does not sound right.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jan 2007
    Posts
    6

    Question Clarification

    To calrify:
    Yes, I CAN use a stored procedure, but the net result would have to be to return the entire dataset, which is what it's doing now that takes so long. The real issue is why is the distributed transaction acting completely differently for an SA account than for a user account, and how can I get the user account to be treated the same way.

    Tracing on the remote server, the optimizer geneates the following for 7 or fewer rows, and something similar (but more entries) for ANY NUMBER of rows for an sa account:
    set implicit_transactions on
    SELECT br."xml" Col1048,br."lang_id" Col1049
    FROM "windowsticker"."dbo"."build_record" br
    WHERE br."lang_id"=@P1 AND @P2=br."vin"
    SELECT br."xml" Col1048,br."lang_id" Col1049
    FROM "windowsticker"."dbo"."build_record" br
    WHERE br."lang_id"=@P1 AND @P2=br."vin"
    SELECT br."xml" Col1048,br."lang_id" Col1049
    FROM "windowsticker"."dbo"."build_record" br
    WHERE br."lang_id"=@P1 AND @P2=br."vin"
    SELECT br."xml" Col1048,br."lang_id" Col1049
    FROM "windowsticker"."dbo"."build_record" br
    WHERE br."lang_id"=@P1 AND @P2=br."vin"
    SELECT br."xml" Col1048,br."lang_id" Col1049
    FROM "windowsticker"."dbo"."build_record" br
    WHERE br."lang_id"=@P1 AND @P2=br."vin"
    SELECT br."xml" Col1048,br."lang_id" Col1049
    FROM "windowsticker"."dbo"."build_record" br
    WHERE br."lang_id"=@P1 AND @P2=br."vin"
    SELECT br."xml" Col1048,br."lang_id" Col1049
    FROM "windowsticker"."dbo"."build_record" br
    WHERE br."lang_id"=@P1 AND @P2=br."vin"
    IF @@TRANCOUNT > 0
    ROLLBACK TRAN
    set implicit_transactions off


    However, the result generated for more than seven rows for the user account is:
    set implicit_transactions on
    SELECT br."vin" Col1047,br."xml" Col1048,br."lang_id" Col1049
    FROM "windowsticker"."dbo"."build_record" br
    WHERE br."lang_id"=@P1
    IF @@TRANCOUNT > 0
    ROLLBACK TRAN
    set implicit_transactions off

    so it bundles up and ships back all 280,000+ rows matching the lang_id, and then filters based on vin at the local server. This is my 11-minute delay point. Why does it treat sa differently in this regard?

Posting Permissions

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