Performance with views?

[macassist]

Hi again,

I have a view which brings together 18 columns of data from various other tables, as well as subtotals by product, subtotals by state and a national total (by using Unions of different selects in the view definition).

If I search in PHPMyAdmin on a week's data (the week's end date is hard coded into the database on data import), it shows the first 30 records in 20 seconds. Dataface is lucky to do it in under 100 seconds (9 columns are hidden in Dataface).

Is there anything I should be looking for that will help improve the performance? I know I've been a bit sketchy, wasn't sure what info to include.

Thanks

Sean

[shannah]

I haven't used views too much myself, but one aspect that causes this slow performance is that Dataface always checks the size of the view. This is very quick with tables because MySQL caches this value, but with views it actually has to perform the query...

This is what allows it to say "Found x of y records in table foo". It is the calculation of "y" that is costly.

The next version reduces the number of calls to this by a factor of 4 per request, but I'm not sure if that will make all the difference. I am contemplating putting a preference directive into a future version to turn off this calculation.

If you want to manually disable this you can look in the Dataface/QueryTool.php file and try to do workarounds for anywhere you see things like "select count(*) ...".

-Steve

[macassist]

I've gotten around the slow performance by creating a table which has the same data that the view would have - there are a distinct number of import events each month, so I'm just emptying that table and regenerating the new data - this takes between 20 seconds and 1 minute each time - but this one off regeneration is more than saved each time someone calls up the reports. There are many more "user generating reports" events a month than import events.

Thanks for your thoughts, Steve.

Sean