January 17, 2019 at 6:42 pm
This query (attached estimated query plan) returns Exception of type 'System.OutOfMemoryException' was thrown. Are queried tables just too huge or can query performance be improved with index?
select
SC.FST_NAME,
SC.LAST_NAME,
SCA.ADDR_PER_ID
from
ODS_TMUS2..CHUB_S_CONTACT AS SC INNER JOIN
ODS_TMUS2..CHUB_S_CON_ADDR AS SCA ON SC.ROW_ID = SCA.CONTACT_ID
CHUB_S_CONTACT TABLE (45,185,725 rows) has PK = ROW_ID
CHUB_S_CON_ADDR TABLE (71,748,682 rows) has PK = ROW_ID
Existing Indexes:
CREATE NONCLUSTERED INDEX [idx01_CHUB_S_CON_ADDR] ON [dbo].[CHUB_S_CON_ADDR]
(
[LogId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
CREATE NONCLUSTERED INDEX [NCI_01_ADDR_PER_ID] ON [dbo].[CHUB_S_CON_ADDR]
(
[ADDR_PER_ID] ASC
)
INCLUDE ( [CONTACT_ID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
CREATE NONCLUSTERED INDEX [NCI01_CONTACT_ID] ON [dbo].[CHUB_S_CON_ADDR]
(
[CONTACT_ID] ASC
)
INCLUDE ( [ADDR_PER_ID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
USE [ODS_TMUS2]
GO
/****** Object: Index [NCI_CHUB_S_CONTACT_01] Script Date: 1/17/2019 5:40:17 PM ******/
CREATE NONCLUSTERED INDEX [NCI_CHUB_S_CONTACT_01] ON [dbo].[CHUB_S_CONTACT]
(
[LAST_UPD] ASC
)
INCLUDE ( [LogId],
[ROW_ID],
[CREATED],
[CREATED_BY],
[LAST_UPD_BY],
[DCKING_NUM],
[MODIFICATION_NUM],
[CONFLICT_ID],
[PAR_ROW_ID],
[ACTIVE_FLG],
[BU_ID],
[COURT_PAY_FLG],
[DISA_CLEANSE_FLG],
[DISP_IMG_AUTH_FLG],
[EMAIL_SR_UPD_FLG],
[EMP_FLG],
[FST_NAME],
[INVSTGTR_FLG],
[LAST_NAME],
[PERSON_UID],
[PO_PAY_FLG],
[PRIV_FLG],
[PROSPECT_FLG],
[PTSHP_CONTACT_FLG],
[PTSHP_KEY_CON_FLG],
[SEND_SURVEY_FLG],
[SPEAKER_FLG],
[SUPPRESS_EMAIL_FLG],
[SUPPRESS_FAX_FLG],
[SUSPECT_FLG],
[SUSP_WTCH_FLG],
[AGENT_FLG],
[ENTERPRISE_FLAG],
[MEMBER_FLG],
[OK_TO_SAMPLE_FLG],
[PROVIDER_FLG],
[PR_REP_DNRM_FLG],
[PR_REP_MANL_FLG],
[PR_REP_SYS_FLG],
[SEND_FIN_FLG],
[SEND_NEWS_FLG],
[SEND_PROMOTES_FLG],
[SUPPRESS_CALL_FLG],
[SUPPRESS_MAIL_FLG],
[ANNL_INCM_EXCH_DT],
[ASGN_USR_EXCLD_FLG],
[CALL_FLG],
[CON_CREATED_DT],
[DB_LAST_UPD],
[ANNL_INCM_CURCY_CD],
[COMMENTS],
[CREATOR_LOGIN],
[CURR_PRI_LST_ID],
[CUST_STAT_CD],
[CUST_VALUE_CD],
[DB_LAST_UPD_SRC],
[EMP_NUM],
[INTEGRATION_ID],
[OWNER_LOGIN],
[OWNER_PER_ID],
[PER_TITLE],
[PER_TITLE_SUFFIX],
[VETERAN_FLG],
[X_EN_SSN],
[X_EN_SSN_KEY],
[X_IDEN_EXP_DT],
[X_IDEN_NUM],
[X_IDEN_STATE_CD],
[X_IDEN_TYPE_CD],
[LOG_POSITION_NUMBER],
[ORACLE_SCN],
[OPERATION_CODE],
[COMMIT_TIMESTAMP]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
--Quote me
January 17, 2019 at 9:01 pm
You're trying to return more than 56 million rows to the screen. The machine you're doing the query from has simply run out of memory.
Also, the "NCI_CHUB_S_CONTACT_01" is crazy. You've essentially rebuilt the table. Improving that won't help you running out of memory while returning 56 million rows to the screen, though.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 18, 2019 at 12:53 pm
Quite right on the "out of memory" error.
Overall, though, for best performance with far fewer total indexes:
cluster the CHUB_S_CON_ADDR TABLE on ( CONTACT_ID, ROW_ID ).
Add a non-clus index to lookup by ROW_ID alone.
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".
January 18, 2019 at 8:56 pm
Jeff Moden regarding the "NCI_CHUB_S_CONTACT_01" is crazy. Shy two columns, it basically covers all columns and every time I run a select * on underlying table it logs another user_scan with this index.. It is crazy because index is practically a copy of the underlying table and will take use disc space, extra I/Os , log records, ie. every time the underlying table is inserted into the index has to be maintained, right?
ScottPletcher Both tables have a PK on ROW_ID already. I should still create a non-clus index on ROW_ID alone for CHUB_S_CON_ADDR or CHUB_S_CONTACT table ?
--Quote me
January 20, 2019 at 3:50 am
Indexes aren't going to help here, because the server is not having any problems.
System.OutOfMemoryException is a .net error. The client tool that you're running this query from is running out of memory. Not the server. Not SQL. The client tool.
Return less data to the client. No one is ever going to read 56 million rows.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 20, 2019 at 7:50 am
polkadot - Friday, January 18, 2019 8:56 PMJeff Moden regarding the "NCI_CHUB_S_CONTACT_01" is crazy. Shy two columns, it basically covers all columns and every time I run a select * on underlying table it logs another user_scan with this index.. It is crazy because index is practically a copy of the underlying table and will take use disc space, extra I/Os , log records, ie. every time the underlying table is inserted into the index has to be maintained, right?
Basically, you're doubling the size of your table with that index, not only for disk space but memory usage, backup/restore space and speed, Insert speed, and making a ton of extra log file activity especially if you have columns whose contents get wider when you do updates. Since the index is on the Last_Upd column, one presumes that you'll be providing some temporal criteria to search the rows on. Done correctly, that means that you're going to return a very small portion of the 56 million rows.and that might mean that index seeks followed by the inevitable row lookups (caused by the SELECT *) might be a pretty good trade off compared to nearly duplicating the content of the table.
You identify the PK of both tables as "RowID". Just guessing here but I'm assuming that both of those columns are IDENTITY columns and that both PKs are actually CLUSTERED PKs? I have some ideas but would need to see the CREATE TABLE statements for both tables including the indexes and constraints (including DEFAULT constraints) on both tables before we go changing any indexes.
As a bit of a sidebar, a lot of people make recommendations about indexes and especially Clustered Indexes without consideration of Page Splits, the resulting massive fragmentation of the Clustered Index, and the constant battle of repairing fragmentation (both Logical and Physical) with index maintenance. The good part is (judging by the names of columns in your big index), the table has very few variable width columns that may cause row expansion (which can cause massive fragmentation) when updated. If we can fix that, we might be able to make some better choices for indexes but I likely wouldn't change the Clustered Index to something that will create "Random Silos" (hotspots of fragmentation) in the Clustered index causing it to rapidly fragment.
The bottom line though is what I said before and what Gail (Gila Monster) confirmed, there is nothing that will help the query in the original post because you're asking for a totally unfiltered JOIN and that's going to return all 56 million rows in the table and that's going to continue to drive your client application (SSMS, whatever) out of memory on the machine it's running from. It can also have some nasty effects on the server because, unless your server has enough room to hold the tables from your query in memory and a whole lot more, your query is going to drive everyone else's table data out of memory and will need to be reloaded the next time they query (which could be at the same time your query is running).
--Jeff Moden
Change is inevitable... Change for the better is not.
January 22, 2019 at 11:18 pm
Jeff Moden
This simple query I shared in opening post is being used as a *subquery* in a view, which is ultimately what I am trying to troubleshoot. When the view is run, it takes a long time. After 8 minutes this query is still running.
select
top 100 * from [BusOps_Transportation].[dbo].CHUB_CustDetailsI think it is because this simply query which is a subquery is so egregious in selecting so many rows into memory that it then wants to JOIN against. Here is the DDL for the view and please tell me if you agree that it is the subquery that is the main problem.
View:
USE [BusOps_Transportation]
GO
/****** Object: View [dbo].[CHUB_CustDetails] Script Date: 1/22/2019 10:05:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--use BusOps_Transportation
CREATE view [dbo].[CHUB_CustDetails]
as
(
select
SCA.FST_NAME 'FirstName',
SCA.LAST_NAME 'LastName',
SAP.ADDR 'Address',
SAP.STATE,
SAP.CITY,
SAP.ZIPCODE,
SAP.COUNTRY,
SAA.X_BAN as BAN,
SAA.X_MSISDN as MSISDN,
SAA.REL_TYPE_CD 'SubscriberStatus',
SAA.X_AST_STATUS_DT 'SubscriberStatusDate',
SA.BAR_CODE_NUM 'IMEI',
SAA.X_AST_SUB_UID 'ULID'
from
(
select
SC.FST_NAME,
SC.LAST_NAME,
SCA.ADDR_PER_ID
from
ODS_TMUS2..CHUB_S_CONTACT AS SC INNER JOIN
ODS_TMUS2..CHUB_S_CON_ADDR AS SCA ON SC.ROW_ID = SCA.CONTACT_ID
--where
--SC.LAST_UPD >= '01-JUN-15'
) sca join
ODS_TMUS2..CHUB_S_ADDR_PER SAP on
SCA.ADDR_PER_ID=SAP.ROW_ID
join
ODS_TMUS2..CHUB_S_ORG_EXT soe on
SOE.NAME=SAP.ADDR_NAME
join
ODS_TMUS2..CHUB_S_ASSET_ACCNT saa on
SOE.ROW_ID=SAA.ACCNT_ID
join
ODS_TMUS2..CHUB_S_ASSET SA on
SAA.ASSET_ID=SA.ROW_ID
WHERE
SAP.ADDR_TYPE_CD = 'ADDRESS'
)
GO
Indexes for the subquery tables, were given in opening post. Scripts for clustered indexes on RowID are below. RowId is *non identity* PK:
USE
[ODS_TMUS2]
GO
/****** Object: Index [PK_CHUB_S_CON_ADDR] Script Date: 1/22/2019 9:31:31 PM ******/
ALTER TABLE [dbo].[CHUB_S_CON_ADDR] ADD CONSTRAINT [PK_CHUB_S_CON_ADDR] PRIMARY KEY CLUSTERED
(
[ROW_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
USE [ODS_TMUS2]
GO
/****** Object: Index [PK_CHUB_S_CONTACT] Script Date: 1/22/2019 9:33:08 PM ******/
ALTER TABLE [dbo].[CHUB_S_CONTACT] ADD CONSTRAINT [PK_CHUB_S_CONTACT] PRIMARY KEY CLUSTERED
(
[ROW_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
CREATE TABLE statements:
CHUB_S_CONTACT
USE [ODS_TMUS2]
GO
/****** Object: Table [dbo].[CHUB_S_CONTACT] Script Date: 1/22/2019 9:34:16 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CHUB_S_CONTACT](
[LogId] [int] NULL,
[ROW_ID] [nvarchar](15) NOT NULL,
[CREATED] [datetime2](7) NOT NULL,
[CREATED_BY] [nvarchar](15) NOT NULL,
[LAST_UPD] [datetime2](7) NOT NULL,
[LAST_UPD_BY] [nvarchar](15) NOT NULL,
[DCKING_NUM] [numeric](22, 7) NULL,
[MODIFICATION_NUM] [numeric](10, 0) NOT NULL,
[CONFLICT_ID] [nvarchar](15) NOT NULL,
[PAR_ROW_ID] [nvarchar](15) NOT NULL,
[ACTIVE_FLG] [nchar](1) NOT NULL,
[BU_ID] [nvarchar](15) NOT NULL,
[COURT_PAY_FLG] [nchar](1) NOT NULL,
[DISA_CLEANSE_FLG] [nchar](1) NOT NULL,
[DISP_IMG_AUTH_FLG] [nchar](1) NOT NULL,
[EMAIL_SR_UPD_FLG] [nchar](1) NOT NULL,
[EMP_FLG] [nchar](1) NOT NULL,
[FST_NAME] [nvarchar](50) NOT NULL,
[INVSTGTR_FLG] [nchar](1) NOT NULL,
[LAST_NAME] [nvarchar](50) NOT NULL,
[PERSON_UID] [nvarchar](100) NOT NULL,
[PO_PAY_FLG] [nchar](1) NOT NULL,
[PRIV_FLG] [nchar](1) NOT NULL,
[PROSPECT_FLG] [nchar](1) NOT NULL,
[PTSHP_CONTACT_FLG] [nchar](1) NOT NULL,
[PTSHP_KEY_CON_FLG] [nchar](1) NOT NULL,
[SEND_SURVEY_FLG] [nchar](1) NOT NULL,
[SPEAKER_FLG] [nchar](1) NOT NULL,
[SUPPRESS_EMAIL_FLG] [nchar](1) NOT NULL,
[SUPPRESS_FAX_FLG] [nchar](1) NOT NULL,
[SUSPECT_FLG] [nchar](1) NOT NULL,
[SUSP_WTCH_FLG] [nchar](1) NOT NULL,
[AGENT_FLG] [nchar](1) NULL,
[ENTERPRISE_FLAG] [nchar](1) NULL,
[MEMBER_FLG] [nchar](1) NULL,
[OK_TO_SAMPLE_FLG] [nchar](1) NULL,
[PROVIDER_FLG] [nchar](1) NULL,
[PR_REP_DNRM_FLG] [nchar](1) NULL,
[PR_REP_MANL_FLG] [nchar](1) NULL,
[PR_REP_SYS_FLG] [nchar](1) NULL,
[SEND_FIN_FLG] [nchar](1) NULL,
[SEND_NEWS_FLG] [nchar](1) NULL,
[SEND_PROMOTES_FLG] [nchar](1) NULL,
[SUPPRESS_CALL_FLG] [nchar](1) NULL,
[SUPPRESS_MAIL_FLG] [nchar](1) NULL,
[ANNL_INCM_EXCH_DT] [datetime2](7) NULL,
[ASGN_USR_EXCLD_FLG] [nchar](1) NULL,
[CALL_FLG] [nchar](1) NULL,
[CON_CREATED_DT] [datetime2](7) NULL,
[DB_LAST_UPD] [datetime2](7) NULL,
[ANNL_INCM_CURCY_CD] [nvarchar](20) NULL,
[COMMENTS] [nvarchar](255) NULL,
[CREATOR_LOGIN] [nvarchar](50) NULL,
[CURR_PRI_LST_ID] [nvarchar](15) NULL,
[CUST_STAT_CD] [nvarchar](30) NULL,
[CUST_VALUE_CD] [nvarchar](30) NULL,
[DB_LAST_UPD_SRC] [nvarchar](50) NULL,
[EMP_NUM] [nvarchar](30) NULL,
[INTEGRATION_ID] [nvarchar](30) NULL,
[OWNER_LOGIN] [nvarchar](50) NULL,
[OWNER_PER_ID] [nvarchar](15) NULL,
[PER_TITLE] [nvarchar](15) NULL,
[PER_TITLE_SUFFIX] [nvarchar](15) NULL,
[VETERAN_FLG] [nchar](1) NOT NULL,
[X_EN_SSN] [nvarchar](50) NULL,
[X_EN_SSN_KEY] [nvarchar](110) NULL,
[X_IDEN_EXP_DT] [datetime2](7) NULL,
[X_IDEN_NUM] [nvarchar](50) NULL,
[X_IDEN_STATE_CD] [nvarchar](50) NULL,
[X_IDEN_TYPE_CD] [nvarchar](50) NULL,
[LOG_POSITION_NUMBER] [nvarchar](50) NULL,
[ORACLE_SCN] [numeric](38, 0) NULL,
[OPERATION_CODE] [nvarchar](1) NULL,
[COMMIT_TIMESTAMP] [datetime2](7) NULL,
CONSTRAINT [PK_CHUB_S_CONTACT] PRIMARY KEY CLUSTERED
(
[ROW_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CHUB_S_CON_ADDR
USE [ODS_TMUS2]
GO
/****** Object: Table [dbo].[CHUB_S_CON_ADDR] Script Date: 1/22/2019 9:35:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CHUB_S_CON_ADDR](
[LogId] [int] NOT NULL,
[ROW_ID] [nvarchar](15) NOT NULL,
[CREATED] [datetime2](7) NOT NULL,
[CREATED_BY] [nvarchar](15) NOT NULL,
[LAST_UPD] [datetime2](7) NULL,
[LAST_UPD_BY] [nvarchar](15) NOT NULL,
[MODIFICATION_NUM] [numeric](10, 0) NOT NULL,
[CONFLICT_ID] [nvarchar](15) NOT NULL,
[ACTIVE_FLG] [nchar](1) NOT NULL,
[ADDR_PER_ID] [nvarchar](15) NOT NULL,
[BL_ADDR_FLG] [nchar](1) NOT NULL,
[FRAUD_FLG] [nchar](1) NOT NULL,
[MAIN_ADDR_FLG] [nchar](1) NOT NULL,
[RELATION_TYPE_CD] [nvarchar](30) NOT NULL,
[SHIP_ADDR_FLG] [nchar](1) NOT NULL,
[ALIGNMENT_FLG] [nchar](1) NULL,
[DB_LAST_UPD] [datetime2](7) NULL,
[DEA_EXPR_DT] [datetime2](7) NULL,
[END_DT] [datetime2](7) NULL,
[START_DT] [datetime2](7) NULL,
[ACCNT_ID] [nvarchar](15) NULL,
[BU_ID] [nvarchar](15) NULL,
[CONTACT_ID] [nvarchar](15) NULL,
[DB_LAST_UPD_SRC] [nvarchar](50) NULL,
[X_ACTIVE_FLG] [nchar](1) NULL,
[X_COMM_MTHD] [nvarchar](50) NULL,
[X_OPT_IN] [nchar](1) NULL,
[X_STATUS] [nvarchar](50) NULL,
[LOG_POSITION_NUMBER] [nvarchar](50) NULL,
[ORACLE_SCN] [numeric](38, 0) NULL,
[OPERATION_CODE] [nvarchar](1) NULL,
[COMMIT_TIMESTAMP] [datetime2](7) NULL,
CONSTRAINT [PK_CHUB_S_CON_ADDR] PRIMARY KEY CLUSTERED
(
[ROW_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
--Quote me
January 28, 2019 at 9:50 am
Having just read the additional info you posted, this may help. To speed up the query that is apparently a sub-query, you really need two new indexes:
SELECT
[SC].[FST_NAME]
, [SC].[LAST_NAME]
, [SCA].[ADDR_PER_ID]
FROM
[ODS_TMUS2].[dbo].[CHUB_S_CONTACT] AS [SC]
INNER JOIN [ODS_TMUS2].[dbo].[CHUB_S_CON_ADDR] AS [SCA]
ON [SC].[ROW_ID] = [SCA].[CONTACT_ID];
GO
/*
To improve the above query, returning just 3 columns of data (yes, 58+ million rows of data), you need two NCI's:
*/
CREATE NONCLUSTERED INDEX ix_ChubSContact_RowId ON [dbo].[CHUB_S_CONTACT](ROW_ID) INCLUDE (FST_NAME,LAST_NAME);
CREATE NONCLUSTERED INDEX ix_ChubSConAddr_ContactId ON [dbo].[CHUB_S_CON_ADDR](CONTACT_ID) INCLUDE (ADDR_PER_ID);
I will start looking at the additional info you have posted, but you may want to post the execution plan for the view you are trying to improve. Give these indexes a try and let us know.
February 9, 2019 at 5:36 pm
Lynn Pettis thanks.
I've added
CREATE NONCLUSTERED INDEX ix_ChubSContact_RowId ON [dbo].[CHUB_S_CONTACT](ROW_ID) INCLUDE (FST_NAME,LAST_NAME);
the other already exists. as 'CHUB_S_CON_ADDR'
I find that the subquery now uses these two recommended indexes. Attached before and after for just the subquery.
I considered that the next index to add should be for the WHERE SAP.ADDR_TYPE_CD = 'ADDRESS' filter of the view, and I find it also already exists:
USE
[ODS_TMUS2]
GO
/****** Object: Index [NCI_01_ADDR_TYPE_CD] Script Date: 2/9/2019 4:02:34 PM ******/
CREATE NONCLUSTERED INDEX [NCI_01_ADDR_TYPE_CD] ON [dbo].[CHUB_S_ADDR_PER]
(
[ADDR_TYPE_CD] ASC
)
INCLUDE ( [ROW_ID],
[ADDR_NAME],
[ADDR],
[CITY],
[COUNTRY],
[STATE],
[ZIPCODE]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
that final WHERE returns 50,000 rows
select count(*) --returns count 49,409,515
from ODS_TMUS2..CHUB_S_ADDR_PER sap
WHERE
SAP.ADDR_TYPE_CD = 'ADDRESS'
The performance of the view returning top 1000 rows is not yet improved. Attached query exec plan.
There are many tables involved in the views JOINs . Will you kindly advise the indexes you would add, given the JOINS and the columns that are able to be retrieved by those JOINS?
--Quote me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply