[dba-SQLServer] Upsize wizard and named queries

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




More information about the dba-SQLServer mailing list