how to improve sql server ce database performance?

  • Thread starter Thread starter prathibhas
  • Start date Start date
P

prathibhas

Hi,

In my project, iam parsing many xml files and storing their content
into sql server ce database as many tables using primary and foreign
key relationship.
Iam using VS2005,WM6(Compact framework and SQL server ce) platform.
To store 5 xml files parsed content(each file is around 150kb)it is
taking around 8mins.
Iam using simple commands, create, insert,update to write into
database, to retreive the data iam using SqlCeDataReader/
SqlCeResultSet.

Note: Iam opening the connection when it is required and closing it
after its usage.

Please let me know, on how to improve the performance.


Thanks and Regards,
Prathibha
 
Firstly are you using SQL Server Mobile 2005 (the latest version) as this
version is considerability faster than the previous versions.

Consider using only the DataReader as opposed to the SqlCeResultSet -
although the SqlCeResultSet is more efficient than the DataSet and in CF 2.0
is bindable and scrollable if you are not using these features, avoid using
it as DataReader is still more efficient.

How are you storing the data? Have you tried dividing the files up into
packets and having a one to many relationship, as this will be a lot cheaper
on memory to process.

Also ensure you have an index defined on your tables and use the index in
the where clauses (if possible).
 
Hi,

In my project, iam parsing many xml files and storing their content
into sql server ce database as many tables using primary and foreign
key relationship.
Iam using VS2005,WM6(Compact framework and SQL server ce) platform.
To store 5 xml files parsed content(each file is around 150kb)it is
taking around 8mins.
Iam using simple commands, create, insert,update to write into
database, to retreive the data iam using SqlCeDataReader/
SqlCeResultSet.

Note: Iam opening the connection when it is required and closing it
after its usage.

Please let me know, on how to improve the performance.

Thanks and Regards,
Prathibha
Click to expand...

Not sure what you mean by "store 5 xml files parsed content." If you
mean that you're reading in the xml files, and then inserting them
into the DB, then your performance may be related more to the xml file
processing/parsing than the actual DB operations themselves. 8
minutes is quite long, but I suppose it depends on the number of
records and other factors.

Also, you should try to reuse the DB connection as much as possible
since each time you establish a new connection, it will cost you some
time.

- Jin
 
Also, you should try to reuse the DB connection as much as possible
since each time you establish a new connection, it will cost you some
time.

- Jin
Click to expand...

This doesn't make much difference as connection pooling takes care of this
overhead.
 
This doesn't make much difference as connection pooling takes care of this
overhead.
Click to expand...

Thanks for clarifying that for me. I guess I've taken some of the
older posts to heart when doing this. It's good to know given that I
may be spending way too much attention to performance at the expense
of needless complexity.

- Jin
 
This might be of interest:

http://community.opennetcf.com/articles/cf/archive/2007/09/21/improvi...
Click to expand...

It's funny that you should provide that link (and you are the author!)
because I've already studied that... Very useful indeed. I have an
application that has several table-based lookup values, which I load
into memory for faster processing. Not as elaborate as your method
since the table rows are limited. I have your page bookmarked just in
case I need that approach later.

Kudos!

- Jin
 
It's funny that you should provide that link (and you are the author!)
because I've already studied that... Very useful indeed. I have an
application that has several table-based lookup values, which I load
into memory for faster processing. Not as elaborate as your method
since the table rows are limited. I have your page bookmarked just in
case I need that approach later.

Kudos!

- Jin- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks a lott for replying to my query.
I will go through the points and incorporte them in my code and let
you know the performance.
I feel that opening and closing connection many times, is one of the
main reason for slow performance.
Because, 1st i was opening the connection, when i was starting xml
parsing and closing the connection after completion of that particular
file.It was pretty faster. But i was getting time out exception and
complete xml content was not stored.
So i gave open and close connection, for every catalogMenu (Iam using
a catalog xml files, which has catalog menu, catalog items inside that
and storing them in database). By giving that way timeout problem was
solved but it started taking more time.
Please suggest me how to go about with this?
 
It's funny that you should provide that link (and you are the author!)
because I've already studied that... Very useful indeed. I have an
application that has several table-based lookup values, which I load
into memory for faster processing. Not as elaborate as your method
since the table rows are limited. I have your page bookmarked just in
case I need that approach later.

Kudos!

- Jin- Hide quoted text -

- Show quoted text -
Click to expand...
 
Hi,
Atlast my problem is solved. Thanks for all your valuable suggestions.

Chris, as you told caching improved the performance of reading from
database. But i was facing performance problem while inserting into database.

As I told before, i was doing open and close database connection many times,
in a loop for individual item store, as i was getting blank exception if i
was doing once, which i had misunderstood as timeout exception.
Actually it was happening becoz i had used an autoincrement column, in a
table,
"Id int IDENTITY (0, 1) PRIMARY KEY.
After commenting this line,my problem was solved, i feel it was crossing its
limit, as the number of records count was more.

After commenting auto increment key, i used open and close connection once,
the peformance improved drastically.

Thanks a lottt for your help,

Regards,
Prathibha
 
Back
Top