By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 1,223 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

Key Numeric Datatype

P: n/a
Hi All,

What are people's experience in timing Query Performance? I have
tried in vain, but get many unpredictable results, with execution
proceeding in the background and problems of timing precision.

I am trying to assess my applications performance difference using FK
using BYTE instead of LONG type. and not enforcing integrity at the
database level. (ie, Form level). This only becomes SIGNIFICANT when
the field is used highly in Sorting, Filtering, Calculating.

Access automatically assigns "Long Integer" for Autonumber Fields.
This is very inefficient in tables where there is only a small number
of records expected. (Ie, OrderTypeID may only have 6 records).

Anyone like to tell their experience in Datatype Speed issues?

Regards,
Elias Farah
Nov 12 '05 #1
Share this Question
Share on Google+
14 Replies


P: n/a
lo******@hotmail.com (Elias Farah) wrote in news:c6cfe5a6.0308310315.143833d3
@posting.google.com:
Access automatically assigns "Long Integer" for Autonumber Fields.
This is very inefficient in tables where there is only a small number
of records expected. (Ie, OrderTypeID may only have 6 records).


Why?

--
Lyle

Nov 12 '05 #2

P: n/a
On 31 Aug 2003 04:15:34 -0700, lo******@hotmail.com (Elias Farah) wrote:
Hi All,

What are people's experience in timing Query Performance? I have
tried in vain, but get many unpredictable results, with execution
proceeding in the background and problems of timing precision.

I am trying to assess my applications performance difference using FK
using BYTE instead of LONG type. and not enforcing integrity at the
database level. (ie, Form level). This only becomes SIGNIFICANT when
the field is used highly in Sorting, Filtering, Calculating.

Access automatically assigns "Long Integer" for Autonumber Fields.
This is very inefficient in tables where there is only a small number
of records expected. (Ie, OrderTypeID may only have 6 records).


According to the literature, as far as performance goes, an index will
probably never even get used on a table with only 6 records. First of all,
the whole table will probably get cached into memory in a negligible amount
of time, and second, it would take more processing power to use the index
to locate a record than to simply look through the table rows. The
optimizer should figure this out and ignore any index you may have
regardless of whether it's a byte, long, or whatever.

- Steve Jorgensen

----
I would have written you a shorter program,
but I didn't have the time.
Nov 12 '05 #3

P: n/a
TC
Query performance is usually determined by proper indexing & primary-keying
of the underlying tabes. Whether you use BYTEs, INTEGERs or LONGs will have
an imperceptible impact - unless there is something wildly wrong with your
code!

Enforcing integrity at the form level (instead of at the db level) for the
sake of improving performance, just doesn't maske sense. *Someone* has to do
the checks! Why would your code, be more efficient than Access'es?

Sorry to be blunt, but you really are on the wrong track here. If your
queries are not performing well, it is probably because (a) the underlying
tables are not primary-keyed correctly, or (b) the queries are doing
somehing which, by its very nature, can not be done efficiently; eg.
counting the number of records in a million-record table where the letter
"i" is the 3rd, 5th or 8th letter in the person surname ...

HTH,
TC

"Elias Farah" <lo******@hotmail.com> wrote in message
news:c6**************************@posting.google.c om...
Hi All,

What are people's experience in timing Query Performance? I have
tried in vain, but get many unpredictable results, with execution
proceeding in the background and problems of timing precision.

I am trying to assess my applications performance difference using FK
using BYTE instead of LONG type. and not enforcing integrity at the
database level. (ie, Form level). This only becomes SIGNIFICANT when
the field is used highly in Sorting, Filtering, Calculating.

Access automatically assigns "Long Integer" for Autonumber Fields.
This is very inefficient in tables where there is only a small number
of records expected. (Ie, OrderTypeID may only have 6 records).

Anyone like to tell their experience in Datatype Speed issues?

Regards,
Elias Farah

Nov 12 '05 #4

P: n/a
Thanks for your response!

Do you mean to say.........

OrderTable (60,000 Records)
OrderID as Long - Indexed
OrderTypeID as Long - Indexed

OrderTypeTable - (6 Records)
OrderTypeID as Long - Indexed.
OrderType as String

There is many situations where OrderTypeID is used, Searching,
Filtering on a 4-Byte Datatype. Despite the Datatyoe could be an
Integer or Byte.

Do you think the Optimiser would perform better on 60,000 record query
on a 1-Byte Datatype or 4-Byte Datatype? Seems logical to use the
Byte Type, but access railroads me into using Long throughout many
tables if they are joined on an Autonumber field.

60,000 is not huge - but enough records to make performance a concern.
Thanks!

Elias.

On 31 Aug 2003 04:15:34 -0700, lo******@hotmail.com (Elias Farah) wrote:
Hi All,

What are people's experience in timing Query Performance? I have
tried in vain, but get many unpredictable results, with execution
proceeding in the background and problems of timing precision.

I am trying to assess my applications performance difference using FK
using BYTE instead of LONG type. and not enforcing integrity at the
database level. (ie, Form level). This only becomes SIGNIFICANT when
the field is used highly in Sorting, Filtering, Calculating.

Access automatically assigns "Long Integer" for Autonumber Fields.
This is very inefficient in tables where there is only a small number
of records expected. (Ie, OrderTypeID may only have 6 records).


According to the literature, as far as performance goes, an index will
probably never even get used on a table with only 6 records. First of all,
the whole table will probably get cached into memory in a negligible amount
of time, and second, it would take more processing power to use the index
to locate a record than to simply look through the table rows. The
optimizer should figure this out and ignore any index you may have
regardless of whether it's a byte, long, or whatever.

- Steve Jorgensen

----
I would have written you a shorter program,
but I didn't have the time.

Nov 12 '05 #5

P: n/a
On 4 Sep 2003 06:51:32 -0700, lo******@hotmail.com (Elias Farah) wrote:
Thanks for your response!

Do you mean to say.........

OrderTable (60,000 Records)
OrderID as Long - Indexed
OrderTypeID as Long - Indexed

OrderTypeTable - (6 Records)
OrderTypeID as Long - Indexed.
OrderType as String

There is many situations where OrderTypeID is used, Searching,
Filtering on a 4-Byte Datatype. Despite the Datatyoe could be an
Integer or Byte.

Do you think the Optimiser would perform better on 60,000 record query
on a 1-Byte Datatype or 4-Byte Datatype? Seems logical to use the
Byte Type, but access railroads me into using Long throughout many
tables if they are joined on an Autonumber field.

60,000 is not huge - but enough records to make performance a concern.


OK, I see that you are talking about the space required for the foreign
keys, not the keys in the lookup tables themselves. Yes, a smaller key
will result in smaller records, and that could result in a speed increase.

I do use narrow keys for lookup tables that are small and relatively
unchanging, such as those maintained only by the developer.

- Steve Jorgensen

----
I would have written you a shorter program,
but I didn't have the time.
Nov 12 '05 #6

P: n/a
G'day Steve,

Don't you have problems using the "Narrow Keys", as Access seems to
dislike joining on non-identical Datatypes.

Ie: PrimaryKey=LONG wont join to a ForeignKey=INTEGER

It seems like a glaring hole in access, unless you make all your keys,
both Primary & Foreign LONGs, dispite some tables only have 3 or 4
records. (BYTE Type would do fine).

Thanks!

Elias Farah
On 4 Sep 2003 06:51:32 -0700, lo******@hotmail.com (Elias Farah) wrote:
Thanks for your response!

Do you mean to say.........

OrderTable (60,000 Records)
OrderID as Long - Indexed
OrderTypeID as Long - Indexed

OrderTypeTable - (6 Records)
OrderTypeID as Long - Indexed.
OrderType as String

There is many situations where OrderTypeID is used, Searching,
Filtering on a 4-Byte Datatype. Despite the Datatyoe could be an
Integer or Byte.

Do you think the Optimiser would perform better on 60,000 record query
on a 1-Byte Datatype or 4-Byte Datatype? Seems logical to use the
Byte Type, but access railroads me into using Long throughout many
tables if they are joined on an Autonumber field.

60,000 is not huge - but enough records to make performance a concern.


OK, I see that you are talking about the space required for the foreign
keys, not the keys in the lookup tables themselves. Yes, a smaller key
will result in smaller records, and that could result in a speed increase.

I do use narrow keys for lookup tables that are small and relatively
unchanging, such as those maintained only by the developer.

