Optimize SQL Scheduler for inserting data from select statement

Question

anyone know how to optimize SQL query for inserting data from select statement and so the log file won't grow too fast? I tried WITH (NOLOCK) already. It works but the log file grow quite fast.

Currently I have SQL Scheduler to move the data from one database to another database under the same server and the same instance

TRUNCATE TABLE Mobile.dbo.[Customer] 

INSERT INTO Mobile.dbo.[Customer]
SELECT
    CST.[No], CST.[Name], CST.[Address], CST.[Address 2], CST.[Post Code], CST.[City], CST.[County] AS [State], 
    CST.[Country_Region Code] AS [Country Code], CST.[Contact], CST.[Phone No_], CST.[Fax No_], CST.[Telex No_] AS [Mobile No_],
    CST.[E-Mail], CST.[Home Page], CAST(CST.[Credit Limit] AS DECIMAL (38,2)) AS [Credit Limit], 
    CASE 
        WHEN (CAST(INV.[Amount] AS DECIMAL) IS NOT NULL) THEN CAST((INV.[Amount]) AS DECIMAL (38,2))
        ELSE '0.00'
    END AS [Balance],
    CST.[Salesperson Code], CST.[Payment Terms], CST.[Payment Code], CST.Zone Code]
FROM Live.dbo.[Customer] AS CST
WITH (NOLOCK)
LEFT JOIN(
    SELECT ORIamt.[Customer No_], SUM(ORIamt.[Amount]) AS [Amount]
    FROM (
        SELECT CLE.[Entry No_], CLE.[Customer No_], CLE.[Document No_], DCLE.[Amount]
        FROM Live.dbo.[Entry] AS CLE
        WITH (NOLOCK)
        LEFT JOIN(
            SELECT [Cust_ Entry No_], SUM([Amount]) AS [Amount]
            FROM Live.dbo.[Detailed Entry]
            WITH (NOLOCK)
            GROUP BY [Cust_ Entry No_]
        ) DCLE ON CLE.[Entry No_] = DCLE.[Cust_ Entry No_]
    ) AS ORIamt
    GROUP BY ORIamt.[Customer No_]
) AS INV ON INV.[Customer No_] = CST.[No_]

As you all can see, I have one database named Mobile. And Actually this database is used for showing the data only. Since this database is for showing the data only, I wonder how to optimize my SQL Scheduler and so the log file won't grow too fast.

Answer 1

Make sure that this database Recovery Model is set to Simple. 

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Thanks for the response Naomi, I set it to simple already but the log file still grow quite fast.

Answer 3

Sounds like your batch is too large? Try breaking the insert statement up into multiple transactions of smaller batches and insert some checkpoints. Even in Simple mode everything needs to be written to the log file first and open transactions cannot be wrapped around in the log in case the transaction has to rollback.

---

Sean Massey | Consultant, iUNITE

Feel free to contact me through My Blog, Twitter or Hire Me.
Please click the Mark as Answer button if a post solves your problem!

Answer 4

Hi Sean, actually it is not too large. But just my client request to make this mobile log file not growing too fast. Because the purpose of this mostly for showing data only. all the transaction has been passed and made to another database.

Answer 5

If the log is growing in simple mode too large for your requirements then simply put the transaction is modifying too much data at once and not allowing the log to wrap around fast enough and therefore is causing it to grow. What size is the log after the insert completes?

SQL uses write ahead logging therefore any insert, update or delete gets logged to the log file first.

---

Sean Massey | Consultant, iUNITE

Feel free to contact me through My Blog, Twitter or Hire Me.
Please click the Mark as Answer button if a post solves your problem!

Answer 6

Hi Sean, Currently I decided to change it into SELECT INTO. And before SELECT INTO is triggered, I drop the table first. Then change the database back up into bulk-logged. Do you think it is better?

Answer 7

Sorry for the delay in my response.

Since "select into" is a minimally logged operation switching to bulk logged then running the select into statement then switch back to full recovery mode and running a differential backup should help.

---

Sean Massey | Consultant, iUNITE

Feel free to contact me through My Blog, Twitter or Hire Me.
Please click the Mark as Answer button if a post solves your problem!