1

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

enter image description here enter image description here

closed as too broad by Erik Darling, Paul White Aug 26 at 6:27

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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
0

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.