Solved

Need to call inline table function without passing parameters or speed up view calling linked server

Posted on 2008-09-29
7
640 Views
Last Modified: 2012-06-27
Hi,

First a bit of background.
I have two SQL 2005 servers.
All of the below are executed on the primary server (ServerA).
The data resides on the second server (ServerB) which is set up as a linked server (LinkedServerB) on ServerA

I have a legacy application that runs queries agains ServerB. I need to move an instance of this application into a firewall and the easiest way to do this it to keep the data on ServerB, create a linked server to ServerB from ServerA and to replace all the tables on ServerA with views that has exactly the same names as the tables on ServerA. The views then make calles to the talbes on ServerB.

Testing performace the following became clear:
1) If I run the following from SQL Management Studio the result comes back sub-sceond
     SELECT *  FROM [LinkedServerB].[Database1].dbo.[Table1] with (nolock)  WHERE sharecode = 'KWV'

2) If I create a view on ServerA called Table1 that does a select against [LinkedServerB].[Database1].dbo.[Table1] in exactly the same way as no 1 above ( SELECT *  FROM [LinkedServerB].[Database1].dbo.[Table1] with (nolock)), then the following query takes op to 50 seconds to complete:
     SELECT *  FROM [ServerA].[Database1].dbo.[Table1] WHERE sharecode = 'KWV'  
                 (Remember that Table1 in this case is actually a view

3) If I create a Inline Table-Valued function called Table1() on ServerAB  that does a select against [ServerB].[Database1].dbo.[Table1] in exactly the same way as no 1 above ( SELECT *  FROM [LinkedServerB].[Database1].dbo.[Table1] with (nolock)) , then the following query takes sub-second to complete:
     SELECT *  FROM [ServerA].[Database1].dbo.[Table1]() WHERE sharecode = 'KWV'  
                 (Remember that Table1 in this case is actually a function)

Wrapping the function in a view has no effect on the speed of the view making the view again taking up to 50 seconds.

Other than re-developing the legacy applications or shipping data between these two databases I seem to have only two options:
A) Find a way to speed up the views
B) FInd a way to call inline table-valued function without have to use the paranthesis when calling them for example:
     SELECT *  FROM [ServerA].[Database1].dbo.[Table1] WHERE sharecode = 'KWV'  as opposed to
     SELECT *  FROM [ServerA].[Database1].dbo.[Table1]() WHERE sharecode = 'KWV'  

This will allow the legacy appliction to use the remote linked server data as if it resides in local tables.

I am willing to give the points for a solution to either A or B above.

Thanks

Gerhatd
       





0
Comment
Question by:dbit
  • 3
  • 2
7 Comments
 
LVL 2

Expert Comment

by:sandson
ID: 22596239
Hello,

it seems that your view retrieves all data on ServerB prior to applying the where close. Can you display the execution plan to confirm that ?

It may also need an index on the sharecode column, try by adding an index on sharecode in the view Table1 on ServerA (look at this articled to create the indexed view : http://msdn.microsoft.com/en-us/library/aa215090(SQL.80).aspx)

regards,
Abdel
0
 

Author Comment

by:dbit
ID: 22596347
Yes, I've already looked at the execution plans for all the scenarios I described. Using a view definitely seem to bring back all the rows from the linked server.

There is already an primary key index on the table on the remote server on the sharecode.
Using the SQL profiler to look at the remote server instructions, I noticed that the bottleneck seem to be caused by the server calling sp_getschemalock:
     exec [sys].sp_getschemalock @p1 output,@p2 output,N'"TimeSeries"."dbo"."hi"'

I tried to get around this by specifying with (nolock) on the query statement in the view but that does not seem to help.
0
 
LVL 2

Expert Comment

by:sandson
ID: 22596729
I ran a test on my servers using your second scenario (a view run a query against the remote server)
ServerA doesn't do anything for me. According to the execution plan it simply exec a remote query on ServerB by combining the where clause of the main query (ie. select * from Table1 where sharecode = 'KWV') with the view definition :
SELECT *  FROM [LinkedServerB].[Database1].dbo.[Table1] with (nolock)  WHERE sharecode = 'KWV'

So meaning that the optimizer understood my query and combined the view and the query to create a remote query to run on ServerB.

How did you linked ServeB to ServerA. Did you set it up as a 'SQL Server' server, thus allowing the SQL engine to use optimizations dedicated to SQL Server ?

regards,
Abdel
0
 

Author Comment

by:dbit
ID: 22597006
Yes, you are correct and I see exactly the same.
As I stated before, it seems to be the fact that the linked server are waiting for schema locks that creates the bottleneck.

In my case waiting for the schema locks take 99% of the time to execute on the linked server.
0
 
LVL 2

Accepted Solution

by:
sandson earned 500 total points
ID: 22603112
Hi dbit,

after a good sleep period, I may have an idea. if ServerB sends all it's data (as it seems to be the case) to ServerA then it means that the two servers may not be compatible for comparing it and ServerB lets ServerA dealing with the translation of incompatible values (here sharecode column and the literal value 'KWV').

Can you check in the server link options for 'Collation Compatible', option which indicate to the server that they share the same collation settings and thus are able to deal with strings (char, varchar, ...) in the same way ? Set it to true and test again.

regards,
Abdel
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VB.NET return row from SQL to DataSet and get column values ? 3 49
SQL Server Update Query Streamline 4 28
Help Required 2 29
Query Help - MSSQL - Averages 5 25
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now