Solved

Slow response over a linked server

Posted on 2004-09-27
4
342 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

910 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

20 Experts available now in Live!

Get 1:1 Help Now