March 5, 2009 at 8:46 am
Okay.
Was able to get acceptable times on the original query I was working with but now I'm working on a different one and am running into the same problem.
Following y'all's advice I have modified my DISTINCT to be a GROUP BY but that is not saving any time for the results to come back. Please note: the time I am worried about is the time it will take to process and return the results. I know it breaks the hearts of many DBAs out here but I am not concerned with the time it takes the database to process the actual request but how long it takes to get the data set.
Here's the queries in question ....
1. SELECT DGRP_ID,DGRP_TITLE FROM IREP_V_POSITION_STANDARDS WHERE WF_STOP BETWEEN '2/1/2009' AND '2/28/2009'
2. SELECT DGRP_ID,DGRP_TITLE FROM IREP_V_POSITION_STANDARDS WHERE WF_STOP BETWEEN '2/1/2009' AND '2/28/2009' GROUP BY DGRP_ID,DGRP_TITLE ORDER BY DGRP_ID,DGRP_TITLE
3. SELECT DISTINCT DGRP_ID,DGRP_TITLE FROM IREP_V_POSITION_STANDARDS WHERE WF_STOP BETWEEN '2/1/2009' AND '2/28/2009' ORDER BY DGRP_ID,DGRP_TITLE
# 1 runs in 2 seconds. Both # 2 and 3 run in 22 seconds.
# 1 returns 25333 rows
# 2 and 3 each return 81
For your viewing pleasure I have attached sql plans for each of the queries.
I'm quite sure that I have indexed everything possible as far as joins go in the tables that make up the view so I don't know where else to look .... and, quite frankly, am still really puzzled as to how both the GROUP BY and DISTINCT versions can return so much slower given the large difference in data returned.
Thanks for all your previous help as well as, hopefully, future help 🙂
Edit: In case anyone was wondering the effect ORDER BY would have on #1, there appears to be no effect .... at least in returning the data .... still 1-3 seconds consistently.
March 5, 2009 at 9:28 am
Is IREP_V_POSITION_STANDARDS a table or a view?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 5, 2009 at 9:38 am
As mentioned later in my post, it's a view.
I've read the Forum Etiquette but not sure how that applies to my question ..... unless you just always paste that into your posts.
If I posted code to totally recreate my tables, views and enough data to properly reflect what I'm doing ..... well, that would be a ton of code 😛
Not sure that anyone is going to want to populate 12 tables, 20+ indexes a view and then millions of rows of data just to help more accurately help me 😛
I should add that if it gets to the point where people don't think they can help me further without seeing the source then I will gladly create a script with most of that in there ..... just from my experience, when dealing with performance issues, SQL plans are what the gurus have always wanted to see so that is what I have gotten in the habit of posting 🙂
March 5, 2009 at 9:51 am
Putts (3/5/2009)
As mentioned later in my post, it's a view.I've read the Forum Etiquette but not sure how that applies to my question ..... unless you just always paste that into your posts.
If I posted code to totally recreate my tables, views and enough data to properly reflect what I'm doing ..... well, that would be a ton of code 😛
Not sure that anyone is going to want to populate 12 tables, 20+ indexes a view and then millions of rows of data just to help more accurately help me 😛
I should add that if it gets to the point where people don't think they can help me further without seeing the source then I will gladly create a script with most of that in there ..... just from my experience, when dealing with performance issues, SQL plans are what the gurus have always wanted to see so that is what I have gotten in the habit of posting 🙂
The view is a source to a report.
I'm sorry, just wanted to clarify. Without the view definition, which is, after all, pretty much all of the code - I can't help you. "My query is running slow" "Can we see it?" "No".
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 5, 2009 at 11:16 am
Okay, well here's everything I guess is pertinent for full troubleshooting of this..
View Definition
CREATE VIEW [dbo].[IREP_V_POSITION_STANDARDS] AS
SELECT SS.SERVICE_STANDARD, PS.SERVICE_STANDARD AS POSITION_SERVICE_STANDARD,
WFI.WS_INSTANCE_ID, WFI.WF_STOP, POS.JOB_POSITION, TTYP_TITLE,
WFI.WF_EFF_DATE, WFI.POLICY_NUMBER, DGRP_TITLE,DGRP.DGRP_ID,
WFI.WF_START,WS_DAYS_TOTAL,PD_DAYS_TOTAL,PD_COUNT,STATEDESC
FROM
IREP_T_WF_INFO WFI
INNER JOIN
IREP_T_WF_POSITION_INFO PINFO ON WFI.ID = PINFO.WFI_ID
INNER JOIN
IREP_T_DOCTYPE_GROUP_REL DGRL ON WFI.DOCTYPE_CD = DGRL.DCTP_ID
INNER JOIN
IREP_T_DOCTYPE_GROUP DGRP ON DGRL.DGRP_ID = DGRP.DGRP_ID
INNER JOIN
IREP_T_TRANSACTION_TYPES_DOCTYPES TTDT ON TTDT.DGRP_ID = DGRP.DGRP_ID
INNER JOIN
IREP_T_TRANSACTION_TYPES TTYP ON TTDT.TTYP_ID = TTYP.TTYP_ID
INNER JOIN
IREP_T_STANDARDS_DOC_TYPES SS ON (WFI.STATE_CODE=SS.STATE_ID) AND (SS.DGRP_ID=DGRP.DGRP_ID)
INNER JOIN
IREP_T_POSITION_STANDARDS_DOCTYPES PS ON DGRP.DGRP_ID = PS.DGRP_ID AND WFI.STATE_CODE = PS.STATE_ID AND PINFO.POSITION_ID = PS.POSITION_ID
INNER JOIN
IREP_T_POSITIONS POS ON PINFO.POSITION_ID = POS.ID
INNER JOIN
IREP_T_WF_STATUS SU ON WFI.WF_STATUS = SU.ID
INNER JOIN
FFG_STATES ST ON WFI.STATE_CODE = ST.STATEID
WHERE SU.WF_STATUS = 'Completed'
GO
Tables that make up that view....
CREATE TABLE [dbo].[IREP_T_DOCTYPE_GROUP](
[DGRP_ID] [int] IDENTITY(1,1) NOT NULL,
[DGRP_TITLE] [varchar](255) NOT NULL DEFAULT (''),
PRIMARY KEY CLUSTERED
(
[DGRP_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]
CREATE TABLE [dbo].[IREP_T_DOCTYPE_GROUP_REL](
[DGRL_ID] [int] IDENTITY(1,1) NOT NULL,
[DGRP_ID] [int] NOT NULL,
[DCTP_ID] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[DGRL_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]
CREATE TABLE [dbo].[IREP_T_POSITION_STANDARDS_DOCTYPES](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DGRP_ID] [int] NOT NULL,
[POSITION_ID] [int] NOT NULL,
[STATE_ID] [int] NOT NULL,
[SERVICE_STANDARD] [decimal](8, 2) NOT NULL DEFAULT ((0)),
PRIMARY KEY CLUSTERED
(
[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]
CREATE TABLE [dbo].[IREP_T_POSITIONS](
[ID] [int] IDENTITY(1,1) NOT NULL,
[JOB_POSITION] [char](50) NULL,
CONSTRAINT [PK_IREP_T_POSITIONS] PRIMARY KEY CLUSTERED
(
[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]
CREATE TABLE [dbo].[IREP_T_STANDARDS_DOC_TYPES](
[ID] [int] IDENTITY(1,1) NOT NULL,
[STATE_ID] [int] NOT NULL,
[SERVICE_STANDARD] [decimal](5, 2) NULL,
[DGRP_ID] [int] NULL,
PRIMARY KEY CLUSTERED
(
[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]
CREATE TABLE [dbo].[IREP_T_TRANSACTION_TYPES](
[TTYP_ID] [int] IDENTITY(1,1) NOT NULL,
[TTYP_TITLE] [varchar](500) NOT NULL DEFAULT (''),
[WTYP_ID] [int] NULL,
PRIMARY KEY CLUSTERED
(
[TTYP_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]
CREATE TABLE [dbo].[IREP_T_TRANSACTION_TYPES_DOCTYPES](
[TTDT_ID] [int] IDENTITY(1,1) NOT NULL,
[DGRP_ID] [int] NOT NULL,
[TTYP_ID] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[TTDT_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]
CREATE TABLE [dbo].[IREP_T_WF_INFO](
[ID] [int] IDENTITY(1,1) NOT NULL,
[WF_START] [datetime] NULL,
[WF_STOP] [datetime] NULL,
[WF_STATUS] [int] NULL,
[WS_INSTANCE_ID] [char](11) NOT NULL,
[WF_ID] [int] NULL,
[WF_MD_ID] [char](11) NULL,
[POLICY_NUMBER] [char](15) NULL,
[COMPANY_ID] [int] NULL,
[ASSIGNED_TO] [int] NULL,
[DOCTYPE_CD] [int] NULL,
[WF_EFF_DATE] [datetime] NULL,
[AGENCY_CODE] [varchar](50) NULL,
[STATE_CODE] [int] NULL,
[DBA_NM] [varchar](1000) NOT NULL DEFAULT (''),
CONSTRAINT [PK_IREP_WF_INFO] PRIMARY KEY CLUSTERED
(
[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]
CREATE TABLE [dbo].[IREP_T_WF_POSITION_INFO](
[ID] [int] IDENTITY(1,1) NOT NULL,
[WS_INSTANCE_ID] [char](11) NOT NULL,
[POSITION_ID] [int] NOT NULL,
[WS_DAYS_TOTAL] [int] NOT NULL,
[WS_COUNT] [int] NULL,
[PD_DAYS_TOTAL] [int] NULL,
[PD_COUNT] [int] NULL,
[WF_WS_DAYS_TOTAL] [int] NULL,
[WF_PD_DAYS_TOTAL] [int] NULL,
[DAY_COMPLETED] [datetime] NULL,
[WFI_ID] [int] NULL,
CONSTRAINT [PK_IREP_T_WF_POSITION_INFO] PRIMARY KEY CLUSTERED
(
[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]
CREATE TABLE [dbo].[IREP_T_WF_STATUS](
[ID] [int] NOT NULL,
[WF_STATUS] [char](50) NULL,
CONSTRAINT [PK_IREP_WF_STATUS] PRIMARY KEY CLUSTERED
(
[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]
ALTER TABLE [dbo].[IREP_T_STANDARDS_DOC_TYPES] WITH CHECK ADD CONSTRAINT [FK_IREP_STANDARDS_DOC_TYPES_IREP_STANDARDS_DOC_TYPES] FOREIGN KEY([DGRP_ID])
REFERENCES [dbo].[IREP_T_DOCTYPE_GROUP] ([DGRP_ID])
ALTER TABLE [dbo].[IREP_T_STANDARDS_DOC_TYPES] CHECK CONSTRAINT [FK_IREP_STANDARDS_DOC_TYPES_IREP_STANDARDS_DOC_TYPES]
ALTER TABLE [dbo].[IREP_T_WF_INFO] WITH CHECK ADD CONSTRAINT [FK_IREP_WF_INFO_IREP_INBOXES] FOREIGN KEY([ASSIGNED_TO])
REFERENCES [dbo].[IREP_T_INBOXES] ([ID])
ALTER TABLE [dbo].[IREP_T_WF_INFO] CHECK CONSTRAINT [FK_IREP_WF_INFO_IREP_INBOXES]
ALTER TABLE [dbo].[IREP_T_WF_INFO] WITH CHECK ADD CONSTRAINT [FK_IREP_WF_INFO_IREP_WF_DESC] FOREIGN KEY([WF_ID])
REFERENCES [dbo].[IREP_T_WF_DESC] ([ID])
ALTER TABLE [dbo].[IREP_T_WF_INFO] CHECK CONSTRAINT [FK_IREP_WF_INFO_IREP_WF_DESC]
ALTER TABLE [dbo].[IREP_T_WF_INFO] WITH CHECK ADD CONSTRAINT [FK_IREP_WF_INFO_IREP_WF_DOCTYPES] FOREIGN KEY([DOCTYPE_CD])
REFERENCES [dbo].[IREP_T_WF_DOCTYPES] ([ID])
ALTER TABLE [dbo].[IREP_T_WF_INFO] CHECK CONSTRAINT [FK_IREP_WF_INFO_IREP_WF_DOCTYPES]
ALTER TABLE [dbo].[IREP_T_WF_INFO] WITH CHECK ADD CONSTRAINT [FK_IREP_WF_INFO_IREP_WF_STATUS] FOREIGN KEY([WF_STATUS])
REFERENCES [dbo].[IREP_T_WF_STATUS] ([ID])
ALTER TABLE [dbo].[IREP_T_WF_INFO] CHECK CONSTRAINT [FK_IREP_WF_INFO_IREP_WF_STATUS]
ALTER TABLE [dbo].[IREP_T_WF_POSITION_INFO] WITH CHECK ADD CONSTRAINT [FK_IREP_T_WF_POSITION_INFO_IREP_POSITIONS] FOREIGN KEY([POSITION_ID])
REFERENCES [dbo].[IREP_T_POSITIONS] ([ID])
ALTER TABLE [dbo].[IREP_T_WF_POSITION_INFO] CHECK CONSTRAINT [FK_IREP_T_WF_POSITION_INFO_IREP_POSITIONS]
ALTER TABLE [dbo].[IREP_T_WF_POSITION_INFO] WITH CHECK ADD CONSTRAINT [FK_IREP_T_WF_POSITION_INFO_IREP_T_WF_INFO] FOREIGN KEY([WFI_ID])
REFERENCES [dbo].[IREP_T_WF_INFO] ([ID])
ALTER TABLE [dbo].[IREP_T_WF_POSITION_INFO] CHECK CONSTRAINT [FK_IREP_T_WF_POSITION_INFO_IREP_T_WF_INFO]
Indexes on those tables
CREATE NONCLUSTERED INDEX [IX_IREP_T_DOCTYPE_GROUP_REL_DCTP_ID] ON [dbo].[IREP_T_DOCTYPE_GROUP_REL]
(
[DCTP_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) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_IREP_T_DOCTYPE_GROUP_REL_DGRP_ID] ON [dbo].[IREP_T_DOCTYPE_GROUP_REL]
(
[DGRP_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) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_IREP_T_POSITION_STANDARDS_DOCTYPES_STATE_ID] ON [dbo].[IREP_T_POSITION_STANDARDS_DOCTYPES]
(
[STATE_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) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_IREP_T_POSITION_STANDARDS_DOCTYPES_POSITION_ID] ON [dbo].[IREP_T_POSITION_STANDARDS_DOCTYPES]
(
[POSITION_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) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_IREP_T_POSITION_STANDARDS_DOCTYPES_DGRP_ID] ON [dbo].[IREP_T_POSITION_STANDARDS_DOCTYPES]
(
[DGRP_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) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_IREP_T_STANDARDS_DOC_TYPES_DGRP_ID] ON [dbo].[IREP_T_STANDARDS_DOC_TYPES]
(
[DGRP_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) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_IREP_T_STANDARDS_DOC_TYPES_STATE_ID] ON [dbo].[IREP_T_STANDARDS_DOC_TYPES]
(
[STATE_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) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_IREP_T_TRANSACTION_TYPES_DOCTYPES_DGRP_ID] ON [dbo].[IREP_T_TRANSACTION_TYPES_DOCTYPES]
(
[DGRP_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) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_IREP_T_TRANSACTION_TYPES_DOCTYPES_TTYP_ID] ON [dbo].[IREP_T_TRANSACTION_TYPES_DOCTYPES]
(
[TTYP_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) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_IREP_T_WF_INFO] ON [dbo].[IREP_T_WF_INFO]
(
[STATE_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) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_IREP_T_WF_INFO_1] ON [dbo].[IREP_T_WF_INFO]
(
[AGENCY_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) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_IREP_T_WF_INFO_2] ON [dbo].[IREP_T_WF_INFO]
(
[ASSIGNED_TO] 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) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_IREP_T_WF_INFO_WF_STATUS] ON [dbo].[IREP_T_WF_INFO]
(
[WF_STATUS] 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) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_IREP_T_WF_INFO_WS_INSTANCE_ID] ON [dbo].[IREP_T_WF_INFO]
(
[WS_INSTANCE_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) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_IREP_T_WF_POSITION_INFO_POSITION_ID] ON [dbo].[IREP_T_WF_POSITION_INFO]
(
[POSITION_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) ON [PRIMARY]
FFG_STATES is actually a linked view from a different server and its source table looks like..
CREATE TABLE [dbo].[FFG_STATES](
[STATEID] [int] NOT NULL,
[STATEDESC] [char](100) NOT NULL,
[STATEABBR] [char](4) NOT NULL,
[COMPANY_0_VALID] [int] NOT NULL,
[COMPANY_1_VALID] [int] NOT NULL,
[COMPANY_2_VALID] [int] NULL,
[BA_VALID] [int] NOT NULL,
[BOP_VALID] [int] NOT NULL,
[CPP_VALID] [int] NOT NULL,
[FP_VALID] [int] NOT NULL,
[HP_VALID] [int] NOT NULL,
[PA_VALID] [int] NOT NULL,
[PUL_VALID] [int] NOT NULL,
[RV_VALID] [int] NOT NULL,
[WC_VALID] [int] NOT NULL,
[YAC_VALID] [int] NOT NULL,
[CUL_VALID] [int] NULL,
CONSTRAINT [PK_FFG_STATES] PRIMARY KEY CLUSTERED
(
[STATEID] 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
If you feel you need data I can try to write something to generate it but we're talking millions of rows and quite a few tables so that will take a while.
To me, the question is more abstract than you're trying to make it. It all comes down to a full SELECT running and returning results much faster than a GROUP BY or DISTINCT all on the same chunk of data. The change in performance should be able to be tracked within the SQL plan but it's beyond my ability to see where. I'm not sure the "my query is performing quickly ... help me" sentiment really applies as much here but if you feel you can dig more efficiently with the structure then there it is.
March 5, 2009 at 11:31 am
Looking at the plan, 73% of the estimated cost comes from 2 joins and a 'not null' filter, which I've reconstructed as
Select
AUND.AUND_INBOX,
RPT_SQL_CL.dbo.FFG_FX_JUST_DATE(A.WF_START) as Expr1007
from
RPT_SQL_CL.dbo.IREP_T_WF_INFO as A
right join -- 13,988,837 rows (19% cost)
RPT_SQL_CL.dbo.IREP_T_UNDW_STAFF
on
A.ASSIGNED_TO = RPT_SQL_CL.dbo.IREP_T_UNDW_STAFF.UNDS_STAFF_INBOX
join -- 13,980,811 rows (50% cost)
RPT_SQL_CL.dbo.IREP_T_AGENCY_UNDW as AUND
on
AUND.AUND_AGENCY_CODE=A.AGENCY_CODE
where
A.ASSIGNED_TO IS NOT NULL -- 22,079,800 rows(14% cost)
The hash join (for the DISTINCT) adds another 14% to the cost.
To do much further analysis really needs the original source of the view IREP_V_DAILY_COUNTS_TERRRITORY (reconstructing it from the plan takes too long!) and DDL for the tables IREP_T_WF_INFO, IREP_T_UNDW_STAFF, IREP_T_AGENCY_UNDW, IREP_T_EMPLOYEES and IREP_T_CDIR_EMPLOYEES.
Also I'd be interested to see the source of the User-defined function FFG_FX_JUST_DATE since, although it doesn't add the the estimated cost, it appears the estimate is for it to be called over 13 million times.
Derek
March 5, 2009 at 11:44 am
Derek Dongray (3/5/2009)
Looking at the plan....
Sorry for a bit of confusion, Derek, but have brought up a new query that is doing something very similar here on page 4. The newer query is actually easier to analyze as the source view is more straight-forward.
March 5, 2009 at 11:47 am
Derek Dongray (3/5/2009)
Also I'd be interested to see the source of the User-defined function FFG_FX_JUST_DATE since, although it doesn't add the the estimated cost
Scalar functions never do. It's one of the problems with them. No matter what the function does, the call to it will appear as 0% cost in an execution plan. The cost estimate in an execution plan cannot be trusted for a user-defined function.
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
March 5, 2009 at 11:47 am
Hey, thanks for going the extra mile, Putts. Can we have some table row counts as well, please? Off the top of your head is fine so long as they're within an order of magnitude of the actuals.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 5, 2009 at 11:57 am
Sure, here ya go ...... actually less data then I was originally thinking .... this view doesn't use a certain table that most in this database do....
IREP_T_WF_INFO 724455
IREP_T_WF_POSTION_INFO 543537
IREP_T_DOCTYPE_GROUP_REL 131
IREP_T_DOCTYPE_GROUP 131
IREP_T_TRANSACTION_TYPES_DOCTYPES 124
IREP_T_TRANSACTION_TYPES 11
IREP_T_STANDARDS_DOC_TYPES 2107
IREP_T_POSITION_STANDARDS_DOCTYPES 8064
IREP_T_POSITIONS 6
IREP_T_WF_STATUS 3
FFG_STATES 53
hope that helps
March 5, 2009 at 12:13 pm
Putts (3/5/2009)
Derek Dongray (3/5/2009)
Looking at the plan....Sorry for a bit of confusion, Derek, but have brought up a new query that is doing something very similar here on page 4. The newer query is actually easier to analyze as the source view is more straight-forward.
I'm getting confused now Putts! Would you mind please posting the new query, the code of any views referenced within it, the associated plan, and the source of any UDF's, not just those specified in joins. That would give us all a solid baseline from which to work.
As you can see, Derek and Gail are already coming up with 'suspects' from analyzing the code.
Thanks again
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 5, 2009 at 12:16 pm
Derek was working off the code on page 1 .... I'll go back and modify that to say "read page 4"
What I've provided you is the current problem that I'm stuck on ...... both this one and the original are very closely related ..... both are situations where a query would return all results very quickly but would take a long time to return a DISTINCT/ GROUP BY from that query. That is why I shied away from starting a new thread.
March 5, 2009 at 12:24 pm
Putts (3/5/2009)
Derek Dongray (3/5/2009)
Looking at the plan....Sorry for a bit of confusion, Derek, but have brought up a new query that is doing something very similar here on page 4. The newer query is actually easier to analyze as the source view is more straight-forward.
Yes, I noticed just after I'd spent an hour perusing your plan and trying to work out what the view did. 😀
I've only glanced at your other plans. My first reaction is always to look for the high percentages. In all three cases thats the Clustered Index Scan on IREP_T_WF_INFO searching on the field WF_STOP for the date filter.
If this is going to be a heavily used query, an index on that field might help. Of course, if there are a lot of inserts/deletes on that table you might badly affect performance elsewhere.
Derek
March 5, 2009 at 12:29 pm
Putts (3/5/2009)
Derek was working off the code on page 1 .... I'll go back and modify that to say "read page 4"
My settings are different. 🙂 It's all page 1 as far as I'm concerned.
A better solutions is "Please see http://qa.sqlservercentral.com/Forums/FindPost669302.aspx".
If you click on the post number at the bottom left hand corner of the post it will give you the link.
Derek
March 5, 2009 at 12:31 pm
GilaMonster (3/5/2009)
Derek Dongray (3/5/2009)
Also I'd be interested to see the source of the User-defined function FFG_FX_JUST_DATE since, although it doesn't add the the estimated costScalar functions never do. It's one of the problems with them. No matter what the function does, the call to it will appear as 0% cost in an execution plan. The cost estimate in an execution plan cannot be trusted for a user-defined function.
Actually, I vaguely recall hearing that before.
These days I just avoid UDFs wherever possible, 'cos I know there's going to come back and haunt me later! 😀
Derek
Viewing 15 posts - 31 through 45 (of 57 total)
You must be logged in to reply to this topic. Login to reply