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

Query is consuming hoge transaction log Expand / Collapse
Author
Message
Posted Monday, January 7, 2013 8:25 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, January 27, 2014 10:38 AM
Points: 90, Visits: 373
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?
Post #1403649
Posted Monday, January 7, 2013 8:46 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:21 PM
Points: 42,495, Visits: 35,566
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 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 #1403672
Posted Monday, January 7, 2013 8:52 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, January 27, 2014 10:38 AM
Points: 90, Visits: 373
Thanks Gail for the quick response.
But since the script is running fine in production why is creating such a mess in Development?
Post #1403678
Posted Monday, January 7, 2013 9:01 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:21 PM
Points: 42,495, Visits: 35,566
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 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 #1403685
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse