sql server programming: I have a process that can potentiall affect alot of records. I was testing
this out and it seems that when I let it go on a large number of records it
takes along time. The time it takes seem to start of linear until you get
to a point and then it becomes an exponential increase in time. I was
wondering if having a transaction that deals with changes to many tables and
with rows numbering 50,000 - 100,000 if you would start getting into memory
issues or other things that my start slowing you down. I haven't tried
taking out the transaction control because it would be a pain to do so at
every level of this call stack not to mention I wouldn't want to leave it
without transaction control and I really don't want my changes to persist
during testing. Any information would be appreciated. Thanks.

Brandon

If this is a single transaction, everything has to be queued until the whole
transaction is committed. The fastest way to load data is to break it up into
smaller batches.

There is a good write-up under bcp/bulk insert in sql book online if you want to
look up.

--
-oj
http://www.rac4sql.net


[quoted text, click to view]

[quoted text, click to view]
in <u6t9VqE0DHA.2160@TK2MSFTNGP12.phx.gbl>

[quoted text, click to view]

I'm not sure with regard to transactions per se but as far as bcp/bulk insert BOL states:

mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\optimsql.chm::/odp_tun_1a_5gyt.htm

A single batch representing the size of the entire client file is recommended for each client.

It also recommends that you break a large load into smaller batches else your
large load will hold the locks until it finishes.

--
-oj
http://www.rac4sql.net


[quoted text, click to view]
mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\optimsq
l.chm::/odp_tun_1a_5gyt.htm
[quoted text, click to view]

Brandon Owensby (123@abc.com) writes:
[quoted text, click to view]

I have also experienced this, though in our case it appeared to expotential
all the way through.

I did make inquiry in our internal MVP forum about it, and I was contacted
by a guy who gave good suggestions to track what was happening.
Unfortunately, I did have the time to investigate. In our case, it was
obvious that the whole operation was in need of a redesign. We were calling
a stored procedure that only accepted data for one row at a time, but had
become a wrapper on a new procedure that accepted many rows at at time.
(So the operation needed to be rewritten to use the new procedure.) In
this case, the innermost procedure in the loop was creating some 20
temp tables on each occassion, and the locks for these in tempdb, system
tables and elsewhere, were held for the duration of the transaction.

I would not expect the above to be very helpful. However, if the case
is that your process is working with one row at a time, then you should
consider a redesign.

Another issue to consider is the size of the database files. If you have a
small transaction log file with a 10% autogrow, then there will be a lot
of autogrow. Also watch tempdb for this.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

My process is a delete process. I delete one row which then possibly
cascades to others. I was testing this trigger based cascade delete. It
worked good for the smaller sets but I noticed that when I tried it on more
extensive deletes that it was exponentially slower. I justed wanted to see
if it had to do with the amount of data in the transaction or a bad design.
Its its the amount of data in the transaction I won't worry about it for we
won't have that many rows at anyone time usually. Thanks for any help you
provide.

Brandon