UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Querying Takes A Lot Of Time For Timekeeping Purpose, Access 2007    
 
   
phil_andre
post Dec 15 2017, 05:32 AM
Post#1



Posts: 212
Joined: 7-December 16



Dear Support Team,

Greetings!

Please your usual support please on my attached DB.

I am getting a problem when i am making a query with more than 16 joints on the same table, it takes a lot of time to read.

If i make a joint from T01 to T016 it loads bit faster.

but if i make a joint from T01 to T17 it takes a lot of time to read, especially T01 to T31.



I need to design a report in tabular mode instead of listing... from other sources.

Kindly support please. it is quite urgent for me... thanks a lot.
Attached File(s)
Attached File  Urgent.zip ( 230.15K )Number of downloads: 7
 
Go to the top of the page
 
GroverParkGeorge
post Dec 15 2017, 08:01 AM
Post#2


UA Admin
Posts: 33,034
Joined: 20-June 02
From: Newcastle, WA


As a general rule, the more complex the joins in a query, the greater the likelihood that you are going to run into this kind of problem. 16 tables in one query is probably on the far side of complexity and it's not unreasonable to think that something less efficient is going on.

For starters, are all of the fields involved in all of the joins indexed in their respective tables?

What are the datatypes of the join fields?

Also, are you doing any kind of aggregation or calculation or formatting of data in any of the queries?

Do any of the queries involve VBA functions?

It may well be that you're just pushing past the point where it's reasonable to expect acceptable performance out of a very complex query.
This post has been edited by GroverParkGeorge: Dec 15 2017, 08:07 AM

--------------------
Go to the top of the page
 
phil_andre
post Dec 15 2017, 08:07 AM
Post#3



Posts: 212
Joined: 7-December 16



Datatype is numerical same in my attached.

EID.

Will add my design report for better understanding what i need.
This post has been edited by phil_andre: Dec 15 2017, 08:09 AM
Go to the top of the page
 
GroverParkGeorge
post Dec 15 2017, 08:16 AM
Post#4


UA Admin
Posts: 33,034
Joined: 20-June 02
From: Newcastle, WA


"...For starters, are all of the fields involved in all of the joins indexed in their respective tables?

What are the datatypes of the join fields? "EID" is a Number.

Also, are you doing any kind of aggregation or calculation or formatting of data in any of the queries?

Do any of the queries involve VBA functions? ..."

--------------------
Go to the top of the page
 
GroverParkGeorge
post Dec 15 2017, 09:11 AM
Post#5


UA Admin
Posts: 33,034
Joined: 20-June 02
From: Newcastle, WA


It occurs to me that this might be a situation where you would be ahead to create a temporary table to hold the results of the component queries, and append those records to it in sequence.

Although, in general, one might prefer not to incur the bloat associated with deleting and appending records, the performance gain could be a worthwhile trade-off.

--------------------
Go to the top of the page
 
phil_andre
post Dec 15 2017, 10:05 AM
Post#6



Posts: 212
Joined: 7-December 16



Dear Support Team,

Please download my updated demo. and kindly check...

added report rpt_TimeCard.

Please try to join qry T01 to T31 and run it and you will get stuck , that is my big problem... how to make it run smoothly or any advise..

i am just demonstrating the scenario in my demo...

kindly upload back the database with correction... tnx a lot.
Attached File(s)
Attached File  Urgent_Scenario.zip ( 259.72K )Number of downloads: 2
 
Go to the top of the page
 
GroverParkGeorge
post Dec 15 2017, 10:25 AM
Post#7


UA Admin
Posts: 33,034
Joined: 20-June 02
From: Newcastle, WA


As I said, I am of the opinion that this is a case where you might be ahead using a temp table to accumulate the results.

You're probably not going to get that 31 table query to respond very well.


--------------------
Go to the top of the page
 
phil_andre
post Dec 15 2017, 10:27 AM
Post#8



Posts: 212
Joined: 7-December 16



Yes sir you are right, a temp table just to accumulate the report.

i am just uploading the data to access from exported data excel from other ERP.

but the report in our ERP is listing type and i would to design a report in access which is rpt_timecard.
This post has been edited by phil_andre: Dec 15 2017, 10:30 AM
Go to the top of the page
 
phil_andre
post Dec 15 2017, 11:19 AM
Post#9



Posts: 212
Joined: 7-December 16



qry_T01 to qry_T15 jointed to tbl_GT and it save faster and no issue.

but when i jointed qry_T01 to T020 or till T31 then it stuck,, takes a lot of time to save the query... my mouse cursor rounding2x so i have to end task the process...

I am thinking that maybe ms access limited to fast save if more than 15 Joints to the same table. because 16 joints above it really suck or i did something wrong or any alternative solution.


I really hope that someone here can fixed this... my manager need this report by max by Sunday and i am worried because i already that i can give this type of report and then i found this problem...

Because i don't have any problem when am initiated query before qry_T16 sequence and this is the problem now and i am thinking alternative solution to give answer to my boss.
This post has been edited by phil_andre: Dec 15 2017, 11:32 AM
Go to the top of the page
 
