Provide direct access to a SQL Server Compact Table

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
markwt
Posts: 8
Joined: Wed 24 Jun 2009 15:45

Provide direct access to a SQL Server Compact Table

Post by markwt » Wed 12 Aug 2009 12:04

I am developing quite a large project using SDAC and a SQL Server Compact database (around 50-60) tables. Unless I am mistaken none of the SDAC components provide access to data in a table via the Base table cursor. Obviously providing this would increase performance of such access. I would imagine this would not be so easy to include in the current components but how about a new table component specifically for this task?

Challenger
Devart Team
Posts: 925
Joined: Thu 17 Nov 2005 10:53

Post by Challenger » Tue 18 Aug 2009 10:31

It seems that this type of cursor can be only implemented in TMSTable. We will investigate this issue and will notify you about the results.

markwt
Posts: 8
Joined: Wed 24 Jun 2009 15:45

Post by markwt » Tue 18 Aug 2009 10:39

That makes sense, would be a very useful addition IMO.

Thanks

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Fri 09 Oct 2009 12:33

We added support for Base table cursor in TMSTable for SQL Server Compact Edition. This cursor is the fastest of the SQL server cursors and the only cursor that interacts directly with the storage engine.
Our investigations say that using this cursor for data fetching or modification allows to increase the speed of working in two or more times.

You can find more detailed information about it in the information about TMSCursorType enumeration of the SDAC help.

This functionality will be included in the next SDAC build that will be released in several days.

markwt
Posts: 8
Joined: Wed 24 Jun 2009 15:45

Post by markwt » Fri 09 Oct 2009 12:52

Excellent news, Christmas has come early :D

markwt
Posts: 8
Joined: Wed 24 Jun 2009 15:45

Post by markwt » Tue 20 Oct 2009 11:27

Now that you have added BaseTable support I have done a bit of testing, definite significant performance benefits - well done.

couple of issues:

1. from my quick tests - problems with reading of SQL Compact money type - reports zero when non-zero.

2. Is there any support for SEEK faster alternative to locate

3. I may be wrong on this, I was hoping that I could use BaseTable dataset as an ultra fast lookup which would see additions and updates created by the same user on a single user database system (uses a different dataset, same connection). The basetable only sees changes if I refresh the basetable (as expected)

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Mon 26 Oct 2009 10:59

markwt wrote:1. from my quick tests - problems with reading of SQL Compact money type - reports zero when non-zero.
Thank you for information. The investigation of the problem is in progress. As soon as we solve the problem we will let you know.
markwt wrote:2. Is there any support for SEEK faster alternative to locate
The problem is that SQL Server Compact Edition does not support bookmarks and location functionality.
markwt wrote:3. I may be wrong on this, I was hoping that I could use BaseTable dataset as an ultra fast lookup which would see additions and updates created by the same user on a single user database system (uses a different dataset, same connection). The basetable only sees changes if I refresh the basetable (as expected)
Set the UniDirectional property to True to see data modifications by other users. But in this case, the resultset can not be represented in multiline controls such as DBGrid, because the cursor does not support bookmarks.

Post Reply