[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 357
  • Last Modified:

Slow response over a linked server

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
sifuhall
Asked:
sifuhall
1 Solution
 
EliasSportsBureauCommented:
Do you have a primary KEY set up on ITE_NAME?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
BillAn1Commented:
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
 
sifuhallAuthor Commented:
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now