Solved

Slow response over a linked server

Posted on 2004-09-27
4
346 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 

Expert Comment

by:EliasSportsBureau
ID: 12159976
Do you have a primary KEY set up on ITE_NAME?
0
 
LVL 143

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

688 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