July 2, 2021 at 4:38 pm
Hello ,
I've created a bit of a monster with my inner Join. I'm hoping someone could give me some thoughts on how to optimize this. It does works but its supper slow, and has issues. I'm sure there is a better way to get the same results.
I have 2 tables joined by ID , the first holds the header data for each transaction and the 2nd table holds the detail.
declare @start datetime
declare @end datetime
set @start = '2021-06-23 00:09:44.910'
set @end = '2021-06-30 13:09:44.910'
SELECT top 300 A. [id] as IDNEW
,A.[entry_time]
,CONVERT(datetime, SWITCHOFFSET([entry_time], DATEPART(TZOFFSET, [entry_time] AT TIME ZONE 'Central Standard Time'))) as DateTimeCST
,A.[location]
,A.[sample_type]
,A.[total_sample_size]
,A.[ntu]
,A.[moisture_percent]
,A.[in_spec_percent]
,A.[username]
,A.[comments]
,B1.[spec_size] as spec1
,B1.[spec_amount] as Amt1
,B2.[spec_size] as spec2
,B2.[spec_amount] as Amt2
,B3.[spec_size] as spec3
,B3.[spec_amount] as Amt3
,B4.[spec_size] as spec4
,B4.[spec_amount] as Amt4
,B5.[spec_size] as spec5
,B5.[spec_amount] as Amt5
,B6.[spec_size] as spec6
,B6.[spec_amount] as Amt6
,B7.[spec_size] as spec7
,B7.[spec_amount] as Amt7
,B8.[spec_size] as spec8
,B8.[spec_amount] as Amt8
,B9.[spec_size] as spec9
,B9.[spec_amount] as Amt9
,B10.[spec_size] as spec10
,B10.[spec_amount] as Amt10
,B11.[spec_size] as spec11
,B11.[spec_amount] as Amt11
,B12.[spec_size] as spec12
,B12.[spec_amount] as Amt12
,B13.[spec_size] as spec13
,B13.[spec_amount] as Amt13
,B14.[spec_size] as spec14
,B14.[spec_amount] as Amt14
,B15.[spec_size] as spec15
,B15.[spec_amount] as Amt15
,B16.[spec_size] as spec16
,B16.[spec_amount] as Amt16
,B17.[spec_size] as spec17
,B17.[spec_amount] as Amt17
,B18.[spec_size] as spec18
,B18.[spec_amount] as Amt18
,B19.[spec_size] as spec19
,B19.[spec_amount] as Amt19
,B20.[spec_size] as spec20
,B20.[spec_amount] as Amt20
, B21.[spec_size] as spec21
,B21.[spec_amount] as Amt21
FROM [dbo].[csi_samples] A
Inner join [dbo].[csi_sample_specs] B1 on A.id = B1.[csi_sample_id] and B1.spec_size = 8
Inner join [dbo].[csi_sample_specs] B2 on A.id = B2.[csi_sample_id] and B2.spec_size = 12
Inner join [dbo].[csi_sample_specs] B3 on A.id = B3.[csi_sample_id] and B3.spec_size = 14
Inner join [dbo].[csi_sample_specs] B4 on A.id = B4.[csi_sample_id] and B4.spec_size = 16
Inner join [dbo].[csi_sample_specs] B5 on A.id = B5.[csi_sample_id] and B5.spec_size = 18
Inner join [dbo].[csi_sample_specs] B6 on A.id = B6.[csi_sample_id] and B6.spec_size = 20
Inner join [dbo].[csi_sample_specs] B7 on A.id = B7.[csi_sample_id] and B7.spec_size = 25
Inner join [dbo].[csi_sample_specs] B8 on A.id = B8.[csi_sample_id] and B8.spec_size = 30
Inner join [dbo].[csi_sample_specs] B9 on A.id = B9.[csi_sample_id] and B9.spec_size = 35
Inner join [dbo].[csi_sample_specs] B10 on A.id = B10.[csi_sample_id] and B10.spec_size = 40
Inner join [dbo].[csi_sample_specs] B11 on A.id = B11.[csi_sample_id] and B11.spec_size = 45
Inner join [dbo].[csi_sample_specs] B12 on A.id = B12.[csi_sample_id] and B12.spec_size = 50
Inner join [dbo].[csi_sample_specs] B13 on A.id = B13.[csi_sample_id] and B13.spec_size = 60
Inner join [dbo].[csi_sample_specs] B14 on A.id = B14.[csi_sample_id] and B14.spec_size = 70
Inner join [dbo].[csi_sample_specs] B15 on A.id = B15.[csi_sample_id] and B15.spec_size = 100
Inner join [dbo].[csi_sample_specs] B16 on A.id = B16.[csi_sample_id] and B16.spec_size = 140
Inner join [dbo].[csi_sample_specs] B17 on A.id = B17.[csi_sample_id] and B17.spec_size = 200
Inner join [dbo].[csi_sample_specs] B18 on A.id = B18.[csi_sample_id] and B18.spec_size = 0
Inner join [dbo].[csi_sample_specs] B19 on A.id = B19.[csi_sample_id] and B19.spec_size = 10
Inner join [dbo].[csi_sample_specs] B20 on A.id = B20.[csi_sample_id] and B20.spec_size = 270
Inner join [dbo].[csi_sample_specs] B21 on A.id = B21.[csi_sample_id] and B21.spec_size = 4
where A.entry_time between @start and @end
and location = 'SAT'
order by 1 desc
This query just grinds and frequently keeps processing.
thanks !!
July 2, 2021 at 6:02 pm
Does csi_sample_specs have an index on csi_sample_id and spec_size (ideally including spec_amount if a nonclustered index)?
How slow is super slow? What is expected/needed execution time? Do other queries execute quickly?
What issues are you referring to when you say it has issues?
DDL for the tables, including indexes, would be helpful. How many rows in csi_samples? in csi_sample_specs?
July 2, 2021 at 7:07 pm
Try the code below. If there are a lot more spec_size values than the ones listed here, uncomment the IN list to pull only the ones you need. If those spec_sizes are (almost) the only ones in the table, leave it commented out. It'll likely be less overhead just reading and ignoring a few extra ones rather than having to process the IN statement.
Important: For best performance, dbo.csi_sample_specs should be clustered (not just a regular index) on ( csi_sample_id, spec_size ). Presumably that's a unique combination. If that table is currently clustered on identity (bad!, that stupid "always cluster by identity" myth again), you need to change it.
declare @start datetime
declare @end datetime
set @start = '2021-06-23 00:09:44.910'
set @end = '2021-06-30 13:09:44.910'
SELECT TOP (300) A. [id] as IDNEW
,A.[entry_time]
,CONVERT(datetime, SWITCHOFFSET([entry_time], DATEPART(TZOFFSET, [entry_time] AT TIME ZONE 'Central Standard Time'))) as DateTimeCST
,A.[location]
,A.[sample_type]
,A.[total_sample_size]
,A.[ntu]
,A.[moisture_percent]
,A.[in_spec_percent]
,A.[username]
,A.[comments]
,8 as spec1
,B.Amt1
,12 as spec2
,B.Amt2
,14 as spec3
,B.Amt3
,16 as spec4
,B.Amt4
,18 as spec5
,B.Amt5
,20 as spec6
,B.Amt6
,25 as spec7
,B.Amt7
,30 as spec8
,B.Amt8
,35 as spec9
,B.Amt9
,40 as spec10
,B.Amt10
,45 as spec11
,B.Amt11
,50 as spec12
,B.Amt12
,60 as spec13
,B.Amt13
,70 as spec14
,B.Amt14
,100 as spec15
,B.Amt15
,140 as spec16
,B.Amt16
,200 as spec17
,B.Amt17
,0 as spec18
,B.Amt18
,10 as spec19
,B.Amt19
,270 as spec20
,B.Amt20
, 4 as spec21
,B.Amt21
FROM [dbo].[csi_samples] A
CROSS APPLY (
SELECT
MAX(CASE WHEN B.spec_size = 8 THEN B.spec_amount END) AS Amt1,
MAX(CASE WHEN B.spec_size = 12 THEN B.spec_amount END) AS Amt2,
MAX(CASE WHEN B.spec_size = 14 THEN B.spec_amount END) AS Amt3,
MAX(CASE WHEN B.spec_size = 16 THEN B.spec_amount END) AS Amt4,
MAX(CASE WHEN B.spec_size = 18 THEN B.spec_amount END) AS Amt5,
MAX(CASE WHEN B.spec_size = 20 THEN B.spec_amount END) AS Amt6,
MAX(CASE WHEN B.spec_size = 25 THEN B.spec_amount END) AS Amt7,
MAX(CASE WHEN B.spec_size = 30 THEN B.spec_amount END) AS Amt8,
MAX(CASE WHEN B.spec_size = 35 THEN B.spec_amount END) AS Amt9,
MAX(CASE WHEN B.spec_size = 40 THEN B.spec_amount END) AS Amt10,
MAX(CASE WHEN B.spec_size = 45 THEN B.spec_amount END) AS Amt11,
MAX(CASE WHEN B.spec_size = 50 THEN B.spec_amount END) AS Amt12,
MAX(CASE WHEN B.spec_size = 60 THEN B.spec_amount END) AS Amt13,
MAX(CASE WHEN B.spec_size = 70 THEN B.spec_amount END) AS Amt14,
MAX(CASE WHEN B.spec_size = 100 THEN B.spec_amount END) AS Amt15,
MAX(CASE WHEN B.spec_size = 140 THEN B.spec_amount END) AS Amt16,
MAX(CASE WHEN B.spec_size = 200 THEN B.spec_amount END) AS Amt17,
MAX(CASE WHEN B.spec_size = 0 THEN B.spec_amount END) AS Amt18,
MAX(CASE WHEN B.spec_size = 10 THEN B.spec_amount END) AS Amt19,
MAX(CASE WHEN B.spec_size = 270 THEN B.spec_amount END) AS Amt20,
MAX(CASE WHEN B.spec_size = 4 THEN B.spec_amount END) AS Amt21
FROM [dbo].[csi_sample_specs] B
WHERE A.id = B.[csi_sample_id] /*AND
B.spec_size IN (8, 12, 14, 16, 18, 20, 25, 30, 35, 40, 45, 50, 60, 70, 100, 140, 200, 0, 10, 270, 4)*/
) AS B
where A.entry_time between @start and @end
and A.location = 'SAT'
order by 1 desc
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 2, 2021 at 9:10 pm
OMG !!
Wow .....the cross apply worked like a Charm. and it was quick , even though now checking the table index is Clustered on identity, I will go ahead and change that.
Thank you so much !!
July 3, 2021 at 3:16 pm
One other note - probably not a performance issue. It appears you are storing entry_time as a datetimeoffset and want to return the time in 'Central Standard Time'. The datetimeoffset is already in UTC so you really only need:
, entry_time at time zone 'Central Standard Time'
If you must return the data as a datetime - then you should only need:
, cast(entry_time at time zone 'Central Standard Time' as datetime)
Final note: your search is based on entry_time before it has been converted to Central Standard Time and it is using BETWEEN which is inclusive. If your search criteria is not already defined for UTC then the results may not be as expected - and since you are comparing a datetimeoffset to a datetime you could be missing values.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 3, 2021 at 9:37 pm
OMG !!
Wow .....the cross apply worked like a Charm. and it was quick , even though now checking the table index is Clustered on identity, I will go ahead and change that.
Thank you so much !!
While I agree that having the right clustered index is important, building a clustered index to support just one query is frequently the wrong thing to do. Before you go making such a change, you really need to determine if that change is going to kill you in a dozen other places. For example, do you have any Foreign Keys that rely on the current clustered index? What else relies on it? And will change create a fragmentation nightmare that put's the screws to inserts and updates?
Must look eye. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
July 3, 2021 at 11:03 pm
gjoelson 29755 wrote:OMG !!
Wow .....the cross apply worked like a Charm. and it was quick , even though now checking the table index is Clustered on identity, I will go ahead and change that.
Thank you so much !!
While I agree that having the right clustered index is important, building a clustered index to support just one query is frequently the wrong thing to do. Before you go making such a change, you really need to determine if that change is going to kill you in a dozen other places. For example, do you have any Foreign Keys that rely on the current clustered index? What else relies on it? And will change create a fragmentation nightmare that put's the screws to inserts and updates?
Must look eye. 😉
It's a virtual certainly that these tables are joined most often, since they are a header and detail table. This is the standard pattern. Yeah, this might be a one-in-million-exception, but that's extraordinarily unlikely. [As to fragmentation, presumably the id on the header table is itself an identity, making the lead key sequential.] As for FKs, naturally you can easily retain a unique index or primary key constraint on identity, it's just a nonclus index rather than clus.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 4, 2021 at 12:00 am
Jeff Moden wrote:gjoelson 29755 wrote:OMG !!
Wow .....the cross apply worked like a Charm. and it was quick , even though now checking the table index is Clustered on identity, I will go ahead and change that.
Thank you so much !!
While I agree that having the right clustered index is important, building a clustered index to support just one query is frequently the wrong thing to do. Before you go making such a change, you really need to determine if that change is going to kill you in a dozen other places. For example, do you have any Foreign Keys that rely on the current clustered index? What else relies on it? And will change create a fragmentation nightmare that put's the screws to inserts and updates?
Must look eye. 😉
It's a virtual certainly that these tables are joined most often, since they are a header and detail table. This is the standard pattern. Yeah, this might be a one-in-million-exception, but that's extraordinarily unlikely. [As to fragmentation, presumably the id on the header table is itself an identity.] As of FKs, naturally you can easily retain a unique index on identity, it just be a nonclus index rather than clus.
While I mostly agree with you, changing the clustered index isn't a trivial event. I've also found that such changes are usually NOT "one-in-a-million" exceptions especially when you the fact they they're usually built on the IDENTITY column with "great purpose and intent" in mind. It's always worth doing a deep check because have the proverbial poo hit the fan because of something like this type of change is never worth the anguish of an emergency fix in production nor the loss in trust/reputation because some guy on the internet said it would probably be ok. 😉
Always check... the career you save may be your own. And, remember, I'm saying that even though I think you're "probably" right on this one. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
July 4, 2021 at 7:25 pm
ScottPletcher wrote:Jeff Moden wrote:gjoelson 29755 wrote:OMG !!
Wow .....the cross apply worked like a Charm. and it was quick , even though now checking the table index is Clustered on identity, I will go ahead and change that.
Thank you so much !!
While I agree that having the right clustered index is important, building a clustered index to support just one query is frequently the wrong thing to do. Before you go making such a change, you really need to determine if that change is going to kill you in a dozen other places. For example, do you have any Foreign Keys that rely on the current clustered index? What else relies on it? And will change create a fragmentation nightmare that put's the screws to inserts and updates?
Must look eye. 😉
It's a virtual certainly that these tables are joined most often, since they are a header and detail table. This is the standard pattern. Yeah, this might be a one-in-million-exception, but that's extraordinarily unlikely. [As to fragmentation, presumably the id on the header table is itself an identity.] As of FKs, naturally you can easily retain a unique index on identity, it just be a nonclus index rather than clus.
While I mostly agree with you, changing the clustered index isn't a trivial event. I've also found that such changes are usually NOT "one-in-a-million" exceptions especially when you the fact they they're usually built on the IDENTITY column with "great purpose and intent" in mind. It's always worth doing a deep check because have the proverbial poo hit the fan because of something like this type of change is never worth the anguish of an emergency fix in production nor the loss in trust/reputation because some guy on the internet said it would probably be ok. 😉
Always check... the career you save may be your own. And, remember, I'm saying that even though I think you're "probably" right on this one. 😀
Great point, fair enough.
So how do you check in this situation? I've got a script to give me the min info I feel is necessary to evaluate indexes and make changes, but naturally I'd be curious to know what you use.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 6, 2021 at 3:36 pm
ScottPletcher wrote:Jeff Moden wrote:gjoelson 29755 wrote:OMG !!
Wow .....the cross apply worked like a Charm. and it was quick , even though now checking the table index is Clustered on identity, I will go ahead and change that.
Thank you so much !!
While I agree that having the right clustered index is important, building a clustered index to support just one query is frequently the wrong thing to do. Before you go making such a change, you really need to determine if that change is going to kill you in a dozen other places. For example, do you have any Foreign Keys that rely on the current clustered index? What else relies on it? And will change create a fragmentation nightmare that put's the screws to inserts and updates?
Must look eye. 😉
It's a virtual certainly that these tables are joined most often, since they are a header and detail table. This is the standard pattern. Yeah, this might be a one-in-million-exception, but that's extraordinarily unlikely. [As to fragmentation, presumably the id on the header table is itself an identity.] As of FKs, naturally you can easily retain a unique index on identity, it just be a nonclus index rather than clus.
While I mostly agree with you, changing the clustered index isn't a trivial event. I've also found that such changes are usually NOT "one-in-a-million" exceptions especially when you the fact they they're usually built on the IDENTITY column with "great purpose and intent" in mind. It's always worth doing a deep check because have the proverbial poo hit the fan because of something like this type of change is never worth the anguish of an emergency fix in production nor the loss in trust/reputation because some guy on the internet said it would probably be ok. 😉
Always check... the career you save may be your own. And, remember, I'm saying that even though I think you're "probably" right on this one. 😀
I've found that identities are most often used as the clustering key with no thought at all, that it's an automatic default for all tables rather than any great intent.
Of course we keep the identity as a pk, it's just nonclus. I can't imagine how that change could create a real problem for any query that used identity for any purpose. I suppose there could be a performance hit in certain odd cases, but nothing like the performance gain from properly clustering the table.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply