Solved

Slow response over a linked server

Posted on 2004-09-27
4
343 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
ID: 12159976
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
ID: 12159993
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
ID: 12160056
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
ID: 12160917
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

809 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