bigint data type and system latency (1 Viewer)

zgray

Registered User.
Local time
Today, 08:35
Joined
Mar 8, 2013
Messages
55
Hello,

We have an SQL backend with our access front end. We use a custom unique ID for the primary keys in our tables that is an INT data type. In the coming months another system that our data base talks to is changing that unique ID to be over 10 digits. To accommodate that change in our system we changed all the data types from int to big int.

This is working across the board but it seems to have caused major latency issues system wide if you want to pull any type of report. In our troubleshooting we found that if you create a query in Access with just ONE table it works fine but once you link two or more tables it slows down considerably. For example I created a simple query that pulled 12 fields from two tables and it took just under 9 minutes to pull 57 records. We also verified that the keys are indexed.

We are using the most recent version of MS Access that comes with the o365 license. Any help would be appreciated in trying to solve the latency issue.
 

isladogs

MVP / VIP
Local time
Today, 12:35
Joined
Jan 14, 2017
Messages
18,186
I've never needed to use bigint and suggest you avoid it if you can.
Normally ID fields use long integer which allows for 10 digits from -2,147,483,648 to 2,147,483,647.
If that's not quite enough, use Single or Double

The info below is from https://support.microsoft.com/en-us...0ad644f-946c-442e-8bd2-be067361987c#refnumber.
1602164754876.png

BTW although an official MS webpage it contains errors for Single/Double & Decimal datatypes in that you need to replace all 'x' by '10^'
e.g. 3.4 x 1038 should actually be 3.4 x 10^38.

Hope that helps
 

namliam

The Mailman - AWF VIP
Local time
Today, 13:35
Joined
Aug 11, 2003
Messages
11,696
We use a custom unique ID for the primary keys in our tables
This is where your troubles start(ed), never use a meaningfull value as your primary key !
I doubt the change from int to bigint is the real problem... most likely you have linked the tables and are running a query in access.... as a result it is pulling the data to access instead of pushing the query to the database.

Your best, though not easiest solution is to fix your issue by fixing the root cause which is having a meaningfull primary key and use a seperate field to store your meaningfull information.
 

Users who are viewing this thread

Top Bottom