Jim Lawrence
accessd at shaw.ca
Fri Oct 23 13:09:58 CDT 2009
Hi Mark: If you can, it is better to use ADO-OLE than ODBC-Pass-Through. I stopped using ODBC, except in extreme circumstances over 10 years ago and have never looked back. Back in 1997, a client did month end reports through MS Access to a MS SQL BE DB via a pass-through implementation. The report was done in two separate sections with each section taking 50 minutes before the report would start. They would do one report in the morning and one in the afternoon. The query was a 'Select * ..." type. When I converted their month-end report to ADO-OLE and used a SP. The results were that the entire report started in less than 2 seconds. See the sample code at: http://www.databaseadvisors.com/newsletters/newsletter112003/0311UnboundRepo rts.asp HTH Jim -----Original Message----- From: dba-vb-bounces at databaseadvisors.com [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Mark Breen Sent: Friday, October 23, 2009 3:55 AM To: Discussion concerning Visual Basic and related programming issues. Subject: Re: [dba-VB] Can anyone suggest a Windows good client for mySQL Hello Jim, thanks for that, yes, I will train him on the benefits of utilising the Client - Server aspect of the db, Just curious though, if I want to return all rows from the web based hosting centre, and I have to choices of a) Select * from Customers ran as a PST query or b) usp_GetAllCustomers ran as a PST query or c) Select * from Customers ran as a linked table in Access do you think that there would be any noticeable performance difference, my instinct would be that there would be none. Of course if there was a million orders and we wanted to sum all orders where customer id = 22124, that would have to be better as Pass Through, but I would assume minor differences between an sproc and simply including all the SQL within the PST query. We would loose the precomplile but from a data and query perspective, I would expect similar (1-2 seconds) performance. Thanks Mark 2009/10/22 Jim Lawrence <accessd at shaw.ca> > Hi Mark: > > If you are setting up MySQL you should start thinking of using SPs. MySQL > version 5 has this feature and pass-through queries in MSAccess, especially > SELECT type queries are soooo slowwwwww. > > Check out the following: > > http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.html > > HTH > Jim > > -----Original Message----- > From: dba-vb-bounces at databaseadvisors.com > [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Mark Breen > Sent: Thursday, October 22, 2009 10:54 AM > To: Discussion of Hardware and Software issues; Discussion concerning > Visual > Basic and related programming issues. > Subject: [dba-VB] Can anyone suggest a Windows good client for mySQL > > Hello All, > I have to assist a friend tomorrow in connecting to a mySQL database and > starting to build some queries. > > I intend to show him the differences between writing queries in MS Access > when linked to mySQL, and using pass-through queries within Access. > > I will also be explaining the benefits of having a few views created also. > > What I am looking for is a Windows based GUI tool that would connect to > mySQL and allow him to build queries with click and drag like SSMS or > Access > does. > > This evening I have downloaded and installed two tools, one is the default > GUI as shipped by mySQL and another is named EnginSite. Both seem, on > initial looking anyway, to to allow me to drag a few fields from a table, > but they do not seems to allow me to drag two or three tables and for the > joins to be done in the GUI. > > Would anyone have any suggestions? > > TIA for your time, > > Mark > _______________________________________________ > dba-VB mailing list > dba-VB at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-vb > http://www.databaseadvisors.com > > _______________________________________________ > dba-VB mailing list > dba-VB at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-vb > http://www.databaseadvisors.com > > _______________________________________________ dba-VB mailing list dba-VB at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-vb http://www.databaseadvisors.com