Extremely Slow Query/Report (1 Viewer)

[khurram7x]

Hi,


Need your guys help once again. I'm facing a very slow report which is basically derived from multiple queries feeding each other. I tried indexing certain fields in criteria, cut certain table sizes at least 100 times shorter by reducing data which is required in production database but it still runs slow.
I even might need to run full report without any criteria and then I will need to use this report with several other criteria's, but the way it is working it is not gonna work for production!!


Help required please.
I'm a attaching very cut down version of heavily loaded original database, but as said, query is still slow.


Please run the only report 'DailySE' on 'frmSwitchBoard' which should automatically open once database is opened. Please select in criteria 'Supervisor' as 'Kappes, Viktor' and 'Date' as '7/5/2016' and look at the speed it opens the report.


Regards,
K

 

[CJ_London]

for me it opened immediately, but with no data displayed.

I've not tried to track back but see you have union queries - these can be slow.

Similarly group by queries can be slow when used as a source for another query

In both cases because indexing is not 'carried through'

Have you tried using the performance analyser - ran it on rptDailySE_GroupBy_Union_Supervisor and got this

 

[Minty]

Your report contains 3 sub reports , these will slow things down significantly as the queries in them will run for every value in the parent report.

My approach to a similar issue with sub reports was to use a temporary table with all the data for the report correctly gathered in one place, then to format the report accordingly without the need for the sub reports.

The report in question was taking 5-10 minutes to run on a large data-set. It now runs in 5 - 10 seconds worst case.

 

[Galaxiom]

Have not looked at your sample but on seeing the other posts, not this.

With Union queries, be sure to use UNION ALL or the query will spend a lot of time excluding duplicate records. (Unless you need that behaviour of course.)

Another common time waster is to Group on a field that has a condition that will return only one value. In these cases use WHERE or it will waste time checking of variations in the one value before it applies HAVING.

 

[CJ_London]

one other thing about group by - if you are not summing, counting, maxing etc use SELECT DISTINCT instead

 

[Galaxiom]

Another grouping thing especially where there are subqueries. Sometimes a query returns a field that will be aggregated as well as another field which will always have same value within each group. Use a First instead of Group By on that other field because it doesn't matter which of the records is returned for that value. It save processing it as a group

 

[khurram7x]

for me it opened immediately, but with no data displayed.

Because, maybe, frmSelection_Box_SE was not opened and values were not selected. Use Viktor please and 7/6/2016 let's say.

Similarly group by queries can be slow when used as a source for another query

I've not other way to get desired results. I've to combine two totally irrelevant tables and get them to the point to produce desired results.

In both cases because indexing is not 'carried through'
Have you tried using the performance analyser

What does 'Carried through' means please??
Thanks for introducing me to 'Performance analyser'. Used first time, attaching screenshot, even I've applied index on 'Assignment_Date' it still advises to apply index?
Additionally, it shows two fields together to apply index 'Assignmnet_Date, ID'. Does it mean i apply index on 'Assignment_Date' and include 'ID' in the index, or it means two separate indexes on both fields??
Furthermore, one index doesn't fit all queries. Applying multiple indexes for various queries on a single table is ok please??

...My approach to a similar issue with sub reports was to use a temporary table with all the data for the report correctly gathered in one place, then to format the report accordingly without the need for the sub reports...

Thanks Minty, i would really like to use this approach and I read earlier about it somewhere but, i absolutely have not idea about how to implement this.
I think it'll the answer to my slow query, but maybe some more elaboration helps me understand this?

...use UNION ALL or the query will spend a lot of time excluding duplicate records. (Unless you need that behaviour of course.)
Another common time waster is to Group on a field that has a condition that will return only one value. In these cases use WHERE or it will waste time checking of variations in the one value before it applies HAVING.

UNION ALL applied, but obviously it does not make much difference in my case.
Will keep in mind advice about WHERE/HAVING. Good one.

one other thing about group by - if you are not summing, counting, maxing etc use SELECT DISTINCT instead

Always try to do so.

...Use a First instead of Group By on that other field because it doesn't matter which of the records is returned for that value. It save processing it as a group

Got you, but if i select 'First' it shows the mapped 'ID' number in returned query, and now EmployeeName... as in LookUp scenario. Then i need to tblEmployee in the related query to display EmployeeName?

Also, it further slows down when queries feed the report and I run report directly instead of underlying queries!!!

Looking for further advice from you all. I wanna implement database, everyone in the management is looking at my face and i'm stuck with couple of report having same problem... not to mention, the embarrassment (

Thank you.

 

[Minty]

As far as creating a temporary table is concerned I do mine in SQL Server via a stored procedure, and report on that. From Access exactly the same process should work.

Look at your end data-set and imagine it in a flat file. It doesn't matter that a lot of the data will repeat over many lines, as long as all your data is in the one table.

This is the table you will ultimately construct. Start with the top most information, then add and append data to the table using the simplest individual queries you can construct. Run them one after the other to get to your end result. This will almost certainly be quicker than running multiple grouped, sub queries.

Once you have the data in one temporary table your report will not need sub reports and can be run from the table extremely quickly.

 

[CJ_London]

What does 'Carried through' means please??

if a union query uses indexed fields in one or more of its parts, those indexes are not available for other queries which reference the union query.

Does it mean i apply index on 'Assignment_Date' and include 'ID' in the index, or it means two separate indexes on both fields??

looks like it is suggesting a multi field index

 

[khurram7x]

...Look at your end data-set and imagine it in a flat file. It doesn't matter that a lot of the data will repeat over many lines, as long as all your data is in the one table.

This is the table you will ultimately construct. Start with the top most information, then add and append data to the table using the simplest individual queries you can construct. Run them one after the other to get to your end result. This will almost certainly be quicker than running multiple grouped, sub queries.

Once you have the data in one temporary table your report will not need sub reports and can be run from the table extremely quickly.

I think I'll be much better if you could direct me to some article/video on this please. Being still a learner, I'm not getting a complete idea, and need bit of details to get it working.

 

[khurram7x]

if a union query uses indexed fields in one or more of its parts, those indexes are not available for other queries which reference the union query.

looks like it is suggesting a multi field index

Thanks for explanation CJ.

 

[plog]

I'm late to the party and know others are giving good advice, but there's no reason for your UNION.

tblProgress_Daily & tblProgress_Daily_History have the exact same structure. When you have tables like that their data should go in the same table. If you need to differentiate the _History records you add a field in the table to do that--you don't seperate the data via tables. When you do that, you eliminate the need for a UNION.

I see other issues with your table structure:

~Storing data in field names. tblBoQ_Progress has fields like [Today_ManHour], [Budget_ManHour], Delta_ManHour, etc.. WHen you start prefixing field names with values its time for a new table. Instead of those fields in tblBoQ_Progress, you need a ManHours table like so:

ManHours
ManHoursID, autonumber, primary key
ManHours_Type, text, will hold type of ManHours (e.g. Today, Actual, etc.)
ManHours, number, will hold actual value in all those fields now

You've made that mistake in other tables with other field suffixes (_Value, _Hours).

~Redundant data at different levels. tblBoQ_Progress probably shouldn't even have those _ManHour fields because the table beneath them in the relationship (tblProgress_Daily) has those values. If tblBoQ_Progress.Budget_ManHour is equal to the sum of all its tblProgress_Daily.Budget_ManHour records then you shouldn't store that value in tblBoQ_Progress. It becomes a calculable value and you don't store those.

My guess a lot of fields in tblBoQ_Progress are calculated from the lower tables in that relationship and therefore unnecessary. You've got a lot of similiarly named fields all over the place--I'm pretty sure they only need to be in one table and then related properly.

~Improperly structured tables. tblEMp_Rates and tblEmp_Contact_Info have too many fields. I believe you are storing values in field names in both these tables. For example, tblEmp_Contact_Info should be structured as so:

tblEmpContact_Info
tblEmpContact_InfoID, autonumber, primary key
EmployeeID, number, foreign key to tblEmployee
ContactMethod, text, will hold the type of contact method (e.g. Work, Home, Mobile, Extension, etc.)
ContactInfo, text, will hold the actual phone number, email addres, etc.

tblEmp_Rates should be structured similarly with the majority of the fields in there now becoming values in a EmpRateType field.

~What is tblEmp_Training_Records doing? You use it to link tblEmployee to itself on the same ID? This relationship makes no sense at all and does nothing. My best guess is its some sort of hack around another poor structure element.

~I see more, but those are the biggies.

I'd tighten up your structure then come back to whatever you are working on to see if it resolves itself with a better structure.

 

[khurram7x]

I'm late to the party and know others are giving good advice, but there's no reason for your UNION.

tblProgress_Daily & tblProgress_Daily_History have the exact same structure. When you have tables like that their data should go in the same table. If you need to differentiate the _History records you add a field in the table to do that--you don't seperate the data via tables. When you do that, you eliminate the need for a UNION.

Didn't appear to my mind earlier when i was designing. I was only making sure that data is not repeating. I didn't know that UNION slows down the performance and doesn't let indexing carried through as well. Can't do any change for now, because already spent money to get android version development for this database. Any change will need more funding, and I will not get more funding until I make this first version run properly.

~Storing data in field names. tblBoQ_Progress has fields like [Today_ManHour], [Budget_ManHour], Delta_ManHour, etc.. WHen you start prefixing field names with values its time for a new table...

~Redundant data at different levels... My guess a lot of fields in tblBoQ_Progress are calculated from the lower tables in that relationship and therefore unnecessary. You've got a lot of similiarly named fields all over the place--I'm pretty sure they only need to be in one table and then related properly.

Understood, same reasons as above for now. Will get these fixed in near future versions of the program.

~Improperly structured tables. tblEMp_Rates and tblEmp_Contact_Info have too many fields. I believe you are storing values in field names in both these tables. For example, tblEmp_Contact_Info should be structured as...

Restructured them just now because these tables are not used in android version of the program.

~What is tblEmp_Training_Records doing? You use it to link tblEmployee to itself on the same ID?

tblEmp_Training_Records will be used in the future. Removed the link tblEmployeeID to ...Training_Records, it was a mistake.

I'd tighten up your structure then come back to whatever you are working on to see if it resolves itself with a better structure.

Thanks for database design lesson. Will keep these things in mind for upcoming developments. i regret why i didn't ask about design earlier... but in my opinion there aren't too many records in the database itself to make query that slow, even if table structure is not very good??

My point is, I need to keep going for now to get approval for further development!!

Thank you,
K

 

[khurram7x]

Sorted, by using tables instead of queries. Thanks for advice.