# How to implement a Set based algorithm/UDF

I have an algorithm that I need to run against every row in a table with 800K rows and 38 columns. The algorithm is implemented in VBA and does a bunch of math using values from some columns to manipulate other columns.

I'm currently using Excel (ADO) to query SQL and use VBA with client side cursors to apply the algorithm by loop through every row. It works but takes 7 hours to run.

The VBA code is complex enough that it would be a lot of work to recode it into T-SQL.

I've read about CLR integration and UDFs as possible routes. I also thought about putting the VBA code in an SSIS script task to get closer to the database but am sure an expert methodology to this type of performance problem exists.

Ideally I'd be able to run the algorithm against as many rows (all?) as possible in a parallel set based way.

Any help greatly predicated on how to get best performance with this type of problem.

--Edit

Thanks for the comments, I'm using MS SQL 2014 Enterprise, here's some more details:

The algorithm finds characteristic patterns in time series data. The functions within the algorithm perform polynomial smoothing, windowing, and finds regions of interest based on input criteria, returning a dozen values and some Boolean results.

My question is more about methodology than the actual algorithm: If I want to achieve parallel computation on many rows at once, what are my options.

I see re-code into T-SQL is recommended which is a lot of work but possible, however the algorithm developer works in VBA and it changes frequently so I'd need keep in sync with the T-SQL version and re-validate every change.

Is T-SQL the only way to implement set based functions?

• SSIS can offer some native parallelization assuming you design your data flow well. That's the task that you'd be looking for since you need to do this row by row calculation. But that said, unless you can give us specifics (schema, calculations involved and what these calculations hope to accomplish) it's impossible to help you optimize. They say writing things in assembly can make for the fastest code but if, like me, you suck horrifically at it, it's not going to be efficient at all – billinkc Sep 30 '15 at 1:17
• If you process each row independently, then you can split 800K rows into `N` batches and run `N` instances of your algorithm on `N` separate processors/computers. On the other hand, what is your main bottleneck - transferring the data from SQL Server to Excel or actual computations? If you change the VBA function to return some dummy result immediately, how long would the whole process take? If it still takes hours, then bottleneck is in data transfer. If it takes seconds, then you need to optimize the VBA code that does the computations. – Vladimir Baranov Oct 1 '15 at 3:15
• It's the filter which gets called as a stored procedure: `SELECT AVG([AD_Sensor_Data]) OVER (ORDER BY [RowID] ROWS BETWEEN 5 PRECEDING AND 5 FOLLOWING) as 'AD_Sensor_Data' FROM [AD_Points] WHERE [FileID] = @FileID ORDER BY [RowID] ASC` In Management Studio this function which gets called for each of the rows takes 50mS – medwar19 Oct 1 '15 at 3:36
• So the query that takes 50 ms and executes 800000 times (11 hours) is what is taking time. Is the @FileID unique for each row or are there duplicates so you could minimize the number of times you need to execute the query? You could also pre calculate the rolling avg for all fileid's to a staging table in one go (use partition on FileID) and then query that table without the need of a windowing function for each row. The best setup for the staging table looks like it should be with a clustered index on `(FileID, RowID)`. – Mikael Eriksson Oct 1 '15 at 6:07
• Best of all would be if you somehow could remove the need to touch the db for each row. That means you either have to go TSQL and probably join to the rolling avg query or fetch enough information for each row so everything the algorithm needs is right there on the row, perhaps encoded in some way if there are multiple child rows involved (xml). – Mikael Eriksson Oct 1 '15 at 6:11

With regards to methodology, I believe you are barking up the wrong b-tree ;-).

What we know:

First, let's consolidate and review what we know about the situation:

• Somewhat complex calculations need to be performed:
• This needs to happen on every row of this table.
• The algorithm changes frequently.
• The algorithm ... [uses] values from some columns to manipulate other columns
• Current processing time is: 7 hours
• The table:
• contains 800,000 rows.
• has 38 columns.
• The application back-end:
• Database is SQL Server 2014, Enterprise Edition.
• There is a Stored Procedure that is called for every row:

• This takes 50 ms (on avg, I assume) to run.
• It returns approximately 4000 rows.
• The definition (at least in part) is:

``````SELECT AVG([AD_Sensor_Data])
OVER (ORDER BY [RowID] ROWS BETWEEN 5 PRECEDING AND 5 FOLLOWING)
WHERE  [FileID] = @FileID
ORDER BY [RowID] ASC
``````

What we can surmise:

Next, we can look at all of these data points together to see if we can synthesize additional details that will help us find one or more bottle necks, and either point towards a solution, or at least rule some possible solutions out.

The current direction of thought in the comments is that the major issue is data transfer between SQL Server and Excel. Is that really the case? If the Stored Procedure is called for each of the 800,000 rows and takes 50 ms per each call (i.e. per each row), that adds up to 40,000 seconds (not ms). And that is equivalent to 666 minutes (hhmm ;-), or just over 11 hours. Yet the whole process was said to take only 7 hours to run. We are already 4 hours over the total time, and we have even added in time to do the calculations or save the results back to SQL Server. So something is not right here.

Looking at the definition of the Stored Procedure, there is only an input parameter for `@FileID`; there isn't any filter on `@RowID`. So I suspect that one of the following two scenarios is happening:

• This Stored Procedure does not actually get called per each row, but instead per each `@FileID`, which appears to span approximately 4000 rows. If the stated 4000 rows returned is a fairly consistent amount, then there are only 200 of those grouping in the 800,000 rows. And 200 executions taking 50 ms each amounts to only 10 seconds out of that 7 hours.
• If this stored procedure actually does get called for every row, then wouldn't the first time a new `@FileID` is passed in take slightly longer to pull new rows into the Buffer Pool, but then the next 3999 executions would typically return faster due to already being cached, right?

I think that focusing on this "filter" Stored Procedure, or any data transfer from SQL Server to Excel, is a red herring.

For the moment, I think the most relevant indicators of lackluster performance are:

• There are 800,000 rows
• The operation works on one row at a time
• The data is being saved back to SQL Server, hence "[uses] values from some columns to manipulate other columns" [ my emphasis ;-) ]

I suspect that:

• while there is some room for improvement on the data retrieval and calculations, making those better wouldn't amount to a significant reduction in processing time.
• the major bottleneck is issuing 800,000 separate `UPDATE` statements, which is 800,000 separate transactions.

My recommendation (based on currently available information):

1. Your biggest area of improvement would be to update multiple rows at one time (i.e. in one transaction). You should update your process to work in terms of each `FileID` instead of each `RowID`. So:

1. read in all 4000 rows of a particular `FileID` into an array
2. the array should contain elements representing the fields being manipulated
3. cycle through the array, processing each row as you currently do
4. once all rows in the array (i.e. for this particular `FileID`) have been calculated:
1. start a transaction
2. call each update per each `RowID`
3. if no errors, commit the transaction
4. if an error occurred, rollback and handle appropriately
2. If your clustered index isn't already defined as `(FileID, RowID)` then you should consider that (as @MikaelEriksson suggested in a comment on the Question). It won't help these singleton UPDATEs, but it would at least slightly improve the aggregate operations, such as what you are doing in that "filter" stored procedure since they are all based on `FileID`.

3. You should consider moving the logic to a compiled language. I would suggest creating a .NET WinForms app or even Console App. I prefer Console App as it is easy to schedule via SQL Agent or Windows Scheduled Tasks. It shouldn't matter whether it is done in VB.NET or C#. VB.NET might be a more natural fit for your developer, but there will still be some learning curve.

I don't see any reason at this point to move to SQLCLR. If the algorithm changes frequently, that would get annoying have to re-deploy the Assembly all of the time. Rebuilding a Console App and having the .exe get placed into the proper shared folder on the network such that you just run the same program and it just happens to always be up-to-date, should be fairly easy to do.

