External Query Performance Question

[datasponge]

We have many external tools to lookup contact info using ODBC from MySQL on Linux, either from web (ASP migrating to PHP) or via Microsoft Access.

In SugarCRM, the basic contact information is spread over the accounts, contacts and email_addresses table plus custom tables and you have to join using the accounts_contacts and email_addr_bean_rel table. So the query is a join of 7 tables.

In our very old CRM application that had this in one table, querying all contact info took about 5 seconds.

With this query it takes almost a minute.

Does anyone have any recommendations how to improve performance for a basic contact information query that will always be the same?

For example, is this easy to do using Stored Procedures in MySQL on Linux?

Any other suggestions?

Thanks

Phil

[JVWay]

Looks like mySQL has views. That might be a good way to go. Haven't tried it though.

[eggsurplus]

I'd imagine if you reran it a second time it would just take a couple of seconds. If not, do you have enough memory on the server? If it's a query that you run often then it may be worth looking at creating the proper indexes for your query.

[datasponge]

Thanks for the comments. Here's more info (and more questions )

I formed the same query to run it locally on the server and it reported it took 3.03 seconds to return 20530 records.

The missing data was that I was doing this query in MS Access over ODBC ..AND.. joining it to a selection query defined in Access using a LEFT join. My guess is that this combination causes MySQL to return 20000+ records over the network, then join it within Access making for an ugly combination.

This results in the query taking about 45-50 seconds when run this way.

Actually all the data is stored in MySQL on the same server, but it is in multiple databases and all the current tools to relate them are in Access.

So I am researching Stored Procedures as a way to move all the tools into MySQL, then call the stored procedure to populate the current Access forms.

If anyone has any experience with that I would appreciate advice on whether this is possible and how best to go about it.

Also, has anyone used Open Office? Does it have an Access replacement? Does it have tools that would make any of this cleaner and easier?

Thanks again.

Phil

[JVWay]

I have used the Open Office Base program, on Linux, to attach via ODBC to Sugar. It works just fine. I've not tried it across multiple databases though. It's certainly faster than Access and has plenty of options. I was mostly using it as a way to create joins in a graphical view without having to use something like access.

I've got plenty of Access experience but it's just soooo irritating.

JW

[datasponge]

Here's an update on what I've researched so far.

Open Office Base looks pretty good, but doesn't suit my needs right now. It is very clean and it is easy to point it to the sugarcrm database on my MySQL server through ODBC. It might improve performance, but from my poking around there is no way to have it read Access files and we have too many accumulated tools to rewrite them. We may migrate over time with new tools written for Office Base - not sure yet.

Stored Procuedures look very useful, but not in this application. They appear to be very well suited to updating data, doing fast calculations or manipulation, etc, but not well suited to a subquery sort of model that returns a large volume of data then selects data from it or joins to another table.

I understand the performance issue a little better now and it is specifically related to mixing access queries with high data volume MySQL queries. Basically by joining MySQL to a local Access query, you're telling Access to retrieve all the records over the network, then match them up on the client by chugging through all the data. That creates a performance hit in my specific tables of about 15X.

So with some more intelligent selection of records that minimizes the volume of data sent over the network then handled by Access, I think I can make the performance acceptable.

I'd still love to learn how to better to most of the work in advance on the server, so I plan to look more closely as subqueries and other tools, so if anyone out there has good tips, please share.

Thanks

Phil

[datasponge]

Further update on this.

The problem is indeed with returning a large number of records to Access. I have found that Access is very slow at applying filters, but very fast selecting individual records as a Record Source.

So for example, if I define a query in Access that uses a complex join, but only of SugarCRM tables (accounts, accounts_cstm, accounts_contacts, contacts, contacts_cstm, email_addr_bean_rel, email_addresses), then select a limited set of records using an indexed field, the response is almost instantaneous for less than a few thousand records.

For example calling the query above SugarQuery I can set Record Source of a form programmatically to:

SELECT * FROM SugarQuery WHERE accounts.name LIKE 'A*';

This will almost instantly return all records whose account name start with A.

The key is having the accounts table indexed on the name field and structuring the form so that it selects data first, so doesn't send a large volume of data over the network.

Hopefully this might help someone else out there tired of waiting for queries to finish.

Phil

[juandelapg]

Hi,
I have a question, How can I access to Sugar MySql data with other aplication???? I can use the info of sugar, but how can I do it if I'm in other aplication that I want to see the info of the DataBase...
Thank vey much

[joel.oliveira]

Hi,

Sorry for the late and a little ambiguous post but here goes my 20 cents. Depending on your kind of application you can always access the Sugar DB.

For example in my PHP application I only have to do :

$db = mysql_pconnect("localhost","sugarcrm","sugarcrm");
mysql_select_db("sugarcrm",$db);

The rest you can figure yourself by doing some googlin' .

Hope that this post answered your question

Best regards,
Joel Oliveira

[kuske]

It is a real good idea to use mySQL Views for such queries.

We implemented an activities_view for our reporting which creates a UNION SELECT of calls meetings and tasks.
This view can than be linked in MS Access to get results in short time.