- Steve Jorgensen

----
I would have written you a shorter program,
but I didn't have the time.

Nov 12 '05 #7

P: n/a
Douglas,

Thankyou for your time. I am not an expert, but an struggling to
understand as everyone is talking on tangents. My question maybe not
worded too well either!!

Access wont let you create an Index between OrderTypeID
(PK,LONG,Indexed) and OrderTypeID (FK,Byte,Indexed). Converting
(CInt,Clng etc are not an issue).

Just about every operation, query (or subqueries) and filters use the
OrderTypeID field, so using a 1-Byte Datatype DOES signigicantly speed
up this application. I have tested it.

My problem is (and it might be an Access thing) that I cannot enforce
any integrity on this join, unless I make them both LONGs, and take
the speed hit.

I just need some re-assurance that there is "no ways around this".
It's seems quirky, and inefficient - considering they are like
datatypes, but not identical datatypes.

Thanks!
Elias Farah.
You should be able to use CLng to convert the foreign key to a Long, or CInt
to convert the primary key to an integer.

However, I wouldn't get too concerned about 6 or 8 bytes (that's all you're
talking about saving by making the FK an integer vs. a long in a 3 or 4 row
table)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
"Elias Farah" <lo******@hotmail.com> wrote in message
news:c6**************************@posting.google.c om...
G'day Steve,

Don't you have problems using the "Narrow Keys", as Access seems to
dislike joining on non-identical Datatypes.

Ie: PrimaryKey=LONG wont join to a ForeignKey=INTEGER

It seems like a glaring hole in access, unless you make all your keys,
both Primary & Foreign LONGs, dispite some tables only have 3 or 4
records. (BYTE Type would do fine).

Thanks!

Elias Farah
On 4 Sep 2003 06:51:32 -0700, lo******@hotmail.com (Elias Farah) wrote:

>Thanks for your response!
>
>Do you mean to say.........
>
>OrderTable (60,000 Records)
>OrderID as Long - Indexed
>OrderTypeID as Long - Indexed
>
>OrderTypeTable - (6 Records)
>OrderTypeID as Long - Indexed.
>OrderType as String
>
>There is many situations where OrderTypeID is used, Searching,
>Filtering on a 4-Byte Datatype. Despite the Datatyoe could be an
>Integer or Byte.
>
>Do you think the Optimiser would perform better on 60,000 record query
>on a 1-Byte Datatype or 4-Byte Datatype? Seems logical to use the
>Byte Type, but access railroads me into using Long throughout many
>tables if they are joined on an Autonumber field.
>
>60,000 is not huge - but enough records to make performance a concern.

OK, I see that you are talking about the space required for the foreign
keys, not the keys in the lookup tables themselves. Yes, a smaller key
will result in smaller records, and that could result in a speed increase.
I do use narrow keys for lookup tables that are small and relatively
unchanging, such as those maintained only by the developer.

- Steve Jorgensen

----
I would have written you a shorter program,
but I didn't have the time.

Nov 12 '05 #8

P: n/a
*********** REPOST ***************
There was a mistake in the previous message, line1, para2
***********************************

Douglas,

Thankyou for your time. I am not an expert, but an struggling to
understand as everyone is talking on tangents. My question maybe not
worded too well either!!

Access wont let you create a RELATIONSHIP between OrderTypeID
(PK,LONG,Indexed) and OrderTypeID (FK,Byte,Indexed). Converting
(CInt,Clng etc are not an issue).

Just about every operation, query (or subqueries) and filters use the
OrderTypeID field, so using a 1-Byte Datatype DOES signigicantly speed
up this application. I have tested it.

My problem is (and it might be an Access thing) that I cannot enforce
any integrity on this join, unless I make them both LONGs, and take
the speed hit.

I just need some re-assurance that there is "no ways around this".
It's seems quirky, and inefficient - considering they are like
datatypes, but not identical datatypes.

Thanks!
Elias Farah.
You should be able to use CLng to convert the foreign key to a Long, or CInt
to convert the primary key to an integer.

However, I wouldn't get too concerned about 6 or 8 bytes (that's all you're
talking about saving by making the FK an integer vs. a long in a 3 or 4 row
table)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
"Elias Farah" <lo******@hotmail.com> wrote in message
news:c6**************************@posting.google.c om...
G'day Steve,

