Re: Concurrent database access in SQL 2005 Mobile


1) Performance
Please stick to having an "dummy" connection in Open State.

2) Data Visibility
By default, SQL CE/Mobile has a background thread that flushes the changes
to disk. So it is a kind of asynchronous commit which happens every 10
seconds. If you want to commit the changes immediately/synchronously,
please use DBPROPVAL_SSCE_TCM_FLUSH and see if that helps your data
visibility problem. If you want to go with asynchronous commit, but wanted
to change the interval of asynchronous flush, please use
DBPROP_SSCE_FLUSH_INTERVAL.

3) Long running application leads to memory leak
I am not sure of this claim as we have many applications built on SQL CE and
they run for long hours and we have not seen memory leaks so far. However,
I see that it is just a perception. SQL CE has a shared memory to host
preallocated buffer pool, ...etc. This will be there as occupied/used for
the lifetime of SQL CE usage. This is not a memory leak but just the
preallocated buffer held by SQL CE. If you want to change the parameters
that affect the shared memory, please look at Books Online. Note that,
decresing buffer pool preallocation size affects performance.

Thanks,
Laxmi


"sardo007" <sardo007@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:412766F6-02BA-49FA-AA7D-F3C1216477AA@xxxxxxxxxxxxxxxx
Thank you very much... I'll see if somebody can help... in the meantime
I'll
try with the newer versions of SQLCE and in case they work fine we'll have
to
discuss it with the commercial staff and with the customer... In the
meantime
if there's anyone else who has faced a similar issue, I would appreciate
any
help.

Thanks again for everything Ginny

"Ginny Caughey MVP" wrote:

Thanks for your detailed answers. Perhaps somebody with more OleDb
experience will spot something. I'm afraid that I don't. Your approach of
opening one connection for the lifetime of the app and keeping it open
seems
to me that it should work to improve performance, and unless you aren't
committing your changes made on the other connection(s) I don't
understand
why the changes aren't visible when you execute a new query. I don't
recall
if this was a problem that has been fixed in a newer version of SQL CE,
but
I do understand the issue of testing and rolling out that kind of change
to
many different types of devices.

--

Ginny Caughey
Device Application Development MVP


"sardo007" <sardo007@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6B492C56-AF76-416F-A42E-697E55FD0382@xxxxxxxxxxxxxxxx
Hi Ginny thanks for the reply... I answer each question individually.

1. What version of SQL CE are you using? I'm assuming 3.1. If so,
there
is
also 3.5 that is available, and 3.5 sp1 which is in beta and due to be
released very soon.

I'm using SQL CE 3.0 (2005 Mobile edition) I could have tried using
newer
versions but we have more than one thousand users splitting more than
one
country, with difficulties to connect (the application allows them to
work
online and offline depending on the connection availability) and it
would
not
be practical to change the database version. In case there's no other
way
we'd have to assume the costs, but that would be the last resort. They
use
more than 10 types of terminals with older and newer versions of
Windows
Mobile 2005 and the siwtch would be very painful.

2. Are you accessing the database from an app written in C++? The
OleDb
implementation for SQL CE is not a complete implementation and you'd
get
more functionality using the managed code provider if you are writing
in
a
managed code language.

Yes, the application is written in C++ Managed code is just not
practical
for the application... It's been working since PocketPC 2003, we tried
to
migrate it to CFW for the 2005 version and its just too slow in some of
the
devices we have in our customer's users. So it has to be just native
code.
I
know Microsoft is encouraging everyone to move to .NET and we have done
son
in our web and PC applications, but mobile devices are still too slow
running
complex applications with .NET Wrapping the managed code driver into a
COM
that I can use in the C++ application would eat up all the memory of
the
device. I also posted an issue here (still not solved) about SQL CE
eating
device's memory slowly through the application cycle, but since it
seems
there's no solution we have worked around the biggest issues. I know
the
OLEDB driver for SQL CE does not implement every function, but one of
the
things it claims to solve is the multiuser access, so I hope this is
addresses correctly. I don't know it it's a bug or I'm just missing
something.

3. Are you accessing the database from multiple threads in any of the
apps?

The main application has more than one thread accessing the database,
but
even disabling the second thread and leaving one thread per application
the
problem persists. The notifying application has only one thread
accessing
data.

4. Are all the apps accessing the database on the same physical
machine?

Yes, both applications are located in the same mobile device. The main
application is a form-based user-entry application. The notifying
application
receives data via remote connections, writes it to the database and
makes
any
changes requested by the host application (deletions and updates)

5. How is the main app "looking" to see changes? Is it issuing a new
query
for example?

Yes. I uses a plain windows timer, since receiving notifications is not
a
realtime issue. The user just has to know it has available data. When
the
timer message is processed the application runs a query again the
notification table and in case it has new data, it notifies de user.
The
user
then opens his work forms (they are normaly listviews) and it's in
those
forms in which he still sees deleted data or does not see newly
inserted
records. The notifying loop is also failing to detect the presence of
new
notifications, but we had worked around it with a named event and a
blocking
thread.

It wouldn't seem to me that you'd need a new connection to
issue a new query, but I don't use OleDb against SQL CE data so I
might
have
missed something.

The pplication was written in the SQCE 2003 style: only one connection
and
everything done on it. So the connection was open at the beginning and
kept
that way. Both applications exchanged messages to close and open the
connection since they could not open it at the same time. Since SQLCE
2005
does not have that problem we changed the model and now they both use
their
own connections. One of the well-behaving things we learned was: open
the
connection, make your tasks and then close it again. The problem with
that
issue (it is the only one working well) is that's too slow (just on
some
devices) while keeping one connection open all the time for everything
or
just using a dummy connection and opening/closing the others triggers
the
data visibility problem.

We can now reproduce it in a simpler way:

1- Open the main application
2- Delete some rows in a table using isql
3- Open the data form in main application... changes are invisible
(deleted
rows still there)

I had neveer seen this behaviour before...

Thank you very much





.

Relevant Pages

  • Re: Concurrent database access in SQL 2005 Mobile
    ... Please stick to having an "dummy" connection in Open State. ... So it is a kind of asynchronous commit which happens every 10 ... I am not sure of this claim as we have many applications built on SQL CE ... then opens his work forms and it's in ...
    (microsoft.public.sqlserver.ce)
  • Re: Concurrent database access in SQL 2005 Mobile
    ... What version of SQL CE are you using? ... Are you accessing the database from an app written in C++? ... then opens his work forms and it's in those ... It wouldn't seem to me that you'd need a new connection to ...
    (microsoft.public.sqlserver.ce)
  • Re: Concurrent database access in SQL 2005 Mobile
    ... opening one connection for the lifetime of the app and keeping it open seems ... What version of SQL CE are you using? ... Are you accessing the database from an app written in C++? ... then opens his work forms and it's in those ...
    (microsoft.public.sqlserver.ce)
  • Re: Getting an out of memory error
    ... I went thru every single procedure that opens a connection and make sure ... uses them and closed before exiting, so in theory I should not even have to ... >> out of memory error message. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Fatal Application Exception
    ... to send from SQL CE or whatever you do to SQL CE after sending those records ... > connection every 5 minutes, transfer all records and then disconnect. ... the network goes down and transfers cannot be made. ... > breakdown of RAM between the Object Store and program memory, ...
    (microsoft.public.pocketpc.developer)