Would anyone know how well Access can handle large number of tables and handling the queries? Perhaps it's not the best database design, but I have with me a database that stores values for 42 variables (x dimension) by 267 variables (y dimension) by 8 units of time (z dimension). I need to run a report based on a query that joins more than 15 tables together, and some times, they includes calculated fields. I found that this task has not been possible (takes mor than 30 mins for the report to be printed on preview), but I really need that query to run. I'm seeking for more efficient and effective ways of handling 3 dimensional data that can go rather deep. Is Access of any help at all? If so, how should I design the db, if not, what are my alternatives? Thank you so much.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Huge sql query, very slow performance, pls help
- Thread starter Sammy25
- Start date
I have had luck with joining lots and lots of tables, but I do see the reports run very slow.
My recomendation is change your query to a make table query, and base the report on that table, so the records are all static. That seems to enhance performance a great deal.
ChaZ
My recomendation is change your query to a make table query, and base the report on that table, so the records are all static. That seems to enhance performance a great deal.
ChaZ
Sammy25,
You don't provide too much information on your table structures, indexes, etc. Maybe we don't want to know.
Consider creating temporary tables for your reports. Deleter the records from the "report" tables and then run some append queries to fill them up. Then base your reports in part on these tables.
Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]
You don't provide too much information on your table structures, indexes, etc. Maybe we don't want to know.
Consider creating temporary tables for your reports. Deleter the records from the "report" tables and then run some append queries to fill them up. Then base your reports in part on these tables.
Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]
- Status
Similar threads
- Locked
- Question