GroverParkGeorge
post Dec 15 2017, 11:31 AM
Post#10


UA Admin
Posts: 33,034
Joined: 20-June 02
From: Newcastle, WA


All I can suggest is the same thing I've already suggested twice.

Instead of an extremely complex query, TRY A TEMP TABLE WHICH YOU POPULATE ONE AT A TIME FROM YOUR 31 QUERIES.

--------------------
Go to the top of the page
 
phil_andre
post Dec 15 2017, 11:33 AM
Post#11



Posts: 212
Joined: 7-December 16



Please Mr. George,

can you please upload back my database with your suggestion if you can spare time on that.. sorry for that. still not clear to me.
Go to the top of the page
 
HairyBob
post Dec 15 2017, 11:38 AM
Post#12



Posts: 987
Joined: 26-March 08
From: London, UK


It looks to me that you could use a crosstab query for this - see qryCrosstab in the attachment.

HTH...

Hairy.
Attached File(s)
Attached File  Urgent_Scenario2.zip ( 280.03K )Number of downloads: 1
 
Go to the top of the page
 
GroverParkGeorge
post Dec 15 2017, 11:46 AM
Post#13


UA Admin
Posts: 33,034
Joined: 20-June 02
From: Newcastle, WA


I must not be making myself clear.

I don't think you CAN do this effectively with 31 joins.

I think you need to come up with an alternative.

Create a temp table--and use the crosstab as suggested against it, or just try to use a crosstab against your table.

It is also likely that you COULD get better performance if you have a computer with adequate resources. In other words, more complexity calls for more RAM and CPU power. Add RAM to your machine, or use a different one with say, `6 GB or even 32 GB of RAM. See if that helps significantly.

--------------------
Go to the top of the page
 
phil_andre
post Dec 15 2017, 11:49 AM
Post#14



Posts: 212
Joined: 7-December 16



Hi Bob,

Well, yeah.. thumbs up... :-) , thanks a lot... can you do me a favor to apply it in my rpt_timecard report design... i tried to apply your qry_crosstab but am getting confuse to do so in report..

i need your crosstab to be seen in report... or print output ...


Also thanks a lot to you Mr. George.,,, the crosstab from Mr. Bob is the same result what i need, but i need it to look in my print report like my rpt_timecard./

it loads quickly in crosstab.....

will my pc performance is okay, win 10, 32GB Ram, I7 6700K.
This post has been edited by phil_andre: Dec 15 2017, 11:55 AM
Go to the top of the page
 
phil_andre
post Dec 15 2017, 12:11 PM
Post#15



Posts: 212
Joined: 7-December 16



Dear Mr. Bob,

I can generate the report design from your qry_crosstab.

I need more from you if you can sort like this the type.

Normal
Overtime
Breakdown
IDLE
Fuel

because what i seen your crosstab was sorted A-Z ,, am not really expert in SQL view, i am always in design query...

appreciate your quick reply.

thankks
This post has been edited by phil_andre: Dec 15 2017, 12:12 PM
Go to the top of the page
 
RJD
post Dec 15 2017, 12:19 PM
Post#16


UtterAccess VIP
Posts: 8,481
Joined: 25-October 10
From: Gulf South USA


Hi Phil: PMFJI, but I really don't think you need all those queries. One query should suffice, just adding the day number field to the other fields. Then use a Sum(IIf([D]=1,[Normal],0)) approach in the report.

1 table
1 query
1 report
No temp tables
Runs fast

See the attached revision to your db. I did the first few columns. You can add the others, using the same approach.

HTH
Joe
Attached File(s)
Attached File  Urgent_Scenario_Rev1.zip ( 27.3K )Number of downloads: 5
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
phil_andre
post Dec 15 2017, 12:28 PM
Post#17



Posts: 212
Joined: 7-December 16



Der Mr. RJD,

Many2x thanks. perfect.

thanks a lot to all, i will finalize the report design now.
Go to the top of the page
 
RJD
post Dec 15 2017, 12:42 PM
Post#18


UtterAccess VIP
Posts: 8,481
Joined: 25-October 10
From: Gulf South USA


You are very welcome. We are all happy to assist. thumbup.gif

Good luck with your project...

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
phil_andre
post Dec 15 2017, 12:58 PM
Post#19



Posts: 212
Joined: 7-December 16



Mr. RJD,

Sorry, i want to ask.

how i can make it "-" instead showing 0 for the zero values..

i need zero (0) to be displayed as - "-" if zero value in your if condition.

i tried to changed 0 to - but it gives error message.


=Sum(IIf([D]=1,[Normal],"-"))
This post has been edited by phil_andre: Dec 15 2017, 12:59 PM
Go to the top of the page
 
HairyBob
post Dec 15 2017, 01:10 PM
Post#20



Posts: 987
Joined: 26-March 08
From: London, UK


Hi Phil,

Sorry I didn't get back quickly enough - glad to see that Joe sorted you out.

Hairy thumbup.gif
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    19th July 2018 - 05:02 AM