Bumped by Community user
3 deleted 28 characters in body
source | link
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 [RWELogger][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 [RWELogger][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
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 [RWELogger][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 [RWELogger][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
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 [RWELogger].[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 [RWELogger].[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
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 [RWELogger].[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 [RWELogger].[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
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
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
2 Actual query plans added
source | link

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...

enter image description here enter image description here

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...

enter image description here enter image description here

1
source | link

Awful performance on joined queries while great separately

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 [RWELogger].[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 [RWELogger].[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 [RWELogger].[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 [RWELogger].[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!!