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 |
|
davehk@gmail.com
Starting Member
1 Post |
 Posted - 2009-05-08 : 18:07:45
|
| Hey:I have a routine which opens a cursor; pulls out a select list and for each item in the select list, inserts 0-10 records in another table.This routine runs inside a stored procedure; and by virtue of its design, runs in non-transaction mode (i.e. no begin/end transaction).The select list is ~ 20,000 records. The first 2,000 records process at ~ 20 records/second; but currently (at row 16,600) we're at 3.6 rows/second.The reason I know we're @ 16,600 is because the SP has "verbose" mode written into it.The code is all running on the server; which is a 4 processor box with 6 GB of RAM; 4 of which is in use. Windows Task Manager indicates that one of the 4 CPUs is pretty well pegging @ 100%. DBCC OPENTRAN reveals that the DB is all mine. Underlying disk system is a ridiculously fast SAN.Curious about a) what might be going on b) what might be done about it |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-05-08 : 21:05:56
|
| Post some code perhaps? With the table structures that are being accessed? Can't really tell where the problem might be without them.I am however certain the cursor is not helping you. 20 records/sec is really poor for just a plain SELECT, unless you're joining dozens of tables with no indexes.You say "non-transaction" mode, but then "DBCC OPENTRAN reveals the DB is all mine". If there's really no transaction then OPENTRAN should report nothing, is that what you actually get? Perhaps you have an implicit transaction open? Does any of your SQL code get called by an app layer via ADO or ADO.Net? |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-09 : 06:12:57
|
| adding to earlier questions, does your destination table has any indexes? |
|
|
|
|
|
|
Insert performance slows