Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»»

Distinct going slower than * Expand / Collapse
Author
Message
Posted Thursday, March 5, 2009 8:46 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:59 AM
Points: 89, Visits: 165
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.


  Post Attachments 
select.zip (3 views, 16.92 KB)
Post #669302
Posted Thursday, March 5, 2009 9:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 7,229, Visits: 13,706
Is IREP_V_POSITION_STANDARDS a table or a view?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #669341
Posted Thursday, March 5, 2009 9:38 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:59 AM
Points: 89, Visits: 165
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 :P
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 :P

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 :)
Post #669352
Posted Thursday, March 5, 2009 9:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 7,229, Visits: 13,706
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 :P
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 :P

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".


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #669369
Posted Thursday, March 5, 2009 11:16 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:59 AM
Points: 89, Visits: 165
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.
Post #669458
Posted Thursday, March 5, 2009 11:31 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,344, Visits: 1,983
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
Post #669471
Posted Thursday, March 5, 2009 11:44 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:59 AM
Points: 89, Visits: 165
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.

Post #669488
Posted Thursday, March 5, 2009 11:47 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 42,827, Visits: 35,957
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #669491
Posted Thursday, March 5, 2009 11:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 7,229, Visits: 13,706
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


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #669492
Posted Thursday, March 5, 2009 11:57 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:59 AM
Points: 89, Visits: 165
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
Post #669503
« Prev Topic | Next Topic »

Add to briefcase «««23456»»»

Permissions Expand / Collapse