July 21, 2015 at 7:45 am
I have the following code I am confused on. I am able to select the average, max, etc of the variable in a simple select statement but when I do the following, it doesn't work. The reason I am doing the following is because I am calculating the average and such over a 5 min interval, then saving the output as one line. That is why I condense average, max, min, and stdev into one variable which I then output. Any ideas on why it won't run when I have it like this but will run when it is like this?: Thank you for any input
will run:
SELECT AVG(NacTemp), MAX(NacTemp),MIN(NacTemp), STDEV(NacTemp)
FROM [DATABASE]
WHERE [UTCDeviceTimeStamp] between DATEADD(minute, -5, GETUTCDATE()) and GETUTCDATE()
won't run but I need it to:
DECLARE @now datetime SET @now = GETUTCDATE() --Universal Time
DECLARE @timeint int SET @timeint = '5' --time interval in minutes
DECLARE @time datetimeSET @time = (SELECT MIN([UTCDeviceTimeStamp]) FROM [DATABASE] WHERE [UTCDeviceTimeStamp] BETWEEN DATEADD(minute, -@timeint,@now) AND @now) -- Timestamp data will be saved as
DECLARE @comma varchar(4) SET @comma = ', '
DECLARE --variables
@nactempavg numeric(5,2), @nactempmax numeric(5,2), @nactempmin numeric(5,2), @nactempStdDev numeric(5,2)
--NACELLE TEMP
SET @nactempavg = (SELECT AVG([NacTemp]) FROM [DATABASE] WHERE [UTCDeviceTimeStamp] BETWEEN DATEADD(minute, - @timeint, @now) AND @now)
SET @nactempmax = (SELECT MAX([NacTemp]) FROM [DATABASE] WHERE [UTCDeviceTimeStamp] BETWEEN DATEADD(minute, - @timeint, @now) AND @now)
SET @nactempmin = (SELECT MIN([NacTemp]) FROM [DATABASE] WHERE [UTCDeviceTimeStamp] BETWEEN DATEADD(minute, - @timeint, @now) AND @now)
SET @nactempStdDev = (SELECT STDEV([NacTemp]) FROM [DATABASE] WHERE [UTCDeviceTimeStamp] BETWEEN DATEADD(minute, - @timeint, @now) AND @now)
DECLARE @NACELLE varchar(max) SET @NACELLE = convert(char(6),@nactempavg) + @comma + convert(char(6),@nactempmax) + @comma + convert(char(6),@nactempmin) + @comma + convert(char(6),@nactempStdDev)
IF @NACELLE is null
BEGIN
SET @NACELLE = 'NULL, NULL, NULL, NULL'
END
July 21, 2015 at 7:50 am
What exactly do you mean by 'won't run'?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 21, 2015 at 7:52 am
Try putting a print after each set to find problems
July 21, 2015 at 7:53 am
It has been executing for over 6 hours. When I run the same script using a different database, it normally completes in under 20 seconds.
July 21, 2015 at 7:54 am
djj (7/21/2015)
Try putting a print after each set to find problems
Will do.
July 21, 2015 at 7:58 am
I forgot to say and step through the code. If it is taking that long it sounds like you need to look at indexes.
July 21, 2015 at 8:00 am
What's wrong with keeping it simple with a single SELECT statement?
DECLARE @NACELLE varchar(max) = 'NULL, NULL, NULL, NULL',
@timeint int = 5; --time interval in minutes
SELECT @NACELLE =
ISNULL( CONVERT(CHAR(6),AVG(NacTemp)) , 'NULL') + ','
+ ISNULL( CONVERT(CHAR(6),MAX(NacTemp)) , 'NULL') + ','
+ ISNULL( CONVERT(CHAR(6),MIN(NacTemp)) , 'NULL') + ','
+ ISNULL( CONVERT(CHAR(6),STDEV(NacTemp)), 'NULL')
FROM [DATABASE]
WHERE [UTCDeviceTimeStamp] between DATEADD(minute, @timeint * -1, GETUTCDATE()) and GETUTCDATE();
July 21, 2015 at 8:11 am
Luis Cazares (7/21/2015)
What's wrong with keeping it simple with a single SELECT statement?
DECLARE @NACELLE varchar(max) = 'NULL, NULL, NULL, NULL',
@timeint int = 5; --time interval in minutes
SELECT @NACELLE =
ISNULL( CONVERT(CHAR(6),AVG(NacTemp)) , 'NULL') + ','
+ ISNULL( CONVERT(CHAR(6),MAX(NacTemp)) , 'NULL') + ','
+ ISNULL( CONVERT(CHAR(6),MIN(NacTemp)) , 'NULL') + ','
+ ISNULL( CONVERT(CHAR(6),STDEV(NacTemp)), 'NULL')
FROM [DATABASE]
WHERE [UTCDeviceTimeStamp] between DATEADD(minute, @timeint * -1, GETUTCDATE()) and GETUTCDATE();
Each of my individual SET statements complete in under a second but when together, they take forever (longer than I have had patience to wait). I just tried your suggestion and I am not sure why it is taking so long to execute. If I could get your suggestion to execute, I believe that would be another option too. I am really just lost as to why my SET statements work individually but not together.
July 21, 2015 at 8:16 am
Have you checked if anything is blocking this query, or if you have an adequate index?
July 21, 2015 at 8:23 am
Luis Cazares (7/21/2015)
Have you checked if anything is blocking this query, or if you have an adequate index?
There are only indexes for the timestamp column.
July 21, 2015 at 8:40 am
Okay, help us help you. Please post the DDL (CREATE TABLE Statement) for the table(s) involved, sample data for the table(s) (INSERT INTO statements), and expected results based on the sample data.
July 21, 2015 at 8:43 am
Lynn Pettis (7/21/2015)
Okay, help us help you. Please post the DDL (CREATE TABLE Statement) for the table(s) involved, sample data for the table(s) (INSERT INTO statements), and expected results based on the sample data.
And the DDL for indexes as well.
July 21, 2015 at 8:44 am
I figured it out! something was funky having @timeint = 5 and @now = GETUTCDATE() and @comma = ', '. I replaced all the variables with what they equal and it worked. I have no idea why but I am not complaining. It will reduce the ease of changing the time interval and start time but oh well.
Thanks for the help guys!
July 21, 2015 at 9:40 am
Parameter sniffing, or more correctly lack thereof. google: Grant Fritchey parameter sniffing, you should get one of his articles.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 21, 2015 at 12:22 pm
GilaMonster (7/21/2015)
Parameter sniffing, or more correctly lack thereof. google: Grant Fritchey parameter sniffing, you should get one of his articles.
I will definitely check this out. Thanks again. It is much appreciated. 🙂
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply