Solved

Another Sql performance query

Posted on 2015-02-19
16
44 Views
Last Modified: 2015-05-21
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
Comment
Question by:erikTsomik
16 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40619738
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
 
LVL 19

Author Comment

by:erikTsomik
ID: 40619810
I mean improve the performance. It currently takes 1 minute to run.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40619975
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
 
LVL 19

Author Comment

by:erikTsomik
ID: 40620063
Unfortunately I ca not combine unions
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40620286
@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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40620299
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40620327
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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40620331
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 19

Author Comment

by:erikTsomik
ID: 40620431
from the query perspective the most time consuming part is the first UNION
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40620447
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
 
LVL 19

Author Comment

by:erikTsomik
ID: 40620485
THe attached file will become public I do not really want expose it to public
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40622487
What would be an alternative to the ta query
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40622490
to the ta query? what is "ta query" (sorry)
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40622531
sorry I meant to say to that query
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40622748
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
 
LVL 19

Author Comment

by:erikTsomik
ID: 40623003
The derived tables would be preferred.  As for cte I think they are slower than derived tables
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now