Mark Breen
marklbreen at gmail.com
Mon Oct 26 02:24:14 CDT 2009
Hello All, In the past I found it a PITA to use MS Access as a SQL generator for SQL Server because Access / ODBC drivers applied a prefix of the Scheme name for all the tables - dbo_Customers. This meant that I could not build a query and then copy and paste the SQL unless I first renamed the tables, either manually or with code. However, I can let you know that with the ODBC Driver and the mySQL db that I hooked up to, the tables came with just their names - Customers, so I had a successful session on Friday showing my friend how to build queries. He was delighted, one query that takes three seconds used to take them three days in the past. Interestingly, I did some tests on performace of Linked tables compared to pass-through. The biggest query we ran was a six or seven table query that we saved and then ran a cross tab against that. It ran in 2 - 3 seconds, with all tables linked and no pass through used at all. I have to say in that case, or in similar examples it is not worth while running it as pass-through. I know that in certain cases PST will be faster, but I could not demonstrate a practical example on Friday. I know the benefits, so I am not trying to persuade anyone here, but I am just noting that you may find it is not worth jumping through hoops for PST until you know you need it. Thanks to all, Mark 2009/10/23 Jim Lawrence <accessd at shaw.ca> > 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 > > _______________________________________________ > dba-VB mailing list > dba-VB at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-vb > http://www.databaseadvisors.com > >