access query taking too much time (1 Viewer)

zubair mirza

New member
Local time
Today, 21:43
Joined
Jul 30, 2020
Messages
6
hi
i have a mobile phone database of 20+ tables of same kind data. All these tables have more then 400 million records. all 20+ tables have 5 columns, Subscriber Number, Name, Address, CNIC, and BVS. i run this code to fetch the specific record:
SELECT Telenor1.SUBNO, Telenor1.Name, Telenor1.CNIC, Telenor1.Address, Telenor1.BVS, "Telenor" AS QRYTYPE, "Telenor1" AS DBTYPE
FROM Telenor1
WHERE Telenor1.SUBNO=[Forms]![frmSearchNUM]![tbNUM]
UNION SELECT Telenor2.SUBNO, Telenor2.Name, Telenor2.CNIC, Telenor2.Address, Telenor2.BVS,"Telenor" AS QRYTYPE, "Telenor2" AS DBTYPE
FROM Telenor2
WHERE Telenor2.SUBNO=[Forms]![frmSearchNUM]![tbNUM]

and so on include all the tables like this and when i run the query, query runs fine and i get the record but it take too much time like 10 mins. if anyone can help me. how can it run fast
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:43
Joined
Oct 29, 2018
Messages
11,114
Hi. Welcome to AWF!

Have you considered combining all the data into one table?
 

Isaac

Well-known member
Local time
Today, 09:43
Joined
Mar 14, 2017
Messages
1,497
hi
i have a mobile phone database of 20+ tables of same kind data. All these tables have more then 400 million records. all 20+ tables have 5 columns, Subscriber Number, Name, Address, CNIC, and BVS. i run this code to fetch the specific record:
SELECT Telenor1.SUBNO, Telenor1.Name, Telenor1.CNIC, Telenor1.Address, Telenor1.BVS, "Telenor" AS QRYTYPE, "Telenor1" AS DBTYPE
FROM Telenor1
WHERE Telenor1.SUBNO=[Forms]![frmSearchNUM]![tbNUM]
UNION SELECT Telenor2.SUBNO, Telenor2.Name, Telenor2.CNIC, Telenor2.Address, Telenor2.BVS,"Telenor" AS QRYTYPE, "Telenor2" AS DBTYPE
FROM Telenor2
WHERE Telenor2.SUBNO=[Forms]![frmSearchNUM]![tbNUM]

and so on include all the tables like this and when i run the query, query runs fine and i get the record but it take too much time like 10 mins. if anyone can help me. how can it run fast
@zubair mirza
If you do not specifically need your Union operation to de-duplicate the results, then consider changing UNION to UNION ALL. This should speed up considerably, because it eliminates the Union operation of removing duplicates.

Secondly, is SUBNO column indexed in the Table? It should be
 

zubair mirza

New member
Local time
Today, 21:43
Joined
Jul 30, 2020
Messages
6
@zubair mirza
If you do not specifically need your Union operation to de-duplicate the results, then consider changing UNION to UNION ALL. This should speed up considerably, because it eliminates the Union operation of removing duplicates.

Secondly, is SUBNO column indexed in the Table? It should be
no SUBNO is not indexed
and here it code attached in a text file .. if you do it for me .. UNION ALL
and thank to reply
 

Attachments

jdraw

Super Moderator
Staff member
Local time
Today, 12:43
Joined
Jan 23, 2006
Messages
13,268
I agree with theDBGuy -consider 1 table and add fields QRYTYPE and DBTYPE.

400 million records??
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 11:43
Joined
Feb 28, 2001
Messages
17,310
I believe this cannot be put into a single table if this is an Access BE file because a single table that size would exceed 1 GB. Phone number and name would surely be more than 2 1/2 bytes and 400 Mill * 2 1/2 = 1 G. Access specs tell us no single table can exceed 1 GB. If this information was in a non-Access backend, then it would be possible, perhaps.

It would help us to know which fields are being searched. I.e. do you always look for specific names or do you always look for specific numbers or can you search ANY field? What are the most common searches you do?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:43
Joined
Oct 29, 2018
Messages
11,114
Access specs tell us no single table can exceed 1 GB.
Hmm, I had to look that up, and here's what I found.
spec.png
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 11:43
Joined
Feb 28, 2001
Messages
17,310
So a 2 GB limit means 400 million records X 5 bytes...? Still seems unlikely to fit 400Mill name and phone number records in a single table, much less the other stuff implied by the constant fields. If a table can't get there, pretty sure a query will have a hard time, too.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:43
Joined
Oct 29, 2018
Messages
11,114
So a 2 GB limit means 400 million records X 5 bytes...? Still seems unlikely to fit 400Mill name and phone number records in a single table, much less the other stuff implied by the constant fields. If a table can't get there, pretty sure a query will have a hard time, too.
I see what you're saying, but I didn't get the impression from the OP, that the data source they are trying to UNION were stored in separate database files. Here's what they said:
i have a mobile phone database of 20+ tables of same kind data. All these tables have more then 400 million records.
So, if the OP only has one database file with 20+ tables with a total number of records over 400 million, and Access tables cannot go over 2GB, then maybe we're not talking about an Access backend here.
 

Isaac

Well-known member
Local time
Today, 09:43
Joined
Mar 14, 2017
Messages
1,497
no SUBNO is not indexed
and here it code attached in a text file .. if you do it for me .. UNION ALL
and thank to reply
Ok, you just want help replacing UNION with UNION ALL? Ok........I did this for you, and attached the new file on this post.
You should definitely consider creating an INDEX (duplicates maybe ok) on SUBNO.

The union to union all and the index are the top improvements I can think of. The others have mentioned combining to one table, that may help as well.
 

Attachments

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 11:43
Joined
Feb 28, 2001
Messages
17,310
True, DBG, but my post #6 DID suggest it would work if it was NOT on an Access back-end. Just wasn't a lead-in sentence.
 

jdraw

Super Moderator
Staff member
Local time
Today, 12:43
Joined
Jan 23, 2006
Messages
13,268
My question is -- do all records have to be online at the same time? 400 million? What is the reasonable working set? Is there a current subset that could be in 1 table, and the remainder in "possibly another database"?
Concept here is current year and archives of 5 year for example.
 

zubair mirza

New member
Local time
Today, 21:43
Joined
Jul 30, 2020
Messages
6
I believe this cannot be put into a single table if this is an Access BE file because a single table that size would exceed 1 GB. Phone number and name would surely be more than 2 1/2 bytes and 400 Mill * 2 1/2 = 1 G. Access specs tell us no single table can exceed 1 GB. If this information was in a non-Access backend, then it would be possible, perhaps.

It would help us to know which fields are being searched. I.e. do you always look for specific names or do you always look for specific numbers or can you search ANY field? What are the most common searches you do?
only two field searched .. .. Subscriber No and CNIC
 

zubair mirza

New member
Local time
Today, 21:43
Joined
Jul 30, 2020
Messages
6
Ok, you just want help replacing UNION with UNION ALL? Ok........I did this for you, and attached the new file on this post.
You should definitely consider creating an INDEX (duplicates maybe ok) on SUBNO.

The union to union all and the index are the top improvements I can think of. The others have mentioned combining to one table, that may help as well.
i did everything i know and then i post my problem here.... i think there is something else ... may be my computer is infected ... i tried union all and the result is same 10 min wait.... thanks anyway.....
 

zubair mirza

New member
Local time
Today, 21:43
Joined
Jul 30, 2020
Messages
6
I see what you're saying, but I didn't get the impression from the OP, that the data source they are trying to UNION were stored in separate database files. Here's what they said:

So, if the OP only has one database file with 20+ tables with a total number of records over 400 million, and Access tables cannot go over 2GB, then maybe we're not talking about an Access backend here.
i forgot to tell that i have linked these tables so there is no 1 file ..... see the attachment screen shot
 

Attachments

Isaac

Well-known member
Local time
Today, 09:43
Joined
Mar 14, 2017
Messages
1,497
i did everything i know and then i post my problem here.... i think there is something else ... may be my computer is infected ... i tried union all and the result is same 10 min wait.... thanks anyway.....
That does seem odd. Union All is normally much faster than Union, due to not needing to evaluate for duplicates.

Out of curiousity, let me ask you this question: What are you comparing this experience to, that makes you believe this query "should" be much faster? Microsoft Access dealing with tables that have 400 million records is absolutely well into the "can barely handle it" range. I've never even come remotely close to using Access to deal with that big of data. In my experience, Access bogs down horribly at even a much smaller level.
My expectation would be that such a query would easily take 10 minutes or even more, so I am curious what makes you feel that this result should be different?
Can you upgrade your back end to SQL server?
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 11:43
Joined
Feb 28, 2001
Messages
17,310
Now I'm curious about something else. You can surely have more than one BE file by pointing each of those different table links to various places. How many back end files are there?

Here is an experiment for you. In your environment, copy any one of larger the back-end files and time it. Doing a "straight copy" is the fastest operation you can possibly execute on a disk file (other than deleting it). Do the math. How long would it take your actual system to copy all of the files? Because that is the absolutely fastest non-trivial operation you will ever perform. Anything else will be slower on that machine.

Also, you cannot forget that Access is single-threaded. That is, even if your system has multiple cores, Access operations are linearized. They are NOT coded to use parallel processing capabilities.
 

zubair mirza

New member
Local time
Today, 21:43
Joined
Jul 30, 2020
Messages
6
That does seem odd. Union All is normally much faster than Union, due to not needing to evaluate for duplicates.

Out of curiousity, let me ask you this question: What are you comparing this experience to, that makes you believe this query "should" be much faster? Microsoft Access dealing with tables that have 400 million records is absolutely well into the "can barely handle it" range. I've never even come remotely close to using Access to deal with that big of data. In my experience, Access bogs down horribly at even a much smaller level.
My expectation would be that such a query would easily take 10 minutes or even more, so I am curious what makes you feel that this result should be different?
Can you upgrade your back end to SQL server?
... i have done it before and i will do it again... my query is working fine and the only problem is time.. and i will manage it very soon... 400 million records are really hard to handle but it can be handle.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom