November 24, 2008 at 6:56 am
Hi, I'm new to SQL 2005 and I am having a proble with a *very* slow INSERT. I've tried some of the tricks from this forum, like putting multiple inserts into a single query, and it helped, but not that much. It is running around 10 INSERTS/second on a local DB. This is a single user application. The INSERT runs faster when the table is nearly empty ( < 20K records), but slows down after that point. Even at its peak performance it probably only runs 3x to 5x faster than it is now. Any help is GREATLY appreciated
- Greg
Here's how I create the table
sqlConnection = new SqlConnection("Server=localhost;Integrated security=SSPI;database=" + dbName);
sqlCommandString = "CREATE TABLE MarketTransactionTable (" +
"Id INT IDENTITY PRIMARY KEY NOT NULL," +
"StartDate DATETIME NOT NULL," +
"EndDate DATETIME NOT NULL," +
"Market1 VARCHAR(50) NOT NULL," +
"Market2 VARCHAR(50) NOT NULL," +
"TransactionAmount REAL NOT NULL," +
"NumberOfTransactions REAL NOT NULL)";
Here's the INSERT
string connectionString = "Server=localhost;Integrated security=SSPI;database=" + dbName + ";Pooling=false";
string sqlCommandString = "INSERT INTO MarketTransactionTable (Startdate, EndDate, Market1, Market2, TransactionAmount, NumberOfTransactions) VALUES (" +
"'" + cityPairRecord.ConvertDate(cityPairRecord.startDate) + "'," +
"'" + cityPairRecord.ConvertDate(cityPairRecord.endDate) + "'," +
"'" + cityPairRecord.market1 + "'," +
"'" + cityPairRecord.market2 + "'," +
cityPairRecord.transactionAmount.ToString() + "," +
cityPairRecord.numberOfTransactions.ToString() + ")";
November 25, 2008 at 8:29 am
You haven't mentioned what kind of hardware this is running on, or how much RAM it has, or what else might be making use of the available resources. Please list at least the following:
CPU / speed
RAM quantity and how much is "available" (from Task Manager)
DISK size / available space
Operating System, version, and service pack level
Other significant resource usage software that's installed, such as audio or video capture or DVD/CD burning (NERO, Roxio, etc.), or Anti-Virus or anti-spyware
While this may be a single-user application, there may be a LOT of other things that could get in the way in a typical PC environment.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 13, 2009 at 6:02 am
Hi
Instead of using many Insert statement, you can use DataAdapter.Update function. Before send data to SQL DB, insert data to any dataset or datatable which has same schema as database table. Then use DA.Update.
It would be faster.
😉
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply