Another Sql performance query

Whip cream would improve it.  Or whiskey.  Or chocolate sauce.   Or The Pretty Reckless tickets.   A three-car garage would be nice too.

Define 'improve', as it's difficult for us to interpret a big honkin' pile of T-SQL and infer what is meant by improve.

I mean improve the performance. It currently takes 1 minute to run.

I think you can combine the 1st and 3rd queries into a single one.  It's likely you can do some combining on 2 and 4 as well; hopefully you can use the code below as a guide to that.

SELECT U.userKey,
	U.firstname,
	U.lastname,
	SM.sessionMapKey,
	SM.sessionKey,
	SU.sessionUnitKey,
	SU.sessionStart,
	SU.sessionEnd,
	SU.unit,
	US.userScoreKey,
	US.userScoreValue,
	SM.officeStateKey
FROM users U 
	INNER JOIN vMDScoreFlags V  ON U.userKey = V.userKey
		AND V.InClassIn = 1
	INNER JOIN sessionMap SM  ON V.DCKey = SM.sessionKey
		AND U.userKey = SM.userKey
		AND SM.type = 'DC'
	INNER JOIN users_sessionUnit USU  ON SM.sessionMapKey = USU.sessionMapKey
		AND U.userKey = USU.userKey
	INNER JOIN sessionUnit SU  ON USU.sessionUnitKey = SU.sessionUnitKey
	CROSS JOIN (
	    SELECT 'MDDC' AS userScoreTypeToMatch UNION ALL
	    SELECT 'VADC'
	) AS userScoreTypesToMatch
	LEFT OUTER JOIN userScore US  ON U.userKey = US.userKey
		AND USU.sessionUnitKey = US.sessionUnitKey
		AND US.userScoreType = userScoreTypeToMatch
WHERE
    userScoreTypeToMatch = 'VADC' OR
    EXISTS(
        SELECT 1
        FROM vSessionStartEnd VS
        WHERE 
            SM.sessionKey = VS.sessionKey
            AND VS.sessionStart >= '20100925'
	)

UNION

SELECT U.userKey,
	U.firstname,
	U.lastname,
	SMX.sessionMapKey,
	SU.sessionKey,
	SU.sessionUnitKey,
	SU.sessionStart,
	SU.sessionEnd,
	NULL AS Unit,  
	US.userScoreKey,
	US.userScoreValue,
	SM.officeStateKey
FROM users U 
	INNER JOIN vMDScoreFlags V  ON U.userKey = V.userKey
		AND V.BTWIn = 1
	INNER JOIN sessionMap SMX  ON V.BWKey = SMX.sessionKey
		AND U.userKey = SMX.userKey
		AND SMX.type IN ('BW','SBW')
	INNER JOIN sessionUnit SUX  ON SMX.sessionKey = SUX.sessionKey
		AND SUX.sessionStart >= '9/25/2010'
	INNER JOIN sessionMap SM  ON U.userKey = SM.userKey
		AND SM.type IN ('BW','SBW')
	INNER JOIN sessionUnit SU  ON SM.sessionKey = SU.sessionKey
		AND SMX.sessionMapKey <= SM.sessionMapKey
	LEFT OUTER JOIN userScore US  ON U.userKey = US.userKey
		AND SUX.sessionUnitKey = US.sessionUnitKey
		AND US.userScoreType = 'MDBTWSU'
UNION

SELECT U.userKey,
	U.firstname,
	U.lastname,
	SMX.sessionMapKey,
	SU.sessionKey,
	SU.sessionUnitKey,
	SU.sessionStart,
	SU.sessionEnd,
	NULL AS Unit,   
	NULL AS userScoreKey,  
	(
		SELECT CAST(SUM(CAST(USX.studentPerfKey AS INT)) AS VARCHAR)
		FROM sessionUnit SUX 
			INNER JOIN userScore USX  ON SUX.sessionUnitKey = USX.sessionUnitKey
		WHERE USX.userScoreType = 'VABTW'
			AND SUX.sessionKey = SM.sessionKey
			AND USX.userKey = SM.userKey
    ) + ' / ' +
    (
		SELECT CAST(COUNT(USX.studentPerfKey) * 4 AS VARCHAR)
		FROM sessionUnit SUX 
			INNER JOIN userScore USX  ON SUX.sessionUnitKey = USX.sessionUnitKey
		WHERE USX.userScoreType = 'VABTW'
			AND SUX.sessionKey = SM.sessionKey
			AND USX.userKey = SM.userKey
			AND USX.studentPerfKey IS NOT NULL
    ) AS userScoreValue,
	SM.officeStateKey