Don't you have problems using the "Narrow Keys", as Access seems to
dislike joining on non-identical Datatypes.

Ie: PrimaryKey=LONG wont join to a ForeignKey=INTEGER

It seems like a glaring hole in access, unless you make all your keys,
both Primary & Foreign LONGs, dispite some tables only have 3 or 4
records. (BYTE Type would do fine).

Thanks!

Elias Farah
On 4 Sep 2003 06:51:32 -0700, lo******@hotmail.com (Elias Farah) wrote:

>Thanks for your response!
>
>Do you mean to say.........
>
>OrderTable (60,000 Records)
>OrderID as Long - Indexed
>OrderTypeID as Long - Indexed
>
>OrderTypeTable - (6 Records)
>OrderTypeID as Long - Indexed.
>OrderType as String
>
>There is many situations where OrderTypeID is used, Searching,
>Filtering on a 4-Byte Datatype. Despite the Datatyoe could be an
>Integer or Byte.
>
>Do you think the Optimiser would perform better on 60,000 record query
>on a 1-Byte Datatype or 4-Byte Datatype? Seems logical to use the
>Byte Type, but access railroads me into using Long throughout many
>tables if they are joined on an Autonumber field.
>
>60,000 is not huge - but enough records to make performance a concern.

OK, I see that you are talking about the space required for the foreign
keys, not the keys in the lookup tables themselves. Yes, a smaller key
will result in smaller records, and that could result in a speed increase.
I do use narrow keys for lookup tables that are small and relatively
unchanging, such as those maintained only by the developer.

- Steve Jorgensen

----
I would have written you a shorter program,
but I didn't have the time.

Nov 12 '05 #9

