SQL Server Performance Forum – Threads Archive

A Long Running Query and Transaction Log

I am using SQL Server 2000(Service Pack 3). I have 3 Tables. tblA, tblB and tblC. tblA contains 1 column called tblA_PK. This is the Primary Key and is of the Type int. tblA:
—————-
tblA_PK
—————-
1
2
3
4 tblB contains 2 columns : tblB_PK and tblBA_PK. tblB_PK is the Primary Key and is of the Type int. tblBA_PK contains rows from tblA. tblB:
———————–
tblB_PK tblBA_PK
———————–
1 1
2 1
3 1
4 2
5 2
6 3
7 4
8 4
9 4
10 4
11 5
12 6
13 6
14 7
15 7
16 7
17 8
18 8
19 9
tblC contains 1 column called result. This is NOT a primary key and is empty.
I want all the rows from tblB which match tblA to be in tblC. Therefore after the Insert statement tblC will contain: tblC
———–
Result
———–
1
1
1
2
2
3
4
4
4
4 I wrote the following query to accomplish this: INSERT INTO tblC(Result) SELECT tblB.tblBA_PK FROM tblB
WHERE tblB.tblBA_PK IN(SELECT tblA_PK FROM tblA) This worked and I got the result in tblC. However the problem is that in Production tblA actually contains 2 million rows of data and tblB contains 130 million rows of data and tblC should result with 60 million rows of data. Every time I run this query the Transaction Log fills up. (I have NO BEGIN TRANS statement in my query). I don’t want to just increase the Transaction Log Size. I will need code for running this in a batch. (A Batch Update — Every 100000 records in a loop are updated). Any HELP appreciated.

I’m not sure that I understand you. You’re inserting data into a third table and don’t want this to be recorded in the T-Log? How should this work? You can’t turn off the T-Log for certain actions. This is an all-or-nothing approach. SQL Server needs to information recorded there to ensure it’s ACID properties in case of a failure. Sure, you can set your recovery model to simple, however this setting isn’t appropriate for most OLTP databases. Even if you don’t start an explicite transaction, read up in BOL about implicite transactions. As for your SQL statement, I can’t see any UPDATE statement. You haven’t posted the whole batch, have you? Btw, here is a very good explanation about IN vs. EXISTS() which is worth reading:
http://groups.google.de/groups?hl=d…8&selm=umy7hwRaBHA.1916%40tkmsftngp05&rnum=48
Check out the first answer by Itzik Ben-Gan in that thread.
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Frank – Thanks for the referral to that article, it was very informative. Caris – There are a number of ways you could do "batches of values", dynamic sql in loops, or using a cursor and a counter. In between batches you could issue a backup log statement to backup the log so it can be refreshed. If you don’t put something in to backup the log the batches aren’t going to help though.

Is the database set to Bulk Logged or Full Recovery? If it is bulk logged, you can use a DTS package to export the data to a file and then import the data into the target table. Keith Payne
Technical Marketing Solutions
www.tms-us.com

]]>