Performance between OLEDB and ADO.NET Providers

Hi,

I am planning on using SSCE in a desktop embedded scenario. In considering deployment options, in addition to the SSCE runtime, I would also need to bundle either MDAC (OLEDB) or .NET Framework 2.0 (ADO.NET).

From the installer footprint standpoint, it's a no-brainer since MDAC is much smaller in size than .NET Framework. However, an article from the MSDN library (http://msdn2.microsoft.com/en-us/library/aa719514(VS.71).aspx) talks about the .NET Framework Data Provider for SQL Server and how it performs much better than the OLEDB counterpart.

So my question is, how much of a performance difference is there between using the OLEDB provider vs. the ADO.NET provider? I am primarily concerned with insert speed.

Also, it does seem a little silly when you need a 22.4Mb installer (.NET Framework) for a provider to a database that is under 2Mb in size.

Eugene

That article refers to the .NET OLE DB data provider for the desktop. The SqlClient classes are optimized for SQL Server, where the OleDb classes are generic. You should see a performance difference. But the article talks about .NET providers, not native providers.

My experience is that native code applications using the OLE DB client for SQL CE are the fastest, but also the hardest to write. So if you want speed, use native code and OLE DB.

|||

Using SqlCeResultSet.CreateRecord (.NET), will give you very good insert performance, and Rapid Application Development. See a sample at http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlceresultset.createrecord.aspx

But of course .NET Framework 2.0 will be a client requirement (it is included in the Vista OS, FYI)

|||

It is indeed no brainer: either your application is managed and it needs .Net Framework 2.0 in order to run anyway or your application is native and it simply can't use managed providers (not without framework dependency anyway).

In first case use SSCE specific managed provider for best performance and to eliminate the need for MDAC/OLEDB.

In the second case use OLEDB//MDAC as you have no other choice.

|||

Thanks for the response, everyone. So OLEDB is it, since my app is actually "managed" by the National Instruments LabVIEW runtime .

perform floating point addition in SQL stored procedure

Hi..
Is there any way to add the value of 4 column and at the same time print the result together with another column?

I have this stored procedure:

CREATE PROCEDURE sp_queuelist AS
BEGIN
DECLARE @.temp1 As Decimal
DECLARE @.temp2 As Decimal
DECLARE @.cash As Decimal
DECLARE @.cheque As Decimal
DECLARE @.card As Decimal
DECLARE @.nets As Decimal
DECLARE @.bill As Decimal
DECLARE @.company As Decimal

SELECT
@.cash=Cash,@.cheque=Cheque,@.card=Card,@.nets=Nets,
@.bill=Bill,@.company=Company
FROM QUEUE
ORDER BY QNo

SET @.temp1 = @.cash + @.cheque + @.card + @.nets
SET @.temp2 = @.bill +@.company

Select QNO,
PCNo,
PName,
@.temp1 As totalCash,
@.temp2 As totalContract,
Doctor

FROM QUEUE
ORDER BY QNo
END
GO

Basically I want to add 4 columns: cash+cheque+card+nets into totalCash and bill+company to totalContract.

All the 6 field types are decimal.

I want to calculate temp1 and temp2 and then select the rest of the column to be displayed in the datagrid.

However, this stored procedure gives me 2 problems:
1. It gives the rounding result of the addition of decimal number, not the decimal itself.
2. All the rows in datagrid display the same result for totalCash and totalContract, which is the total from the last row in the table.

I seldom use stored procedure.
Is there any way to solve this problem?
Any suggestion is most welcomed.
Thank you in advanced.
Sincerely

Agustina(1) you can try changing the decimal to float.

(2) in the design of your table, you can set the formula for the column as sum of the other 4 columns. that way you dont need to worry abt doing the addition. anytime you make any change in any of the columns, the computed column is automatically updated.
if you need more help in this approach, let me know.

HTH.