P: n/a
You're absolutely correct: in order to create a relationship between two
tables, the fields being joined must be of the same type. (Your original
post talked about joining tables, and you can create a join between tables
even if the fields being joined aren't of the same type if you convert them)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
"Elias Farah" <lo******@hotmail.com> wrote in message
news:c6**************************@posting.google.c om...
*********** REPOST ***************
There was a mistake in the previous message, line1, para2
***********************************

Douglas,

Thankyou for your time. I am not an expert, but an struggling to
understand as everyone is talking on tangents. My question maybe not
worded too well either!!

Access wont let you create a RELATIONSHIP between OrderTypeID
(PK,LONG,Indexed) and OrderTypeID (FK,Byte,Indexed). Converting
(CInt,Clng etc are not an issue).

Just about every operation, query (or subqueries) and filters use the
OrderTypeID field, so using a 1-Byte Datatype DOES signigicantly speed
up this application. I have tested it.

My problem is (and it might be an Access thing) that I cannot enforce
any integrity on this join, unless I make them both LONGs, and take
the speed hit.

I just need some re-assurance that there is "no ways around this".
It's seems quirky, and inefficient - considering they are like
datatypes, but not identical datatypes.

Thanks!
Elias Farah.
You should be able to use CLng to convert the foreign key to a Long, or CInt to convert the primary key to an integer.

However, I wouldn't get too concerned about 6 or 8 bytes (that's all you're talking about saving by making the FK an integer vs. a long in a 3 or 4 row table)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
"Elias Farah" <lo******@hotmail.com> wrote in message
news:c6**************************@posting.google.c om...
G'day Steve,

Don't you have problems using the "Narrow Keys", as Access seems to
dislike joining on non-identical Datatypes.

Ie: PrimaryKey=LONG wont join to a ForeignKey=INTEGER

It seems like a glaring hole in access, unless you make all your keys,
both Primary & Foreign LONGs, dispite some tables only have 3 or 4
records. (BYTE Type would do fine).

Thanks!

Nov 12 '05 #10

P: n/a
lo******@hotmail.com (Elias Farah) wrote:
Access wont let you create a RELATIONSHIP between OrderTypeID
(PK,LONG,Indexed) and OrderTypeID (FK,Byte,Indexed). Converting
(CInt,Clng etc are not an issue).
<snip>
My problem is (and it might be an Access thing) that I cannot enforce
any integrity on this join, unless I make them both LONGs, and take
the speed hit.

I just need some re-assurance that there is "no ways around this".
It's seems quirky, and inefficient - considering they are like
datatypes, but not identical datatypes.


I've never seen anything where you can have a relationship between dissimilar fields.

Now in queries what you can do is put a clng() around a field in a query. Then in
another query join that field with a long field in another table. However this could
lose the performance advantage you mention and more.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 12 '05 #11

P: n/a
TC

"Elias Farah" <lo******@hotmail.com> wrote in message
news:c6**************************@posting.google.c om...

(snip)
Just about every operation, query (or subqueries) and filters use the
OrderTypeID field, so using a 1-Byte Datatype DOES signigicantly speed
up this application. I have tested it.

I for one find this hard to believe.

What tests have you done to conclude this?

TC

Nov 12 '05 #12

P: n/a
Dear Tony,
lo******@hotmail.com (Elias Farah) wrote:
Access wont let you create a RELATIONSHIP between OrderTypeID
(PK,LONG,Indexed) and OrderTypeID (FK,Byte,Indexed). Converting
(CInt,Clng etc are not an issue).
<snip>
My problem is (and it might be an Access thing) that I cannot enforce
any integrity on this join, unless I make them both LONGs, and take
the speed hit.

I just need some re-assurance that there is "no ways around this".
It's seems quirky, and inefficient - considering they are like
datatypes, but not identical datatypes.


I've never seen anything where you can have a relationship between
dissimilar fields.

Hmmmmm, why are people making assumptions and overdoing their answers?
I don't think anyone is reading the original question, or not
comprehending me.

My original question in a nutshell is:

I have a number of tables which have a limited number of records
(Maybe 5 or 6) which don't need big datatypes for their keys. Both PK
& FK need only 1-Byte Dataype, but Access doesn't seem to be able to
create a relationship if Autonumber is used, unless both a LONG types.
(ie 4 Bytes).

It seems quirky, the larger datatypes take longer to process, and they
are used millions of times in many queries.
Sorry to expose my weaknesses..... The tone of some of the
respondents to this thread has been very disappointing. One guys just
wrote a twit message like "Why?". Others seemed to answer with their
brain in la-la land, perhaps thinking about their own narrow world.

Steve, Doug and Tony - thanks for your input, my conclusion is to make
both PK & FK LONGs, make the relationship, enforce integrity, and
forget about trying to use a more efficient datatype on either the FK
or PK.
Thanks

Elias Farah

Now in queries what you can do is put a clng() around a field in a query. Then in
another query join that field with a long field in another table. However this could
lose the performance advantage you mention and more.

Tony

Nov 12 '05 #13

P: n/a
Simple answer: A real world application.

Perhaps not written to your standards, but the datatype makes a
difference.

Why don't you change your datatypes to strings, and do some searching,
sorting and screwing around, and you'll learn to use Byte, Integer etc
instead using the biggest datatype you can find.

Some more helpful people have educated me a little..... (Thanks Steve,
Doug & Tony).

I am best to make the PK & FK both LONGs, create a relationship,
enforce integrity and forget about trying to make either the FK or PK
a smaller datatype. (Despite BYTE or INT being more appropiate). This
because Access insists on LONGs for Autonumbers, and wont Relate on
anything except EXACT datatypes.

Respect that we all have different levels of knowledge, and give
people the benefit of doubt. If I say it's quicker, only a dipstick
would tell me from across the world it's not.

Regards,
Elias Farah.
Regards,
Elias Farah.

"Elias Farah" <lo******@hotmail.com> wrote in message
news:c6**************************@posting.google.c om...

(snip)
Just about every operation, query (or subqueries) and filters use the
OrderTypeID field, so using a 1-Byte Datatype DOES signigicantly speed
up this application. I have tested it.

I for one find this hard to believe.

What tests have you done to conclude this?

TC

Nov 12 '05 #14

P: n/a
TC

"Elias Farah" <lo******@hotmail.com> wrote in message
news:c6**************************@posting.google.c om...

If I say it's quicker, only a dipstick
would tell me from across the world it's not.


I did not say that it wasn't. I asked you to say what tests you had
performed.

It's clear who is the dipstick here.

TC

Nov 12 '05 #15

This discussion thread is closed

Replies have been disabled for this discussion.