One query is hogging my baby!!!

  • Ideally, it should be on another server. But, since you mentioned budgeting, I suggested a different DB on the same server as a way to reduce reporting impact on production. . It would prevent any data contention because the reporting would be done from another DB - for example, no table locks that would affect production users.  You could also have different indexes useful for reporting that you may not want in production.

  • quoteMy boss already rewrote the query to pull one day at a time and it runs in under 10 sec per day. He made the changes to it that day. I kept the query the same for my testing.

    Ok... one more time... Post the code, Will

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • compare the execution plans between the 1 day and 10 day executions. You may have to use a hint, or an explicit temporary table.

    How up to date are your statistics? What % of the data is being sampled for your stats? You may need a bigger sample than the default. Statistics has quite often been the culprit for me.

    Andy

  • I really don't care how bad the query was or care about optimizing it. All I wanted to do was make sure that one query couldn't take over my server and I have done that. Someone else could write a query that's worse tomorrow.

    I guess you guys really enjoy a optimizing things.

    Here's the code anyway. (I do appreciate your previous help on the issue)

    CREATE TABLE CURRENT_GEN_REPORT (

    AGENT_FIRST_NAME char (20) NULL ,

    AGENT_TITLE char (25) NULL ,

    AGENT_LAST_NAME char (20) NULL ,

    BUSINESS_UNIT char (20) NULL ,

    CITY char (20) NULL ,

    EFFECTIVE_DATE char (10) NULL ,

    EFFECTIVE_TIME char (11) NULL ,

    LAST_ACCESS char (10) NULL ,

    LAST_ACCESS_TIME char (11) NULL ,

    LEVEL1_ID char (6) NULL ,

    LEVEL1_NAME char (40) NULL ,

    LEVEL2_ID char (6) NULL ,

    LEVEL2_NAME char (40) NULL ,

    LEVEL3_ID char (6) NULL ,

    LEVEL3_NAME char (40) NULL ,

    REGION char (2) NULL ,

    STATUS char (10) NULL ,

    STREET char (50) NULL ,

    TEAM char (20) NULL ,

    AGENT_UUID char (6) NULL ,

    VP_ID char (6) NULL ,

    VP_NAME char (40) NULL ,

    AVAYA_ID int NULL ,

    SALES_CODE char (10) NULL ,

    LOGIN char (10) NULL ,

    MGR_CCPULSE char (20) NULL ,

    MGR_NAME char (40) NULL ,

    CTI_UUID char (6) NULL ,

    PRIMARY_SKILL char (20) NULL ,

    COL_DAT char (10) NULL ,

    LOAD_TIME char (10) NULL

    )

    GO

    CREATE TABLE CTAGENTS (

    IDCOL int IDENTITY (1, 1) NOT NULL ,

    REGION char (1) NOT NULL ,

    OFF_COD char (4) NOT NULL ,

    SUB_ID int NOT NULL ,

    AGENT_ID int NOT NULL ,

    SSN int NOT NULL ,

    SUITSID char (6) NOT NULL ,

    LASTNAME varchar (23) NOT NULL ,

    FIRSTNAME varchar (23) NOT NULL ,

    MIDDLEINI char (1) NULL ,

    NCSDATE char (10) NOT NULL ,

    ISFULLTIME bit NOT NULL ,

    MAXHOURS decimal(5, 2) NOT NULL ,

    ISACD bit NOT NULL

    )

    GO

    CREATE TABLE CTEEXC (

    IDCOL int IDENTITY (1, 1) NOT NULL ,

    REGION char (1) NOT NULL ,

    SSN int NOT NULL ,

    AGENT_ID int NOT NULL ,

    SUB_ID int NOT NULL ,

    COL_DAT char (10) NOT NULL ,

    START_TIME int NOT NULL ,

    STOP_TIME int NOT NULL ,

    RT_SCH_EXC char (4) NOT NULL ,

    ELINK_CODE char (5) NOT NULL ,

    CODE_TYPE char (1) NOT NULL ,

    DAT_VAL decimal(6, 2) NOT NULL ,

    REASON varchar (6) NOT NULL ,

    ERR varchar (200) NOT NULL ,

    L_UPD char (14) NOT NULL ,

    L_SUB char (14) NOT NULL ,

    COST_CODE varchar (4) NOT NULL ,

    L_MOD smalldatetime NOT NULL ,

    APP bit NOT NULL ,

    REPAPP bit NOT NULL

    )

    GO

    CREATE CLUSTERED INDEX C_ADH_AGENT_NO_AGG ON CURRENT_GEN_REPORT (COL_DAT, AGENT_UUID) WITH FILLFACTOR = 80

    CREATE INDEX u_agent_uuid_col_dat ON CURRENT_GEN_REPORT (AGENT_UUID, COL_DAT) WITH DROP_EXISTING

    CREATE INDEX SchedOpen ON CURRENT_GEN_REPORT (COL_DAT, AVAYA_ID) WITH DROP_EXISTING

    CREATE CLUSTERED INDEX REPACCESS ON CTAGENTS (REGION, OFF_COD, LASTNAME, FIRSTNAME) WITH DROP_EXISTING

    CREATE INDEX IDCOL ON CTAGENTS (IDCOL) WITH DROP_EXISTING

    CREATE INDEX SSN ON CTAGENTS (SSN, OFF_COD, REGION, ISFULLTIME, MAXHOURS) WITH DROP_EXISTING

    CREATE INDEX offcod ON CTAGENTS (OFF_COD, SSN) WITH DROP_EXISTING

    CREATE CLUSTERED INDEX Rep9115Exc ON CTEEXC (SSN, COL_DAT desc , ELINK_CODE, REGION) WITH DROP_EXISTING

    CREATE INDEX CcapIndex2 ON CTEEXC (SSN, COL_DAT desc , ELINK_CODE, L_UPD, L_SUB) WITH DROP_EXISTING

    CREATE INDEX Ajax9115 ON CTEEXC (ELINK_CODE, COL_DAT desc , SSN, REGION) WITH DROP_EXISTING

    CREATE INDEX IDCOLEXC ON CTEEXC (IDCOL) WITH DROP_EXISTING

    CREATE INDEX ERROR_CHECK_EXC ON CTEEXC (ERR, SSN, COL_DAT desc , REGION) WITH DROP_EXISTING

    go

    select 'Active Employee' [Employee Status], c.TEAM, c.LEVEL3_NAME, c.LEVEL2_NAME, c.LEVEL1_NAME, 0 SSN,

    b.SUITSID, ltrim(rtrim(c.AGENT_LAST_NAME)), ltrim(rtrim(c.AGENT_FIRST_NAME)),

    a.ELINK_CODE, sum(a.DAT_VAL)

    from PAYROLL..CTEEXC a left join

    PAYROLL..CTAGENTS b on a.SSN = b.SSN left join

    CCA_DATAMART..CURRENT_GEN_REPORT c on upper(b.SUITSID) = upper(c.AGENT_UUID)

    where a.COL_DAT between '2007-07-01' and '2007-07-31' and c.COL_DAT = a.COL_DAT and

    a.SSN = b.SSN and OFF_COD between '0000' and '9999' and a.SSN > 999999 and VP_ID in ('RE4832','JP8983')

    group by c.TEAM, c.LEVEL3_NAME, c.LEVEL2_NAME, c.LEVEL1_NAME, a.SSN, b.SUITSID,

    ltrim(rtrim(c.AGENT_LAST_NAME)), ltrim(rtrim(c.AGENT_FIRST_NAME)), a.ELINK_CODE


    Live to Throw
    Throw to Live
    Will Summers

  • Will,

    Not really digging through too hard, but this line caught my eye

    OFF_COD between '0000' and '9999'

    Are there other values? This seems like a strange join condition. Can it be < '0000'?

  • Also, we have multiple drive arrays on our production box, so even if you're reporting off the same server, you can put your reporting DB on different drives, to reduce production impact.

  • quoteI really don't care how bad the query was or care about optimizing it. All I wanted to do was make sure that one query couldn't take over my server and I have done that. Someone else could write a query that's worse tomorrow.

    I guess you guys really enjoy a optimizing things.

    Heh... If you don't care about it, then I guess I shouldn't.    Telling your boss to run it for just a month at a time was definitely the right thing to do

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 0000 and 9999 are a range of office codes. We also have offices codes that are alpha like '4RFT' and 'H7EE' and such.


    Live to Throw
    Throw to Live
    Will Summers

  • "Heh... If you don't care about it, then I guess I shouldn't. Telling your boss to run it for just a month at a time was definitely the right thing to do "

    I'm sure you meant 'just a day at a time' instead of 'just a month at a time'. Like I said, my boss is pretty cool. He realized that it could rewrite the query to go a day at a time before I had a chance to fix the server.

    It could have been worse, I could have a boss that wouldn't rewrite the query!


    Live to Throw
    Throw to Live
    Will Summers

  • If there are alpha codes, wouldn't "< '9999'" work? Alphas should order above that. Might help it slightly,

  • That's what the query has. Not sure what you are asking. Alpha does order above that.


    Live to Throw
    Throw to Live
    Will Summers

  • OFF_COD between '0000' and '9999'

    vs.

    OFF_COD <= '9999' (or, OFF_COD < 'A')

    - using a single '<' rather than between might speed up execution a little. I'm synthesizing above posts to clarify the point.

  • Yep, steve_smith, of (steve) Smith and Jones fame, hit it. It should run a little faster if that field is indexed and it's used.

  • Ah, now I see what you're saying. I thought that you were comparing <9999 to <'9999'. I thought that it was a little strange that the forum admin would post something like that. That's a good idea. I'll remember that in the future and also check out the query plan with that change.


    Live to Throw
    Throw to Live
    Will Summers

Viewing 14 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply