August 31, 2021 at 4:02 pm
Hello,
I am new to sql and learning my way around writing views for our company. Any tips and tricks to make this query a little faster or streamlined?
SELECT TOP (100) PERCENT ord.orderid, ord.lt_orderid AS LTOrderID, ord.datetimecreated, ord.BookedByDT AS DateTimeBooked, us.firstname + ' ' + us.lastname AS BookedByStaffer, ord.VoidDT AS DateTimeVoided, ord.CancelledByWho,
ord.voidreason AS CancellationReason, ord.voidnote AS CancellationNote, ord.status, ot.ordertype, ord.orderCert AS OrderCertification, ord.specialty, cl.recordid AS ClientID, cl.state AS ClientState, cl.clientname, ord.filledby,
tre.regionname AS HCPRegion, ord.jobdatestart, ord.shiftstarttime, ord.shiftendtime, DATEDIFF(MINUTE, ord.shiftstarttime, ord.shiftendtime) / 60 AS HoursGiven, ISNULL(ltot.OrderTypeLT, 'Per Diem') AS LTOrderType,
uss.firstname + ' ' + uss.lastname AS StaffingSpecialist, sc.Week, sc.Month, sc.Quarter, sc.Year
FROM dbo.orders AS ord LEFT OUTER JOIN
dbo.profile_client AS cl ON ord.customerid = cl.recordid LEFT OUTER JOIN
dbo.order_type AS ot ON ord.ordertype = ot.ordertypeID LEFT OUTER JOIN
dbo.profile_temp AS hcp ON ord.filledby = hcp.recordid LEFT OUTER JOIN
dbo.users AS us ON ord.BookedByUserID = us.userid LEFT OUTER JOIN
dbo.regions AS tre ON hcp.homeregion = tre.regionid LEFT OUTER JOIN
dbo.lt_order AS lto ON ord.lt_orderid = lto.lt_orderid LEFT OUTER JOIN
dbo.order_type_LT AS ltot ON lto.OrderTypeLTID = ltot.OrderTypeLTID LEFT OUTER JOIN
dbo.users AS uss ON cl.staffingspecialist = uss.userid LEFT OUTER JOIN
dbo.[4-4-5 Schedule] AS sc ON ord.jobdatestart >= sc.[Start Date] AND ord.jobdatestart <= sc.[End Date]
WHERE (ord.jobdatestart > '6/1/2021')
August 31, 2021 at 4:40 pm
Nothing useful can be determined by just posting a query.
Read the 2 "How to Post" links at the bottom of Jeff Moden's post.
August 31, 2021 at 5:43 pm
I agree... there isn't much to go on.
Having an execution plan and DDL would definitely help us help you. Without that, it is impossible to know what would help. Adding indexes may help, but if you already have covering indexes, that advice may be pointless. Plus adding indexes may help the performance of this query while hurting the performance of others.
We have no idea of how data relates to each other or if any of those joins MAY be able to be reduced. That is a lot of JOINs. If the date in the WHERE clause is static, you may get a performance boost by turning this into an indexed view, but those can come with problems too.
Now, my first step to tuning the query would be to make it easier to read. I ran it through one and go this result:
SELECTTOP (100) PERCENT
[ord].[orderid]
, [ord].[lt_orderid] AS [LTOrderID]
, [ord].[datetimecreated]
, [ord].[BookedByDT] AS [DateTimeBooked]
, [us].[firstname] + ' ' + [us].[lastname] AS [BookedByStaffer]
, [ord].[VoidDT] AS [DateTimeVoided]
, [ord].[CancelledByWho]
, [ord].[voidreason] AS [CancellationReason]
, [ord].[voidnote] AS [CancellationNote]
, [ord].[status]
, [ot].[ordertype]
, [ord].[orderCert] AS [OrderCertification]
, [ord].[specialty]
, [cl].[recordid] AS [ClientID]
, [cl].[state] AS [ClientState]
, [cl].[clientname]
, [ord].[filledby]
, [tre].[regionname] AS [HCPRegion]
, [ord].[jobdatestart]
, [ord].[shiftstarttime]
, [ord].[shiftendtime]
, DATEDIFF( MINUTE
, [ord].[shiftstarttime]
, [ord].[shiftendtime]
) / 60 AS [HoursGiven]
, ISNULL([ltot].[OrderTypeLT]
, 'Per Diem'
) AS [LTOrderType]
, [uss].[firstname] + ' ' + [uss].[lastname] AS [StaffingSpecialist]
, [sc].[Week]
, [sc].[Month]
, [sc].[Quarter]
, [sc].[Year]
FROM[dbo].[orders] AS [ord]
LEFT OUTER JOIN[dbo].[profile_client] AS [cl]
ON [ord].[customerid] = [cl].[recordid]
LEFT OUTER JOIN[dbo].[order_type] AS [ot]
ON [ord].[ordertype] = [ot].[ordertypeID]
LEFT OUTER JOIN[dbo].[profile_temp] AS [hcp]
ON [ord].[filledby] = [hcp].[recordid]
LEFT OUTER JOIN[dbo].[users] AS [us]
ON [ord].[BookedByUserID] = [us].[userid]
LEFT OUTER JOIN[dbo].[regions] AS [tre]
ON [hcp].[homeregion] = [tre].[regionid]
LEFT OUTER JOIN[dbo].[lt_order] AS [lto]
ON [ord].[lt_orderid] = [lto].[lt_orderid]
LEFT OUTER JOIN[dbo].[order_type_LT] AS [ltot]
ON [lto].[OrderTypeLTID] = [ltot].[OrderTypeLTID]
LEFT OUTER JOIN[dbo].[users] AS [uss]
ON [cl].[staffingspecialist] = [uss].[userid]
LEFT OUTER JOIN[dbo].[4-4-5 Schedule] AS [sc]
ON [ord].[jobdatestart] >= [sc].[Start Date]
AND [ord].[jobdatestart] <= [sc].[End Date]
WHERE([ord].[jobdatestart] > '6/1/2021');
Exact same code, but formatted and to me, it is much easier to read.
One thing I probably would change is drop the "TOP (100) PERCENT " as that isn't doing anything helpful, but it won't help performance.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
September 1, 2021 at 1:06 pm
Further to what Brian says, that TOP might even hurt performance. So, yeah, ditch it. It's not adding functionality for you.
The only thing I can spot right now without an execution plan is that one join to [4-4-5 Schedule] table. The OR clause implied by the greater than or equal to and less than or equal to can lead to poor choices by the optimizer. Sometimes. Maybe. It depends. Simple AND clauses usually help the optimizer make better choices.
I have one question though. What kind of percentage of the data in the table will be '> '6/1/2020'? Two or three rows? Or tens of thousands? When you get into situations where you're moving simply huge amounts of data, performance tuning really comes down to hardware tuning more than query tuning. There's only so much query tuning you can do to enhance table scans.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 1, 2021 at 3:39 pm
How is the orders table clustered? I'm guessing by orderid.
But, for this query, since jobdatestart is the key restrictor on the number of rows, that would make a better clustering key.
If you often use that type of WHERE condition on this table -- WHERE jobdatestart >= <some_date> -- then you should cluster the orders table on ( jobdatestart, orderid ) instead of just ( orderid ). Be sure to create a separate, nonclustered index with ( orderid ) as the key.
I'm assuming the date value is meant to be:
WHERE (ord.jobdatestart > '20210601')
and not
WHERE (ord.jobdatestart > '20210106')
but either way the new clustering should dramatically help.
That will almost certainly take care of the performance issues for this query, unless one of the other tables is causing an issue. We'll have to address that if/when it happens.
You won't typically any really harmful effects on other queries with this, but naturally you should keep a look out for such issues just in case.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 2, 2021 at 1:34 am
As a bit of a sidebar, the TOP thing may have been someones attempt to "set a row goal" to trick the optimizer into doing a better job. The thing is that TOP 100 Percent doesn't work (to the best of my knowledge). If you're going to try (it doesn't always work) for a "row goal" optimization, use the max value for an integer, which is 2,147,483,647 (without the commas). If that's to much of a pain, then us a "2" follow by nine "0"s.
I believe that Scott is on the right track but I don't believe that you need to change the Clustered Index (which is a pain to do and may negatively affect things you might not have expected). As in interim trial, you might want to simply create a unique non-clustered index based on (in this case) the jobdatestart and the orderid (which is probably the current Clustered Index).
Then, the trick. You need to select from the order table to find the orderids that are greater than the date in the WHERE clause (should probably be >= instead) and it needs to be treated like a table. You can either dump the results into a temp table or us a "blocking operator" in a CTE. Even though the non_clustered index is unique already, you could use a DISTINCT or a TOP 2 billion with an order by.
Then do an inner join of the order table to that temp table or "blocked" cte and continue the rest of the query except for the WHERE clause which should have been in the query that made the temp table or the "blocked" CTE.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 2, 2021 at 2:21 am
Just change the clustered index. The other method is too much overhead and too much of a pain. A clus index change is a one-time thing. All the create-a-temp-table-and-then-join-to-it has to be done forever, for every query using jobdatestart as a WHERE condition.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 6, 2021 at 11:53 am
This was removed by the editor as SPAM
September 6, 2021 at 11:39 pm
Ah... be careful. Changing the clustered index on the quick fly could cost you everywhere else.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 7, 2021 at 12:13 am
So what do you do then? Use XE to determine which queries are run and how often to determine whether the workload improves or not with the new indexing?
September 7, 2021 at 4:33 am
Step 1 would be to do kind of what you say. Capture the queries that use the table and the column. Then, look at them and remember that the OP's query is one query and it's a bloody reporting query. I'm not willing to make will to go through the little slice of hell of changing the clustered index, which would also require all of the non-clustered indexes, which also includes possible changes to a whole raft of FK constraints to be rebuilt just to make one reporting query run faster. You need to look really look at things to figure out what you might be screwing up for everything else and, yeah, it could take some good bit of time to do that.
But you DO need to do that to make real sure that your not killing the Golden goose to get 1 egg.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 7, 2021 at 9:50 am
If jobdatestart is a date or datetime, compare it with a date(time) instead of a string regarding [jobdatestart] > '6/1/2021'
September 7, 2021 at 5:19 pm
Ah... be careful. Changing the clustered index on the quick fly could cost you everywhere else.
How is that, specifically, when the current clus index is an identity column? You don't search for ranges of identities. And a single value lookup is not much more overhead thru a nonclus index than thru a clus one (two seeks instead of one).
As I said before, IF you often / most often query this table using that date -- which seems quite likely -- then you should recluster the table and save yourself a ton of I/O and hassles in the future trying to work around the table being clustered on identity only instead.
Keep in mind that you cannot assume that the identity values are in date order, or in any other specific order. And SQL can't assume that either, when it's creating a query plan.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply