Serious Alpha to MySQL Speed Issue

[FSNut01]

This question has been asked before but I haven't found any resolutions that seem to apply to my situation.

I have set up a web app to create some pages for our Volunteer Fire Dept members to look at to see their attendance records. The response by the grids is painfully slow. The initial return takes about a minute and a half on some grids. When I am working on them from within Alpha 5 it takes just as long to save or preview my work. Ajax callbacks take about 30 seconds to complete.

Here's my setup. The MySQL database resides on a Windows Small Business server. The WAS is on a separate Windows XP machine on a different network. The connections are by Alpha DAO. I am developing on my laptop using a Verizon Air card for connectivity but I get similar results on high speed connections.

The grid gets its data from a view in the MySQL database.The view consists of 2 separate select queries joined together in a union query. It is necessary to do it this way to combine data from 2 different tables. One of the tables records alarm attendance records and the other table records all other attendance records. There are also several other tables involved through joins to gather related information.

I realize this is a rather complex query and in and of itself requires some degree of time to return. However when I run the view from a stand alone query tool the data is returned in about 15 seconds. This isn't ideal but would be acceptable. The grids in Alpha 5 or in the browser are taking almost a minute and a half. Clearly not acceptable.

Does anyone have any thoughts on how to speed this up?

[rsibrent]

Re: Serious Alpha to MySQL Speed Issue

Need to know more about the tables. How many rows? Are the fields in your filter and join conditions indexed? How big is the result set?

[FSNut01]

Re: Serious Alpha to MySQL Speed Issue

The primary tables in the view are 23,000 and 43,000 rows. The union results in 66,000 rows. The result sets are based on the user log in ID so they only see their own records. The joined fields and the filtered fields are indexed.

[rsibrent]

Re: Serious Alpha to MySQL Speed Issue

66,000 records in a result set is probably going to be slow no matter how you slice it. A 15 second query on tables of this size is pretty slow. This can probably be optimized.

Would you mind posting your MySQL view definition and the Alpha SQL code you're using to query the view?

[ierpsite]

Re: Serious Alpha to MySQL Speed Issue

Did you try mysql and alpha on the same network? I had performance problems when I had mysql on a different server

thanks
raj

[Selwyn Rabins]

Re: Serious Alpha to MySQL Speed Issue

This question has been asked before but I haven't found any resolutions that seem to apply to my situation.

I have set up a web app to create some pages for our Volunteer Fire Dept members to look at to see their attendance records. The response by the grids is painfully slow. The initial return takes about a minute and a half on some grids. When I am working on them from within Alpha 5 it takes just as long to save or preview my work. Ajax callbacks take about 30 seconds to complete.

Here's my setup. The MySQL database resides on a Windows Small Business server. The WAS is on a separate Windows XP machine on a different network. The connections are by Alpha DAO. I am developing on my laptop using a Verizon Air card for connectivity but I get similar results on high speed connections.

The grid gets its data from a view in the MySQL database.The view consists of 2 separate select queries joined together in a union query. It is necessary to do it this way to combine data from 2 different tables. One of the tables records alarm attendance records and the other table records all other attendance records. There are also several other tables involved through joins to gather related information.

I realize this is a rather complex query and in and of itself requires some degree of time to return. However when I run the view from a stand alone query tool the data is returned in about 15 seconds. This isn't ideal but would be acceptable. The grids in Alpha 5 or in the browser are taking almost a minute and a half. Clearly not acceptable.

Does anyone have any thoughts on how to speed this up?

it is not an alpha five issue. it is a mysql issue.

alpha five is not doing the query. mysql is doing the query.
alpha five is merely sending a sql statement to mysql and mysql is then executing it. when mysql has completed, it makes a result set available to alpha five.

alpha five has nothing to do with how long the mysql engine takes to do a query.

you need to figure out why mysql is taking a long time to do the query.

[Pat Bremkamp]

Re: Serious Alpha to MySQL Speed Issue

Doug,

I think there is definately something wrong. I have a very complex query in MySQL that pulls from 60 million records (from the combined joins) in about 2 seconds.

Is Alpha pulling from the view (preferred), or have you recreated the select statements in Alpha?

Pat

[Selwyn Rabins]

Re: Serious Alpha to MySQL Speed Issue

Doug,

I think there is definately something wrong. I have a very complex query in MySQL that pulls from 60 million records (from the combined joins) in about 2 seconds.

Is Alpha pulling from the view (preferred), or have you recreated the select statements in Alpha?

Pat

i think you need to clarify here that you think that there is something wrong with his mysql database, not with alpha five.
(I presume that's what you meant).

[Pat Bremkamp]

Re: Serious Alpha to MySQL Speed Issue

Actually, I dont think either MySQL or Alpha is the problem. I think the problem is "There are also several other tables involved through joins"

Pat

[FSNut01]

Re: Serious Alpha to MySQL Speed Issue

I agree that it is a MySQL issue. I'm open to any suggestion that will fix it.

I have to retract my statement above that it was faster from the query browser. It was actually only returning a partial result set in that time. It actually took a little longer than Alpha to return the full set.

I don't know how to assemble the necessary data any other way than how I have it. I have tried many variations of the query over the past several months and this seems to be the only way I can gather the required fields. The MySQL database was created by the vendor of our Fire Dept records system. They have a proprietary front end to it. I am trying to develop some web based views into the data for the general membership of the dept. I could probably ask the original creators of the database for suggestions too. Their reporting against it runs pretty fast.

Alpha is pulling from the view.

The view is as follows:

SELECT `nia`.`NIID` AS `EVENT_ID`,`nia`.`NIATTID` AS `ATTEND_ID`,`nia`.`PERSID` AS `PERSID`,
`p`.`LNAME` AS `LNAME`,`p`.`FNAME` AS `FNAME`,`n`.`DATETIMESTART` AS `ATTEND_DATE`,
`n`.`DESCR` AS `DESCR`,`nc`.`DESCR` AS `ATTEND_TYPE`,year(`n`.`DATETIMESTART`) AS `ATTEND_YEAR`
FROM (((`niatt` `nia` join `ni` `n` on((`nia`.`NIID` = `n`.`NIID`))) 
JOIN `pers` `p` on((`nia`.`PERSID` = `p`.`PERSID`)))
JOIN `nilosapcat` `nc` on((`nc`.`NILOSAPCATID` = `n`.`NILOSAPCATID`))) 
WHERE (`p`.`PERSTYPEID` in (1,3,8,9)) 
UNION
SELECT `nfa`.`NFIRSMAINID` AS `EVENT_ID`,`nfa`.`NFIRSATTID` AS `ATTEND_ID`,`nfa`.`PERSID` AS `PERSID`,
`p`.`LNAME` AS `LNAME`,`p`.`FNAME` AS `FNAME`,`nfm`.`DATETIMEALARM` AS `ATTEND_DATE`,
`nfm`.`NARR` AS `DESCR`, 'Alarms' AS `ATTEND_TYPE`,year(`nfm`.`DATETIMEALARM`) AS `ATTEND_YEAR`
FROM ((`nfirsatt` `nfa` join `pers` `p` on((`nfa`.`PERSID` = `p`.`PERSID`))) 
JOIN `nfirsmain` `nfm` on((`nfm`.`NFIRSMAINID` = `nfa`.`NFIRSMAINID`))) 
WHERE (`p`.`PERSTYPEID` in (1,3,8,9)) order by `ATTEND_DATE`;

[rsibrent]

Re: Serious Alpha to MySQL Speed Issue

What are the parent/child relationships in these tables? Are these one-to-many, many-to-many, etc.? You have 5 joins and a union.

If it is slow in the MySQL query browser, it will certainly be slow from Alpha. Are you filtering the view in Alpha? If you are, copy the view code and add the additional filter criteria to it, run it as a query, and skip the view altogether.

[bea2701]

Re: Serious Alpha to MySQL Speed Issue

1)
To eliminate the problems run the query by mysql workbench on the server if the response is the same check the querypath in sqlmonitor, check how long the pid needs to run the query object on the mysql engine, check the are no spookey proces running on the DB engine. A normal query result needs to prompt result in about 2-3 seconds. Check the logfiles on the MySQLlserver for errors

2)
If the response on the DB engine is oke next connect your laptop to the same network as only one network with out gateways.
Run the query again from the laptop check the results is the result is eq step 1.

3) run step 2 Check on a network other then your default network consult your DBA en network administrator

4) run step 3 compare the result

[GarthMc]

Re: Serious Alpha to MySQL Speed Issue

one possibility is the UNION

I see you are hardcoding the contents of the first column to be different. A union will sort and merge the data to remove duplicated rows.

Try UNION ALL which will not add this overhead

Garth

I re-read the query... its not hard coded but the observation still stands....

[frankbicknell]

Re: Serious Alpha to MySQL Speed Issue

Do you have all of the tables indexed on the join fields?

[Pat Bremkamp]

Re: Serious Alpha to MySQL Speed Issue

Doug,

Here's what I would do.

Yu can build views out of views, so first, try building several smaller views to do the joins and a separate view to do the union. Look for smaller views that minimize the number of records in the result set. For example:

Since you are looking for attendance records for a person, start with pers instead of nia and filter it on perstypeid to minimize the number of records in that view.

Then join that to niatt to get the events and ni to get the date and description, etc and make the union the last view.

As you move step by step, check the time to query.

Pat