Distinct going slower than *

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

  • 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

  • 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 πŸ™‚

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

    β€œ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

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

  • 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

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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • 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_DOCTYPES8064

    IREP_T_POSITIONS 6

    IREP_T_WF_STATUS 3

    FFG_STATES 53

    hope that helps

  • 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

    β€œ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

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

  • 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

  • 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://www.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

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

    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