I have another requirement:

CREATE TABLE #test (theDate DATE, value int, flag int);

select * from #test

INSERT INTO #test VALUES

('20210109', 12, 1),

('20210109', 12, 0),

('20210131', 12, 0),

('20210131', 12, 0),

('20210131', 12, 0),

('20210225', 23, 1),

('20210225',...

• This reply was modified 3 years, 5 months ago by  rohitkocharda.

Thanks for replying everyone! Sorry I should have posted create scrip as well

query cost was mostly sorting (80%). I created the non-clustered index on columns Infocode and ExchIntCode but no change in performance.

Those are some great suggestions .I started with :

• getting rid of nolock()
• remove * from one of the table only one column was being used

Question: Do I need  :...

Thanks for all your reply!

Putting OPTION (QUERYTRACEON 9481); at the end of the query did the magic.

Also, I have rebuild the index and I checked to make sure that all index and stats were same as in the old sql 2012 machine.

sELECT name AS index_name,STATS_DATE(OBJECT_ID, index_id)...

Hi Grant,

What concerning is that same query is running  faster in 2012. I would look at the query and execution plan but at this point of stage in this project,...

HI David,

tempdb is configured the same way on both machines. But I have Compatibility level to 2012 (110) and Legacy Cardinality Estimation to TRUE on sql 2016 temodb as well....

Most of the performance  is good but one of my SSIS package that is deployed in both old ( 2012) and new (2016) has significant performance differences.  Tht SSIS package...

Thanks for your suggestion. I was aware of the cardinality factor and applied this already to new sql 2016 machine.

is this a better way to do this?

SELECT *

FROM sys.configurations

---where value_in_use = 1

ORDER BY name ;

GO

Als , does it make sense to replace

EQY_FUND_IND =

coalesce ((SELECT TOP 1 i.Name FROM dbo.#Entity e2

LEFT join [dbo].[#EntityIndustry] ei ON ei.MasterEntityId = e2.MasterEntityId

----AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate

left join [dbo].[Industry]...

Those are some amazing suggestions. Thanks!

I will change the script and hopefully  see the benefit. Do you recommend any non-clustered indexes?

That worked perfectly. Thanks!

alter PROCEDURE [dbo].[spGetEntities_RO_test] 
 ---1:19:15

@AsOfDate date = '9999-12-31',
@PrivateMarketStatus bit = 0

as 

SET NOCOUNT ON;

select CountryOfIssueId,
MasterEntityId,
ValidFromDate,
ValidToDate,
CountryOfIncorporationId,
CountryOfDomicileId,
MarketStatusId,
PrimaryCurrencyId,
PrimaryExchangeId,
SourceEntityId,
Name,
PrimaryExchangeTicker,
TickerExchangeCode,
RecentPeriodEndDate,
EntityId,
RecentSemiEnd,
RecentQuarterEnd,
RecentQuarterEndDate,
RecentBSPeriod,
IsCurrent
 into dbo.#Entity
from [dbo].[Entity]
where  @AsOfDate between ValidFromDate  and ValidToDate

CREATE CLUSTERED INDEX...