November 29, 2007 at 2:31 pm
Hi Matt,
I ran just the selects in the views themselves and they seemed to take just as long as running the entire query. I think we are right to be focused on trying to improve these two specific views.
November 29, 2007 at 2:54 pm
k - let's focus on just one. Say - the one I updated. What indexes do you have on the tables involved?
Also - did you get a chance to look at my other question (how to make that view only return just a handful of results).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 29, 2007 at 3:22 pm
Here are the indexes on those tables:
USE [ALTA_Staging]
GO
/****** Object: Table [dbo].[DB_HTXLINC] Script Date: 11/29/2007 15:20:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DB_HTXLINC](
[P_K] [numeric](16, 0) NOT NULL,
[N_K_HTXTITL_HTXLINC] [numeric](16, 0) NULL,
[F_K_HTXTITL_HTXLINC] [numeric](16, 0) NULL,
[DHL_LINC_RIGHTS_ID_GRP] [varchar](14) NULL,
[DHL_LINC_NBR] [varchar](10) NULL,
[DHL_RIGHTS_IND] [char](1) NULL,
[DHL_RIGHTS_NBR] [decimal](3, 0) NULL,
[DHL_LINC_NBR_REVRSD] [varchar](10) NULL,
[JRNL_TIMESTAMP] [datetime] NULL,
[JRNL_JSN] [varchar](20) NULL,
[JRNL_PROGRAM_NAME] [varchar](8) NULL,
CONSTRAINT [DB_HTXLINC_PK] PRIMARY KEY CLUSTERED
(
[P_K] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[DB_HTXTITL] Script Date: 11/29/2007 15:20:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DB_HTXTITL](
[P_K] [numeric](16, 0) NOT NULL,
[N_K_HTXTITL_HTXDOCU] [numeric](16, 0) NULL,
[N_K_HTXTITL_HTXLINC] [numeric](16, 0) NULL,
[DHT_TITLE_REFRNC_NBR] [varchar](12) NULL,
[DHT_FILLER4_FLR] [varchar](4) NULL,
[JRNL_TIMESTAMP] [datetime] NULL,
[JRNL_JSN] [varchar](20) NULL,
[JRNL_PROGRAM_NAME] [varchar](8) NULL,
CONSTRAINT [DB_HTXTITL_PK] PRIMARY KEY CLUSTERED
(
[P_K] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[DB_PROPRTY] Script Date: 11/29/2007 15:21:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DB_PROPRTY](
[P_K] [numeric](16, 0) NOT NULL,
[N_K_PROP_RIGHTS] [numeric](16, 0) NULL,
[N_K_PROP_RLDLAND] [numeric](16, 0) NULL,
[DPR_LINC_NBR] [varchar](10) NULL,
[DPR_CONDMNM_SHARES_NBR] [decimal](7, 2) NULL,
[DPR_LAND_AREA_4] [decimal](10, 4) NULL,
[DPR_LAND_AREA_DISPLY_IND] [char](1) NULL,
[DPR_CONTRLLD_LAND_IND] [char](1) NULL,
[DPR_MINRL_IND] [char](1) NULL,
[DPR_MINRL_WORK_IND] [char](1) NULL,
[DPR_LEGAL_TEXT_IND] [char](1) NULL,
[DPR_NON_PATENT_IND] [char](1) NULL,
[DPR_MUNC_CODE] [varchar](4) NULL,
[DPR_DISTRCT_IND] [char](1) NULL,
[DPR_PROPRTY_PARCEL_ID_GRP] [varchar](41) NULL,
[DPR_LANDREF_TYPE_CODE] [char](1) NULL,
[DPR_PROPRTY_PARCEL_ID] [varchar](40) NULL,
[JRNL_TIMESTAMP] [datetime] NULL,
[JRNL_JSN] [varchar](20) NULL,
[JRNL_PROGRAM_NAME] [varchar](8) NULL,
CONSTRAINT [DB_PROPRTY_PK] PRIMARY KEY CLUSTERED
(
[P_K] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
I haven't figured out yet how to change the query to return only the rows needed. I have inherited this SP. 🙂
November 29, 2007 at 3:27 pm
Sorry...see this one.
USE [ALTA_Staging]
GO
/****** Object: Table [dbo].[DB_HTXLINC] Script Date: 11/29/2007 15:27:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DB_HTXLINC](
[P_K] [numeric](16, 0) NOT NULL,
[N_K_HTXTITL_HTXLINC] [numeric](16, 0) NULL,
[F_K_HTXTITL_HTXLINC] [numeric](16, 0) NULL,
[DHL_LINC_RIGHTS_ID_GRP] [varchar](14) NULL,
[DHL_LINC_NBR] [varchar](10) NULL,
[DHL_RIGHTS_IND] [char](1) NULL,
[DHL_RIGHTS_NBR] [decimal](3, 0) NULL,
[DHL_LINC_NBR_REVRSD] [varchar](10) NULL,
[JRNL_TIMESTAMP] [datetime] NULL,
[JRNL_JSN] [varchar](20) NULL,
[JRNL_PROGRAM_NAME] [varchar](8) NULL,
CONSTRAINT [DB_HTXLINC_PK] PRIMARY KEY CLUSTERED
(
[P_K] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [F_K_HTXTITL_HTXLINC_3110] ON [dbo].[DB_HTXLINC]
(
[F_K_HTXTITL_HTXLINC] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HTXLINC_IX_3110] ON [dbo].[DB_HTXLINC]
(
[DHL_LINC_NBR_REVRSD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_DB_HTXLINC_LincNumber] ON [dbo].[DB_HTXLINC]
(
[DHL_LINC_NBR] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [N_K_HTXTITL_HTXLINC_3110] ON [dbo].[DB_HTXLINC]
(
[N_K_HTXTITL_HTXLINC] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Table [dbo].[DB_HTXTITL] Script Date: 11/29/2007 15:27:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DB_HTXTITL](
[P_K] [numeric](16, 0) NOT NULL,
[N_K_HTXTITL_HTXDOCU] [numeric](16, 0) NULL,
[N_K_HTXTITL_HTXLINC] [numeric](16, 0) NULL,
[DHT_TITLE_REFRNC_NBR] [varchar](12) NULL,
[DHT_FILLER4_FLR] [varchar](4) NULL,
[JRNL_TIMESTAMP] [datetime] NULL,
[JRNL_JSN] [varchar](20) NULL,
[JRNL_PROGRAM_NAME] [varchar](8) NULL,
CONSTRAINT [DB_HTXTITL_PK] PRIMARY KEY CLUSTERED
(
[P_K] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [DARSCALCHEADER_3100] ON [dbo].[DB_HTXTITL]
(
[DHT_TITLE_REFRNC_NBR] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Table [dbo].[DB_PROPRTY] Script Date: 11/29/2007 15:27:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DB_PROPRTY](
[P_K] [numeric](16, 0) NOT NULL,
[N_K_PROP_RIGHTS] [numeric](16, 0) NULL,
[N_K_PROP_RLDLAND] [numeric](16, 0) NULL,
[DPR_LINC_NBR] [varchar](10) NULL,
[DPR_CONDMNM_SHARES_NBR] [decimal](7, 2) NULL,
[DPR_LAND_AREA_4] [decimal](10, 4) NULL,
[DPR_LAND_AREA_DISPLY_IND] [char](1) NULL,
[DPR_CONTRLLD_LAND_IND] [char](1) NULL,
[DPR_MINRL_IND] [char](1) NULL,
[DPR_MINRL_WORK_IND] [char](1) NULL,
[DPR_LEGAL_TEXT_IND] [char](1) NULL,
[DPR_NON_PATENT_IND] [char](1) NULL,
[DPR_MUNC_CODE] [varchar](4) NULL,
[DPR_DISTRCT_IND] [char](1) NULL,
[DPR_PROPRTY_PARCEL_ID_GRP] [varchar](41) NULL,
[DPR_LANDREF_TYPE_CODE] [char](1) NULL,
[DPR_PROPRTY_PARCEL_ID] [varchar](40) NULL,
[JRNL_TIMESTAMP] [datetime] NULL,
[JRNL_JSN] [varchar](20) NULL,
[JRNL_PROGRAM_NAME] [varchar](8) NULL,
CONSTRAINT [DB_PROPRTY_PK] PRIMARY KEY CLUSTERED
(
[P_K] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [DARSCALCHEADER_2340] ON [dbo].[DB_PROPRTY]
(
[DPR_LINC_NBR] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_DB_PROPRTY_LincNbrEtc] ON [dbo].[DB_PROPRTY]
(
[DPR_LINC_NBR] ASC,
[DPR_NON_PATENT_IND] ASC,
[DPR_MUNC_CODE] ASC,
[DPR_PROPRTY_PARCEL_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_DB_PROPRTY_MuncCode] ON [dbo].[DB_PROPRTY]
(
[DPR_MUNC_CODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_DB_PROPRTY_PropertyParcelID] ON [dbo].[DB_PROPRTY]
(
[DPR_PROPRTY_PARCEL_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
November 29, 2007 at 3:28 pm
That's all the indexes ? Just a clustered primary key on the P_K column and no non-clustered indexes ?
It seems that you are joining frequently on columns like N_K_HTXTITL_HTXLINC, so maybe secondary non-clustered indexes on the columns that you typically join on would help ?
[Edit] Nevermind, I see your revised version has the non-clustered indexes.
November 29, 2007 at 3:38 pm
There are 3 non clustered on DB_PROPRTY
CREATE NONCLUSTERED INDEX [IX_DB_PROPRTY_LincNbrEtc] ON [dbo].[DB_PROPRTY]
(
[DPR_LINC_NBR] ASC,
[DPR_NON_PATENT_IND] ASC,
[DPR_MUNC_CODE] ASC,
[DPR_PROPRTY_PARCEL_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_DB_PROPRTY_MuncCode] ON [dbo].[DB_PROPRTY]
(
[DPR_MUNC_CODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_DB_PROPRTY_PropertyParcelID] ON [dbo].[DB_PROPRTY]
(
[DPR_PROPRTY_PARCEL_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
November 29, 2007 at 6:45 pm
Did u tried update statistics for the tables involved in this problem statement? earlier by mistake i said dbcc updateusage....
based on my experiences this is the one thing which resolves performance issues when moving from 2000 to 2005
another is the execution plan becomes bad and alter view doesn't recreate execution plan in this case.....try dropping the object and create the object again to have a fresh execution plan (i mean drop the view)
lets see after these two thing still you have this issue.....
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
November 29, 2007 at 7:31 pm
Good thing to walk away some times....:) your TWO subqueries look like they can be done as ONE subquery
Select
F_K_HTXTITL_HTXLINC,
Min(DHL_LINC_NBR) as MinDlincNBR,
Count(*) Linccount into #tempmin
FROM ALTA..DB_HTXLINC
group by F_K_HTXTITL_HTXLINC
Personally I'd consider shooting this to a temp table and clustered indexing this on F_K_whatever.
drop table #tempmin
Select F_K_HTXTITL_HTXLINC,Min(DHL_LINC_NBR) as MinDlincNBR,Count(*) Linccount into #tempmin
FROM ALTA..DB_HTXLINC
group by F_K_HTXTITL_HTXLINC
exec('create unique clustered index pk_tmpmin on #tempmin(F_K_HTXTITL_HTXLINC)')
Second point:
You HT table should have an index with included columns so as to make this a covered query:
CREATE NONCLUSTERED INDEX [F_K_HTXTITL_HTXLINC_3110] ON [dbo].[DB_HTXLINC]
(
[F_K_HTXTITL_HTXLINC] ASC,
[DHL_LINC_NBR] ASC
)
INCLUDE ( [DHL_RIGHTS_IND])
WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
same on property
CREATE NONCLUSTERED INDEX [IX_DB_PROPRTY_LincNbrEtc] ON [dbo].[DB_PROPRTY]
(
[DPR_LINC_NBR] ASC
)
INCLUDE
(
[DPR_NON_PATENT_IND],
[DPR_MUNC_CODE],
[DPR_PROPRTY_PARCEL_ID_GRP]
)
WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
Once you do that - it looks like:
Select HTXTITL.DHT_TITLE_REFRNC_NBR as TITLE_REFRNC_NBR,
HTXLINC.DHL_LINC_NBR as LINC_NBR,
HTXLINC.DHL_RIGHTS_IND as RIGHTS_IND,
PROPRTY.DPR_NON_PATENT_IND as NON_PATENT_IND,
PROPRTY.DPR_MUNC_CODE MUNC_CODE,
PROPRTY.DPR_PROPRTY_PARCEL_ID PROPRTY_PARCEL_ID,
Linccount
from
ALTA..DB_HTXLINC HTXLINC
inner join
ALTA..DB_HTXTITL HTXTITL
on HTXTITL.P_K = HTXLINC.F_K_HTXTITL_HTXLINC
inner join
#tempmin HTXLINCB
on HTXLINCB.F_K_HTXTITL_HTXLINC = HTXLINC.F_K_HTXTITL_HTXLINC
and HTXLINC.DHL_LINC_NBR = MinDlincNBR
left outer join
ALTA..DB_PROPRTY PROPRTY
on PROPRTY.DPR_LINC_NBR = HTXLINC.DHL_LINC_NBR
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 30, 2007 at 7:31 am
Statistics have been updated using Update Statistics - twice now.
Indexes have been rebuilt.
I dropped and readded the views as Prakash suggested. It made no difference. The query is still dreadfully slow in 2005. I will look at Matt's suggestsions next.
November 30, 2007 at 7:55 am
Hi Matt,
I got the following errors when trying to create a temp table in that view:
Msg 156, Level 15, State 1, Procedure vwMinHTOXLinc, Line 20
Incorrect syntax near the keyword 'drop'.
Msg 4508, Level 16, State 1, Procedure vwMinHTOXLinc, Line 22
Views or functions are not allowed on temporary tables. Table names that begin with '#' denote temporary tables.
Msg 4508, Level 16, State 1, Procedure vwMinHTOXLinc, Line 24
Views or functions are not allowed on temporary tables. Table names that begin with '#' denote temporary tables.
Msg 4508, Level 16, State 1, Procedure vwMinHTOXLinc, Line 38
Views or functions are not allowed on temporary tables. Table names that begin with '#' denote temporary tables.
Microsoft has confirmed to me today that the issue is a bug in SQL Server 2005. So I guess I need to think about different ways of writing this query in hopes the optimizer will handle it better. Any suggestions on that front are most appreciated.
November 30, 2007 at 8:27 am
I have seen cases where adding the (WITH FASTFIRSTROW) hint can change the query plan and improve the overall performance.
However, usual disclaimers apply - use hints like this as a last resort, and be aware that there will also be situations where it makes things worse.
November 30, 2007 at 8:39 am
what kinda bug microsoft confirmed??
is it specific to a functionality? recreatable??
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
November 30, 2007 at 8:54 am
Statistics have been updated using Update Statistics - twice now.
Has that affected the index scan you mentioned earlier, are these now an index seek in the execution plan? You can check if the stats are there by querying sys.stats with the name of the index.
Are we still at 40 seconds on 2005?
[Edit] Other than this being an optimiser bug, if we are still at 40secs then I would go back to the execution plan and trace it up starting bottom right (I expect your execution plan is complex) comparing the 2000 plan with the 2005 plan looking for physical differences in steps and steps that lead to a big difference in row counts.
Allen
November 30, 2007 at 10:21 am
Jessica (11/30/2007)
Hi Matt,I got the following errors when trying to create a temp table in that view:
Msg 156, Level 15, State 1, Procedure vwMinHTOXLinc, Line 20
Incorrect syntax near the keyword 'drop'.
Msg 4508, Level 16, State 1, Procedure vwMinHTOXLinc, Line 22
Views or functions are not allowed on temporary tables. Table names that begin with '#' denote temporary tables.
Msg 4508, Level 16, State 1, Procedure vwMinHTOXLinc, Line 24
Views or functions are not allowed on temporary tables. Table names that begin with '#' denote temporary tables.
Msg 4508, Level 16, State 1, Procedure vwMinHTOXLinc, Line 38
Views or functions are not allowed on temporary tables. Table names that begin with '#' denote temporary tables.
Microsoft has confirmed to me today that the issue is a bug in SQL Server 2005. So I guess I need to think about different ways of writing this query in hopes the optimizer will handle it better. Any suggestions on that front are most appreciated.
I'm still thinking those views are just holding you back right now. I wasn't thinking of putting this BACK into views - they're hurting you right now.
You can't create or delete table objects through there, and #temp tables aren't allowed. We could get around limitation #2 by making it a "real" table, but you'd still need to some way to repopulate the table (which you COULD do in the stored procedure running this whole thing).
Well - keep us posted on what Microsoft tells you as to root cause and how to fix it. I'm now curious what they will say.
If you want to keep fighting this, but possibly not use views to do it - let us know.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 14 posts - 31 through 43 (of 43 total)
You must be logged in to reply to this topic. Login to reply