Viability of a large database without using indexes and some other things

Question

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');
GO

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:

https://www.brentozar.com/pastetheplan/?id=SkDJAvZQI

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!

Answer 1

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:

SELECT *
INTO #eng_facven
FROM eng_facven;

SELECT *
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
    Join
   #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.

Answer 2

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.