how to improve sql server ce database performance?

[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

 

[Guest]

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).

 

[Jin Chang]

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

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

 

[Guest]

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

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

 

[Jin Chang]

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

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

 

[Guest]

This might be of interest:

http://community.opennetcf.com/arti...ata-access-performance-with-data-caching.aspx

 

[Jin Chang]

This might be of interest:

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

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

 

[prathibhas]

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 -

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?

 

[prathibhas]

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 -

 

[Guest]

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