November 1, 2011 at 4:38 pm
Sorry for the delay - back at it now. Normal flow rates of 0 to 10 mgd. Also there will be lots of data - perhaps a point every second.
November 1, 2011 at 5:52 pm
Brian Ki (11/1/2011)
Sorry for the delay - back at it now. Normal flow rates of 0 to 10 mgd. Also there will be lots of data - perhaps a point every second.
Found a bit of time while I wait on a dev server I blew up. What's the decimal precision you need there, and are things logged on the second exactly or does this need ranges to handle data? IE: can data come in on the same second at .001 and .999 milliseconds?
Also, side note... finally nailed down that "great idea" I had for containering the data. It's not so great. You end up having to approach the aggregation from the wrong side so you can't apply it well. Everything I came up with took longer then the method I handed you above. If Jeff can spin up a quirky update mechanism off a stack push/pull, it's probably the best choice.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 1, 2011 at 10:09 pm
Brian Ki (11/1/2011)
Sorry for the delay - back at it now. Normal flow rates of 0 to 10 mgd. Also there will be lots of data - perhaps a point every second.
MGD??? You have to remember that you're speaking a different language. 😉 Is that "Mega-Gallons per Day or ???? 😛 You were previously talking in cfs (cubic feet per second) and that's what I'm looking for is the per-second rates that you expect.
Not to worry about the amount of data... a point per second is only 2,592,000 rows over 30 days. If I do it right, that might make for an 8 second run on my 9 year old single CPU desktop.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 2, 2011 at 9:22 am
MGD = millions gallons per day - sorry. And that's the flow rate, once I have the average for the time period its easy to convert it to other units (like cubic feet per second). Resolution will be 2 decimals places and the time resolution will be 1 second. There will not be two values for the same time.
November 15, 2011 at 5:33 pm
Comments are in the code.
DataBuild:
IF OBJECT_ID('tempdb..#tester') IS NOT NULL
DROP TABLE #tester
CREATE TABLE #tester
( TimeOfEntry DATETIME NOT NULL,
FlowRate DECIMAL( 4,2) NOT NULL
)
CREATE CLUSTERED INDEX idx_c_tester ON #tester
(TimeOfEntry)
-- Seconds in a day: 60* 60 * 24 = 86400
-- My tally table is a million rows, which will give us ~ 11 days of data.
-- TRUNCATE TABLE #tester
INSERT INTO #tester
SELECT
DATEADD( ss, n, '20110101') AS TimeOfEntry,
ABS( (CHECKSUM( NEWID())/100.)%10) AS FlowRate
FROM
tempdb..Tally AS t
--select top 100 * from #tester
--select min(TimeOfEntry), Max(timeofEntry) FROM #tester
--select min(FlowRate), Max(FlowRate) FROM #tester
Actual Process using a serial update (Please note the final select is just there for manual confirmation):
/*
Assumptions:
There is only one row per second
There are no millisecond entries (all are .000)
There are no gaps in the log entries.
- FlowDiscard and counts will not align without using an external table to monitor counts in avg
*/
DECLARE @DateOfInterestDATETIME,
@IncrementINT,
@SpanINT,
@CurrentcountINT,
@AnchorDATETIME,
@FlowRateDiscardDECIMAL( 4, 2),
@SumOfSpanDECIMAL( 20, 2),
@SequenceINT
SELECT@DateOfInterest = '20110103',
@Increment= 1,
@Span= 60*60, -- 1 hour of increment
@CurrentCount= 1,
@SumOfSpan= 0,
@Sequence= 0
IF OBJECT_ID('tempdb..#WorkingTable') IS NOT NULL
DROP TABLE #WorkingTable
CREATE TABLE #WorkingTable
(TimeOfEntryDATETIME NOT NULL,
FlowRateDECIMAL( 4,2) NOT NULL,
SumOfSpanDECIMAL( 20,2) NULL,
AvgOfSpanDECIMAL( 12,6) NULL -- This needed a whole lot more precision.
)
CREATE CLUSTERED INDEX idx_c_WorkingTable ON #WorkingTable (TimeOfEntry)
-- Let's get this into a structure where we don't care about the source
-- table and can do whatever we please. #tester is assumed to be
-- a sampling of a real source table.
INSERT INTO #WorkingTable (TimeOfEntry, FlowRate)
SELECT
TimeOfEntry,
FlowRate
FROM
#tester
WHERE
TimeOfEntry BETWEEN DATEADD( ss, -@span, @DateOfInterest)
AND DATEADD( dd, 1, @DateOfInterest)
/*
Confirming code that we are correctly getting the correct discard
UPDATE wt
SET
@Anchor = TimeOfEntry,
@FlowRateDiscard = (SELECTFlowRate
FROM#tester /*Note, NOT the #WorkingTable*/
WHERETimeOfEntry = DATEADD( hh, -1, DATEADD( ss, -1, wt.TimeOfEntry))
),
SumOfSpan = @FlowRateDiscard
FROM
#WorkingTable AS wt WITH (TABLOCKX)
OPTION ( MAXDOP 1)
select * from #WorkingTable
WHERE
TimeOfEntry IN
('2011-01-02 23:00:00.000',
'2011-01-02 23:00:01.000',
'2011-01-02 23:00:02.000',
'2011-01-02 23:59:58.000',
'2011-01-02 23:59:59.000',
'2011-01-03 00:00:00.000',
'2011-01-03 00:00:01.000',
'2011-01-03 00:00:02.000')
*/
;WITH SafetyNet AS
(SELECT
Sequence = ROW_NUMBER() OVER (ORDER BY TimeOfEntry ASC),
*
FROM
#WorkingTable
)
UPDATE sn
SET
@Anchor = TimeOfEntry,
@FlowRateDiscard = (SELECTFlowRate
FROM#tester /*Note, NOT the #WorkingTable*/
WHERETimeOfEntry = DATEADD( hh, -1, DATEADD( ss, -1, sn.TimeOfEntry))
),
@SumOfSpan = SumOfSpan = CASE WHEN TimeOfEntry >= @DateOfInterest
THEN @SumOfSpan + FlowRate - @FlowRateDiscard
-- ELSE is true when we're in the feeder.
ELSE @SumOfSpan + FlowRate
END,
AvgOfSpan = @SumOfSpan / @Span,
@Sequence = CASE WHEN Sequence = @Sequence + 1 THEN Sequence ELSE 1/0 END
FROM
SafetyNet AS sn WITH (TABLOCKX)
OPTION ( MAXDOP 1)
-- SELECT * FROM #WorkingTable ORDER BY TimeOfEntry
-- Discard the first 'span' worth of entries, they were feeder values.
DELETE FROM #WorkingTable WHERE TimeOfEntry < @DateOfInterest
-- Find the correct record(s) (can be multiples here if they match)
SELECT
DATEADD( ss, -@span, TimeOfEntry) AS StartTime,
TimeOfEntry AS EndTime,
wt.AvgOfSpan AS HighestAverage
FROM
#WorkingTable AS wt
JOIN
(SELECT
MAX( AvgOfSpan) AS Max_AvgOfSpan
FROM
#WorkingTable
) AS drv
ONwt.AvgOfSpan = drv.Max_AvgOfSpan
SELECT * FROM #WorkingTable
ORDER BY
AvgOfSpan DESC
EDIT: I should mention this takes the run time from 60 seconds to 2 seconds on my local PC.
Some of the components here are remnants of me working out a range mechanic instead of a no-gap single entry per interval query. I'm still working on that.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 16, 2011 at 5:14 am
Oh, my apologies... I lost track of this thread. Glad someone posted against it to refresh it in my inbox.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 16, 2011 at 2:02 pm
Jeff Moden (11/16/2011)
Oh, my apologies... I lost track of this thread. Glad someone posted against it to refresh it in my inbox.
Yeah, just hoping the OP comes back to try using it.
Was an interesting exercise though performing a FIFO push stack with the serial update. I'll eventually finish up the range component and get it dead on target for what I personally want it to do.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 16, 2011 at 5:08 pm
I have had to pass this off to someone else who is finishing it, but they have tested your code and tell me it appears to work well and very fast and will be the basis for the final solution. thx
November 16, 2011 at 7:24 pm
Brian Ki (11/16/2011)
I have had to pass this off to someone else who is finishing it, but they have tested your code and tell me it appears to work well and very fast and will be the basis for the final solution. thx
You're welcome. Good luck and just hit us up if you run into any snags.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply