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