Awful performance on joined queries while great separately The 2019 Stack Overflow Developer...
Is domain driven design an anti-SQL pattern?
I see my dog run
"Riffle" two strings
Spanish for "widget"
"To split hairs" vs "To be pedantic"
Where does the "burst of radiance" from Holy Weapon originate?
is usb on wall sockets live all the time with out switches off
Is it possible for the two major parties in the UK to form a coalition with each other instead of a much smaller party?
Are there any other methods to apply to solving simultaneous equations?
How to answer pointed "are you quitting" questioning when I don't want them to suspect
Why Did Howard Stark Use All The Vibranium They Had On A Prototype Shield?
Feasability of miniature nuclear reactors for humanoid cyborgs
Is there a name of the flying bionic bird?
Should I use my personal or workplace e-mail when registering to external websites for work purpose?
How long do I have to send payment?
What does "sndry explns" mean in one of the Hitchhiker's guide books?
Pristine Bit Checking
Why is Grand Jury testimony secret?
aging parents with no investments
It's possible to achieve negative score?
Does duplicating a spell with wish count as casting that spell?
What do hard-Brexiteers want with respect to the Irish border?
What do the Banks children have against barley water?
Fractional alignment
Awful performance on joined queries while great separately
The 2019 Stack Overflow Developer Survey Results Are InIdentical query, tables, but different EXPLAIN and performanceJoin select queries on an id in SQL ServerTroubleshooting extremely long running query (SQL Server 2012)MySQL query taking too longConversion failed when converting the varchar value 'TerminatedByCompany' to data type intPerformance gap between WHERE IN (1,2,3,4) vs IN (select * from STRING_SPLIT('1,2,3,4',','))Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL queryInvestigating errors from strange queryDate format incompatibility in linked server between SQL Server and OracleConvert Varchar To Datetime and Add Seconds
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
I have a simple queries:
SELECT t1.[IntField], CONVERT(varchar,t1.[KhDateTimeField],121) AS 'TDateTimeField'
, t1.[FloatField1], t1.[FloatField2]
, t2.[FloatField3]
FROM
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField1], t.[FloatField2], dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table1] t
WHERE t.[TDateTimeField]>CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField]<=CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt]>= 20160303
AND t.[DateInt]<= 20160310
AND t.[IntField] IN (206,207,208)
) t1
INNER JOIN
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField3], dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table2] t
WHERE t.[TDateTimeField]>CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField]<=CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt]>= 20160303
AND t.[DateInt]<= 20160310
AND t.[IntField] IN (206,207,208)
) t2
ON t1.IntField=t2.IntField AND t1.TDateTimeField=t2.TDateTimeField
--ON t1.IntField=t2.IntField AND t1.KhDateTimeField=t2.KhDateTimeField
When the query is executed as you see now it runs in less than 1 second. If I run the last two line like this instead
--ON t1.IntField=t2.IntField AND t1.TDateTimeField=t2.TDateTimeField
ON t1.IntField=t2.IntField AND t1.KhDateTimeField=t2.KhDateTimeField
It takes more than 1 minute. The difference is obvious: joining a persisted value against joining a computed value. Running the queries with IO and time statistics will show exactly the same query plans and IO figures, but CPU is huge in the second case.
I've been thinking about how to reorder/rewrite the query to make it run in 1 second but I haven't succeed. The following works as fast as I expect but involves more code than I would like to:
declare @temp1 table (IntField int, TDateTimeField datetime2(7), FloatField1 float, FloatField2 float);
declare @temp2 table (IntField int, TDateTimeField datetime2(7), FloatField3 float);
insert into @temp1
SELECT t.[IntField], dbo.fn_roundToCloser10Min(t.[TDateTimeField]), t.[FloatField1], t.[FloatField2]
FROM [DB].[dbo].[Table1] t
WHERE t.[TDateTimeField]>CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField]<=CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt]>= 20160303
AND t.[DateInt]<= 20160310
AND t.[IntField] IN (206,207,208);
insert into @temp2
SELECT t.[IntField], dbo.fn_roundToCloser10Min(t.[TDateTimeField]), t.[FloatField3]
FROM [DB].[dbo].[Table2] t
WHERE t.[TDateTimeField]>CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField]<=CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt]>= 20160303
AND t.[DateInt]<= 20160310
AND t.[IntField] IN (206,207,208)
SELECT t1.[IntField], CONVERT(varchar,t1.[TDateTimeField],121) AS 'TDateTimeField'
, t1.[FloatField1], t1.[FloatField2]
, t2.[FloatField3]
FROM @temp1 t1 JOIN @temp2 t2 ON t1.IntField=t2.IntField AND t1.TDateTimeField=t2.TDateTimeField
It's obvious that the improve comes because data is pre-calculated and pre-stored, hence reducing the amount of data, just before joining.
Does anybody have a better idea?
Thanks!!
EDIT: Actual query plans added (first and second query respectively). You'll see they are almost identical, and I've checked the CPU costs in them and didn't see anything weird...
sql-server query-performance
asked Mar 17 '16 at 11:42
HauriHauri
3053412
bumped to the homepage by Community♦ 13 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
|
show 8 more comments
I have a simple queries:
SELECT t1.[IntField], CONVERT(varchar,t1.[KhDateTimeField],121) AS 'TDateTimeField'
, t1.[FloatField1], t1.[FloatField2]
, t2.[FloatField3]
FROM
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField1], t.[FloatField2], dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table1] t
WHERE t.[TDateTimeField]>CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField]<=CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt]>= 20160303
AND t.[DateInt]<= 20160310
AND t.[IntField] IN (206,207,208)
) t1
INNER JOIN
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField3], dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table2] t
WHERE t.[TDateTimeField]>CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField]<=CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt]>= 20160303
AND t.[DateInt]<= 20160310
AND t.[IntField] IN (206,207,208)
) t2
ON t1.IntField=t2.IntField AND t1.TDateTimeField=t2.TDateTimeField
--ON t1.IntField=t2.IntField AND t1.KhDateTimeField=t2.KhDateTimeField
When the query is executed as you see now it runs in less than 1 second. If I run the last two line like this instead
--ON t1.IntField=t2.IntField AND t1.TDateTimeField=t2.TDateTimeField
ON t1.IntField=t2.IntField AND t1.KhDateTimeField=t2.KhDateTimeField
It takes more than 1 minute. The difference is obvious: joining a persisted value against joining a computed value. Running the queries with IO and time statistics will show exactly the same query plans and IO figures, but CPU is huge in the second case.
I've been thinking about how to reorder/rewrite the query to make it run in 1 second but I haven't succeed. The following works as fast as I expect but involves more code than I would like to:
declare @temp1 table (IntField int, TDateTimeField datetime2(7), FloatField1 float, FloatField2 float);
declare @temp2 table (IntField int, TDateTimeField datetime2(7), FloatField3 float);
insert into @temp1
SELECT t.[IntField], dbo.fn_roundToCloser10Min(t.[TDateTimeField]), t.[FloatField1], t.[FloatField2]
FROM [DB].[dbo].[Table1] t
WHERE t.[TDateTimeField]>CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField]<=CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt]>= 20160303
AND t.[DateInt]<= 20160310
AND t.[IntField] IN (206,207,208);
insert into @temp2
SELECT t.[IntField], dbo.fn_roundToCloser10Min(t.[TDateTimeField]), t.[FloatField3]
FROM [DB].[dbo].[Table2] t
WHERE t.[TDateTimeField]>CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField]<=CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt]>= 20160303
AND t.[DateInt]<= 20160310
AND t.[IntField] IN (206,207,208)
SELECT t1.[IntField], CONVERT(varchar,t1.[TDateTimeField],121) AS 'TDateTimeField'
, t1.[FloatField1], t1.[FloatField2]
, t2.[FloatField3]
FROM @temp1 t1 JOIN @temp2 t2 ON t1.IntField=t2.IntField AND t1.TDateTimeField=t2.TDateTimeField
It's obvious that the improve comes because data is pre-calculated and pre-stored, hence reducing the amount of data, just before joining.
Does anybody have a better idea?
Thanks!!
EDIT: Actual query plans added (first and second query respectively). You'll see they are almost identical, and I've checked the CPU costs in them and didn't see anything weird...
sql-server query-performance
asked Mar 17 '16 at 11:42
HauriHauri
3053412
bumped to the homepage by Community♦ 13 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
2
Divide et impera: using temporary tables (not table variables, they're awful performance-wise) is not necessarily a bad thing. Why do you need a single statement? Also, please post the actual execution plans.
– spaghettidba
Mar 17 '16 at 11:48
Editted for actual query plans added. Yeah, I agree that temporary tables are fine depending on circumstances. The thing is that this query code needs to run on an application program, so creating all the temp tables and so on adds more code lines and I'm not sure they can be used anyway. If there is no way of achieving a good performance with a single statement then I will encapsulate this into a stored proceudre, but I want to know possible alternatives first.
– Hauri
Mar 17 '16 at 12:04
Thanks for the plans, but we need the XML plan, not a picture of it. Please upload your .sqlplan files somewhere and link them here. It would be perfect if you could upload all the three plans: first query alone, second query alone and first joined to second.
– spaghettidba
Mar 17 '16 at 12:07
I appreciate your interest in helping, but unfortunately I can't post here that, as it contains private data. Besides it's too long.... Sorry.
– Hauri
Mar 17 '16 at 12:43
1
A few things to try - First, if you have more than a couple hundred rows in the temp variables, change them out for temp tables. The latter at least will get stats generated and get you a half way decent estimate. Second, use BETWEEN for the date comparisons instead of the > and <=. Finally, you don't need to CAST the string to DATETIME so long as it is in 'YYYY-MM-DD' format; the engine will do a morning to night comparison for you with just the date
– Steve Mangiameli
Mar 17 '16 at 14:51
|
show 8 more comments
I have a simple queries:
SELECT t1.[IntField], CONVERT(varchar,t1.[KhDateTimeField],121) AS 'TDateTimeField'
, t1.[FloatField1], t1.[FloatField2]
, t2.[FloatField3]
FROM
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField1], t.[FloatField2], dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table1] t
WHERE t.[TDateTimeField]>CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField]<=CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt]>= 20160303
AND t.[DateInt]<= 20160310
AND t.[IntField] IN (206,207,208)
) t1
INNER JOIN
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField3], dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table2] t
WHERE t.[TDateTimeField]>CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField]<=CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt]>= 20160303
AND t.[DateInt]<= 20160310
AND t.[IntField] IN (206,207,208)
) t2
ON t1.IntField=t2.IntField AND t1.TDateTimeField=t2.TDateTimeField
--ON t1.IntField=t2.IntField AND t1.KhDateTimeField=t2.KhDateTimeField
When the query is executed as you see now it runs in less than 1 second. If I run the last two line like this instead
--ON t1.IntField=t2.IntField AND t1.TDateTimeField=t2.TDateTimeField
ON t1.IntField=t2.IntField AND t1.KhDateTimeField=t2.KhDateTimeField
It takes more than 1 minute. The difference is obvious: joining a persisted value against joining a computed value. Running the queries with IO and time statistics will show exactly the same query plans and IO figures, but CPU is huge in the second case.
I've been thinking about how to reorder/rewrite the query to make it run in 1 second but I haven't succeed. The following works as fast as I expect but involves more code than I would like to:
declare @temp1 table (IntField int, TDateTimeField datetime2(7), FloatField1 float, FloatField2 float);
declare @temp2 table (IntField int, TDateTimeField datetime2(7), FloatField3 float);
insert into @temp1
SELECT t.[IntField], dbo.fn_roundToCloser10Min(t.[TDateTimeField]), t.[FloatField1], t.[FloatField2]
FROM [DB].[dbo].[Table1] t
WHERE t.[TDateTimeField]>CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField]<=CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt]>= 20160303
AND t.[DateInt]<= 20160310
AND t.[IntField] IN (206,207,208);
insert into @temp2
SELECT t.[IntField], dbo.fn_roundToCloser10Min(t.[TDateTimeField]), t.[FloatField3]
FROM [DB].[dbo].[Table2] t
WHERE t.[TDateTimeField]>CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField]<=CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt]>= 20160303
AND t.[DateInt]<= 20160310
AND t.[IntField] IN (206,207,208)
SELECT t1.[IntField], CONVERT(varchar,t1.[TDateTimeField],121) AS 'TDateTimeField'
, t1.[FloatField1], t1.[FloatField2]
, t2.[FloatField3]
FROM @temp1 t1 JOIN @temp2 t2 ON t1.IntField=t2.IntField AND t1.TDateTimeField=t2.TDateTimeField
It's obvious that the improve comes because data is pre-calculated and pre-stored, hence reducing the amount of data, just before joining.
Does anybody have a better idea?
Thanks!!
EDIT: Actual query plans added (first and second query respectively). You'll see they are almost identical, and I've checked the CPU costs in them and didn't see anything weird...
sql-server query-performance
asked Mar 17 '16 at 11:42
HauriHauri
3053412
I have a simple queries:
SELECT t1.[IntField], CONVERT(varchar,t1.[KhDateTimeField],121) AS 'TDateTimeField'
, t1.[FloatField1], t1.[FloatField2]
, t2.[FloatField3]
FROM
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField1], t.[FloatField2], dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table1] t
WHERE t.[TDateTimeField]>CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField]<=CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt]>= 20160303
AND t.[DateInt]<= 20160310
AND t.[IntField] IN (206,207,208)
) t1
INNER JOIN
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField3], dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table2] t
WHERE t.[TDateTimeField]>CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField]<=CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt]>= 20160303
AND t.[DateInt]<= 20160310
AND t.[IntField] IN (206,207,208)
) t2
ON t1.IntField=t2.IntField AND t1.TDateTimeField=t2.TDateTimeField
--ON t1.IntField=t2.IntField AND t1.KhDateTimeField=t2.KhDateTimeField
When the query is executed as you see now it runs in less than 1 second. If I run the last two line like this instead
--ON t1.IntField=t2.IntField AND t1.TDateTimeField=t2.TDateTimeField
ON t1.IntField=t2.IntField AND t1.KhDateTimeField=t2.KhDateTimeField
It takes more than 1 minute. The difference is obvious: joining a persisted value against joining a computed value. Running the queries with IO and time statistics will show exactly the same query plans and IO figures, but CPU is huge in the second case.
I've been thinking about how to reorder/rewrite the query to make it run in 1 second but I haven't succeed. The following works as fast as I expect but involves more code than I would like to:
declare @temp1 table (IntField int, TDateTimeField datetime2(7), FloatField1 float, FloatField2 float);
declare @temp2 table (IntField int, TDateTimeField datetime2(7), FloatField3 float);
insert into @temp1
SELECT t.[IntField], dbo.fn_roundToCloser10Min(t.[TDateTimeField]), t.[FloatField1], t.[FloatField2]
FROM [DB].[dbo].[Table1] t
WHERE t.[TDateTimeField]>CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField]<=CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt]>= 20160303
AND t.[DateInt]<= 20160310
AND t.[IntField] IN (206,207,208);
insert into @temp2
SELECT t.[IntField], dbo.fn_roundToCloser10Min(t.[TDateTimeField]), t.[FloatField3]
FROM [DB].[dbo].[Table2] t
WHERE t.[TDateTimeField]>CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField]<=CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt]>= 20160303
AND t.[DateInt]<= 20160310
AND t.[IntField] IN (206,207,208)
SELECT t1.[IntField], CONVERT(varchar,t1.[TDateTimeField],121) AS 'TDateTimeField'
, t1.[FloatField1], t1.[FloatField2]
, t2.[FloatField3]
FROM @temp1 t1 JOIN @temp2 t2 ON t1.IntField=t2.IntField AND t1.TDateTimeField=t2.TDateTimeField
It's obvious that the improve comes because data is pre-calculated and pre-stored, hence reducing the amount of data, just before joining.
Does anybody have a better idea?
Thanks!!
EDIT: Actual query plans added (first and second query respectively). You'll see they are almost identical, and I've checked the CPU costs in them and didn't see anything weird...
sql-server query-performance
sql-server query-performance
asked Mar 17 '16 at 11:42
HauriHauri
3053412
asked Mar 17 '16 at 11:42
HauriHauri
3053412
edited Mar 17 '16 at 12:41
Hauri
asked Mar 17 '16 at 11:42
HauriHauri
3053412
asked Mar 17 '16 at 11:42
HauriHauri
3053412
asked Mar 17 '16 at 11:42
HauriHauri
3053412
3053412
bumped to the homepage by Community♦ 13 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 13 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
2
Divide et impera: using temporary tables (not table variables, they're awful performance-wise) is not necessarily a bad thing. Why do you need a single statement? Also, please post the actual execution plans.
– spaghettidba
Mar 17 '16 at 11:48
Editted for actual query plans added. Yeah, I agree that temporary tables are fine depending on circumstances. The thing is that this query code needs to run on an application program, so creating all the temp tables and so on adds more code lines and I'm not sure they can be used anyway. If there is no way of achieving a good performance with a single statement then I will encapsulate this into a stored proceudre, but I want to know possible alternatives first.
– Hauri
Mar 17 '16 at 12:04
Thanks for the plans, but we need the XML plan, not a picture of it. Please upload your .sqlplan files somewhere and link them here. It would be perfect if you could upload all the three plans: first query alone, second query alone and first joined to second.
– spaghettidba
Mar 17 '16 at 12:07
I appreciate your interest in helping, but unfortunately I can't post here that, as it contains private data. Besides it's too long.... Sorry.
– Hauri
Mar 17 '16 at 12:43
1
A few things to try - First, if you have more than a couple hundred rows in the temp variables, change them out for temp tables. The latter at least will get stats generated and get you a half way decent estimate. Second, use BETWEEN for the date comparisons instead of the > and <=. Finally, you don't need to CAST the string to DATETIME so long as it is in 'YYYY-MM-DD' format; the engine will do a morning to night comparison for you with just the date
– Steve Mangiameli
Mar 17 '16 at 14:51
|
show 8 more comments
2
Divide et impera: using temporary tables (not table variables, they're awful performance-wise) is not necessarily a bad thing. Why do you need a single statement? Also, please post the actual execution plans.
– spaghettidba
Mar 17 '16 at 11:48
Editted for actual query plans added. Yeah, I agree that temporary tables are fine depending on circumstances. The thing is that this query code needs to run on an application program, so creating all the temp tables and so on adds more code lines and I'm not sure they can be used anyway. If there is no way of achieving a good performance with a single statement then I will encapsulate this into a stored proceudre, but I want to know possible alternatives first.
– Hauri
Mar 17 '16 at 12:04
Thanks for the plans, but we need the XML plan, not a picture of it. Please upload your .sqlplan files somewhere and link them here. It would be perfect if you could upload all the three plans: first query alone, second query alone and first joined to second.
– spaghettidba
Mar 17 '16 at 12:07
I appreciate your interest in helping, but unfortunately I can't post here that, as it contains private data. Besides it's too long.... Sorry.
– Hauri
Mar 17 '16 at 12:43
1
A few things to try - First, if you have more than a couple hundred rows in the temp variables, change them out for temp tables. The latter at least will get stats generated and get you a half way decent estimate. Second, use BETWEEN for the date comparisons instead of the > and <=. Finally, you don't need to CAST the string to DATETIME so long as it is in 'YYYY-MM-DD' format; the engine will do a morning to night comparison for you with just the date
– Steve Mangiameli
Mar 17 '16 at 14:51
2
2
Divide et impera: using temporary tables (not table variables, they're awful performance-wise) is not necessarily a bad thing. Why do you need a single statement? Also, please post the actual execution plans.
– spaghettidba
Mar 17 '16 at 11:48
Divide et impera: using temporary tables (not table variables, they're awful performance-wise) is not necessarily a bad thing. Why do you need a single statement? Also, please post the actual execution plans.
– spaghettidba
Mar 17 '16 at 11:48
Editted for actual query plans added. Yeah, I agree that temporary tables are fine depending on circumstances. The thing is that this query code needs to run on an application program, so creating all the temp tables and so on adds more code lines and I'm not sure they can be used anyway. If there is no way of achieving a good performance with a single statement then I will encapsulate this into a stored proceudre, but I want to know possible alternatives first.
– Hauri
Mar 17 '16 at 12:04
Editted for actual query plans added. Yeah, I agree that temporary tables are fine depending on circumstances. The thing is that this query code needs to run on an application program, so creating all the temp tables and so on adds more code lines and I'm not sure they can be used anyway. If there is no way of achieving a good performance with a single statement then I will encapsulate this into a stored proceudre, but I want to know possible alternatives first.
– Hauri
Mar 17 '16 at 12:04
Thanks for the plans, but we need the XML plan, not a picture of it. Please upload your .sqlplan files somewhere and link them here. It would be perfect if you could upload all the three plans: first query alone, second query alone and first joined to second.
– spaghettidba
Mar 17 '16 at 12:07
Thanks for the plans, but we need the XML plan, not a picture of it. Please upload your .sqlplan files somewhere and link them here. It would be perfect if you could upload all the three plans: first query alone, second query alone and first joined to second.
– spaghettidba
Mar 17 '16 at 12:07
I appreciate your interest in helping, but unfortunately I can't post here that, as it contains private data. Besides it's too long.... Sorry.
– Hauri
Mar 17 '16 at 12:43
I appreciate your interest in helping, but unfortunately I can't post here that, as it contains private data. Besides it's too long.... Sorry.
– Hauri
Mar 17 '16 at 12:43
1
1
A few things to try - First, if you have more than a couple hundred rows in the temp variables, change them out for temp tables. The latter at least will get stats generated and get you a half way decent estimate. Second, use BETWEEN for the date comparisons instead of the > and <=. Finally, you don't need to CAST the string to DATETIME so long as it is in 'YYYY-MM-DD' format; the engine will do a morning to night comparison for you with just the date
– Steve Mangiameli
Mar 17 '16 at 14:51
A few things to try - First, if you have more than a couple hundred rows in the temp variables, change them out for temp tables. The latter at least will get stats generated and get you a half way decent estimate. Second, use BETWEEN for the date comparisons instead of the > and <=. Finally, you don't need to CAST the string to DATETIME so long as it is in 'YYYY-MM-DD' format; the engine will do a morning to night comparison for you with just the date
– Steve Mangiameli
Mar 17 '16 at 14:51
|
show 8 more comments
1 Answer
1
active
oldest
votes
A stretch but give this a try
Just deals with one t.[IntField] at a time and union
Now you have 1/3 only comparing to 1/3 so it may be 9 times as fast
And the query optimizer may miraculously decide not to do a loop join
SELECT t1.[IntField]
, CONVERT(varchar,t1.[KhDateTimeField],121) AS 'TDateTimeField'
, t1.[FloatField1], t1.[FloatField2], t2.[FloatField3]
FROM
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField1], t.[FloatField2]
, dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table1] t
WHERE t.[TDateTimeField] > CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField] <= CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt] >= 20160303
AND t.[DateInt] <= 20160310
AND t.[IntField] = 206
) t1
INNER JOIN
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField3]
, dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table2] t
WHERE t.[TDateTimeField] > CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField] <= CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt] >= 20160303
AND t.[DateInt] <= 20160310
AND t.[IntField] = 206
) t2
ON t1.TDateTimeField = t2.TDateTimeField
UNION
SELECT t1.[IntField]
, CONVERT(varchar,t1.[KhDateTimeField],121) AS 'TDateTimeField'
, t1.[FloatField1], t1.[FloatField2], t2.[FloatField3]
FROM
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField1], t.[FloatField2]
, dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table1] t
WHERE t.[TDateTimeField] > CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField] <= CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt] >= 20160303
AND t.[DateInt] <= 20160310
AND t.[IntField] = 207
) t1
INNER JOIN
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField3]
, dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table2] t
WHERE t.[TDateTimeField] > CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField] <= CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt] >= 20160303
AND t.[DateInt] <= 20160310
AND t.[IntField] = 207
) t2
ON t1.TDateTimeField = t2.TDateTimeField
UNION ... 208
If it works then you can just make two stored procedures where you pass in the 5 conditions for the base select and the syntax becomes pretty simple.
Materializing is the safe path. But use #temp over table. An index on [IntField] may help but with only 3 unique values it may not help. Even with materializing make some stored procedures to reduce syntax.
answered Mar 20 '16 at 17:19
paparazzopaparazzo
4,6621230
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f132506%2fawful-performance-on-joined-queries-while-great-separately%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
A stretch but give this a try
Just deals with one t.[IntField] at a time and union
Now you have 1/3 only comparing to 1/3 so it may be 9 times as fast
And the query optimizer may miraculously decide not to do a loop join
SELECT t1.[IntField]
, CONVERT(varchar,t1.[KhDateTimeField],121) AS 'TDateTimeField'
, t1.[FloatField1], t1.[FloatField2], t2.[FloatField3]
FROM
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField1], t.[FloatField2]
, dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table1] t
WHERE t.[TDateTimeField] > CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField] <= CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt] >= 20160303
AND t.[DateInt] <= 20160310
AND t.[IntField] = 206
) t1
INNER JOIN
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField3]
, dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table2] t
WHERE t.[TDateTimeField] > CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField] <= CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt] >= 20160303
AND t.[DateInt] <= 20160310
AND t.[IntField] = 206
) t2
ON t1.TDateTimeField = t2.TDateTimeField
UNION
SELECT t1.[IntField]
, CONVERT(varchar,t1.[KhDateTimeField],121) AS 'TDateTimeField'
, t1.[FloatField1], t1.[FloatField2], t2.[FloatField3]
FROM
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField1], t.[FloatField2]
, dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table1] t
WHERE t.[TDateTimeField] > CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField] <= CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt] >= 20160303
AND t.[DateInt] <= 20160310
AND t.[IntField] = 207
) t1
INNER JOIN
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField3]
, dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table2] t
WHERE t.[TDateTimeField] > CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField] <= CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt] >= 20160303
AND t.[DateInt] <= 20160310
AND t.[IntField] = 207
) t2
ON t1.TDateTimeField = t2.TDateTimeField
UNION ... 208
If it works then you can just make two stored procedures where you pass in the 5 conditions for the base select and the syntax becomes pretty simple.
Materializing is the safe path. But use #temp over table. An index on [IntField] may help but with only 3 unique values it may not help. Even with materializing make some stored procedures to reduce syntax.
answered Mar 20 '16 at 17:19
paparazzopaparazzo
4,6621230
add a comment |
A stretch but give this a try
Just deals with one t.[IntField] at a time and union
Now you have 1/3 only comparing to 1/3 so it may be 9 times as fast
And the query optimizer may miraculously decide not to do a loop join
SELECT t1.[IntField]
, CONVERT(varchar,t1.[KhDateTimeField],121) AS 'TDateTimeField'
, t1.[FloatField1], t1.[FloatField2], t2.[FloatField3]
FROM
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField1], t.[FloatField2]
, dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table1] t
WHERE t.[TDateTimeField] > CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField] <= CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt] >= 20160303
AND t.[DateInt] <= 20160310
AND t.[IntField] = 206
) t1
INNER JOIN
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField3]
, dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table2] t
WHERE t.[TDateTimeField] > CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField] <= CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt] >= 20160303
AND t.[DateInt] <= 20160310
AND t.[IntField] = 206
) t2
ON t1.TDateTimeField = t2.TDateTimeField
UNION
SELECT t1.[IntField]
, CONVERT(varchar,t1.[KhDateTimeField],121) AS 'TDateTimeField'
, t1.[FloatField1], t1.[FloatField2], t2.[FloatField3]
FROM
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField1], t.[FloatField2]
, dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table1] t
WHERE t.[TDateTimeField] > CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField] <= CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt] >= 20160303
AND t.[DateInt] <= 20160310
AND t.[IntField] = 207
) t1
INNER JOIN
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField3]
, dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table2] t
WHERE t.[TDateTimeField] > CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField] <= CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt] >= 20160303
AND t.[DateInt] <= 20160310
AND t.[IntField] = 207
) t2
ON t1.TDateTimeField = t2.TDateTimeField
UNION ... 208
If it works then you can just make two stored procedures where you pass in the 5 conditions for the base select and the syntax becomes pretty simple.
Materializing is the safe path. But use #temp over table. An index on [IntField] may help but with only 3 unique values it may not help. Even with materializing make some stored procedures to reduce syntax.
answered Mar 20 '16 at 17:19
paparazzopaparazzo
4,6621230
add a comment |
A stretch but give this a try
Just deals with one t.[IntField] at a time and union
Now you have 1/3 only comparing to 1/3 so it may be 9 times as fast
And the query optimizer may miraculously decide not to do a loop join
SELECT t1.[IntField]
, CONVERT(varchar,t1.[KhDateTimeField],121) AS 'TDateTimeField'
, t1.[FloatField1], t1.[FloatField2], t2.[FloatField3]
FROM
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField1], t.[FloatField2]
, dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table1] t
WHERE t.[TDateTimeField] > CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField] <= CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt] >= 20160303
AND t.[DateInt] <= 20160310
AND t.[IntField] = 206
) t1
INNER JOIN
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField3]
, dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table2] t
WHERE t.[TDateTimeField] > CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField] <= CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt] >= 20160303
AND t.[DateInt] <= 20160310
AND t.[IntField] = 206
) t2
ON t1.TDateTimeField = t2.TDateTimeField
UNION
SELECT t1.[IntField]
, CONVERT(varchar,t1.[KhDateTimeField],121) AS 'TDateTimeField'
, t1.[FloatField1], t1.[FloatField2], t2.[FloatField3]
FROM
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField1], t.[FloatField2]
, dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table1] t
WHERE t.[TDateTimeField] > CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField] <= CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt] >= 20160303
AND t.[DateInt] <= 20160310
AND t.[IntField] = 207
) t1
INNER JOIN
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField3]
, dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table2] t
WHERE t.[TDateTimeField] > CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField] <= CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt] >= 20160303
AND t.[DateInt] <= 20160310
AND t.[IntField] = 207
) t2
ON t1.TDateTimeField = t2.TDateTimeField
UNION ... 208
If it works then you can just make two stored procedures where you pass in the 5 conditions for the base select and the syntax becomes pretty simple.
Materializing is the safe path. But use #temp over table. An index on [IntField] may help but with only 3 unique values it may not help. Even with materializing make some stored procedures to reduce syntax.
answered Mar 20 '16 at 17:19
paparazzopaparazzo
4,6621230
A stretch but give this a try
Just deals with one t.[IntField] at a time and union
Now you have 1/3 only comparing to 1/3 so it may be 9 times as fast
And the query optimizer may miraculously decide not to do a loop join
SELECT t1.[IntField]
, CONVERT(varchar,t1.[KhDateTimeField],121) AS 'TDateTimeField'
, t1.[FloatField1], t1.[FloatField2], t2.[FloatField3]
FROM
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField1], t.[FloatField2]
, dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table1] t
WHERE t.[TDateTimeField] > CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField] <= CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt] >= 20160303
AND t.[DateInt] <= 20160310
AND t.[IntField] = 206
) t1
INNER JOIN
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField3]
, dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table2] t
WHERE t.[TDateTimeField] > CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField] <= CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt] >= 20160303
AND t.[DateInt] <= 20160310
AND t.[IntField] = 206
) t2
ON t1.TDateTimeField = t2.TDateTimeField
UNION
SELECT t1.[IntField]
, CONVERT(varchar,t1.[KhDateTimeField],121) AS 'TDateTimeField'
, t1.[FloatField1], t1.[FloatField2], t2.[FloatField3]
FROM
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField1], t.[FloatField2]
, dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table1] t
WHERE t.[TDateTimeField] > CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField] <= CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt] >= 20160303
AND t.[DateInt] <= 20160310
AND t.[IntField] = 207
) t1
INNER JOIN
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField3]
, dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table2] t
WHERE t.[TDateTimeField] > CAST('2016-03-01T00:00:00' AS DATETIME)
AND t.[TDateTimeField] <= CAST('2016-03-10T00:00:00' AS DATETIME)
AND t.[DateInt] >= 20160303
AND t.[DateInt] <= 20160310
AND t.[IntField] = 207
) t2
ON t1.TDateTimeField = t2.TDateTimeField
UNION ... 208
If it works then you can just make two stored procedures where you pass in the 5 conditions for the base select and the syntax becomes pretty simple.
Materializing is the safe path. But use #temp over table. An index on [IntField] may help but with only 3 unique values it may not help. Even with materializing make some stored procedures to reduce syntax.
answered Mar 20 '16 at 17:19
paparazzopaparazzo
4,6621230
edited Mar 20 '16 at 17:25
answered Mar 20 '16 at 17:19
paparazzopaparazzo
4,6621230
answered Mar 20 '16 at 17:19
paparazzopaparazzo
4,6621230
answered Mar 20 '16 at 17:19
paparazzopaparazzo
4,6621230
4,6621230
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f132506%2fawful-performance-on-joined-queries-while-great-separately%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Divide et impera: using temporary tables (not table variables, they're awful performance-wise) is not necessarily a bad thing. Why do you need a single statement? Also, please post the actual execution plans.
– spaghettidba
Mar 17 '16 at 11:48
Editted for actual query plans added. Yeah, I agree that temporary tables are fine depending on circumstances. The thing is that this query code needs to run on an application program, so creating all the temp tables and so on adds more code lines and I'm not sure they can be used anyway. If there is no way of achieving a good performance with a single statement then I will encapsulate this into a stored proceudre, but I want to know possible alternatives first.
– Hauri
Mar 17 '16 at 12:04
Thanks for the plans, but we need the XML plan, not a picture of it. Please upload your .sqlplan files somewhere and link them here. It would be perfect if you could upload all the three plans: first query alone, second query alone and first joined to second.
– spaghettidba
Mar 17 '16 at 12:07
I appreciate your interest in helping, but unfortunately I can't post here that, as it contains private data. Besides it's too long.... Sorry.
– Hauri
Mar 17 '16 at 12:43
1
A few things to try - First, if you have more than a couple hundred rows in the temp variables, change them out for temp tables. The latter at least will get stats generated and get you a half way decent estimate. Second, use BETWEEN for the date comparisons instead of the > and <=. Finally, you don't need to CAST the string to DATETIME so long as it is in 'YYYY-MM-DD' format; the engine will do a morning to night comparison for you with just the date
– Steve Mangiameli
Mar 17 '16 at 14:51