Sql-server – Speed up INSERT procedure

insertperformancequery-performancesql serversql-server-2016

I am creating a stored procedure in SQL Server 2016, in which I have the following query:

DECLARE 
    @FechaMovimiento DATETIME

SELECT 
   @FechaMovimiento = ISNULL(MAX(FechaMovimiento), '31/12/2014') 
FROM  
   DB0.[dbo].[TR_TAB_OLAP_FechasCierre]
WHERE 
   FechaCierre <= GETDATE() -1


INSERT INTO DB1.[dbo].[HVentas_1]
   (
   IDTienda, Id, Numero, NumeroFactura, idhora, IdLineaDocumento, 
   IdDocumento, Fecha, UniqueID, Posicion, CodigoDeBarras, 
   CodigoNIT_Productor, CodigoAmarreProductor,
   CodigoMarca, CodigoUnidad, CodigoUnidadDeEmpaque, CodigoImpuesto, CodigoAmarre_Articulo_Referencia,
   CodigoFamilia, CodigoLinea, CodigoCategoria, CodigoSubcategoria,
   DescripcionCortadeProducto, Descripcion, DescripcionTienda, BarraTienda, Unidades, PrecioDefecto, Precio, 
   Descuento, IDImpuesto, Importe, ImporteIVA, ImporteDtoTotal, 
   ImporteDtoTotalIva, ImporteDtoLinea, ImporteDtoLineaIva, Comentario1, Comentario2,
   VolumenTotal, PrecioPorPesoVolumen, Tiendas_ActivasPorBarra
   )
SELECT DISTINCT TOP 5 
   v.IDTienda, v.Id, v.Numero, v.NumeroFactura, v.idhora, v.IdLineaDocumento, 
   v.IdDocumento, v.Fecha, v.UniqueID, v.Posicion, v.CodigoDeBarras, 
   v.CodigoNIT_Productor, v.CodigoAmarreProductor,
   v.CodigoMarca, v.CodigoUnidad, v.CodigoUnidadDeEmpaque, v.CodigoImpuesto, v.CodigoAmarre_Articulo_Referencia,
   v.CodigoFamilia, v.CodigoLinea, v.CodigoCategoria, v.CodigoSubcategoria,
   v.DescripcionCortadeProducto, v.Descripcion, v.DescripcionTienda, v.BarraTienda, v.Unidades, v.PrecioDefecto, v.Precio, 
   v.Descuento, v.IDImpuesto, v.Importe, v.ImporteIVA, v.ImporteDtoTotal, 
   v.ImporteDtoTotalIva, v.ImporteDtoLinea, v.ImporteDtoLineaIva, v.Comentario1, v.Comentario2,
   v.VolumenTotal, v.PrecioPorPesoVolumen, 0
FROM 
   DB1.dbo.TR_TAB_OLAP_HVentas v LEFT JOIN  DB2.[dbo].[HVentas_1] v1 WITH (NOLOCK)
ON
   v.fecha = v1.fecha AND v.[UniqueID] = v1.[UniqueID] AND v.Posicion = v1.Posicion 
WHERE
   v1.fecha IS NULL
AND
   v1.Uniqueid IS NULL
AND
   v1.Posicion IS NULL
AND
   v.fecha > @FechaMovimiento

This is the execution plan:

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

It is taking too much time (around 10 minutes), even though I am taking a small quantity of records. I would like to know If there's any way to speed up this procedure.

Query timings:

@FechaMovimiento assignment : less than 1 second.
Select distinct: less than 1 second
Insert From Select Distinct: almost 10 minutes.

I have a non-clustered index on HVentas on Fecha, UniqueId and Posicion.
And a columnstore index in HVentas_1.

Best Answer

First let's do a quick summary of the query plan for that insert query. SQL Server scans all rows from HVentas_1 to create the hash build and finds 0 relevant rows. It then scans through TR_TAB_OLAP_HVentas and stops if/when it finds 5 distinct rows such that v.fecha > @FechaMovimiento and that don't match a row in HVentas_1. You have an index defined on the table but SQL Server doesn't use it in the query plan that you uploaded. The worst case for performance should be when @FechaMovimiento is a value that doesn't match any rows in the table. In that case SQL Server will scan all 70 million rows from the table only to insert 0 rows. The number of rows inserted isn't relevant here. What's important is the amount of work that SQL Server did to find the rows to insert.

For the actual plan that you uploaded, SQL Server had to scan 70037142 rows from the heap before it found five distinct rows. It seems as if you had a very selective value for the variable, but you're using a local variable in a stored procedure without a RECOMPILE hint. You could be running into an issue with parameter sniffing. In any case, try it again with a RECOMPILE hint. That will allow SQL Server to build a query plan that accounts for the value of the local variable. This should be fine unless you're running this stored procedure hundreds of times per second. With better information, the query optimizer can make more informed decisions about when to use an index. Using an index here is likely to lead to better performance.

As a side node, you have a CCI that only has ten rows. Will that table continue to be that small? If so that may not be a good candidate for columnstore.

Related Solutions

Sql-server – deteriorating stored procedure running times

What is up with FROM part JOIN model ON 1=1? This the same as FROM part, model, which is a cartesian join and will result in a very large number of rows. Is that join supposed to be like that?

You will likely help us help you if you provide details about the tables involved. Please "script" the definition of the tables, along with any indexes defined on those tables.

This sounds like a classic case of parameter sniffing resulting in good plan/bad plan choices for various scenarios in your data.

You may be able to get more reliable performance by making SQL Server cache different plans for different scenarios by using sp_executesql, as in the following example:

