how to improve sql server ce database performance?

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

srhartone
Wed Nov 14 12:53:04 PST 2007

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).
--
Simon Hart
Visual Developer - Device Application Development MVP
http://simonrhart.blogspot.com


"prathibhas@aztecsoft.com" wrote:

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

Jin
Thu Nov 15 11:57:49 PST 2007

On Nov 13, 12:22 am, prathib...@aztecsoft.com wrote:
> 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

srhartone
Thu Nov 15 14:07:01 PST 2007

> 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
Thu Nov 15 18:13:14 PST 2007

On Nov 15, 5:07 pm, Simon Hart [MVP] <srhart...@yahoo.com> wrote:
> > 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.

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

ctacke/>
Thu Nov 15 18:38:54 PST 2007

This might be of interest:

http://community.opennetcf.com/articles/cf/archive/2007/09/21/improving-data-access-performance-with-data-caching.aspx


--

Chris Tacke, eMVP
Join the Embedded Developer Community
http://community.opennetcf.com


<prathibhas@aztecsoft.com> wrote in message
news:1194931360.200702.304720@s15g2000prm.googlegroups.com...
> 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
>

Jin
Thu Nov 15 22:44:33 PST 2007

On Nov 15, 9:38 pm, "<ctacke/>" <ctacke[at]opennetcf[dot]com> wrote:
> This might be of interest:
>
> http://community.opennetcf.com/articles/cf/archive/2007/09/21/improvi...
>
> --
>
> Chris Tacke, eMVP
> Join the Embedded Developer Communityhttp://community.opennetcf.com
>
> <prathib...@aztecsoft.com> wrote in message
>
> news:1194931360.200702.304720@s15g2000prm.googlegroups.com...
>
> > 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

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
Fri Nov 16 01:08:53 PST 2007

On Nov 16, 11:44 am, Jin Chang <jinsooch...@gmail.com> wrote:
> On Nov 15, 9:38 pm, "<ctacke/>" <ctacke[at]opennetcf[dot]com> wrote:
>
>
>
>
>
> > This might be of interest:
>
> >http://community.opennetcf.com/articles/cf/archive/2007/09/21/improvi...
>
> > --
>
> > Chris Tacke, eMVP
> > Join the Embedded Developer Communityhttp://community.opennetcf.com
>
> > <prathib...@aztecsoft.com> wrote in message
>
> >news:1194931360.200702.304720@s15g2000prm.googlegroups.com...
>
> > > 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
>
> 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
Fri Nov 16 01:57:15 PST 2007

On Nov 16, 11:44 am, Jin Chang <jinsooch...@gmail.com> wrote:
> On Nov 15, 9:38 pm, "<ctacke/>" <ctacke[at]opennetcf[dot]com> wrote:
>
>
>
>
>
> > This might be of interest:
>
> >http://community.opennetcf.com/articles/cf/archive/2007/09/21/improvi...
>
> > --
>
> > Chris Tacke, eMVP
> > Join the Embedded Developer Communityhttp://community.opennetcf.com
>
> > <prathib...@aztecsoft.com> wrote in message
>
> >news:1194931360.200702.304720@s15g2000prm.googlegroups.com...
>
> > > 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
>
> 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 -

Prathibha
Sun Nov 18 21:39:01 PST 2007

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