FROM users U 
	INNER JOIN vVAScoreFlags V  ON U.userKey = V.userKey
		AND V.BTWIn = 1
	INNER JOIN sessionMap SMX  ON V.BWKey = SMX.sessionKey
		AND U.userKey = SMX.userKey
		AND SMX.type IN ('BW','SBW')
	INNER JOIN sessionUnit SUX  ON SMX.sessionKey = SUX.sessionKey
	INNER JOIN sessionMap SM  ON U.userKey = SM.userKey
		AND SM.type IN ('BW','SBW')
	INNER JOIN sessionUnit SU  ON SM.sessionKey = SU.sessionKey
		AND SMX.sessionMapKey <= SM.sessionMapKey

Select allOpen in new window

Unfortunately I ca not combine unions

@Scott,
I placed your proposed query into a code block so I can reference by line numbers (and not have to scroll so much).

@Erik
Before dismissing the idea of combining parts of the query, it should be considered

Scott has proposed a technique you might not have used before, in particular I point you to lines 22 to 25
      CROSS JOIN (
          SELECT 'MDDC' AS userScoreTypeToMatch UNION ALL
          SELECT 'VADC'
      ) AS userScoreTypesToMatch

This actually deliberately produces 2 rows (a "pair") for every previous single row. On the first of those rows is  'MDDC' and on the second 'VADC'

Then after those line "pairs" exist the subsequent left join and where clause (lines 26-37) allows the logic of former query 1 and former query 3 to be merged together which should be a substantial improvement to performance.

---------------------
Try also to eradicate/improve those "correlated subqueries"
(always something to re-consider if looking for better performance)

Mmmm, I just noticed
Query 1 uses  INNER JOIN vMDScoreFlags V
Query 3 uses  INNER JOIN vVAScoreFlags V

so perhaps queries 1 & 3 can't be merged as proposed earlier.

I think this removes the correlated subqueries in query 4

SELECT
      U.userKey
    , U.firstname
    , U.lastname
    , SMX.sessionMapKey
    , SU.sessionKey
    , SU.sessionUnitKey
    , SU.sessionStart
    , SU.sessionEnd
    , NULL AS UNIT
    , NULL AS USERSCOREKEY
    , USC.USUM + ' / ' + USC.UCOUNT AS USERSCOREVALUE
    , SM.officeStateKey
FROM users U
INNER JOIN vVAScoreFlags V ON U.userKey = V.userKey AND V.BTWIn = 1
INNER JOIN sessionMap SMX ON V.BWKey = SMX.sessionKey AND U.userKey = SMX.userKey AND SMX.type IN ('BW', 'SBW')
INNER JOIN sessionUnit SUX ON SMX.sessionKey = SUX.sessionKey
INNER JOIN sessionMap SM ON U.userKey = SM.userKey AND SM.type IN ('BW', 'SBW')
INNER JOIN sessionUnit SU ON SM.sessionKey = SU.sessionKey AND SMX.sessionMapKey <= SM.sessionMapKey
LEFT JOIN (
            SELECT
                  SUX.sessionKey
                , USX.userKey
                , CAST(SUM(CAST(USX.studentPerfKey AS int)) as varchar) AS USUM
                , CAST(COUNT(USX.studentPerfKey) * 4 as varchar)  AS UCOUNT
            FROM sessionUnit SUX
            INNER JOIN userScore USX ON SUX.sessionUnitKey = USX.sessionUnitKey
            WHERE USX.userScoreType = 'VABTW'
                  AND USX.studentPerfKey IS NOT NULL
            GROUP BY
                  SUX.sessionKey
                , USX.userKey
      ) USC ON SM.sessionKey = USC.sessionKey AND SM.userKey = USC.userKey

Select allOpen in new window

from the query perspective the most time consuming part is the first UNION

I should have said this a while back. The best method of analysis available to us (and you) is the explain plan.

Can you get the explain plan (as a .sqlplan file) and attach it to the question please.

https://msdn.microsoft.com/en-us/library/ms190646.aspx

THe attached file will become public I do not really want expose it to public

What would be an alternative to the ta query

to the ta query? what is "ta query" (sorry)

sorry I meant to say to that query

an alternative query?

without a great deal more information about the tables/data & requirements I don't think I can deduce an alternative by just looking at the current sql

there are techniques to explore, such as

look hard at each of the 4 queries by themselves. Then look for anything in common or that could be shared.

can you use "derived tables" instead of the tables that you join?
can you use CTEs to avoid repeating the same subquery or the same join? (can some CTEs be the derived tables to join to?)

but I can't do all of this by just reading one query,
& in my view such activity takes some trial and error (plus access to the dbms)

most, and hardest of of all for me to know, I would need to know what is right or wrong when testing any alternatives.

In short; I don't think I can answer your last question.

The derived tables would be preferred.  As for cte I think they are slower than derived tables