SQL Server Performance

Queries not using remote indexes with variables

Discussion in 'Performance Tuning for DBAs' started by gus, Apr 20, 2006.

  1. gus New Member

    I've made this question once.

    I'll make it again, maybe I'll have more luck this time.

    The problem is with simple queries against linked servers not using indexes in the remote server when I'm using varibles.


    for example :

    select * from linked.db.dbo.table where indexed_column = 1

    works fine. The filter is applied remotely, and the query is fast.

    if i do

    declare @a int
    set @a = 1

    select * from linked.db.dbo.table where indexed_column = @a


    the filter is apllied in the local server, not in the remote, so the query becomes slow.

    I've played with linked server options (collation compatible, etc, etc ) with no success.

    Any ideas ?

    Thanks
  2. Luis Martin Moderator

    Could that help?

    http://support.microsoft.com/default.aspx?scid=kb;en-us;q314520



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  3. gus New Member

    It's not the ideal situation, because I've simplified the query in the example given.
    In my real situation I got local joins and other stuff, so I won't be able to do simply as pointed in the KB.

    Anyway, I can rewrite a little bit my code and put the remote rows in a table variable first with your suggestion.

    So far is the best I got.

    Thanks Luis.
  4. gus New Member

    I didn't find a way to put the remote rows in a local table variable with any of the three methods proposed by the KB article.

    Cause every one of them is based on exec.
  5. Luis Martin Moderator

    I'm afraid you have to way for members developers experts.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  6. Adriaan New Member

    If you say "local joins", do you mean you're joining remote and local tables?<br />And are you evaluating the variable in expressions with both local and remote columns?<br /><br />In either case, the local SQL Server probably decides it<br /><br />I would look at changing the reference to the remote table into ...<br /><br />(1) a derived table, containing the variable in its own WHERE statement,<br /><br />or (2) a UDF that returns a table, defined in the remote database, which takes the variable as its parameter,<br /><br />or (3) use the OPENQUERY syntax - not one that I'm familiar with, but it should work just fine -<br /><br />or (4) a sproc defined in the remote database and a local temp table (remember to apply appropriate indexes) ...<br />using the <b>INSERT INTO #Tbl EXEC remoteserver.remotedb.owner.sproc &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />arameter&gt;</b> syntax.<br /><br />or (5) the same as (4), but using <b>EXEC remoteserver.remotedb.dbo.sp_ExecuteSQL ......</b> for maximum flexibility.<br /><br />If you need the same filtered set of data in successive queries, options (4) and (5) would be best.
  7. gus New Member

    yes. "local joins" means joins beteween local table and remote.

    but what I'm trying to do now is, bring the remote rows locally somehow and then join with that.

    I'm trying option 2, using a remote UDF tabled valued, but I can't seem to do the remote select.

    select * from remote_server.database.dbo.function ( @parameter )

    I keep getting Incorrect syntax near '('.

    Is is possible to do that ?
    I mean a remote select from a parametrized tabled valued UDF ?
  8. gus New Member

    I was trying from SQL 2000 to SQL 2005.

    Now I've tried from SQL 2005 to SQL 2005 and got :

    Server: Msg 4122, Level 16, State 1, Line 11
    Remote table-valued function calls are not allowed.


    A little more explicit.

    So i guess it can't be done with UDF.
  9. gus New Member

    This is becoming a nightmare.

    The UDF aproach dind't work.
    The table variable we all know won't work.

    Now I've tried with a #temporary table.

    But all this work I have to do it inside a table valued function.
    And when I try to do the exec into #tmp stuff I get :

    Server: Msg 2772, Level 16, State 1
    Cannot access temporary tables from within a function.

    So (4) and (5) don't work inside a UDF table valued function.
  10. gus New Member

    If I try to "cheat" it doing the insert into #tmp using all dynamic SQL , I got :

    Server: Msg 558, Level 16, State 2
    Remote function calls are not allowed within a function.

    It seems there is no way to get around this.
    All this work sholud be done inside a table valued UDF. This is not negotiable.

  11. Adriaan New Member

    The idea with the temp table was to define it in your local sproc, and make the remote server execute a query that returns the results into the local temp table:

    CREATE TABLE #Tbl (................)

    INSERT INTO #Tbl
    EXEC linked.db.dbo.sp_ExecuteSQL
    N'SELECT * FROM dbo.table t WHERE t.indexed_column = @a', N'@a INT', @a

    Then you can process the results locally, like:

    SELECT * FROM #Tbl

    Make sure your temp table has a PK and proper indexes, certainly if there's a lot of data coming from the remote server.
  12. gus New Member

    I understand that.
    But the problem is I don't have a local sproc, but a local UDF table valued function.
    I can't change that now, the system is running and relying on that.

    And somehow I can't use #temp tables inside a UDF table valued function.
    Is there some way to get around this ?
  13. Adriaan New Member

    Like I've already suggewsted: do this in a stored procedure.

    And you don't have to use the remote UDF.

    Do you have any problem calling sp_ExecuteSQL on the remote server? This will return a rowset, which in my sample code is inserted into the local temp table. You then use the temp table for further local processing.
  14. gus New Member

    It seems I'm not making myself clear.

    All the insert into #tmp and exec remote stuff is perfect and it works fine on its own.

    BUT, all that work I have to do it inside a _LOCAL_ UDF table valued function that existed before all the remote server stuff.

    The local UDF table valued function was used BEFORE the remote server and there's no way to avoid it.

    And the problem is I can't use temp tables inside a UDF. ( I didn't know that until yesterday ).
    And I can't use a table variable cos of course I can't put the remote rows inside a table variable.

    So I'm seriously stuck here...

    Any ideas ?
  15. Adriaan New Member

    OK, so it's a local UDF. Where is this UDF being used? Can't see why it must be a UDF from what you've told us so far.
  16. gus New Member

    It has to do with the system structure. We use table variables and table valued UDFs.
    It's the most flexible way ( except for this damned thing of not letting me use #temp tables.... )

    It can't be changed now.
  17. Adriaan New Member

    You are aware that you can use the table that you define as the return value of the UDF, as a temp table within the UDF itself? So no need to declare a separate table (which indeed is not allowed in a UDF).

    create function dbo.test1()
    returns @test table (col1 varchar(10))
    as
    begin

    insert into @test values ('a')
    insert into @test values ('b')

    return

    end
    go

    select * from dbo.test1()
  18. gus New Member

    Yes. Actually the local table valued UDF it's like that.

    But inside that pre-existantant table valued UDF I have to do the remote exec and stuff first into some table.
    Then some joining with local tables and then feed the resulting table.

    And where do I am supposed to store the remote rows ?
    The only place is inside a #temp table. And I can't use it inside a function.

    It's really annoying.
  19. Adriaan New Member

    Yes, but what kind of object is it from which you're calling the table-valued UDFs? It sounds like a system designed by an object-oriented programmer, instead of a database developer.
  20. gus New Member

    It's called from sprocs.
  21. Adriaan New Member

    And how come those sprocs are carved in stone?
  22. gus New Member

    The thing is it's more flexible with a table valued UDF, because I can select some columns from it, filter rows, etc.

    If it were an sproc the resulting rowset can't be "manipulated".
  23. Adriaan New Member

    I can understand that, but why can't those sprocs have temp tables where you put the results from the UDFs?

    Also remember that when you refer to that UDF twice in a procedure, it is retrieving the data twice - it's not a recordset object like in a regular programming language.
  24. gus New Member

    Because I don't like and avoid #temp tables. It's a lot cleanear table valued UDFs.

    The secoind point it's perfectly clear.
  25. Adriaan New Member

    Yes, but you're running into the limitations here. And if you don't like temp tables, your sprocs can always use table variables instead.[<img src='/community/emoticons/emotion-5.gif' alt=';)' />]
  26. gus New Member

    I've resolved.<br /><br />Instead of changing my system phylosophy and design, I've got the damned remote filter work remotely as it should be in the first place.<br /><br />I've set DynamicParameters=1 and NestedQueries=1 in SQLOLEDB registry as suggested in :<br /><br /<a target="_blank" href=http://www.dbforums.com/archive/index.php/t-508636.html>http://www.dbforums.com/archive/index.php/t-508636.html</a><br />and<br /<a target="_blank" href=http://sqlpass.org/Forums/messageview.cfm?catid=359&threadid=7543>http://sqlpass.org/Forums/messageview.cfm?catid=359&threadid=7543</a><br /><br />and magically the original <br /><br />select * from linked.db.dbo.table where indexed_column = @a<br /><br />Started to apply the filter remotely and not locally.<br /><br />I'm good or what ? <br />well, at least at googling around <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br />
  27. Adriaan New Member

    Good for you!
  28. gus New Member

    Good for all of us.

    Next time somebody came around asking the same thing somebody will remember this.
  29. mmarovic Active Member

    Thank you for posting the resolution.
  30. gus New Member

    Sometimes I have to give back something to the community for a change [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  31. Adriaan New Member

    Apologies, that came out wrong. Thanks for sharing the knowledge!

    Any info on possible side-effects when changing that registry setting?
  32. gus New Member

    No problem buddy.<br /><br />What "side-effects" ? Once something works I never look back [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Actually I should see why those registry settings where different than the supposedly should be.<br /><br />I don't think that may adversely affect something, as they were set to more restricted values.<br />Now the provider is "more capable" than before.<br /><br />Well, any investigation on this won't hurt me, right ? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />
  33. Adriaan New Member

    That's the spirit!
  34. gus New Member

    Tell my bosses that's not wasted time [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />
  35. Adriaan New Member

    So you like to live dangerously, Mr Bond?
  36. Luis Martin Moderator

    Good for you hermano!!!


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  37. gus New Member

    All the problems were due to that damned server was migrated (ages ago.....) from 7.0 to 8.0<br />I did't remeber that though.<br /><br />I guess that's why those SQLOLEDB settings were set.<br /><br />Strange I didn't check the linked stuff problem from another server.<br /><br />Another "How I didn't see it?" after the resolution cames to the light [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Won't be the last, huh ? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />
  38. Adriaan New Member

    There are lots of legacy systems out there, both in software and hardware, so it's good to know about this type of complication.

    Thanks!

Share This Page