I don't think moving the processing fully into T-SQL would help if the problem is what I suspect and you are just doing one UPDATE at a time.

4. If the processing is moved into .NET, you can then make use of Table-Valued Parameters (TVPs) such that you would pass the array into a Stored Procedure that would call an `UPDATE` that JOINs to the TVP table variable and is hence a single transaction. The TVP should be faster than doing 4000 `INSERT`s grouped into a single transaction. But the gain coming from using TVPs over 4000 `INSERT`s in 1 transaction likely won't be as significant as the improvement seen when moving from 800,000 separate transactions to only 200 transactions of 4000 rows each.

The TVP option is not natively available for the VBA side, but someone came up with a work-around that might be worth testing:

How do I improve the database performance when going from VBA to SQL Server 2008 R2?

5. IF the filter proc is only using `FileID` in the `WHERE` clause, and IF that proc is really being called per every row, then you can save some processing time by caching the results of the first run and using them for the rest of the rows per that `FileID`, right?

6. Once you get the processing done per FileID, then we can start talking about parallel processing. But that might not be necessary at that point :). Given that you are dealing with 3 fairly major non-ideal parts: Excel, VBA, and 800k transactions, any talk of SSIS, or parallelograms, or who-knows-what, is premature optimization / cart-before-the-horse type stuff. If we can get this 7 hour process down to 10 minutes or less, would you still be thinking of additional ways to make it faster? Is there a target completion time that you have in mind? Keep in mind that once processing is done on a per FileID basis, if you had a VB.NET Console App (i.e. command-line .EXE), there would be nothing stopping you from running a few of those FileIDs at a time :), whether via SQL Agent CmdExec step or Windows Scheduled Tasks, etc.

AND, you can always take a "phased" approach and make a few improvements at a time. Such as starting with doing the updates per `FileID` and hence using one transaction for that group. Then, see if you can get the TVP working. Then see about taking that code and moving it to VB.NET (and TVPs work in .NET so it will port nicely).

What we do not know that could still help:

• Does the "filter" Stored Procedure run per RowID or per FileID? Do we even have the full definition of that Stored Procedure?
• Full schema of the table. How wide is this table? How many variable length fields are there? How many fields are NULLable? If any are NULLable, how many contain NULLs?
• Indexes for this table. Is it partitioned? Is either ROW or PAGE Compression being used?
• How large is this table in terms of MB / GB?
• How is index maintenance handled for this table? How fragmented are the indexes? How update to date are the statistics?
• Do any other processes write to this table while this 7 hour process is taking place? Possible source of contention.
• Do any other processes read from this table while this 7 hour process is taking place? Possible source of contention.

UPDATE 1:

** There seems to be some confusion about what VBA (Visual Basic for Applications) and what can be done with it, so this is just to make sure we are all on the same web-page:

UPDATE 2:

One more point to consider: How are connections being handled? Is the VBA code opening and closing the Connection per each operation, or does it open the connection at the start of the process and close it at the end of the process (i.e. 7 hours later)? Even with connection pooling (which, by default, should be enabled for ADO), there should still be quite an impact between opening and closing once as opposed to opening and closing either 800,200 or 1,600,000 times. Those values are based on at least 800,000 UPDATEs plus either 200 or 800k EXECs (depending on how often the filter stored procedure is actually being executed).

This issue of too many connections is automatically mitigated by the recommendation I outlined above. By creating a transaction and doing all of the UPDATEs within that transaction, you are going to be keeping that connection open and reusing it for each `UPDATE`. Whether or not the connection is kept open from the initial call to get the 4000 rows per the specified `FileID`, or closed after that "get" operation and opened again for the UPDATEs, is far less impacting since we are now talking about a difference of either 200 or 400 total connections across the entire process.

UPDATE 3:

I did some quick testing. Please keep in mind that this is a rather small scale test, and not the exact same operation (pure INSERT vs EXEC + UPDATE). However, the differences in timing related to how connections and transactions are handled are still relevant, hence the information can be extrapolated to having a relatively similar impact here.

