Jim Lawrence
accessd at shaw.ca
Sat Jan 21 00:37:12 CST 2006
Hi John: I have been working with ODBC and ADO-OLE connections for a number of years. (almost 10.) IMHO with a SQL server as the BE and connecting with ODBC the performance can be faster, doing UPDATE and DELETE but retrieving large Recordsets should be avoided.... it can be very slow and even an MDB would be faster. IMHO do not waste your time and effort with ODBC. Go straight to ADO-OLE. It is simpler to connect, to maintain, using its disconnected architecture is much more stable and the performance is far superior.... With all/most of the queries in SPs, the whole system will be really optimized. I have never had any luck with automated upsizing with queries and have always had to do them manually. It would be best to assume that you will have to manually build them all and go from there. HTH Jim -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of John Colby Sent: January 20, 2006 7:41 PM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] Upsize wizard and named queries Oh yea, I'm reading up on the "gotchas" and it looks like darned few of my queries will upsize correctly. It's interesting now because all of the literature out there discusses upsizing to ADPs and now that is a dead end road. Luckily in this specific case the client is using 2002 so I can at least do conversions one by one from queries to SPs, where the most effect is gained. Does anyone have any experience with how the performance compares between jet with large recordsets and SQL Server "emulating" jet over ODBC connections (linked tables)? IOW, If I just upsize the tables and replace the links to the MDB with links to SQL Server tables will the database take a huge performance hit? This specific db is a smallish (10 active users) "call center" kind of database where users enter tiny little records re the calls they place, but they do look at data about 150k people, divided by state etc. I think I could fairly rapidly upgrade the main form to use SPs to pull just the small set of people data they need to work with which would speed up that main form dramatically I think, even over JET. John W. Colby www.ColbyConsulting.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Susan Harkins Sent: Friday, January 20, 2006 7:50 PM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] Upsize wizard and named queries I thought that someone said I needed to pull all SQL out of properties and create named queries so that the upsize wizard could upsize them. I did so but I am not seeing any indication that the saved queries are being upsized? =======Not all queries will upsize correctly. If you have the book that Martin and I did together, he mentions a number of problems you have to fix before upsizing. I'd look up the page if I had a copy handy, but I don't right this minute. If you don't have it, Martin or I could probably create a short list of gotchas. Susan H. _______________________________________________ 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