locked
SQL query question RRS feed

  • Question

  • Hello,

    I'm able to received expected output from the below query,

    IF OBJECT_ID('tempdb..#TEMP1') IS NOT NULL
    	DROP TABLE #TEMP1
    
    IF OBJECT_ID('tempdb..#TEMP2') IS NOT NULL
    	DROP TABLE #TEMP2
    
    CREATE TABLE #TEMP1
    (
    	[ID]		INT,
    	[MCK]		INT,
    	[CLS]		CHAR(1),
    	[EFF]		DATE
    )
    
    CREATE TABLE #TEMP2
    (
    	[TID]		INT,
    	[Date]		DATE,
    	[MonDate]	DATE
    )
    
    INSERT INTO #TEMP1 VALUES ( 1, 123, 'M', '2012-07-25' )
    INSERT INTO #TEMP1 VALUES ( 2, 456, 'D', '2015-04-01' )
    INSERT INTO #TEMP1 VALUES ( 3, 456, 'V', '2015-04-01' )
    INSERT INTO #TEMP1 VALUES ( 4, 789, 'M', '2016-10-15' )
    INSERT INTO #TEMP1 VALUES ( 5, 789, 'V', '2016-10-15' )
    
    INSERT INTO #TEMP2 VALUES ( 12, '2012-01-07', '2012-01-31' )
    INSERT INTO #TEMP2 VALUES ( 23, '2012-08-31', '2012-08-31' )
    INSERT INTO #TEMP2 VALUES ( 32, '2015-03-31', '2015-03-31' )
    INSERT INTO #TEMP2 VALUES ( 39, '2016-04-07', '2016-04-30' )
    INSERT INTO #TEMP2 VALUES ( 42, '2016-04-30', '2016-04-30' )
    INSERT INTO #TEMP2 VALUES ( 45, '2017-11-30', '2017-11-30' )
    
    SELECT	t.[MCK], t.[TID],
    		CASE 
    			WHEN t.DT = 1 AND t.MD = 0 AND t.VI = 0 THEN 1
    			WHEN t.DT = 0 AND t.MD = 1 AND t.VI = 0 THEN 2
    			WHEN t.DT = 0 AND t.MD = 0 AND t.VI = 1 THEN 3
    			WHEN t.DT = 1 AND t.MD = 1 AND t.VI = 0 THEN 4
    			WHEN t.DT = 1 AND t.MD = 1 AND t.VI = 1 THEN 5
    			WHEN t.DT = 1 AND t.MD = 0 AND t.VI = 1 THEN 6
    			WHEN t.DT = 0 AND t.MD = 1 AND t.VI = 1 THEN 7
    		END AS [COMBO]
    FROM
    	(
    		SELECT	a.[MCK], b.[TID],-- a.[CLS],
    				MAX(CASE WHEN a.[CLS] = 'D' THEN 1 ELSE 0 END) AS DT,
    				MAX(CASE WHEN a.[CLS] = 'M' THEN 1 ELSE 0 END) AS MD,
    				MAX(CASE WHEN a.[CLS] = 'V' THEN 1 ELSE 0 END) AS VI
    		FROM	#TEMP1 a
    				INNER JOIN #TEMP2 b
    					ON a.[EFF] <= b.[Date]
    		WHERE	b.[Date] = b.[MonDate]
    		GROUP BY	a.[MCK], b.[TID]
    		--ORDER BY	a.[MCK]--, a.[CLS]
    	) t
    ORDER BY	t.[MCK]
    
    IF OBJECT_ID('tempdb..#TEMP1') IS NOT NULL
    	DROP TABLE #TEMP1
    
    IF OBJECT_ID('tempdb..#TEMP2') IS NOT NULL
    	DROP TABLE #TEMP2

    But above query is taking a lot time to execute.

    Is there any other way to write narrow down this query into single query instead of sub-query using new windows SQL functions or so.

    Help needed.

    Thank You

    Regards,

    gk03


    Please do let us know your feedback. Thank You - KG, MCTS

    Thursday, September 7, 2017 4:34 PM

