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