Accelerate Sql Express?

Hi,

we're planning to migrate an existing dataset cache mechanism to Sql Server Express. The dataset isn't really able to handle and search one million recordsets any more :-)

Once a day the Cache Sql Database wil be filled with fresh data. In comparison with the dataset takes up to fourty times longer to build the cache on the same machine!! Ok, I expected that i will take longer than creating the objects in a database with all its "overhead" like atomic transactions etc. But not so much.

The current throughput on SQL Server ist nearly 400 inserted records per second in the same table. The dataset stores up to 17.000 of the same data.

I'm wondering about the the following fact. Allthough I'm testing the Mass-Inserts with generating synthetic records in a unlimited for-loop, the server CPU load is allways less than 3 %. For all I care it could be much higher while the nightly process of initilializing the cache. Could there be a unnecessary throttle that limits the amount of inserts per second?

Any ideas how to accelerate the database engine or optimizing bulk-inserts?

P.S. I tested some different options and played around with connection string options like packekt size, Named Pipes connections, recycling the last commando and parameters and so on. Without any significant effects..

Marcus

Could you post how you are doing the inserts are the moment? Are you using BULK INSERT or bcp, or are you doing regular inserts? Also, what is the transaction size you are using, and what are the specifications or your machine, disks, etc.|||Im inserting each record in a single regular insert statement via ADO.NET (2.0) ExecuteComand. As far as I know, buldinsert are only for files available, right?

The Maschine is a 4 x XEON 3,6 Ghz, 32 bit Win2003 Server Sp1, 4GB Ram and a fast SCSI Raid

What is the transaction size? Is this a configurable value? At the moment all values are unchanged
installation defaults.

Thanks
Marcus|||There are several ways to speedup:

1) Use a SqlTransaction in your ADO.NET code, and issue a commit after every N rows that you insert. If you don't do this, a commit will be done for each row, which is very expensive.

2) To get big perf improvements, you should use BulkInsert or BCP to bulk load your data in your database. These methods are much faster than doing individual inserts.

Thanks,

about processXMLdata sample,how to insert a return in end of each row data of the result text fi

I am newbie, having a job to handle some XML files into sql server 2005. In the procXMLdata sample,,pls tell me how to insert a return in end of each row data of the result text file?

I am not familiar with this sample, but my guess is that you need to use Ragged Right Flat File format and choose {CR}{LF} as the row delimiter.

Give us more of the problem's context, if this is not what you are looking for.

Thanks.

|||

hi,

Thanks for your reply.

The processXMLdata sample was put in the sample directory of ms sql server,which just converted a xml file into a txt file ,using two xml task. The first was to preform XPATH method,output a variable, and the second xml task was to do XSLT method ,input the variable above, output a txt.

I know XML little , i guess the problem may be on propery of the second XML task.

ABout identity column in replication

Hi Guys,
I read the microsoft article about how to handle the identity column in
replication. There is sentence: " If you are using transactional replication
with the immediate-updating Subscribers option, do not use the IDENTITY NOT
FOR REPLICATION design." That means I can not use the IDENTITY NOT FOR
REPLICATION option when I want to use transactional replication with the
immediate-updating Subscribers options, is that correct? That also means I
cannot created identity column in subscriber, right? If I do need to create
identity column in the subscriber, how can I work around? Thanks
This isn't correct. If you have immediate updating you don't need NFR as the
transaction is applied on the publisher before the subscriber.
You can use NFR and identity columns on the subscriber, however, but it is
not necessary.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:43F4ECD0-EDC3-4932-AF29-C2782E66CEDD@.microsoft.com...
> Hi Guys,
> I read the microsoft article about how to handle the identity column in
> replication. There is sentence: " If you are using transactional
> replication
> with the immediate-updating Subscribers option, do not use the IDENTITY
> NOT
> FOR REPLICATION design." That means I can not use the IDENTITY NOT FOR
> REPLICATION option when I want to use transactional replication with the
> immediate-updating Subscribers options, is that correct? That also means I
> cannot created identity column in subscriber, right? If I do need to
> create
> identity column in the subscriber, how can I work around? Thanks