Solved

Slow response over a linked server

Posted on 2004-09-27
4
339 Views
Last Modified: 2007-11-19
I am retrieving records from a linked server.  Both servers are in the same room and network latency is not an issue.  Also, in the server connection I have collation compatable checked.


This query takes several minutes to run, and only returns about 13 records.

Here is the query:

select *, (select Count(*)
from [MLSIRSQL].[LifeRecordSIR].dbo.Batches B,
        [MLSIRSQL].[LifeRecordSIR].dbo.Images I
where B.BatchID like RTrim(ITE_NAME)+'%' and B.BatchID = I.BatchID)
from #Items

The contents of the #items table is:

BillingID   ITE_ID      ITE_NAME                         GroupingID                                         UnitsQty    
----------- ----------- -------------------------------- -------------------------------------------------- -----------
26          127         SKL1040819023                    NULL                                               1
26          311         SKL3040830009                    NULL                                               1
26          317         SKL6040830005                    NULL                                               1
26          129         SKL1040819025                    NULL                                               1
26          130         SKL1040819026                    NULL                                               1
26          131         SKL1040819027                    NULL                                               1
26          259         SKL1040824001                    NULL                                               1
26          43          SKL2040817003                    NULL                                               1
26          304         SKL3040830002                    NULL                                               1
26          306         SKL3040830004                    NULL                                               1
26          308         SKL3040830006                    NULL                                               1
26          309         SKL3040830007                    NULL                                               1
26          310         SKL3040830008                    NULL                                               1

If I substitute one of the ITE_NAME in the above query it also executes immediately.

For example:

select Count(*)
from [MLSIRSQL].[LifeRecordSIR].dbo.Batches B,
        [MLSIRSQL].[LifeRecordSIR].dbo.Images I
where B.BatchID like RTrim('SKL1040819023                   ')+'%' and B.BatchID = I.BatchID

Executes in less than 1 second, and I can get the same results for all 13 ITE_NAME fields.

Why does the query take several minutes and what can I do to increase the performance?
0
Comment
Question by:sifuhall
4 Comments
 

Expert Comment

by:EliasSportsBureau
Comment Utility
Do you have a primary KEY set up on ITE_NAME?
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
What about this:

select *, (select Count(*)
from OpenQuery(MLSIRSQL, ' SELECT * FROM [LifeRecordSIR].dbo.Batches B,  [LifeRecordSIR].dbo.Images I WHERE B.BatchID = I.BatchID ' ) X, #Items
where  X.BatchID like RTrim(ITE_NAME)+'%'

Note that you will need to replace the SELECT * inside the OpenQuery by naming all the columns you need (and every column name needs to be unique) for this to work.

The performance problem you noticed is that for your query, the linked server had to be queries several times, which is the greatest overhead.
By using OpenQuery as I showed, the linked server will be queried only once, and the join will be performed locally.
This has the drawback for large tables that it will require much temporary space on the local server, thus this cannot be used for each and every solution. you might need to workaround this...

CHeers
0
 
LVL 17

Expert Comment

by:BillAn1
Comment Utility
SQLServer is not that hot at optimising queries across servers (not surprisingly, it cannot amintain detailed stats etc of all tables in remote servers...) IN this case it is probably running the nested query for EACH row in the #temp table, which is taking a long time.

Probably the best thing to do is to run a single remote query to pull back counts and stroe in a 2nd temp table :

SELECT B.BatchID, Count(*) as Total
INTO #Temp2
FROM [MLSIRSQL].[LifeRecordSIR].dbo.Batches B JOIN
        [MLSIRSQL].[LifeRecordSIR].dbo.Images I
ON B.BatchID = I.BatchID
GROUP BY B.BatchID

Then you can join the 2 temp tables :

SELECT #Items.*, Total
from #Items JOIN #Temp2 ON
BatchID like RTrim(ITE_NAME)+'%'


or, if you really need the nested SELECT :

SELECT *, (SELECT SUM(Total) from #Temp2 WHERE BatchID like RTrim(ITE_NAME)+'%' )
FROM #Items
0
 
LVL 1

Author Comment

by:sifuhall
Comment Utility
Taking the note from AngelIII that the overhead was being used by linking the server serveral times I moved the final statement into a cursor and it now runs in less than 5 seconds.

Many thanks for the help and for those that are having a similar issue here is my final code:


SET NOCOUNT ON

declare @DateFrom datetime
declare @DateTo datetime

SET @DateFrom = '08/17/04'
SET @DateTo = '09/17/04'


      IF OBJECT_ID('TEMPDB..#Items') IS NOT NULL
      DROP TABLE #Items

select 26 as BillingID, I.ITE_ID, I.ITE_NAME, Cast(NULL as varchar(50))  as GroupingID, 1 as UnitsQty
into #Items
from [LEX-SQL-3].[mcpLifeRecord].dbo.mcpItems I, [LEX-SQL-3].[mcpLifeRecord].dbo.mcpItemTasks IT, [LEX-SQL-3].[mcpLifeRecord].dbo.mcpTasks T
where I.ITE_ID = IT.ITE_ID and T.TAS_NAME = 'Indexing' and IT.TAS_ID = T.TAS_ID and I.ITT_NAME = 'Stacks'
and ITS_DTSTOP between @DateFrom and @DateTo


      -- CREATE TEMP TABLE FOR RESULTS --
      IF OBJECT_ID('TEMPDB..#ITE_RESULTS') IS NOT NULL
      DROP TABLE #ITE_RESULTS

      CREATE TABLE #ITE_RESULTS (ITE_NAME CHAR(32), ITE_COUNT INT)

      -- PREPARE CURSOR --
      DECLARE @ITENAME VARCHAR(255)
      DECLARE ITE_NAME CURSOR FOR
            SELECT ITE_NAME
            FROM #Items
      
      -- OPEN THE CURSOR --
      OPEN ITE_NAME
      
      -- LOOP THROUGH ALL THE INDEXES IN THE TABLE --
      FETCH NEXT
         FROM ITE_NAME
         INTO @ITENAME
      
      WHILE @@FETCH_STATUS = 0
      BEGIN
            EXEC('INSERT INTO #ITE_RESULTS
                  select ''' + @ITENAME + ''', Count(*)
                  from [MLSIRSQL].[LifeRecordSIR].dbo.Batches B, [MLSIRSQL].[LifeRecordSIR].dbo.Images I
                  where B.BatchID like RTrim('''  + @ITENAME + ''') + ''%'' and B.BatchID = I.BatchID')
      
            FETCH NEXT
                  FROM ITE_NAME
                  INTO @ITENAME
      END
      
      -- CLOSE AND DEALLOCATE THE CURSOR --
      CLOSE ITE_NAME
      DEALLOCATE ITE_NAME
      
SET NOCOUNT OFF

SELECT I.* , C.ITE_COUNT
FROM #Items I
INNER JOIN #ITE_RESULTS C
ON I.ITE_NAME = C.ITE_NAME
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

763 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

13 Experts available now in Live!

Get 1:1 Help Now