Timeline for How to implement a Set based algorithm/UDF
Current License: CC BY-SA 3.0
26 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| S Oct 18, 2015 at 0:37 | history | bounty ended | Paul White♦ | ||
| S Oct 18, 2015 at 0:37 | history | notice removed | Paul White♦ | ||
| Oct 17, 2015 at 20:01 | comment | added | Dan Guzman | @medwar19, what type of ADO client-side cursor are you using? A fast-forward read-only (firehose) cursor can be orders of magnitude faster by reducing network round trips. | |
| Oct 17, 2015 at 1:37 | comment | added | Solomon Rutzky | @medwar19 Hi. Is it possible to get some feedback on any of the suggestions and/or questions posted here? Any additional information would greatly help us in our efforts to help you solve this issue :-). | |
| Oct 13, 2015 at 15:57 | answer | added | Peter Vandivier | timeline score: 2 | |
| Oct 11, 2015 at 16:16 | answer | added | Solomon Rutzky | timeline score: 8 | |
| Oct 11, 2015 at 11:47 | comment | added | Vladimir Baranov | Still too much is unknown. The VBA function - where does it take its source data from? Directly from SQL Server by running some query over the network that returns one row or from a single row in Excel spreadsheet? Or it takes several rows as an argument? Where do results go? Into a single row in Excel spreadsheet? Or several rows? If VBA function talks to SQL Server, then the first thing I'd try to do is make VBA function that works just with local Excel spreadsheet. Export all data from SQL into Excel (maybe with some pre-calculations as needed) as a separate step, then process it by VBA. | |
| Oct 10, 2015 at 22:47 | history | tweeted | twitter.com/StackDBAs/status/652978890248466432 | ||
| S Oct 10, 2015 at 20:24 | history | bounty started | Paul White♦ | ||
| S Oct 10, 2015 at 20:24 | history | notice added | Paul White♦ | Draw attention | |
| Oct 1, 2015 at 11:01 | comment | added | medwar19 | I was prepared to go one step further and do the filtering once on data entry, storing the result in another column for direct lookup, but when I tested this it saved ~20ms on 80ms, good but not where I need to be. So it looks like IO is the bottleneck (the filter returns ~4000 rows). Next test will be to filter in T-SQL and return just the point values needed | |
| Oct 1, 2015 at 6:11 | comment | added | Mikael Eriksson | 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). | |
| Oct 1, 2015 at 6:07 | comment | added | Mikael Eriksson |
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).
|
|
| Oct 1, 2015 at 3:36 | comment | added | medwar19 |
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
|
|
| Oct 1, 2015 at 3:15 | comment | added | Vladimir Baranov |
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.
|
|
| Oct 1, 2015 at 2:39 | history | edited | Paul White♦ |
edited tags
|
|
| Oct 1, 2015 at 2:38 | history | reopened | Paul White♦ | ||
| Oct 1, 2015 at 0:56 | review | Reopen votes | |||
| Oct 1, 2015 at 2:39 | |||||
| Oct 1, 2015 at 0:39 | history | edited | medwar19 | CC BY-SA 3.0 |
added 769 characters in body
|
| Sep 30, 2015 at 2:53 | history | closed | Paul White♦ | Needs more focus | |
| Sep 30, 2015 at 2:53 | history | reopened | Paul White♦ | ||
| Sep 30, 2015 at 2:52 | history | closed | Paul White♦ | Needs details or clarity | |
| Sep 30, 2015 at 1:17 | comment | added | billinkc | 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 | |
| Sep 30, 2015 at 0:26 | history | edited | Erik | CC BY-SA 3.0 |
Removed signature
|
| Sep 30, 2015 at 0:24 | history | edited | Hannah Vernon♦ |
edited tags
|
|
| Sep 30, 2015 at 0:12 | history | asked | medwar19 | CC BY-SA 3.0 |