Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
spromtet
Starting Member
7 Posts |
 Posted - 2003-04-30 : 21:21:51
|
| Hello,I have two SQL Server 2000 linked servers. A stored procedure on one server is inserting records into a table on the remote server. The performance is awful: A maximum of 50-60 rows inserted per second.Any ideas on how to improved performance? I tried setting the following options and each option doesn't change the performance much:SET REMOTE_PROC_TRANSACTIONS OFFSET XACT_ABORT OFFThe inserts don't have to be 100% bulletproof reliable. A seperate process runs to make sure entries get copied over to the database.Replication isn't an option because the schemas are so different. Remote queries return rows very quickly so it doesn't appear to be a network issue. The DTC seems to play a part in this, but I'm not sure how to bypass the DTc.Any suggestions or ideas is greatly appreciated. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-04-30 : 21:34:01
|
| Are you using cursors? (post your code) What about the traffic on the remote server? SELECT performance is never a good indicator of general performance anyway; it might be fine because locking requirements are lower than what's needed for an INSERT. SELECT also doesn't alter indexes, and can use indexes instead of accessing the table directly, and never deals with the transaction log. INSERT always has to accommodate these extra operations.One option you might want to consider is outputting the data into a text file and using BULK INSERT, bcp, or DTS to transfer the data. You wouldn't really need a transaction for it unless one table of many didn't bulk insert properly and you needed to undo all of them. |
 |
|
|
spromtet
Starting Member
7 Posts |
Posted - 2003-04-30 : 22:30:54
|
| Thank you for replying!Once I read 'cursor', everything clicked. It looks like the round trips are the problem. I tried a INSERT <RemoteTable> SELECT <MyData> and it took 7 seconds to insert 4000 rows. I can't believe the cursor is 10X -15X slower. I figured at most it would be 2X-3X slower.I was trying to take a shortcut and do some row by row processing using the cursor. I'll have to add some columns to my local table and do the processing up front, then do a multirow INSERT/SELECT, amd finally do some cleanup -- Thanks again!Hypothetically, let's say that the cursor is the only way to go. What kind of optimizations could I have made?The code is basically like:DECLARE CURSOR...WHILE ...BEGIN -- Perform some processing on the current values ... INSERT <REMOTE TABLE>... ... UPDATE <LOCAL TABLE> SET <flag> WHERE CURRENT FETCH NEXT ...END |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-05-01 : 06:42:30
|
quote:
Hypothetically, let's say that the cursor is the only way to go. What kind of optimizations could I have made?
None. The cursor really can't utilize whatever optimizations the query processor made.There's the old question "How do you want to put sugar in your tea/coffee? A spoon, or tweezers?" Cursors are tweezers, so no matter how much or how little sugar you have, or how well you line it up, you're still stuck with picking up each grain individually and dropping it in the cup. The spoon takes all the sugar as one unit and dumps it the cup.And, 10-15 times slower is actually pretty good, there are instances where cursors are HUNDREDS of times slower than using SQL. The spoon-tweezers analogy is a lot closer than you think. People have posted here that cursor operations that took several hours were rewritten as SQL and now take only a few minutes or less. |
|
|
|
|
|
|
|
|
Linked Servers - Insert performance