January 7, 2013 at 8:25 am
The following script is takeing hrs to complete and consuming 100GB of Transaction log in Dev environment,
DECLARE @THIS_CALENDAR_YR_START_DT INT
SET @THIS_CALENDAR_YR_START_DT = (SELECT CAST(THIS_CALENDAR_YR_START_DT AS INT)
FROM ECDI)
TRUNCATE TABLE EISRCYR
INSERT INTO EISRCYR
SELECT
Col1,
Col2,
Col3,
Col4,
Col5,
Col6,
Col7,
Col8,
Col9,
Col10,
Col11,
Col12,
Col13,
Col14,
Col15,
Col16,
Col17,
Col18,
Col19,
Col20,
Col21,
Col22,
Col23,
Col24,
Col25,
Col26,
,SUM(QUANTITY) QUANTITY
,SUM(DEALER_SALES) DEALER_SALES
FROM VERSC
WHERE TIME_SID >= @THIS_CALENDAR_YR_START_DT
GROUP BY
Col1,
Col2,
Col3,
Col4,
Col5,
Col6,
Col7,
Col8,
Col9,
Col10,
Col11,
Col12,
Col13,
Col14,
Col15,
Col16,
Col17,
Col18,
Col19,
Col20,
Col21,
Col22,
Col23,
Col24,
Col25,
Col26,
UNION ALL
DECLARE @THIS_CALENDAR_YR_START_DT INT
SET @THIS_CALENDAR_YR_START_DT = (SELECT CAST(THIS_CALENDAR_YR_START_DT AS INT)
FROM ECDI)
select
Col1,
Col2,
Col3,
Col4,
Col5,
Col6,
Col7,
Col8,
Col9,
Col10,
Col11,
Col12,
Col13,
Col14,
Col15,
Col16,
Col17,
Col18,
Col19,
Col20,
Col21,
Col22,
Col23,
Col24,
Col25,
Col26,
,SUM(A.QUANTITY) QUANTITY
,SUM(A.DEALER_SALES ) DEALER_SALES
FROM VEB A
,VETC B
,VECC C
,VETP D
,VECP E
WHERE A.TYPE2_CUSTOMER_SID = B.TYPE2_CUSTOMER_SID
AND B.CUSTOMER_NO = C.CUSTOMER_NO
AND A.TYPE2_PRODUCT_SID = D.TYPE2_PRODUCT_SID
AND D.PRODUCT_NO = E.PRODUCT_NO
AND C.MAJOR_BUY_GROUP_CD <> 'RET'
AND A.TIME_SID >= @THIS_CALENDAR_YR_START_DT
GROUP BY
Col1,
Col2,
Col3,
Col4,
Col5,
Col6,
Col7,
Col8,
Col9,
Col10,
Col11,
Col12,
Col13,
Col14,
Col15,
Col16,
Col17,
Col18,
Col19,
Col20,
Col21,
Col22,
Col23,
Col24,
Col25,
Col26,
TRUNCATE TABLE EISRC
INSERT INTO EISRC
SELECT * FROM TEISRPYR
UNION ALL
SELECT * FROM EISRCYR
DELETE EISRC
WHERE TIME_SID = 1
The same query is running in production within 20 mins.
Dev environment and production both has 3 GB RAM.
Nos of processor is double in Dev environment.
Dev environment also hosts SQL 2008 instance along with SQL 2000.
Can suggest how can I fix the issue and what is the reason for the issue?
January 7, 2013 at 8:46 am
The script you've posted doesn't even parse.
Msg 102, Level 15, State 1, Line 37
Incorrect syntax near ','.
Msg 134, Level 15, State 1, Line 76
The variable name '@THIS_CALENDAR_YR_START_DT' has already been declared. Variable names must be unique within a query batch or stored procedure.
Msg 102, Level 15, State 1, Line 106
Incorrect syntax near ','.
Msg 156, Level 15, State 1, Line 74
Incorrect syntax near the keyword 'DECLARE'
It uses a lot of log space because you're doing a lot of inserts and deletes. Unless you can figure a way so that you don't need to insert so much and delete so much, you won't be able to do much about the log space.
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
January 7, 2013 at 8:52 am
Thanks Gail for the quick response.
But since the script is running fine in production why is creating such a mess in Development?
January 7, 2013 at 9:01 am
Knowledge Hunter (1/7/2013)
But since the script is running fine in production why is creating such a mess in Development?
Err... *rolls dice*
More data
Less memory
Older stats
Different recovery model
etc
etc
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply