January 9, 2019 at 6:43 am
Hello Friends,
We have been looking to optimize a query which at times run erratic and keeps changing its execution plan. While this query is static and does not have any parameters passed which could have caused parameter sniffing leading to bad plans, we suspect that the change in behavior could be cause by TF-2371 which we enabled on SQL 2014.
While we are pondering if this TF is useful to us in the long run, we also started looking at possible optimization of this query where it might be beneficial in the long run. We do not see heavy index scans due to lack of indexes or implicit data conversions so seeking your opinion on the query as a whole.
SELECT DISTINCT STATUS = 'E',
SymbolId
INTO #temp
FROM CIQData.dbo.Symbol_tbl s
WITH
(
NOLOCK
)
WHERE 1=1
AND s.symbolTypeId IN (5082, 7622)
AND EXISTS
(
SELECT NULL FROM RatingsData.dbo.XF_GISFPF_History_Security_vw XF_GISFPFhc INNER JOIN RatingsData.dbo.RatingSecurity_tbl rs WITH ( NOLOCK
)
ON XF_GISFPFhc.SecuritySymbolValue = rs.SecuritySymbolValue
AND XF_GISFPFhc.dataVendorId = rs.dataVendorId
WHERE 1=1
AND rs.securityId = s.objectId
)
UNION
SELECT 'E', SymbolId
FROM CIQData.dbo.Symbol_tbl s
WITH
(
NOLOCK
)
WHERE 1=1
AND s.symbolTypeId = 7575
AND EXISTS
(
SELECT NULL
FROM RatingsData.dbo.XF_GISFPF_History_Instrument_vw xi
INNER JOIN RatingsData.dbo.RatingInstrument_tbl ri
WITH
(
NOLOCK
)
ON xi.InstrumentSymbolValue = ri.InstrumentSymbolValue
INNER JOIN RatingsData.dbo.XF_ObjectSector_tbl oi
WITH
(
NOLOCK
)
ON oi.instrumentSymbolvalue = ri.instrumentSymbolValue
WHERE 1=1
AND ri.instrumentId = s.objectId
)
UNION
SELECT DISTINCT STATUS = 'E',
SymbolId
FROM CIQData.dbo.Symbol_tbl s
WITH
(
NOLOCK
)
WHERE 1=1
AND s.symbolTypeId IN (7574,7575, 7573)
AND EXISTS
(
SELECT NULL FROM RatingsData.dbo.XF_GISFPF_History_Company_tbl XF_GISFPFhc
WITH ( NOLOCK )
INNER JOIN RatingsData.dbo.RatingCompany_tbl rc
WITH ( NOLOCK )
ON XF_GISFPFhc.CompanySymbolValue = rc.CompanySymbolValue
AND XF_GISFPFhc.dataVendorId = rc.dataVendorId
WHERE 1=1
AND rc.CompanyId = s.objectId
)
drop table #temp
Please review this and see if there is something which looks being repetitive in this query. I tried using CTE and temporary table for accessing Symbol_tbl in the above query but found no major gains.
Best Regards
Chandan
January 9, 2019 at 6:51 am
What is 1=1 trying to achieve here?
It looks like you're selecting data from views as well; we'll need the DDL of those as well ideally to help on this. I also assume those views aren't selecting from other views; if so that'll be a big candidate for why the query is performing poorly.
Also, you're mixing DISTINCT and UNION. UNION already only returns distinct results, so you don't need both; however UNION does make a query perform slower than using UNION ALL (with DISTINCT in the queries too).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 9, 2019 at 7:08 am
Thom A - Wednesday, January 9, 2019 6:51 AMWhat is 1=1 trying to achieve here?It looks like you're selecting data from views as well; we'll need the DDL of those as well ideally to help on this. I also assume those views aren't selecting from other views; if so that'll be a big candidate for why the query is performing poorly.
Also, you're mixing DISTINCT and UNION. UNION already only returns distinct results, so you don't need both; however UNION does make a query perform slower than using UNION ALL (with DISTINCT in the queries too).
1=1 looks to be of no value to me but since we have more than that in 'and'. And I have attached schema too, do not see deep nesting of views which causes bad performance at times. Thanks for looking.
January 9, 2019 at 7:38 am
Thom A - Wednesday, January 9, 2019 7:16 AMDoes your VIEW (XF_GISFPF_History_Security_vw and XF_GISFPF_History_Instrument_vw) need to use UNION rather than UNION ALL.
I believe the union will eliminate the duplicates coming from the three different code blocks. That's obvious but may be you are trying to ask something else which I couldn't understand.
January 9, 2019 at 8:01 am
chandan_jha18 - Wednesday, January 9, 2019 7:38 AMThom A - Wednesday, January 9, 2019 7:16 AMDoes your VIEW (XF_GISFPF_History_Security_vw and XF_GISFPF_History_Instrument_vw) need to use UNION rather than UNION ALL.I believe the union will eliminate the duplicates coming from the three different code blocks. That's obvious but may be you are trying to ask something else which I couldn't understand.
UNION does eliminate the duplciates, yes, but that also comes at a (possibly significant) cost.SELECT *
FROM XF_GISFPF_History_Security_A_tbl
UNION
SELECT *
FROM XF_GISFPF_History_Security_B_tbl
In the case of this view, firstly you're using *; this is a bad idea as if anyone changes the definition of either of your tables it could easily break. But, for the UNION, the DISTINCT applies to every row and column. I've no idea how wide or tall your tables are (we don't have the DDL), but that could be a huge ask. Considering it's a History table, I assume they are tall, but I've no idea on the width but if it's based on the RatingSecurity_tbl then it I assume it has at least 20 columns. That could easily be a huge resource hit.
So, my question, really, is, does it need to be UNION; why do you need only DISTINCT results?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 9, 2019 at 8:45 am
Thom A - Wednesday, January 9, 2019 8:01 AMchandan_jha18 - Wednesday, January 9, 2019 7:38 AMThom A - Wednesday, January 9, 2019 7:16 AMDoes your VIEW (XF_GISFPF_History_Security_vw and XF_GISFPF_History_Instrument_vw) need to use UNION rather than UNION ALL.I believe the union will eliminate the duplicates coming from the three different code blocks. That's obvious but may be you are trying to ask something else which I couldn't understand.
UNION does eliminate the duplciates, yes, but that also comes at a (possibly significant) cost.
SELECT *
FROM XF_GISFPF_History_Security_A_tbl
UNION
SELECT *
FROM XF_GISFPF_History_Security_B_tbl
In the case of this view, firstly you're using *; this is a bad idea as if anyone changes the definition of either of your tables it could easily break. But, for the UNION, the DISTINCT applies to every row and column. I've no idea how wide or tall your tables are (we don't have the DDL), but that could be a huge ask. Considering it's a History table, I assume they are tall, but I've no idea on the width but if it's based on the RatingSecurity_tbl then it I assume it has at least 20 columns. That could easily be a huge resource hit.So, my question, really, is, does it need to be UNION; why do you need only DISTINCT results?
Well I believe the resultset needs to be distinct for further processing based on it. I mean, for simplicity I put the resultset into a temporary table to show here, in realty it goes to a temporary but permanent table in SQL Server(kid of gets recreated at every execution) because that table is consumed by another process. So removing the distinct results and putting duplicates might not be beneficial for the business.
and here is the table defnition for A and B tables you mentioned above:
CREATE TABLE [dbo].[XF_GISFPF_History_Security_A_tbl]
(
[SecuritySymbolValue] [varchar](200) NOT NULL,
[InstrumentSymbolValue] [varchar](200) NOT NULL,
[dataVendorId] [int] NOT NULL
) ON [DATA]
GO
Same definition for table B. Also, below are results from 'set io statistics on' after the query execution:
Table 'Symbol_tbl'. Scan count 42, logical reads 41472, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'RatingInstrument_tbl'. Scan count 9, logical reads 5559, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'XF_GISFPF_History_Instrument_A_tbl'. Scan count 9, logical reads 3297, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'XF_GISFPF_History_Instrument_B_tbl'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'XF_ObjectSector_tbl'. Scan count 9, logical reads 3181, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'XF_GISFPF_History_Company_tbl'. Scan count 9, logical reads 383, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'RatingCompany_tbl'. Scan count 9, logical reads 511, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'XF_GISFPF_History_Security_B_tbl'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'XF_GISFPF_History_Security_A_tbl'. Scan count 9, logical reads 14839, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'RatingSecurity_tbl'. Scan count 9, logical reads 13618, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
January 10, 2019 at 7:52 am
I am trying to see if an indexed view can be created and benefit the query. Because except parallelism, I don't see any other wait type for this query.
January 10, 2019 at 10:28 am
Issue got resolved after we ran sp_refreshview
I believe we needed to do this post SQL Server Upgrade to SQL 2014.
Sometimes we miss simple things.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply