[dba-VB] Can anyone suggest a Windows good client for mySQL

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
>
>



More information about the dba-VB mailing list