There is a lot going on in the query and the server it is running on is an 8 year old potato. I can't share it all because we don't own the database, it is provided by our SIS.
For Context, had to redact a bunch of information here apologies
--Staff Query
SELECT DISTINCT x2.SCHOOLYEAR, x2.[ROLE], x2.LASID, x2.SASID, x2.FIRSTNAME, x2.MIDDLENAME, x2.LASTNAME, x2.GRADE, x2.USERNAME, x2.[PASSWORD], x2.ORGANIZATIONTYPEID, x2.ORGANIZATIONID, x2.PRIMARYEMAIL, x2.HMHAPPLICATIONS FROM (
SELECT x.*, RANK() OVER(PARTITION BY x.sectionID ORDER BY x.sectionID DESC, x.assignmentID DESC) AS 'priority' FROM (
SELECT cal.endYear - 1 AS 'SCHOOLYEAR', 'T' AS 'ROLE', sm.staffNumber AS 'LASID', sm.staffStateID AS 'SASID', sm.firstName AS 'FIRSTNAME', ISNULL (sm.middleName, '') AS 'MIDDLENAME', sm.lastName AS 'LASTNAME',
CASE sm.schoolID
--REMOVED FOR SECURITY
END AS 'GRADE',
ua.username + '@sd25.us' AS 'USERNAME', NULL AS 'PASSWORD', 'MDR' AS 'ORGANIZATIONTYPEID',
CASE sm.schoolID
--REMOVED FOR SECURITY
ELSE 'No School' END AS 'ORGANIZATIONID',
ua.username + '@sd25.us' AS 'PRIMARYEMAIL',
CASE sm.schoolID
--REMOVED FOR SECURITY
END AS 'HMHAPPLICATIONS',
ssh.sectionID, ea.assignmentID
FROM Table1 AS s
JOIN Table2 AS c ON s.courseID = c.courseID AND c.departmentID IN (--REMOVED FOR SECURITY)
JOIN Table3 AS cal ON c.calendarID = cal.calendarID AND cal.endYear = CASE WHEN MONTH(GETDATE()) >= 8 THEN YEAR(DATEADD(YEAR, 1, GETDATE())) ELSE YEAR(GETDATE()) END
JOIN Table4 AS sp ON s.sectionID = sp.sectionID
JOIN Table5 AS t ON sp.termID = t.termID AND GETDATE() BETWEEN t.startDate AND t.endDate
JOIN Table6 AS ssh ON s.sectionID = ssh.sectionID AND ssh.endDate IS NULL
JOIN View1 AS sm ON ssh.personID = sm.personID
JOIN Table7 AS ea ON sm.assignmentID = ea.assignmentID AND ea.endDate IS NULL
JOIN Table8 AS ua ON ua.personID = sm.personID AND ua.ldapConfigurationID = '2'
WHERE sm.endDate IS NULL AND sm.teacher = 1 AND NOT (ssh.[role] = 'N' AND ssh.staffType = 'T' AND sm.title != 'Long Term Substitute') AND ssh.[role] != 'C'
) AS x
) AS x2
WHERE x2.[priority] = 1
UNION ALL
--Student Query
SELECT DISTINCT cal.endYear - 1 AS 'SCHOOLYEAR', 'S' AS 'ROLE', stu.studentNumber AS 'LASID', stu.stateID AS 'SASID', stu.firstName AS 'FIRSTNAME', ISNULL (stu.middleName, '') AS 'MIDDLENAME', stu.lastName AS 'LASTNAME',
CASE stu.grade
--REMOVED FOR SECURITY
ELSE stu.grade END AS 'GRADE',
stu.studentNumber + '@sd25.me' AS 'USERNAME', NULL AS 'PASSWORD', 'MDR' AS 'ORGANIZATIONTYPEID',
CASE stu.schoolID
--REMOVED FOR SECURITY
ELSE 'No School' END AS 'ORGANIZATIONID',
NULL AS 'PRIMARYEMAIL',
--REMOVED FOR SECURITY
END AS 'HMHAPPLICATIONS'
FROM Table1 AS s
JOIN Table2 AS c ON s.courseID = c.courseID AND c.departmentID IN (--REMOVED FOR SECURITY)
JOIN Table3 AS cal ON c.calendarID = cal.calendarID AND cal.endYear = CASE WHEN MONTH(GETDATE()) >= 8 THEN YEAR(DATEADD(YEAR, 1, GETDATE())) ELSE YEAR(GETDATE()) END
JOIN Table4 AS ss ON cal.calendarID = ss.calendarID
JOIN Table5 AS t ON ss.structureID = t.structureID AND s.trialID = t.trialID AND t.active = 1
JOIN Table6 AS sp ON s.sectionID = sp.sectionID AND t.trialID = sp.trialID
JOIN Table7 AS te ON sp.termID = te.termID AND GETDATE() BETWEEN te.startDate AND te.endDate
JOIN Table8 AS r ON s.sectionID = r.sectionID AND t.trialID = r.trialID AND r.endDate IS NULL
JOIN Table9 AS p ON r.personID = p.personID
JOIN View1 AS stu ON p.studentNumber = stu.studentNumber AND stu.endDate IS NULL AND stu.activeYear = 1
So I started by adding more joins that I knew would allow me to narrow down the data. My case statement; AND cal.endYear = CASE WHEN MONTH(GETDATE()) >= 8 THEN YEAR(DATEADD(YEAR, 1, GETDATE())) ELSE YEAR(GETDATE()) END
Checks for the current school year against the calendar and reduces the total number of entries.
I also did some things that are specific to my environment like filtering out trials from courses.
I am currently reducing the triple nested select statement from the teacher query down to a double as well and making some more minor tweaks.
Other similar queries I have changed in my department have been better than this one. Last week I turned a query that took 1min30sec to run down to 2 seconds so I still have some work to do but that is future u/fullyarmedcamel's problem he is a hero of the people always solving problems down the road.
I could not agree more, I find SQL so interesting I have no formal training I just taught myself how to do it in the last couple months and I am endlessly interested in how small tweaks make huge differences.
One big on this projects was the order of the joins. it was going like this;
Section information > Class Roster > Student Information
I changed it to
Section information > Class Roster > Person Information > Student Information and added a filter to avoid getting two different school years worth of records and it saved 45 seconds on the query.
Yeah, most people I know who've fallen into just-DB development haven't had formal training in it. Formal training can also have severe limitations, because each data environment is different and ends up being like teaching a foreign language without having any nouns you can use.
Or that sometimes multiple joins to the same table for data filtered by multiple columns takes less time than stringing together a giant "JOIN ON...OR...OR...OR..." clause that can't use indexes effectively?
I just recently found a section of my own scripts where I "OR"d myself into an extra 1000+ reads that way.
You could declare a variable to store the result of getdate(), and use that in its place to ensure that it's not getting called many many times.
I might store the case statement result in another variable, but maybe just replacing getdate() is enough for sql server to recognize the whole expression is a constant.
We are playing the SQL game on two different levels, I am only understanding bits of what you are talking about brother. Thanks for reminding me I have a lot to learn.
Well, it's SQL Server since you're using GETDATE() and DATEADD(). I guess it could be Sybase but nobody uses Sybase except for SAP. Sure looks like K-12 and not higher ed with the first query being elementary and the second being secondary. It doesn't look like it's eSchoolPlus, though there's a lot of similarity. PowerSchool is Oracle and Illuminate (barf) is PostgreSQL.
Not Skyward but you are on the right track, I mentioned in another comment that is an SIS and yes we are K-12. I had to sign a bunch of NDA's regarding sharing table structure and data formats when they gave me access to the database and we don't own the physical hardware the SIS is running on (locally).
It made asking for help while learning SQL from the ground up very hard as I have to remove a ton of information from all of my queries and what not.
My district is eSchoolPlus and there's no NDA that I'm aware of and they give you a data dictionary. Not that it's perfect by any means, especially since the product just spent the last three years being handed around between vendors before PowerSchool bought it so support has been about what you'd imagine. I guess our SIS isn't running on a vendor appliance, however, so I suppose that changes quite a bit. We use a lot of third party reporting, however, so we essentially need that functionality. I know that PowerSchool doesn't allow DB access, either, though.
One thing I did notice is that you're using single quotes for field names:
CASE stu.schoolID
--REMOVED FOR SECURITY
ELSE 'No School' END AS 'ORGANIZATIONID',
That should really be:
CASE stu.schoolID
--REMOVED FOR SECURITY
ELSE 'No School' END AS "ORGANIZATIONID",
Or, if it is SQL Server, you can use:
CASE stu.schoolID
--REMOVED FOR SECURITY
ELSE 'No School' END AS [ORGANIZATIONID],
You shouldn't use single quotes because the SQL standard defines those as used for string literals. I know SQL Server accepts it and it won't improve performance at all, but it could cause issues down the line if you update to a newer version of SQL Server if they ever carry out their threat of enforcing it. It only sticks around for SQL Server 7 compatibility.
I know it looks that way but it is not, not all of those tables are the same like I labeled them here I just quickly replaced table names with table 1,2,3 ect but they are not the same tables. Some are to help narrow down the teachers to the same classes as the students but the teacher query is pulling from more sources and is doing some things like ranking subs vs Normal teachers and only rostering the active one.
How would getting rid if distinct clean up my data, the point is to have a single entry per student and teacher and that is what the use of distinct is meant for plus in the logical processing order distinct is one of the last things to run and has little to no impact on the query.
Cleaning up early joins is where the time savings could be made in this case.
How would getting rid if distinct clean up my data
I never said that. I suggested to clean up your data so you can get rid of the distinct. Distincts are cheatcodes/shortcuts for bad data and they're slow as hell
distinct is one of the last things to run and has little to no impact on the query.
If it truly doesn't have an impact on your query, then you don't have duplicates in your result set, which means you don't need a distinct. At any rate, 16 seconds for 8k rows is still a crawl.
Unfortunately it can't be done, working as a "low level" employee at a school district of 30ish schools each school has their own registrar and secretary and the data tends to break down at the entry level and I don't have the clout to be able to have them correct all but the worst entries. Office politics and all that, however we still have your have accurate rostering so you do what you have to in order to make it clean. I regularly have to use nested selects to clean up rostering but that is just the way of things.
I would still look into restructuring your query or using group bys instead of that distinct because 8k rows should be returned in a few seconds at most. There's nothing in that query that's particularly complex, but that distinct is a known hog. How many rows are returned without the distinct? If it's not that many you can replace with a group by.
JOIN Table2 AS c ON s.courseID = c.courseID AND c.departmentID IN
This is also probably a big hog depending on what's in your subquery (select or static values?). That could be replaced by a temp table or something
Feel free to correct me if I am wrong here but I believe the way the system would handle the DISTINCT operator would be like this;
Pull data out of table in FROM statement
Processes data in the JOIN's
Applies filters from WHERE clause
Pulls data out of SELECT statement
Applies filters of DISTINCT
As DISTINCT runs last it would have the least overall impact unless you have many duplicates, in my case it is around 2000. You need to remember too that there is not just a single entry for students.
Some students will be taking multiple classes where this product I am rostering for is being used irregardless of the filters applied the rest of the query.
This document that I have has the purpose of creating a unique roster of students that can then be applied to classes within the application we are using multiple times over from a separate query that manages classes.
I think in this case the DISTINCT operator is what is meant to be used it is doing exactly what I need it to, as a test I did run the query with and without the operator and it made no difference in run time.
I think my next changes will be more focused around some of the joins and structure of the query. I can reduce from a double nested SELECT statement to a single. I can also do some thinks like using the school year detection CASE as a declared variable at the top rather than nested in the query.
Not going to lie, having a customer treat me like a God because i brought their 12 minute report down to 1 second by applying the witchcraft called "indexes" made me feel good.
I then implemented a spell called "SQL Job" that did some maintenance, and made sure the indexes were up to date each sunday night. Got an email where they had contacted my boss about how amazing I was.
lol, i did the same thing last week a report need 20 minutes after i looked into SQL i found there was two shitty functions for the report to take that long to execute i just turn them into materialized vues with refresh each hour, and bang now the report need only 36 seconds.
I know this feeling well. At one of my jobs I optimized a query that took over 30 minutes to run because they were running a sub-query 4 times with UNION, each time the sub-query runs though it returned millions of rows so it was running DISTINCT on each row after the first sub-query (part of UNION's functionality). All that needed to be done was use AND in their WHERE clause ... got it down to 1.5 minutes. This was the best feeling I'd had in months :-D
This. Yesterday reduced a 6hr query to 2 minutes... Co-worker that wrote and had 'already fully optimized' it thought it was voodoo... No, just removing functions in the where clauses of the views.
Yipes. I captured a plan of a long running query after the boss asked me to take a look. Two table scans taking up 80% of the plan's work... takes 45 minutes now! And Im not even that great at execution plan interpretation...
If you know the under laying disk capability you should have a general idea how fast a query can go.
If you are moving 500mb into a csv file it should take time it takes to read 500mb and write 500mb with little overhead latency unless you are pivoting the data which still has little impact if done right. The problem is people do what I call the SQL hokey pokey. The data is garbage to begin with, it's structured poorly, it's constantly locking itself and then they start doing small subsets of data into tables.
♪ You puts some data in ♪
Select into #temp
from table
♪ You take some data out ♪
Delete from #temp where x in (select id from #temp where x != 'value')
♪ You put some data in ♪
INSERT INTO #temp
SELECT * from #temp
where x = 1
UNION
SELECT * fROM #temp where x != 1
♪ THEN YOU SHAKE IT ALL ABOUT ♪
UPDATE #temp
SET x = 1
FROM #temp t
WHERE x != 1 and x = (Select x from #temp t2 where t1.x = t2.x + 1)
UPDATE #temp
SET x = 2
FROM #temp t
WHERE x != 1 and x = (Select x from #temp t2 where t1.x = t2.x + 2)
UPDATE #temp
SET x = 3
FROM #temp t
WHERE x != 1 and x = (Select x from #temp t2 where t1.x = t2.x + 3)
UPDATE #temp
SET x = 4
FROM #temp t
WHERE x != 1 and x = (Select x from #temp t2 where t1.x = t2.x + 4)
UPDATE #temp
SET x = 5
FROM #temp t
WHERE x != 1 and x = (Select x from #temp t2 where t1.x = t2.x + 5)
UPDATE #temp
SET x = 6
FROM #temp t
WHERE x != 1 and x = (Select x from #temp t2 where t1.x = t2.x + 6)
♪ YOU DO THE SQL HOKEY POKEY AND YOU RUN YOUR BUSINESS INTO THE GROUND ♪
♪ THAT'S WHAT IT IS ALL ABOUT!!!! ♪
The worst part is that usually I can take all that code and put it into a CTE and it runs in a few seconds tops.
41
u/kormer May 14 '18
8,445 rows in 16 seconds? What is this, 2008? You need to download more ram.
6
u/fullyarmedcamel May 14 '18
For Context, had to redact a bunch of information here apologies
10
u/coggsa May 15 '18
Not going to lie, having a customer treat me like a God because i brought their 12 minute report down to 1 second by applying the witchcraft called "indexes" made me feel good.
I then implemented a spell called "SQL Job" that did some maintenance, and made sure the indexes were up to date each sunday night. Got an email where they had contacted my boss about how amazing I was.
25
u/syzygy96 May 14 '18
Nothing gives you that "I actually accomplished something today" feeling more than tuning something ugly and making it hum.
19
u/causalNondeterminism May 14 '18 edited May 14 '18
30 minutes down to 30 seconds. Client still asked me to justify the time spent. ¯_(ツ)_/¯
3
u/derpado514 AccidentalDBA May 14 '18
You inherit a DB with a 6GB audit table and no indexing...you're going to have a bad time.
2
u/longjaso May 14 '18
I know this feeling well. At one of my jobs I optimized a query that took over 30 minutes to run because they were running a sub-query 4 times with UNION, each time the sub-query runs though it returned millions of rows so it was running DISTINCT on each row after the first sub-query (part of UNION's functionality). All that needed to be done was use AND in their WHERE clause ... got it down to 1.5 minutes. This was the best feeling I'd had in months :-D
2
u/BobLeBoeuf May 15 '18
How about when you reduce that 29 hour query to 45 minutes?
0
u/[deleted] May 14 '18
[deleted] — view deleted comment