December 19, 2013 at 8:52 am
I need a query to get the first value greater than a specific threshold value
LoadTemp1 LoadTemp2 LoadTemp3 LoadTemp4 TimeStamp
300 200 100 320 12-10-2013 13:30:29
100 250 113 340 12-10-2013 14:20:12
114 339 209 345 12-10-2013 14:45:01
I need to get the first value >= 340 threshold
I need to make a Benchmark on when the first LoadTemp crosses Threshold of 340 and capture the time.
Please help.
December 19, 2013 at 9:15 am
Something like this?
declare @Threshold int = 340
select MIN(TimeStamp)
from YourTable
where LoadTemp1 > @Threshold
or LoadTemp2 > @Threshold
or LoadTemp3 > @Threshold
or LoadTemp4 > @Threshold
If you have a lot of data this is going to suck for performance. If you could normalize your data structure this would be a lot better.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 19, 2013 at 10:07 am
What about UNPIVOT?
DECLARE @test-2 TABLE (
LoadTemp1 int,
LoadTemp2 int,
LoadTemp3 int,
LoadTemp4 int,
[TimeStamp] datetime
)
INSERT INTO @test-2 VALUES
(300, 200, 100, 320, '12-10-2013 13:30:29'),
(100, 250, 113, 340, '12-10-2013 14:20:12'),
(114, 339, 209, 345, '12-10-2013 14:45:01')
declare @Threshold int = 340
SELECT TOP(1) value, [TimeStamp]
FROM @test-2 t
UNPIVOT(value FOR name IN (LoadTemp1, LoadTemp2, LoadTemp3, LoadTemp4)) AS u
WHERE value > @Threshold
ORDER BY [TimeStamp]
-- Gianluca Sartori
December 19, 2013 at 10:33 am
spaghettidba (12/19/2013)
What about UNPIVOT?
I don't use UNPIVOT a lot because I don't have tables denormalized like this as a general rule. You got me curious so I put together one of Jeff's million row tables filled with random data.
Here are the article from Jeff where he talks about how to generate random test data.
http://www.sqlservercentral.com/articles/Data+Generation/87901/[/url]
http://www.sqlservercentral.com/articles/Test+Data/88964/[/url]
Here is the setup:
if OBJECT_ID('LoadTemp') is not null
drop table LoadTemp
create table LoadTemp
(
LoadTemp1 int,
LoadTemp2 int,
LoadTemp3 int,
LoadTemp4 int,
[TimeStamp] datetime
)
--===== Declare some obviously named variables
DECLARE @NumberOfRows INT,
@StartValue INT,
@EndValue INT,
@Range INT,
@StartDate DATETIME,
@EndDate DATETIME,
@Days INT
;
SELECT @NumberOfRows = 1000000,
@StartValue = 100,
@EndValue = 450,
@Range = @EndValue - @StartValue + 1,
@StartDate = '2010', --Inclusive
@EndDate = '2020', --Exclusive
@Days = DATEDIFF(dd,@StartDate,@EndDate)
;
--===== Create the test table with "random constrained" integers and floats
-- within the parameters identified in the variables above.
insert LoadTemp (LoadTemp1, LoadTemp2, LoadTemp3, LoadTemp4, [TimeStamp])
SELECT TOP (@NumberOfRows)
ABS(CHECKSUM(NEWID())) % @Range + @StartValue,
ABS(CHECKSUM(NEWID())) % @Range + @StartValue,
ABS(CHECKSUM(NEWID())) % @Range + @StartValue,
ABS(CHECKSUM(NEWID())) % @Range + @StartValue,
RAND(CHECKSUM(NEWID())) * @Days + @StartDate
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
This only takes about 4 or 5 seconds on my local instance to generate the million rows.
Now I took the UNPIVOT and my rather clunky conditional code for a test drive.
Here is the code I used.
declare @Threshold int = 340
SELECT TOP(1) value, [TimeStamp]
FROM LoadTemp t
UNPIVOT(value FOR name IN (LoadTemp1, LoadTemp2, LoadTemp3, LoadTemp4)) AS u
WHERE value > @Threshold
ORDER BY [TimeStamp]
select MIN(TimeStamp)
from LoadTemp
where LoadTemp1 > @Threshold
or LoadTemp2 > @Threshold
or LoadTemp3 > @Threshold
or LoadTemp4 > @Threshold
Net result is that the clunky conditional code runs a bit quicker. However, either approach is pretty acceptable as they are both quite fast even against a million rows.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 19, 2013 at 5:57 pm
Sean Lange (12/19/2013)
Net result is that the clunky conditional code runs a bit quicker. However, either approach is pretty acceptable as they are both quite fast even against a million rows.
+1 to Sean. In my testing I couldn't come up with anything better and the results were (with Sean's test harness):
Sean's conditional WHERE
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 1234 ms, elapsed time = 619 ms.
spaghettidba's UNPIVOT
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 3641 ms, elapsed time = 2345 ms.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
December 20, 2013 at 8:28 am
Just kind of eyeballed this because I just used similar syntax to find the max value across multiple columns for something else.
SELECT TOP(1) TimeStamp,
(SELECT MIN(g)
FROM (VALUES (LoadTemp1), (LoadTemp2), (LoadTemp3),(LoadTemp4)) AS value(g)
WHERE g > @Threshold
) as Value
FROM LoadTemp
December 20, 2013 at 8:35 am
erikd (12/20/2013)
Just kind of eyeballed this because I just used similar syntax to find the max value across multiple columns for something else.
SELECT TOP(1) TimeStamp,
(SELECT MIN(g)
FROM (VALUES (LoadTemp1), (LoadTemp2), (LoadTemp3),(LoadTemp4)) AS value(g)
WHERE g > @Threshold
) as Value
FROM LoadTemp
That is an interesting approach. Never would of thought of that. In order for this to return the correct result however we would need to add an order by which is going to kill performance without some indexing.
To get the correct results we would have to do this.
SELECT TOP(1) TimeStamp,
(SELECT MIN(g)
FROM (VALUES (LoadTemp1), (LoadTemp2), (LoadTemp3),(LoadTemp4)) AS value(g)
WHERE g > @Threshold
) as Value
FROM LoadTemp
order by TimeStamp
Here are the results on my machine.
Gianluca
SQL Server Execution Times:
CPU time = 748 ms, elapsed time = 313 ms.
Sean
SQL Server Execution Times:
CPU time = 453 ms, elapsed time = 67 ms.
erikd
SQL Server Execution Times:
CPU time = 1108 ms, elapsed time = 365 ms.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 20, 2013 at 8:54 am
Nuts. The query I had did it pretty quick with about 40 columns and 200k rows. For some reason I felt silly writing OR 40-ish times, but not enclosing 40-ish columns in parentheses. Go figure.
SELECT S_ID,
(SELECT MAX(g)
FROM (VALUES (g1), (g2), (g3),(g4)...(g43)) AS value(g)) as [MaxMark]
FROM gtest
December 20, 2013 at 9:00 am
erikd (12/20/2013)
Nuts. The query I had did it pretty quick with about 40 columns and 200k rows. For some reason I felt silly writing OR 40-ish times, but not enclosing 40-ish columns in parentheses. Go figure.
SELECT S_ID,
(SELECT MAX(g)
FROM (VALUES (g1), (g2), (g3),(g4)...(g43)) AS value(g)) as [MaxMark]
FROM gtest
That doesn't mean that what you did in your situation is not the best approach. This thread can serve as proof of that. There have been 3 very different methods posted here all of which accomplish the same thing. Depending on the actual table structure and the indexing scheme of the real tables may prove that any of the three will perform the best.
In your case you were looking for the largest value across each row. For that type of output I think what you have is likely the best way. This thread is a little different because they are looking for the first time a value exceeds a given threshold across any row. Very different situation.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 20, 2013 at 9:15 am
Sean Lange (12/20/2013)
erikd (12/20/2013)
Nuts. The query I had did it pretty quick with about 40 columns and 200k rows. For some reason I felt silly writing OR 40-ish times, but not enclosing 40-ish columns in parentheses. Go figure.
SELECT S_ID,
(SELECT MAX(g)
FROM (VALUES (g1), (g2), (g3),(g4)...(g43)) AS value(g)) as [MaxMark]
FROM gtest
That doesn't mean that what you did in your situation is not the best approach. This thread can serve as proof of that. There have been 3 very different methods posted here all of which accomplish the same thing. Depending on the actual table structure and the indexing scheme of the real tables may prove that any of the three will perform the best.
In your case you were looking for the largest value across each row. For that type of output I think what you have is likely the best way. This thread is a little different because they are looking for the first time a value exceeds a given threshold across any row. Very different situation.
Yeah, for sure. I'm just sort of twitchy this week after a few interviews and getting asked some outlandish query questions. One of them clearly with an HR person who had a prepared Q&A from someone else.
I think I'm going to try UNPIVOT, though, if anything just to see if it behaves differently than what I have.
December 23, 2013 at 7:31 am
create TABLE #temp(LoadTemp1 int,LoadTemp2 int,LoadTemp3 int,LoadTemp4 int,[TimeStamp] datetime)
INSERT INTO #temp VALUES
(300, 200, 100, 320, '12-10-2013 13:30:29'),
(100, 250, 113, 340, '12-10-2013 14:20:12'),
(114, 339, 209, 345, '12-10-2013 14:45:01')
declare @Threshold int = 340
select distinct a.TimeStamp FROM #tempa inner join #temp b
on a.LoadTemp1 > b.LoadTemp1 or a.LoadTemp2 > b.LoadTemp2 or a.LoadTemp3 > b.LoadTemp3or a.LoadTemp4 > b.LoadTemp4
where a.LoadTemp1 > @Thresholdor a.LoadTemp2 > @Thresholdor a.LoadTemp3 > @Thresholdor a.LoadTemp4 > @Threshold
select min(a.TimeStamp) FROM #tempa inner join #temp b
on a.LoadTemp1 > b.LoadTemp1 or a.LoadTemp2 > b.LoadTemp2 or a.LoadTemp3 > b.LoadTemp3or a.LoadTemp4 > b.LoadTemp4
where a.LoadTemp1 > @Thresholdor a.LoadTemp2 > @Thresholdor a.LoadTemp3 > @Thresholdor a.LoadTemp4 > @Threshold
December 23, 2013 at 8:57 pm
Tamil Vengai (12/23/2013)
create TABLE #temp(LoadTemp1 int,LoadTemp2 int,LoadTemp3 int,LoadTemp4 int,[TimeStamp] datetime)INSERT INTO #temp VALUES
(300, 200, 100, 320, '12-10-2013 13:30:29'),
(100, 250, 113, 340, '12-10-2013 14:20:12'),
(114, 339, 209, 345, '12-10-2013 14:45:01')
declare @Threshold int = 340
select distinct a.TimeStamp FROM #tempa inner join #temp b
on a.LoadTemp1 > b.LoadTemp1 or a.LoadTemp2 > b.LoadTemp2 or a.LoadTemp3 > b.LoadTemp3or a.LoadTemp4 > b.LoadTemp4
where a.LoadTemp1 > @Thresholdor a.LoadTemp2 > @Thresholdor a.LoadTemp3 > @Thresholdor a.LoadTemp4 > @Threshold
select min(a.TimeStamp) FROM #tempa inner join #temp b
on a.LoadTemp1 > b.LoadTemp1 or a.LoadTemp2 > b.LoadTemp2 or a.LoadTemp3 > b.LoadTemp3or a.LoadTemp4 > b.LoadTemp4
where a.LoadTemp1 > @Thresholdor a.LoadTemp2 > @Thresholdor a.LoadTemp3 > @Thresholdor a.LoadTemp4 > @Threshold
The problem here is that you have triangular joins. Add to that the multiple OR predicates and you a real performance issue here. You can read more about triangular joins here. http://www.sqlservercentral.com/articles/T-SQL/61539/[/url]
I tried to put your query through the same million row test harness. After about 7 minutes my laptop overheated and I had to reboot it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply