I'll explain my situation as briefly as possible. My group is working for another company and I'm mostly creating stored procedures for making reports.

The twist is that, last month, they decided to do some major changes, which increased a lot the time required to what before worked nicely. I don't know the details, but one of the big changes is that what used to be tables now are views that get the data from an external source. Those are defined as:

CREATE view [dbo].[eng_contra_env] as 
select ce_serial, ce_empresa, ce_ganado, ce_contra, ce_fecha_env, ce_fecha_rec, ce_observa
  from OPENROWSET('MSDASQL', '***', 'SELECT * from eng_contra_env');

Also, since that change, I'm not allowed to do some basic things like indexing some tables, as my boss tells me not to do so due to those changes that the client did.

So, I'm asked to improve the performance of some procedure (which again, before the changes it finished, now it doesn't). One bit of the procedure requires me to do the following:


How am I supposed to optimize that? Also, with what I said already, how viable do you think it is to work under these conditions.

Update: Apparently they had a meeting at the client and they decided to revert these changes. Thank god!

  • Instead of using view which is defined to get data from linked server, I would suggest to create a temp table, insert data from linked server table to this table and use that temp table inside your procedure, would definitely improve the performance. Feb 12, 2020 at 12:57
  • In addition to @Learning_DBAdmin's suggestion, can you persist the data on a daily or other periodic basis so that it your report can hit real tables instead of going to some external source? A bonus with that is you can then index these new tables in a way that is more efficient for your report.
    – BCM
    Feb 12, 2020 at 13:29

2 Answers 2


The use of views backed by linked server queries puts you in a pretty tough situation. You get bad cardinality estimates coming out of the "remote query" operators (10k rows estimated, vs. the actuals of 521k and 30k respectively). This results in SQL Server choosing to sort and use a merge join, when it would probably have chosen a hash join had the estimates been better.

For this specific query, you could try a hash join hint to avoid the sort + merge join plan:

From  eng_facven A
    inner hash Join
   eng_facved B On A.fv_serial = B.fd_serial 

As a general solution, you might consider materializing the remote tables into temp tables first. This will give you better estimates and thus likely better plans.

Of course, the feasability of this option depends a lot on the size of the data / number of rows. In the query referenced above, you'd only be loading around 16 MB total into temp tables, which is probably reasonable - especially for a report that might only be run once a day, or a few times per day.

The example query you provided would look like this:

INTO #eng_facven
FROM eng_facven;

INTO #eng_facved
FROM eng_facved;

Select  A.fv_empresa, A.fv_ganado, A.fv_contra
   , Sum(A.fv_animales)        fv_animales
   , Sum(A.fv_primales)        fv_primales
   , Sum(B.fd_prima)/Nullif(Sum(A.fv_animales),0)  fv_prima_total
   , Sum(B.fd_kilos)         fv_kg_factu
   , Sum(B.fd_bruto)         fd_precio
   , Sum(A.fv_imp_dto_trans)       fv_dto_fac

   , (Sum(B.fd_bruto) - Sum(A.fv_imp_dto_trans))
    Nullif(Sum(B.fd_kilos),0)       fv_fd_prec_kg_canal

From  #eng_facven A
   #eng_facved B On A.fv_serial = B.fd_serial 

Group by A.fv_empresa, A.fv_ganado, A.fv_contra;

For larger tables, you might need to consider having a scheduled (daily / hourly) job that copies the contents of the remote tables into local tables, and have your queries reference those. This depends on how up-to-date your reports are required to be.

  • I kind of wished that I could have avoided using temp tables, as I'm sure if my boss will like it. At least in this case, the hash join didn't improve the time of the query, remaining at ~1 min. However, after bringing those tables it took only one secound to complete the query. I hadn't thought about setting up a job, so I'll discuss it with my boss as soon as I see her. Besides, as @BCM mentioned, I could index those tables to improve performance even more. If I want to keep you updated on how the situation unfolds, how should I do it? Feb 12, 2020 at 16:15
  • @IsmaelManzanaresCarmona I'm glad the temp tables improved things (sorry the hash join hint didn't, that was a bit of a guess)! And yes, being able to index the real tables (or even the temp tables) is a big benefit to those approaches. As far as updates about the situation: if you end up with a solution, you can post an answer to your own question (self-answers are totally okay here). Feb 12, 2020 at 16:25
  • @IsmaelManzanaresCarmona You're also perfectly welcome to leave a comment here 😀 But FYI, comments are considered temporary (and might be deleted at any time), and aren't meant for extended discussions. So really useful stuff should be added in answers, or edited into the question. Feb 12, 2020 at 16:56

one of the big changes is that what used to be tables now are views that get the data from an external source.

So the data has moved from one database into another but you're still querying it through the original. This might be deliberate on the client's part and they intend to maintain both going forward, or you may find that you're now working with a database that the client intends to get rid of at some point in the [perhaps not too distant] future!

You really need to talk to the clients and find out what they changed, with a view to getting the data from the new location, if at all possible.

This is also worrying:

CREATE view ... as 
select ce_serial, ce_empresa, ce_ganado, ce_contra, ce_fecha_env, ce_fecha_rec, ce_observa
from OPENROWSET('MSDASQL', '***', 'SELECT * from eng_contra_env');

This might be another case that falls foul of the "Rule":

Never use select * in Production code.

Your view is returning seven fields. You have no idea how many fields may be defined in the eng_contra_env table (now, or in the future). There could be hundreds of fields and each of them could contain huge amounts of data, all of which is being pulled back across the network by your view definition for absolutely Zero benefit.

Always explicitly specify the columns that you want retrieved.

  • I'm not in direct contact with the client, but I understand that this setup is already in production, as the final users are already toying with the app and sending complaints, which is why I have to take a look at the performance. Do you think that setting up a job for bringing those tables is the way to go? I've been suggested this multiple times here. Feb 12, 2020 at 16:23
  • Also, I just found out that there's in fact some kind of migration going on, but they plan on keeping this view approach. Feb 12, 2020 at 17:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.