2

One of my clients has two Head Offices. Each HO has almost 300 workstation and my client doesn't want to replicate all the info into one HO, so he asked if i can get some info into secondary tables with jobs from HO no. 2 to HO no. 1 for some reports... This part is done and i had no issue but the real problem appeared when i had to modify the stored procedure for the reports.

Before i had the code like this:

SELECT D.IdDocument, D.etc...
FROM Document D (NOLOCK)
  JOIN OtherTable OT (NOLOCK) on etc...

Now i've tried it like this:

1) First attempt:

SELECT DXD.IdDocument, DXD.etc...
FROM 
(
  SELECT D.*
  FROM Document D (NOLOCK)

  UNION ALL

  SELECT D2.*
  FROM RemoteDocument D2 (NOLOCK)
) DXD
  JOIN OtherTable OT on etc...

2) Second attempt:

CREATE TABLE #TempDocument
(
  IdDocument INT,
  IdLocation INT,
  etc..
)
CREATE INDEX IDX_TMP_Document ON #TempDocument (IdDocument, IdLocation)

INSERT INTO #TempDocument
SELECT DXD.*
FROM 
(
  SELECT D.*
  FROM Document D (NOLOCK)

  UNION ALL

  SELECT D2.*
  FROM RemoteDocument D2 (NOLOCK)
) DXD

SELECT DXD.IdDocument, DXD.etc...
FROM #TempDocument DXD
  JOIN OtherTable OT (NOLOCK) on etc...

The problem is that before the sp ran in 5-10 minutes and now 30-40 minutes, the main issue that execution plan detected is in the union/insert using the union...

Can someone tell me a better/faster way to concatenate the info before using it?

Pana Costin
  • 21
  • 1
  • 3
    linked queries are very slow.further you are getting all the data and there by reducing any chances in reducing data returned.I recommend based on your frequency to generate reports,use SSIS /some other way to get data and dump them into local database ahead of generating these reports – TheGameiswar Feb 02 '17 at 12:20

1 Answers1

0

I would recommend that you utilize a linked server. Then once you have created the linked server. Create a view to the linked server on one of your systems.

Server 1:
   Linked Servers:
       Server 2
   Views:
     MyNewView(Select * from openquery(linkedServer,'Select * from SomeDB.dbo.SomeTable')

When doing this. Your main server caches data from the view. So you can now work on one server and the return time should be fairly quick depending on the amount of data you're filtering through and returning.

for setting up a linked server please review the following article: Here.

This does not actually consolidate but it hopefully should improve some speed issues as well as allow you to write like you are in a single database. Cleaning up your code and also preventing any other communication problems as the only communication issue @ the linked server end.

Community
  • 1
  • 1
GrafixMasta
  • 89
  • 9
  • I have a linked server already set, thats how i get data from second HO. I have a job that insert into table "RemoteDocument". Actually i think this will be bad since i work with 80-100 million rows. – Pana Costin Feb 06 '17 at 08:27