Provide direct access to a SQL Server Compact Table

markwt · 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 · 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 · Post by **markwt** » Tue 18 Aug 2009 10:39

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

Thanks

Dimon · 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 · Post by **markwt** » Fri 09 Oct 2009 12:52

Excellent news, Christmas has come early

markwt · 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 · 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.