0

So essentially my query is currently grabbing data from two different linked servers which is causing the query to run at a unbelievable slow time (both are slow servers and need to be replaced). Normally for queries if they all grabbing information from the same database I'd do something like this:

EXEC (@SQL) AT [SERVER]

The server above that I execute it at makes the query run blazing fast. Like I'm talking a 43 minutes query running in 14 seconds. Not sure exactly why but was told it may have better indexing (not quite sure how indexing works that much).

But I can't do this anymore since one of the database's doesn't exist within this server. And no, I can't copy the database over to the other server.

Can anyone give me any advice on what to replace the server prefix with or what's a good way to approach this?

Example:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
    -- Several Columns.
FROM 
dbo.mem m
    INNER JOIN [SERVER1].DATABASE1.dbo.TABLE1 c on c.COL1 = m.COL1 
    INNER JOIN [SERVER2].DATABASE2.dbo.TABLE2 BM ON BM.COL1= c.COL1
WHERE
  -- CONDITIONS

GO
Jona
  • 327
  • 4
  • 19
  • What does `@SQL` contain? – Lukasz Szozda Aug 02 '19 at 14:53
  • @LukaszSzozda The quoted query itself. It's grabbing information from the two different servers. – Jona Aug 02 '19 at 14:54
  • I asked about the query. Could you provide the result of `PRINT @SQL` – Lukasz Szozda Aug 02 '19 at 14:54
  • 3
    @LifeOfJona They want to know what the exact query is, to look at performance issues. Also, if you don't know about indexing, it's time to start learning. It is very useful when working in a database. – Ryan Wilson Aug 02 '19 at 14:54
  • @RyanWilson Any specific link that'd help me learn it fast? I started SQL 2 months ago. – Jona Aug 02 '19 at 15:10
  • 2
    The world's best indexing won't help you if, in order to join these tables, one of the servers has to pump over a couple of million rows to perform the join. There's only so much the optimizer can do, here. It will try, based on estimates, to produce an execution plan that minimizes the amount of data that has to be remoted, but it gets it wrong quite often. Splitting up the query in stages with temp tables and "flat" copying of data is often helpful. – Jeroen Mostert Aug 02 '19 at 15:20
  • @LifeOfJona (https://stackoverflow.com/questions/2955459/what-is-an-index-in-sql) – Ryan Wilson Aug 02 '19 at 15:26
  • Are both servers different from executing server? – Adnan Ahmed Ansari Aug 02 '19 at 15:56
  • I would suggest using `OpenQuery()` and populate local temp tables from each of the linked servers, and do the processing locally. – Brian Aug 02 '19 at 16:07
  • Yes, both servers are different and contain different databases.@Brian OpenQuery only accept 1 server. And I think get what you're trying to say. So essentially select everything from the database of the linked server and put it into the temp table and then go from there? Can you please answer the question down below so we can go into further details? thanks. – Jona Aug 02 '19 at 17:33
  • @Brian Actually no, selecting everything and putting it into a temp table won't work due to when I would go to execute it, it won't recognize the server that it's coming from. So I'm back to square one. – Jona Aug 02 '19 at 18:56
  • @LifeOfJona Just select your required result and put into temp table one by one from the server and then use them in your join query. It will be faster than your query. – Adnan Ahmed Ansari Aug 05 '19 at 10:58
  • 1
    @LifeOfJona forgive me - I wasn't clear. I meant that you should use two `OpenQuery()` calls - one for each server. You'll then end up with two local temp tables, and you do your `Join` logic on them. – Brian Aug 05 '19 at 19:23

0 Answers0