Solved

Transaction Problem

Posted on 2002-03-07
13
456 Views
Last Modified: 2009-12-16
Hello

I have a big problem !

I have an SQL 7 Database with a table with 40 columns
and 150,000 Rows.

I gets very fast Streaming data - 2000 Messages a Second
I need to Update this table with the Messages.

I Succeeded to Update 180 Updates in a a second without
Transactions, and 280 Updates with Begin and Commint Transactions each 3 Seconds.

I tried to Update from 10 Clients (Each Client Update 200
in A Second) and there was not a Big Performance Change.
In the Form load I Executed "Begin Transaction" and
in the timer(of 3 seconds) i Executed "Commit Transaction" and a row after I Executed "Begin Transaction".

When I Tried To Update with the 10 Client With Begin
and Commit Transactions, In all of the Clients  
(except one ) I got an Error -
"2147467259 - The COMMIT TRANSACTION request has no
corresponding BEGIN TRANSACTION." (But there is!)

And after 15 seconds I get this Error from the clients:
"-2147467259 - Transaction(Process ID 57) was deadlocked on {lock resources with anothe process ... the deadlock victim. Rerun the transaction."

Please Help Me.
0
Comment
Question by:JOKER
  • 3
  • 3
  • 3
  • +3
13 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6847057
How do you submit the transaction? I guess using ADO in VB or C, and my question is if you use the BeginTransaction and CommitTransaction from the ADO Connection object, or use execute the COMMIT statement explicitely?

CHeers
0
 

Author Comment

by:JOKER
ID: 6847144
Hello angelll

I am Using VB 6, ADO and I Execute the Begin and Commit
Transaction like this :
    Connection.Execute ("COMMIT TRAN")
    Connection.Execute ("BEGIN TRAN")

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6847185
In fact, in VB+ADO, you need either to use the
Connection.BeginTrans
Connection.CommitTrans
instead, or execute the whole transaction in 1 Execute:

dim strSQL as string
strSQL = "BEGIN TRAN" & vbcrlf
strSQL = strSQL & " UPDATE your table ... " & vbcrlf
strSQL = strSQL & " UPDATE your table ... " & vbcrlf
strSQL = strSQL & "COMMIT TRAN" & vbcrlf

I guess that the second way will also improve your overall speed...

CHeers
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6847190
i obviously forgot the Conneciton.Execute strSQL line for the second example...

CHeers
0
 
LVL 7

Expert Comment

by:simonsabin
ID: 6847346
The fastest way I have found is to commit every 2000 rows. The quickest method to insert the data would be to use a stored procedure.
The other option would be to create a flat file and BULK INSERT it in v.fast.
0
 

Author Comment

by:JOKER
ID: 6854179
Hi simonsabin

What do you mean by BULK INSERT?
(all the answers was not giving me more than
400 messages for a second), I need 3000...

Thanks
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 4

Expert Comment

by:wqw
ID: 6854365
joker,

here is my experience:

DECLARE @D DATETIME
SET @D = GETDATE()

BULK INSERT datingv1r0.dbo.main_dev
FROM '\\wqw\d$\temp\main_dev.bcp'
WITH (DATAFILETYPE='native')

SELECT GETDATE()-@D

gives me:

(50000 row(s) affected)
                                                       
------------------------------------------------------
1900-01-01 00:00:01.893

(1 row(s) affected)

i.e. over 25k rows per second (over LAN)

table main_dev is a 15 columns table (mixed varchar, int, bit, GUID columns) and DOES NOT have ANY indexes (for the test). d:\temp\main_dev.bcp was prepared this way:

d:\>bcp "datingv1r0.dbo.main_Dev" out d:\temp\main_dev.bcp -S pgbr -T -n -C 1251

which gives me:

50000 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total     3675

i.e. 50k rows in less than 3.7 secs (over LAN)

file main_dev.bcp is 6'260'964 in size in 'native' bulk copy format. also, mind that i'm using code page 1251 (cyrillic) for the varchar fields which might not be your case.

HTH,

</wqw>
p.s. your results could be bettered if you drop (or at least reduce) the indexes on your table. also you might try dropping clustered index and leaving nonclustered indexes just based on the heap the table data will be.
0
 

Author Comment

by:JOKER
ID: 6854376
Hi wgw

Thanks for this answer BUT,
I do 95% Updates and 5% Inserts,
so I need a fast UPDATE.

Thank you
0
 
LVL 7

Expert Comment

by:simonsabin
ID: 6856266
Thats what I meant by BULK INSERT.

Optimizeing updates, make sure you are not updating an index, especially a clustered index. IF you are make sure your FILLFACTOR is correct or you will end up with a lot of page splitting. Also try to ensure your data is of the same size. i.e putting '111' in a varchar and then updatign it to '12312312312312738123912937213' might cause a page split.

It is often quicker to DELETE and INSERT rather than UPDATE. Although as you are doing continuous updates then this is probably not the case for you.
0
 
LVL 4

Expert Comment

by:wqw
ID: 6856583
simonsabin: "It is often quicker to DELETE and INSERT rather than UPDATE."

can you elaborate, please? when does this happen and why?

</wqw>
0
 

Expert Comment

by:CleanupPing
ID: 9280785
JOKER:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
LVL 7

Expert Comment

by:simonsabin
ID: 9282624
When performing an update page slipts often occur that cause the data originally on one page to be split across two pages, this has a knock on impact of updating the indexes, additionally the updates will have to scan the pages held by the table to identify the pages to update, this can be a slow process. Deleting all data does just that, goes through and releases the pages assigned to the table, inserting the data then reassigns the pages and fills the data with the values required.
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 10292025
PAQed - no points refunded (of 300)

modulo
Community Support Moderator
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now