Re: question of performance
From: GreySky (GreySky_at_discussions.microsoft.com)
Date: 11/04/04
- Next message: Adrian: "Recordas not to show"
- Previous message: D: "Recordas not to show"
- In reply to: John Spencer (MVP): "Re: question of performance"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 4 Nov 2004 06:49:05 -0800
I don't disagree with what you said, John.
Specifically, with smaller databases on smaller queries, I would agree the
user would see virtually no difference in execution speed. I may, however,
disagree slightly that the number of _selected_ records would need to be
large. What would be required is the building of a significant CPT, where
the joins would need to be processed before application of a WHERE statement.
I most certainly agree text-based keys would exacerbate the issue if they
weren't indexed by orders of magnitude due to differences in join processes
the engine would have to employ as well as additional table scans.
I think we're more on the same page than you think, and I wasn't
specifically aiming my answer at you or anyone else in particular. I do,
however, have a bone to pick with pundits who push form over power without
understanding what they're asking the computer to do.
--Grey
"John Spencer (MVP)" wrote:
> Not going to start a war over this, but I agree with you in theory that integer
> values are handled quicker. HOWEVER, in most cases the user won't ever see the
> difference in performance.
>
> If the number of records _selected_ is fairly large (100,000 and up), you might
> see a difference in performance, especially with non-indexed fields. That said,
> I tend to use integers as my primary keys.
>
> On the other hand, the OP didn't say the fields he/she was defining were key
> fields. So, my answer to that is if the value you are storing is all numeric
> characters and you aren't going to be doing math of some type on the values,
> then use a string to store the value. That way you don't lose leading zeroes
> and if you want you can store the formatting with the "number".
>
>
> GreySky wrote:
> >
> > Integer values can fit into registers on a chip. Strings are always pointers
> > to locations in memory, and operations must be performed against string data
> > brought to the chip's cache, other hardware or software cache, or bussed in
> > from way out of town.
> >
> > I've always wondered why *anyone* would recommend using strings as primary
> > keys. I realize they can make primary keys more "readable" if built up like
> > a code, and I realize vendors like Siebel issue text-based primary keys in
> > 100-block increments to keep their primary keys unique among all tables. I
> > also realize the official stance from some of the mvp's on this board is to
> > use text-based keys (*sigh*).
> >
> > But think about it. If you're comparing millions of keys to build a
> > Cartesian Product Table (i.e., build your query), doesn't it make more sense
> > to use something that can literally sit on the chip in a register and be
> > compared in one CMP operation (one op to move the base integer to a register,
> > one op to move the other integer to a register, one op to tell the chip to
> > compare the two, done), rather than having to point to memory for the data to
> > begin the comparison?
> >
> > Another thing -- (long) integers are 4 bytes, period. I don't think I've
> > seen character-based primary keys this short. Not only that, these guys are
> > likely unicode, so each character is two bytes of not compressed (and if so,
> > perhaps requires translation before comparisons can be undertaken). Smaller
> > indexes = faster scans = quicker results.
> >
> > Use Long Integers / Autonumbers for primary keys. Use indexes. Watch your
> > queries scream. And if you want, look at www.intel.com and read about the
> > Intel architecture.
> >
> > David Atkins, MCP
> >
> > "Nemo" wrote:
> >
> > > Hello Folks,
> > > about the performance(speed) of a query, does it make difference if I define
> > > my field type as string or integer. Thanks.
> > >
> > >
> > >
>
- Next message: Adrian: "Recordas not to show"
- Previous message: D: "Recordas not to show"
- In reply to: John Spencer (MVP): "Re: question of performance"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