Answers

  • Hi gk1393,

    How about the following query?

    ;WITH CTE AS
    (
    SELECT	a.[MCK], 
    		b.[TID],-- a.[CLS],
    		MAX(CASE WHEN a.[CLS] = 'D' THEN 1 ELSE 0 END) AS DT,
    		MAX(CASE WHEN a.[CLS] = 'M' THEN 1 ELSE 0 END) AS MD,
    		MAX(CASE WHEN a.[CLS] = 'V' THEN 1 ELSE 0 END) AS VI
    		FROM	#TEMP1 a
    		--INNER JOIN #TEMP2 b ON a.[EFF] <= b.[Date]
    		--WHERE	b.[Date] = b.[MonDate]
    		CROSS APPLY (SELECT [TID] FROM #TEMP2 WHERE [Date]>= a.[EFF] and [Date]=[MonDate]) b
    		GROUP BY	a.[MCK], b.[TID]
    		--ORDER BY	a.[MCK]--, a.[CLS]
    )
    SELECT	t.[MCK], 
            t.[TID],
    		CASE WHEN t.DT = 1 AND t.MD = 0 AND t.VI = 0 THEN 1
    			 WHEN t.DT = 0 AND t.MD = 1 AND t.VI = 0 THEN 2
    			 WHEN t.DT = 0 AND t.MD = 0 AND t.VI = 1 THEN 3
    			WHEN t.DT = 1 AND t.MD = 1 AND t.VI = 0 THEN 4
    			WHEN t.DT = 1 AND t.MD = 1 AND t.VI = 1 THEN 5
    			WHEN t.DT = 1 AND t.MD = 0 AND t.VI = 1 THEN 6
    			WHEN t.DT = 0 AND t.MD = 1 AND t.VI = 1 THEN 7
    		END AS [COMBO]
    FROM CTE t
    	--(
    	--	SELECT	a.[MCK], 
    	--	        b.[TID],-- a.[CLS],
    	--			MAX(CASE WHEN a.[CLS] = 'D' THEN 1 ELSE 0 END) AS DT,
    	--			MAX(CASE WHEN a.[CLS] = 'M' THEN 1 ELSE 0 END) AS MD,
    	--			MAX(CASE WHEN a.[CLS] = 'V' THEN 1 ELSE 0 END) AS VI
    	--	FROM	#TEMP1 a
    	--	INNER JOIN #TEMP2 b ON a.[EFF] <= b.[Date]
    	--	WHERE	b.[Date] = b.[MonDate]
    	--	GROUP BY	a.[MCK], b.[TID]
    	--	--ORDER BY	a.[MCK]--, a.[CLS]
    	--) t
    ORDER BY	t.[MCK]

    Besides, you'd better provide the exact execution plan, so that we could know the exact effect of the query.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by gk1393 Friday, September 8, 2017 7:59 PM
    Friday, September 8, 2017 7:43 AM

All replies

  • Thursday, September 7, 2017 5:02 PM
  • Can you please tell us more about it...

    It will be helpful for us if you can send execution plan too.


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    Thursday, September 7, 2017 5:06 PM
  • Just below query brings 158K million counts with 1 min of execution,

    SELECT	COUNT(*)--158,000,000
    FROM	#TEMP1 a
    		INNER JOIN #TEMP2 b ON a.[EFF] <= b.[Date]
    WHERE	b.[Date] = b.[MonDate]

    Thanks

    Regards,

    gk03


    Please do let us know your feedback. Thank You - KG, MCTS

    Thursday, September 7, 2017 5:18 PM
  • I was trying to see if we can replace CASE/MAX statement and can drive my initial query with more robust execution timings since CASE/MAX statement are giving me results set with 2 hours of total time.

    Please do let us know your feedback. Thank You - KG, MCTS

    Thursday, September 7, 2017 5:21 PM
  • Thursday, September 7, 2017 5:38 PM
  • Business problem above query is taken 1-2 hours with the total execution

    Please do let us know your feedback. Thank You - KG, MCTS

    Thursday, September 7, 2017 5:48 PM
  • Use regular tables instead of temp tables and add primary key and appropriate index to each table.
    Thursday, September 7, 2017 5:57 PM
  • They are regular physical tables and NOT temp table with column store indexes on those table.

    I was just throwing an example in this forum with the temp table example.


    Please do let us know your feedback. Thank You - KG, MCTS

    Thursday, September 7, 2017 6:18 PM
  • Check your execution plan to find out the query cost to see where it can be improved.
    Thursday, September 7, 2017 6:57 PM
  • Up to my knowledge execution plan looks good but still query is running too slow to pull data from physical tables.

    Please do let us know your feedback. Thank You - KG, MCTS

    Thursday, September 7, 2017 8:12 PM
  • Did you try to move the statement SELECT... FROM... WHERE... GROUP BY... out of the FROM statement and put the result set into a temporary table? 

    A Fan of SSIS, SSRS and SSAS

    Thursday, September 7, 2017 9:18 PM
  • Yes, but still it is not helping out.

    Please do let us know your feedback. Thank You - KG, MCTS

    Thursday, September 7, 2017 9:44 PM
  • Hi gk1393,

    How about the following query?

    ;WITH CTE AS
    (
    SELECT	a.[MCK], 
    		b.[TID],-- a.[CLS],
    		MAX(CASE WHEN a.[CLS] = 'D' THEN 1 ELSE 0 END) AS DT,
    		MAX(CASE WHEN a.[CLS] = 'M' THEN 1 ELSE 0 END) AS MD,
    		MAX(CASE WHEN a.[CLS] = 'V' THEN 1 ELSE 0 END) AS VI
    		FROM	#TEMP1 a
    		--INNER JOIN #TEMP2 b ON a.[EFF] <= b.[Date]
    		--WHERE	b.[Date] = b.[MonDate]
    		CROSS APPLY (SELECT [TID] FROM #TEMP2 WHERE [Date]>= a.[EFF] and [Date]=[MonDate]) b
    		GROUP BY	a.[MCK], b.[TID]
    		--ORDER BY	a.[MCK]--, a.[CLS]
    )
    SELECT	t.[MCK], 
            t.[TID],
    		CASE WHEN t.DT = 1 AND t.MD = 0 AND t.VI = 0 THEN 1
    			 WHEN t.DT = 0 AND t.MD = 1 AND t.VI = 0 THEN 2
    			 WHEN t.DT = 0 AND t.MD = 0 AND t.VI = 1 THEN 3
    			WHEN t.DT = 1 AND t.MD = 1 AND t.VI = 0 THEN 4
    			WHEN t.DT = 1 AND t.MD = 1 AND t.VI = 1 THEN 5
    			WHEN t.DT = 1 AND t.MD = 0 AND t.VI = 1 THEN 6
    			WHEN t.DT = 0 AND t.MD = 1 AND t.VI = 1 THEN 7
    		END AS [COMBO]
    FROM CTE t
    	--(
    	--	SELECT	a.[MCK], 
    	--	        b.[TID],-- a.[CLS],
    	--			MAX(CASE WHEN a.[CLS] = 'D' THEN 1 ELSE 0 END) AS DT,
    	--			MAX(CASE WHEN a.[CLS] = 'M' THEN 1 ELSE 0 END) AS MD,
    	--			MAX(CASE WHEN a.[CLS] = 'V' THEN 1 ELSE 0 END) AS VI
    	--	FROM	#TEMP1 a
    	--	INNER JOIN #TEMP2 b ON a.[EFF] <= b.[Date]
    	--	WHERE	b.[Date] = b.[MonDate]
    	--	GROUP BY	a.[MCK], b.[TID]
    	--	--ORDER BY	a.[MCK]--, a.[CLS]
    	--) t
    ORDER BY	t.[MCK]

    Besides, you'd better provide the exact execution plan, so that we could know the exact effect of the query.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by gk1393 Friday, September 8, 2017 7:59 PM
    Friday, September 8, 2017 7:43 AM