Key Numeric Datatype

[Elias Farah]

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

[Lyle Fairfield]

Re: Key Numeric Datatype

longnose@hotmai l.com (Elias Farah) wrote in news:c6cfe5a6.0 308310315.14383 3d3
@posting.google .com:
[color=blue]
> 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).[/color]

Why?

--
Lyle

[Steve Jorgensen]

Re: Key Numeric Datatype

On 31 Aug 2003 04:15:34 -0700, longnose@hotmai l.com (Elias Farah) wrote:
[color=blue]
>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).[/color]

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.

[TC]

Re: Key Numeric Datatype

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" <longnose@hotma il.com> wrote in message
news:c6cfe5a6.0 308310315.14383 3d3@posting.goo gle.com...[color=blue]
> 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[/color]

[Elias Farah]

Re: Key Numeric Datatype

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.





[color=blue]
> On 31 Aug 2003 04:15:34 -0700, longnose@hotmai l.com (Elias Farah) wrote:
>[color=green]
> >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).[/color]
>
> 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.[/color]

[Steve Jorgensen]

Re: Key Numeric Datatype

On 4 Sep 2003 06:51:32 -0700, longnose@hotmai l.com (Elias Farah) wrote:
[color=blue]
>Thanks for your response!
>
>Do you mean to say.........
>
>OrderTable (60,000 Records)
>OrderID as Long - Indexed
>OrderTypeID as Long - Indexed
>
>OrderTypeTab le - (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.[/color]

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.

[Elias Farah]

Re: Key Numeric Datatype

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=INTE GER

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
[color=blue]
> On 4 Sep 2003 06:51:32 -0700, longnose@hotmai l.com (Elias Farah) wrote:
>[color=green]
> >Thanks for your response!
> >
> >Do you mean to say.........
> >
> >OrderTable (60,000 Records)
> >OrderID as Long - Indexed
> >OrderTypeID as Long - Indexed
> >
> >OrderTypeTab le - (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.[/color]
>
> 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.[/color]

[Elias Farah]

Re: Key Numeric Datatype

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,Indexe d) and OrderTypeID (FK,Byte,Indexe d). 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.
[color=blue]
> 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" <longnose@hotma il.com> wrote in message
> news:c6cfe5a6.0 309070507.17eaf 658@posting.goo gle.com...[color=green]
> > 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=INTE GER
> >
> > 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
> >[color=darkred]
> > > On 4 Sep 2003 06:51:32 -0700, longnose@hotmai l.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
> > > >
> > > >OrderTypeTab le - (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[/color][/color]
> increase.[color=green][color=darkred]
> > >
> > > 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.[/color][/color][/color]

[Elias Farah]

Re: Key Numeric Datatype

*********** 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,Indexe d) and OrderTypeID (FK,Byte,Indexe d). 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.


[color=blue]
> 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" <longnose@hotma il.com> wrote in message
> news:c6cfe5a6.0 309070507.17eaf 658@posting.goo gle.com...[color=green]
> > 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=INTE GER
> >
> > 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
> >[color=darkred]
> > > On 4 Sep 2003 06:51:32 -0700, longnose@hotmai l.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
> > > >
> > > >OrderTypeTab le - (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[/color][/color]
> increase.[color=green][color=darkred]
> > >
> > > 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.[/color][/color][/color]

[Douglas J. Steele]

Re: Key Numeric Datatype

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" <longnose@hotma il.com> wrote in message
news:c6cfe5a6.0 309080402.4c7bf aa1@posting.goo gle.com...[color=blue]
> *********** 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,Indexe d) and OrderTypeID (FK,Byte,Indexe d). 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.
>
>
>[color=green]
> > You should be able to use CLng to convert the foreign key to a Long, or[/color][/color]
CInt[color=blue][color=green]
> > to convert the primary key to an integer.
> >
> > However, I wouldn't get too concerned about 6 or 8 bytes (that's all[/color][/color]
you're[color=blue][color=green]
> > talking about saving by making the FK an integer vs. a long in a 3 or 4[/color][/color]
row[color=blue][color=green]
> > table)
> >
> > --
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele
> >
> >
> > "Elias Farah" <longnose@hotma il.com> wrote in message
> > news:c6cfe5a6.0 309070507.17eaf 658@posting.goo gle.com...[color=darkred]
> > > 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=INTE GER
> > >
> > > 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!
> > >[/color][/color][/color]

[Tony Toews]

Re: Key Numeric Datatype

longnose@hotmai l.com (Elias Farah) wrote:
[color=blue]
>Access wont let you create a RELATIONSHIP between OrderTypeID
>(PK,LONG,Index ed) and OrderTypeID (FK,Byte,Indexe d). Converting
>(CInt,Clng etc are not an issue).[/color]

<snip>
[color=blue]
>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.[/color]

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

[TC]

Re: Key Numeric Datatype


"Elias Farah" <longnose@hotma il.com> wrote in message
news:c6cfe5a6.0 309080402.4c7bf aa1@posting.goo gle.com...

(snip)
[color=blue]
> 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.[/color]


I for one find this hard to believe.

What tests have you done to conclude this?

TC

[Elias Farah]

Re: Key Numeric Datatype

Dear Tony,
[color=blue]
> longnose@hotmai l.com (Elias Farah) wrote:
>[color=green]
> >Access wont let you create a RELATIONSHIP between OrderTypeID
> >(PK,LONG,Index ed) and OrderTypeID (FK,Byte,Indexe d). Converting
> >(CInt,Clng etc are not an issue).[/color]
>
> <snip>
>[color=green]
> >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.[/color]
>
> I've never seen anything where you can have a relationship between
> dissimilar fields.[/color]


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



[color=blue]
> 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[/color]

[Elias Farah]

Re: Key Numeric Datatype

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.

[color=blue]
> "Elias Farah" <longnose@hotma il.com> wrote in message
> news:c6cfe5a6.0 309080402.4c7bf aa1@posting.goo gle.com...
>
> (snip)
>[color=green]
> > 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.[/color]
>
>
> I for one find this hard to believe.
>
> What tests have you done to conclude this?
>
> TC[/color]

[TC]

Re: Key Numeric Datatype


"Elias Farah" <longnose@hotma il.com> wrote in message
news:c6cfe5a6.0 309100526.752ba 285@posting.goo gle.com...[color=blue]
>
> If I say it's quicker, only a dipstick
> would tell me from across the world it's not.[/color]

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