CREATE PROCEDURE [dbo].[create_grid_materials2] 
(
    @partlistid bigint
    , @pid bigint
    , @masterid bigint
)
AS
BEGIN
    begin
        DECLARE @cmd NVARCHAR(MAX);

        SET @cmd = '   
        INSERT INTO material (partid, personid, modelID)
        SELECT 
            partid = part.id
            , personid = @pid
            , modelid = model.id  
        FROM part
            INNER JOIN model ON 1=1
        WHERE (
            model.masterid = ' + CONVERT(NVARCHAR(50), @masterid) + ' 
                AND model.modelSetID IS NULL
                AND part.partlistid = ' + CONVERT(NVARCHAR(50), @partlistid) + '
                AND (
                    part.partType = 100 
                    or part.partType=120 
                    or part.partType = 130
                )
            )
            AND NOT EXISTS (
                SELECT 1 
                FROM material AS a1 
                WHERE a1.partid = part.id 
                    AND a1.personid=@pid 
                    AND a1.modelid=model.id
                )';
        DECLARE @Params VARCHAR(200);
        SET @Params = '@pid INT';
        EXEC sys.sp_executesql @cmd
            , @Params
            , @pid = @pid;
    end
End

The above code will cause a new plan to be generated for each combination of @partlistid, and @masterid.

The presumption here is some combinations of those two variables lead to a very small number of rows, whereas some combinations lead to a very large number of rows.

Forcing a plan for each combination allows SQL Server to generate more efficient plans for each. I've explicitly not included @pid since you probably want to try it with a fairly small number of combinations first; adding a third variable to the mix will make for an exponentially larger number of possible plans.

Sql-server – Why is the index not being used in a SELECT TOP

If I let the server decide which index to use, it picks IX_MachineryId, and it takes up to a minute.

That index is not partitioned, so the optimizer recognizes it can be used to provide the ordering specified in the query without sorting. As a non-unique nonclustered index, it also has the keys of the clustered index as subkeys, so the index can be used to seek on MachineryId and the DateRecorded range:

The index does not include OperationalSeconds, so the plan has to look that value up per row in the (partitioned) clustered index in order to test OperationalSeconds > 0:

The optimizer estimates that one row will need to be read from the nonclustered index and looked up to satisfy the TOP (1). This calculation is based on the row goal (find one row quickly), and assumes a uniform distribution of values.

From the actual plan, we can see the estimate of 1 row is inaccurate. In fact, 19,039 rows have to be processed to discover that no rows satisfy the query conditions. This is the worst case for a row goal optimization (1 row estimated, all rows actually needed):

You can disable row goals with trace flag 4138. This would most likely result in SQL Server choosing a different plan, possibly the one you forced. In any case, the index IX_MachineryId could be made more optimal by including OperationalSeconds.

It is quite unusual to have non-aligned nonclustered indexes (indexes partitioned in a different way from the base table, including not at all).

That really suggests to me that I have made the index right, and the server is just making a bad decision. Why?

As usual, the optimizer is selecting the cheapest plan it considers.

The estimated cost of the IX_MachineryId plan is 0.01 cost units, based on the (incorrect) row goal assumption that one row will be tested and returned.

The estimated cost of the IX_MachineryId_DateRecorded plan is much higher, at 0.27 units, mostly because it expects to read 5,515 rows from the index, sort them, and return the one that sorts lowest (by DateRecorded):

This index is partitioned, and cannot return rows in DateRecorded order directly (see later). It can seek on MachineryId and the DateRecorded range within each partition, but a Sort is required:

If this index were not partitioned, a sort would not be required, and it would be very similar to the other (unpartitioned) index with the extra included column. An unpartitioned filtered index would be slightly more efficient still.

You should update the source query so that the data types of the @From and @To parameters match the DateRecorded column (datetime). At the moment, SQL Server is computing a dynamic range due to the type mismatch at runtime (using the Merge Interval operator and its subtree):

<ScalarOperator ScalarString="GetRangeWithMismatchedTypes([@From],NULL,(22))">
<ScalarOperator ScalarString="GetRangeWithMismatchedTypes([@To],NULL,(22))">

This conversion prevents the optimizer from reasoning correctly about the relationship between ascending partition IDs (covering a range of DateRecorded values in ascending order) and the inequality predicates on DateRecorded.

The partition ID is an implicit leading key for a partitioned index. Normally, the optimizer can see that ordering by partition ID (where ascending IDs map to ascending, disjoint values of DateRecorded) then DateRecorded is the same as ordering by DateRecorded alone (given that MachineryID is constant). This chain of reasoning is broken by the type conversion.

Demo

A simple partitioned table and index:

CREATE PARTITION FUNCTION PF (datetime)
AS RANGE LEFT FOR VALUES ('20160101', '20160201', '20160301');

CREATE PARTITION SCHEME PS AS PARTITION PF ALL TO ([PRIMARY]);

CREATE TABLE dbo.T (c1 integer NOT NULL, c2 datetime NOT NULL) ON PS (c2);

CREATE INDEX i ON dbo.T (c1, c2) ON PS (c2);

INSERT dbo.T (c1, c2) 
VALUES (1, '20160101'), (1, '20160201'), (1, '20160301');

Query with matched types

-- Types match (datetime)
DECLARE 
    @From datetime = '20010101',
    @To datetime = '20090101';

-- Seek with no sort
SELECT T2.c2 
FROM dbo.T AS T2 
WHERE T2.c1 = 1 
AND T2.c2 >= @From
AND T2.c2 < @To
ORDER BY 
    T2.c2;

Query with mismatched types

-- Mismatched types (datetime2 vs datetime)
DECLARE 
    @From datetime2 = '20010101',
    @To datetime2 = '20090101';

-- Merge Interval and Sort
SELECT T2.c2 
FROM dbo.T AS T2 
WHERE T2.c1 = 1 
AND T2.c2 >= @From
AND T2.c2 < @To
ORDER BY 
    T2.c2;