Query Optimization for MS Sql Server 2012

Question

I have a table containing ~5,000,000 rows of scada data, described by the following: create table data (o int, m money).

Where: - o is PK with clustered index on it. o's fill factor is close to 100%. o represents the date of meter reading, can be thought of as X axis. - m is a decimal value laying within 1..500 region and is the actual meter reading can be thought of as Y axis.

I need to find out about certain patterns i.e. when, how often and for how long they had been occurring.

Example. Looking for all occurrences of m changing by a region from 500 to 510 within 5 units (well from 1 to 5) of o I run the following query:

select d0.o as tFrom, d1.o as tTo, d1.m - d0.m as dValue
from data d0
    inner join data d1 
        on (d1.o = d0.o + 1 or d1.o = d0.o + 2 or d1.o = d0.o + 3 or d1.o = d0.o + 4)
            and (d1.m - d0.m) between 500 and 510

the query takes 23 seconds to execute.

Previous version took 30 minutes (90 times slower), I' managed to optimize it using a naive approach by replacing : on (d1.o - d0.o) between 1 and 4 with on (d0.o = d1.o - 1 or d0.o = d1.o - 2 or d0.o = d1.o - 3 or d0.o = d1.o - 4)

It's clear to me why it's faster - on one hand indexed column scan should fork fast enough on another one I can afford it as dates are discrete (and I always give 5 minutes grace time to any o region, so for 120 minutes it's 115..120 region). I can't use the same approach with m values as they are integral though.

Things I've tried so far:

1. Soft sharding by applying where o between @oRegionStart and @oRegionEnd at the bottoom of my script. and running it within a loop, fetching results into a temp table. Execution time - 25 seconds.

2. Hard sharding by splitting data into a number of physical tables. The result is 2 minutes nevermind the maintenance hassle.

3. Using some precooked data structures, like:

create table data_change_matrix (o int, dM5Min money, dM5Max money, dM10Min money, dM10Max money ... dM{N}Min money, dM{N}Max money)

where N is max dept for which I run the analysis. Having such table I could easily write a query:

select * from data_change_matrix where dMin5Min between 500 and 510

The result is - it went nowhere due to the tremendous size requirements (5M X ~ 250) and maintenance related costs, I need to support that matrix actuality close to real time.

1. SQL CLR - don't even ask me what went wrong it just didn't work out.

Right now I'm out of inspiration and looking for help.

All in all - is it possible to get a close to instant response time running such type of queries on large volumes of data?

All's run on MS Sql Server 2012. Didn't try it on MS Sql Server 2014 but happy to do it if it'll make sense.

Update - execution plan: http://pastebin.com/PkSSGHvH.

Update 2 - While I really love LAG function suggested by usr I wonder if there's a LAG**S** function allowing for

select o, MIN(LAG**S**(o, 4)) over(...) - or what's its shortest implementation in TSL?

I tried something very similar using SQL CLR and got it working but the performance was awful.

Answer 1

I assume you meant to write "on (d1.o = ..." and not "on (d.o = ...". Anyway, I got pretty drastic improvements just by simplifying the statement (making it easy for the query optimizer to pick a better plan I guess):

select d0.o as tFrom, d1.o as tTo, d1.m - d0.m as dValue
from data d0
    inner join data d1 
        on d1.o between d0.o + 1 and d0.o + 4
            and (d1.m - d0.m) between 500 and 510

Good luck with your query!

Answer 2

You say you've already tried CLR but don't give any code.

It was fastest in my test for my sample data.

CREATE TABLE data
  (
     o INT PRIMARY KEY,
     m MONEY
  );

INSERT INTO data
SELECT TOP 5000000 ROW_NUMBER() OVER (ORDER BY @@SPID),
                   1 + ABS(CAST(CRYPT_GEN_RANDOM(4) AS INT) %500)
FROM   master..spt_values v1,
       master..spt_values v2 

None of the versions actually return any results (it is impossible for m to be a decimal value laying within 1..500 and simultaneously for two m values to have a difference > 500) but disregarding this typical timings I got for the code submitted so far are.

+-----------------+--------------------+
|                 | Duration (seconds) |
+-----------------+--------------------+
| Lag/Lead        | 39.656             |
| Original code   | 40.478             |
| Between version | 21.037             |
| CLR             | 13.728             |
+-----------------+--------------------+

The CLR code I used was based on that here

To call it use

EXEC    [dbo].[WindowTest]
        @WindowSize = 5,
        @LowerBound = 500,
        @UpperBound = 510

Full code listing

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    public struct DataRow
    {
        public int o;
        public decimal m;
    }

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void WindowTest(SqlInt32 WindowSize, SqlInt32 LowerBound, SqlInt32 UpperBound)
    {
        int windowSize = (int)WindowSize;
        int lowerBound = (int)LowerBound;
        int upperBound = (int)UpperBound;

        DataRow[] window = new DataRow[windowSize];

        using (SqlConnection conn = new SqlConnection("context connection=true;"))
        {
            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandText = @"
                                SELECT o,m
                                FROM data
                                ORDER BY o";

            SqlMetaData[] columns = new SqlMetaData[3];
            columns[0] = new SqlMetaData("tFrom", SqlDbType.Int);
            columns[1] = new SqlMetaData("tTo", SqlDbType.Int);
            columns[2] = new SqlMetaData("dValue", SqlDbType.Money);


            SqlDataRecord record = new SqlDataRecord(columns);

            SqlContext.Pipe.SendResultsStart(record);

            conn.Open();

            SqlDataReader reader = comm.ExecuteReader();

            int counter = 0;

            while (reader.Read())
            {
                DataRow thisRow = new DataRow() { o = (int)reader[0], m = (decimal)reader[1] };

                int i = 0;

                while (i < windowSize && i < counter)
                {
                    DataRow previousRow = window[i];

                    var diff = thisRow.m - previousRow.m;

                    if (((thisRow.o - previousRow.o) <= WindowSize-1) && (diff >= lowerBound) && (diff <= upperBound))
                    {
                        record.SetInt32(0, previousRow.o);
                        record.SetInt32(1, thisRow.o);
                        record.SetDecimal(2, diff);

                        SqlContext.Pipe.SendResultsRow(record);
                    }

                    i++;
                }

                window[counter % windowSize] = thisRow;

                counter++;
            }

            SqlContext.Pipe.SendResultsEnd();
        }
    }
}

Answer 3

This looks like a great case for windowed aggregate functions or LAG. Here a version using LAG:

select *
from (
 select o
      , lag(m, 4) over (order by o) as m4
      , lag(m, 3) over (order by o) as m3
      , lag(m, 2) over (order by o) as m2
      , lag(m, 1) over (order by o) as m1
      , m as m0
 from data
) x
where 0=1
 or (m1 - m0) between 500 and 510
 or (m2 - m0) between 500 and 510
 or (m3 - m0) between 500 and 510
 or (m4 - m0) between 500 and 510

Using a windowed aggregate function you should be able to remove the manual expansion of those LAG calls.

SQL Server implements these things using a special execution plan operator called Window Spool. That makes it quite efficient.