Re: ado.net and oledb - SELECT performance

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hello Ray,

This actually depends on the properties you are providing to the command when opening. My experience with OLE DB shows that this is a very fast low-level interface to access SQL CE data, but you can also get your fingers burned with it if not used correctly. Also, I have consistently experience better results with OLE DB than with the .NET provider (I recognize this is a bit of an apples-to-oranges comparison but right now I'm making money writing custom native components to perform bulk updates to SQL CE databases and the performance is far better with OLE DB and native code).

Your best bet for a SELECT statement is to request a read-only forward-only cursor. Next to the base-table cursor, this is the fastest thing when retrieveing data. If you specify a scrollable cursor, all the data is cached and you get a performance hit.

Can you show the code you are using?


Regards,
João Paulo Figueira
Device Application Development MVP

Has anyone developed applications, using both ado.net and oledb, to
access sql compact? What has your experience been with regards to
performance? In our case, using oledb has been consistently slower
than ado.net, at least for SELECTs. Is the oledb provider a
work-in-progress?

Thanks.

Ray Mond



.

Relevant Pages

  • Re: AsyncExecute blocks new connection on Connect:=True
    ... ADO always uses OLEDB provider as back-end (except the case of in-memory ... Here there is only one OLEDB provider - you name it ODBC-bridge, ... > OLE DB come into it? ... > Kjell Rilbe ...
    (borland.public.delphi.database.ado)
  • Re: Dynamically set Datasource
    ... (i.e Textfile, Excel, OLEDB Provider ... Assuming you mean OLE DB Provider for SQL Server, ... specifying? ...
    (microsoft.public.sqlserver.dts)
  • Re: asynchronously fetching data
    ... Could you please let me know the database and the OleDb provider you are ... some Ole Db provider doesn't support ...
    (microsoft.public.data.oledb)