Test Parameters:

• SQL Server 2012 Developer Edition (64-bit), SP2
• Table:

`````` CREATE TABLE dbo.ManyInserts
(
RowID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
InsertTime DATETIME NOT NULL DEFAULT (GETDATE()),
SomeValue BIGINT NULL
);
``````
• Operation:

``````INSERT INTO dbo.ManyInserts (SomeValue) VALUES ({LoopIndex * 12});
``````
• Total Inserts per each test: 10,000
• Resets per each test: `TRUNCATE TABLE dbo.ManyInserts;` (given the nature of this test, doing the FREEPROCCACHE, FREESYSTEMCACHE, and DROPCLEANBUFFERS didn't seem to add much value.)
• Recovery Model: SIMPLE (and maybe 1 GB free in the Log file)
• Tests that use Transactions only use a single Connection regardless of how many Transactions.

Results:

``````Test                                   Milliseconds
-------                                ------------
10k INSERTs across 10k Connections     3968 - 4163
10k INSERTs across 1 Connection        3466 - 3654
10k INSERTs across 1 Transaction       1074 - 1086
10k INSERTs across 10 Transactions     1095 - 1169
``````

As you can see, even if the ADO connection to the DB is already being shared across all operations, grouping them into batches using an explicit transaction (the ADO object should be able to handle this) is guaranteed to significantly (i.e. over 2x improvement) reduce the overall process time.

• There is a nice "middle man" approach to what srutzky is suggesting, and that is to use PowerShell to get the data you need out of SQL Server, call your VBA script to work the data, and then call an update SP in SQL Server, passing the keys and updated values back to SQL server. In this way you combine a set based approach with what you already have. – Steve Mangiameli Oct 13 '15 at 15:54
• @SteveMangiameli Hi Steve and thanks for the comment. I would have replied sooner but have been sick. I'm curious how your idea is that much different than what I am suggesting. All indications are that Excel is still required to run the VBA. Or are you suggesting that PowerShell would replace ADO, and if much faster at the I/O, would be worth it even if just to replace only the I/O ? – Solomon Rutzky Oct 17 '15 at 1:24
• No worries, glad your feeling better. I don't know that it would be better. We don't know what we don't know and you've done some great analysis but still have to make some assumptions. The I/O may be significant enough to replace on it's own; we just don't know. I just wanted to present another approach that may be helpful with the things you've suggested. – Steve Mangiameli Oct 17 '15 at 5:26
• @SteveMangiameli Thanks. And thank you for clarifying that. I wasn't sure of your exact direction and figured it best not to assume. Yes, I agree that having more options is better since we don't know what constraints there are on what changes can be made :). – Solomon Rutzky Oct 17 '15 at 15:10
• Hey srutzky, thanks for the detailed thoughts! I've been back testing on the SQL side getting indexes and queries optimized and trying to find the bottlenecks. I've invested in a proper server now, 36cores, 1TB stripped PCIe SSDs as IO was bogging down. Now onto calling the VB code directly in SSIS which appears to open multiple threads for parallel executions. – medwar19 Oct 19 '15 at 1:35

IMHO and working from the assumption that it's not feasible to re-code the VBA sub into SQL, have you considered allowing the VBA script to finish evaluating in the Excel file and then write the results back to SQL server via SSIS?

You could have the VBA sub start and end with flipping an indicator either in a filesystem object or in the server ( if you've already configured the connection to write back into the server ) and then use an SSIS expression to check this indicator for the `disable` property of a given task within your SSIS solution ( so that the import process waits until the VBA sub completes if you're worried about it overrunning its schedule ).

Additionally, you could have the VBA script start programmatically ( a little wonky, but I've used the `workbook_open()` property to trigger "fire and forget" tasks of this nature in the past ).

If evaluation time of the VB script starts to become an issue, you could see if your VB developer is willing and able to port his code into a VB script task within the SSIS solution - in my experience the Excel application pulls a lot of overhead when working with data at this volume.