Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 55
  • Last Modified:

Another Sql performance query

I am having trouble improving this query

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 vSessionStartEnd VS  ON SM.sessionKey = VS.sessionKey
		AND VS.sessionStart >= '9/25/2010'
	INNER JOIN users_sessionUnit USU  ON SM.sessionMapKey = USU.sessionMapKey
		AND U.userKey = USU.userKey
	INNER JOIN sessionUnit SU  ON USU.sessionUnitKey = SU.sessionUnitKey
	LEFT OUTER JOIN userScore US  ON U.userKey = US.userKey
		AND USU.sessionUnitKey = US.sessionUnitKey
		AND US.userScoreType = 'MDDC'
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,
	SM.sessionMapKey,
	SM.sessionKey,
	SU.sessionUnitKey,
	SU.sessionStart,
	SU.sessionEnd,
	SU.unit,
	US.userScoreKey,
	US.userScoreValue,
	SM.officeStateKey
FROM users U 
	INNER JOIN vVAScoreFlags 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
	LEFT OUTER JOIN userScore US  ON U.userKey = US.userKey
		AND USU.sessionUnitKey = US.sessionUnitKey
		AND US.userScoreType = 'VADC'
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

Open in new window

0
erikTsomik
Asked:
erikTsomik
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
 
erikTsomikSystem Architect, CF programmer Author Commented:
I mean improve the performance. It currently takes 1 minute to run.
0
 
Scott PletcherSenior DBACommented:
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

Open in new window

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
erikTsomikSystem Architect, CF programmer Author Commented:
Unfortunately I ca not combine unions
0
 
PortletPaulfreelancerCommented:
@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)
0
 
PortletPaulfreelancerCommented:
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.
0
 
PortletPaulfreelancerCommented:
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

Open in new window

0
 
PortletPaulfreelancerCommented:
this may be a bad idea and/or be quite slow, but I'd want to know where the major problems are if trying to tune this query

and as I know nothing about the data, just some counting may help focus attention.

so, try this
with
 q1 as (
        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 vSessionStartEnd VS ON SM.sessionKey = VS.sessionKey AND VS.sessionStart >= '9/25/2010'
        INNER JOIN users_sessionUnit USU ON SM.sessionMapKey = USU.sessionMapKey AND U.userKey = USU.userKey
        INNER JOIN sessionUnit SU ON USU.sessionUnitKey = SU.sessionUnitKey
        LEFT OUTER JOIN userScore US ON U.userKey = US.userKey AND USU.sessionUnitKey = US.sessionUnitKey AND US.userScoreType = 'MDDC'
        )
, q2 as (
          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'
          )
, Q3 as (
          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 vVAScoreFlags 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
          LEFT OUTER JOIN userScore US ON U.userKey = US.userKey AND USU.sessionUnitKey = US.sessionUnitKey AND US.userScoreType = 'VADC'
        ), 
q4 as (

        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
        'Q1' as query
      , (select count(*) from q1) as rawcount
      , (select count(*) from (select distinct * from q1) ) as distinctcount
 UNION ALL
SELECT
        'Q2' as query
      , (select count(*) from q2) as rawcount
      , (select count(*) from (select distinct * from q2) ) as distinctcount
 UNION ALL
SELECT
        'Q3' as query
      , (select count(*) from q3) as rawcount
      , (select count(*) from (select distinct * from q3) ) as distinctcount
 UNION ALL
SELECT
        'Q4' as query
      , (select count(*) from q4) as rawcount
      , (select count(*) from (select distinct * from q4) ) as distinctcount

Open in new window


oh, and while I'm here, this is NOT "safe"

     AND SUX.sessionStart >= '9/25/2010'

YYYYMMDD is the "safest" way to handle date literal in SQL Server

     AND SUX.sessionStart >= '20100925'
0
 
erikTsomikSystem Architect, CF programmer Author Commented:
from the query perspective the most time consuming part is the first UNION
0
 
PortletPaulfreelancerCommented:
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
0
 
erikTsomikSystem Architect, CF programmer Author Commented:
THe attached file will become public I do not really want expose it to public
0
 
erikTsomikSystem Architect, CF programmer Author Commented:
What would be an alternative to the ta query
0
 
PortletPaulfreelancerCommented:
to the ta query? what is "ta query" (sorry)
0
 
erikTsomikSystem Architect, CF programmer Author Commented:
sorry I meant to say to that query
0
 
PortletPaulfreelancerCommented:
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.
0
 
erikTsomikSystem Architect, CF programmer Author Commented:
The derived tables would be preferred.  As for cte I think they are slower than derived tables
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now