[dba-SQLServer] OLEDB

Charlotte Foust cfoust at infostatsystems.com
Wed Apr 21 16:19:46 CDT 2004


Shamil,

I think Jim is talking about creating a code connection with ADO using the MSSQL OLEDB driver to trigger the SQL Server activity.   Since the Jet query engine isn't engaged (if you do it right, at least :-} ), the speed should be acceptable from Access or VB, not just from MSVC++.  Executing the *code* that creates the connection and passes parameters may be faster in VC++, but the ADO speed should be about the same.  I don't use VC++, so I can't test it, but I know that doing it the way I described is faster by orders of magnitude than using Jet queries against linked SQL tables.

Charlotte Foust

-----Original Message-----
From: Shamil Salakhetdinov [mailto:shamil-users at mns.ru] 
Sent: Wednesday, April 21, 2004 12:00 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] OLEDB


Jim,

I talked about 2000+ calls from client to the MS SQL backend's stored procs with passing/returning parameters(fields values) etc. - do you think that something like that is possible with MS Access and ADO? - I didn't check that but I expect that the communication between MS Access and MS SQL backend will be several times more slow. I can be wrong of course - no time to test that now...

Shamil


----- Original Message ----- 
From: "Jim Lawrence (AccessD)" <accessd at shaw.ca>
To: <dba-sqlserver at databaseadvisors.com>
Sent: Tuesday, April 20, 2004 4:39 AM
Subject: RE: [dba-SQLServer] OLEDB


> Hi Shamil:
>
> > I think such results can't be obtained when ADO is used with VB/MS
Access.
>
> Just could not let this one pass. Actually the performance using ADO
OLE
> combination is very fast in VB/MS Access. Performance levels of 50,000
plus
> records in 2 seconds but that is with the MS SQL BE. See
>
http://www.databaseadvisors.com/newletters/newsletter112003/0311UnboundRepor
> ts.htm (watch for wrap). I think the performance is all in the BE.
>
> Jim
>
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Shamil 
> Salakhetdinov
> Sent: Monday, April 19, 2004 1:18 PM
> To: dba-SQLServer
> Subject: Re: [dba-SQLServer] OLEDB
>
>
> Michael,
>
> The company I work for now and where I use OLE DB with MSVC++ - they
use
> Crystal Reports to make a lot of very advanced reports: they exploit
to
> the max drill-down, multi pass reporting, multi-section etc. features
of
> Crystal reports - the results are amazingly powerful, very informative 
> and user-friendly reports... BTW, they use MS Access as a storage for 
> their reports data - with production data being processed and stored
in
> legacy Raima Data Manager DBMS... And using this architecture and 
> Crystal Reports as reporting tool they are able to have one very 
> advanced payroll application to work in desktop mode or in remote 
> Web-Site based mode etc.... (Crystal Reports Server is very smart  in 
> Web-site based reports browsing...)
>
> I think that usage of OLE DB doesn't define what tool one will select
to
> use as a reporting tool - so the reporting tool can be also 
> ActiveReports and others...
>
> But OLE DB used from C++ results in lightning fast data manipulation 
> procedures - e.g. I've here tested it and I've ~2000+ MS SQL 2000
stored
> procedures used to insert, update and delete data from several
tables -
> all finished within 2 seconds...
>
> I think such results can't be obtained when ADO is used with VB/MS 
> Access.
>
> But such speedy results can be obtained when one will use ADO.NET with 
> C#/VB.NET and .NET Framework's System.Data.SqlClient namespace's 
> SqLConnection, SqlTransaction, SqlCommand etc. objects - they may even 
> be more speedy because as I've heard .NET uses native MS SQL 2000 data 
> access (drivers) called (?) TDS - Table Data Stream:
>
> <<<
> Native APIs are low-level APIs implemented with providers or drivers 
> that communicate directly to SQL Server using the Tabular Data Stream
> (TDS) protocol. They are relatively complex APIs, but offer the best 
> performance because they have the least overhead.
> >>>
> (If anyone has any refs on articles about using TDS from (MSV)C++ they 
> are very welcome to send them to me! :))
>
> As for reporting in general - it looks like MS SQL 2000 Reporting 
> Services is a coming killer technology for most of the currently 
> existing reporting tools:
>
> http://www.microsoft.com/sql/reporting/default.asp
>
> And BTW, when reports aren't that complicated then using C#/VB.NET 
> System.Drawing.Printing and System.Drawing.* objects are not the worst 
> choice for custom reports....
>
> Shamil
>
> ----- Original Message -----
> From: "Michael Brösdorf" <michael.broesdorf at web.de>
> To: <dba-sqlserver at databaseadvisors.com>
> Sent: Monday, April 19, 2004 1:47 PM
> Subject: AW: [dba-SQLServer] OLEDB
>
>
> > Shamil, what would you recommend for reporting in such an
environment?
> >
> > Michael
> >
> > -----Urspr?ngliche Nachricht-----
> > Von: dba-sqlserver-bounces at databaseadvisors.com
> > [mailto:dba-sqlserver-bounces at databaseadvisors.com]Im Auftrag von
> Shamil
> > Salakhetdinov
> > Gesendet: Samstag, 10. April 2004 22:48
> > An: dba-SQLServer
> > Betreff: Re: [dba-SQLServer] OLEDB
> >
> >
> > C#/VB.NET + System.Windows.Forms + System.Data +
> System.Data.SqlClient -
> > they all together are as RAD as MS Access or even more IMO (pun 
> > intended)...
> >
> > (Managed)C++ & OLE DB (ATL OLE DB) - this is the most flexible and 
> > speedy solution... (And in VS.NET 2003 WinForms are available for 
> > C++ programmers)...
> >
> > As for SQL Injection and other related topics - .NET Framework/CLR
> have
> > multi-level/multi-purpose and very strong security model, which,
when
> > properly used and activated, disallow to run any malicious code/stop
> any
> > attacks....
> >
> > Shamil
> >
> >
> > From: "Martin Reid" <mwp.reid at qub.ac.uk>
> > To: <dba-sqlserver at databaseadvisors.com>
> > Sent: Thursday, April 08, 2004 11:24 PM
> > Subject: [dba-SQLServer] OLEDB
> >
> >
> > > You have moved your databases up to SQL Server. What would you use
> for
> > the
> > > front end connection, for example would you change to OLEDB??
> > >
> > >
> > > Martin
> > >
> > > _______________________________________________
> > > dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > > http://www.databaseadvisors.com
> > >
> >
> > _______________________________________________
> > dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> >
> > _______________________________________________
> > dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> >
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com




More information about the dba-SQLServer mailing list