Thread: primary keys as TEXT
Hi. There can be performancs problems in having primary keys of type TEXT? What about having a primary key of 3 columns (all of type TEXT)? Regards Manlio Perillo
On Jul 28, 2006, at 17:37 , Manlio Perillo wrote: > There can be performancs problems in having primary keys of type TEXT? > What about having a primary key of 3 columns (all of type TEXT)? What defines a problem in terms of performance is heavily dependent on your particular needs and requirements. What are your requirements? What profiling have you done to see where your performance bottlenecks may be? Michael Glaesemann grzm seespotcode net
On 7/28/06, Manlio Perillo <manlio_perillo@libero.it> wrote:
If you are really worried about it, why not just use surrogate keys? They are very easy to use. Then your problem is solved.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================
Hi.
There can be performancs problems in having primary keys of type TEXT?
What about having a primary key of 3 columns (all of type TEXT)?
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================
On Fri, 2006-07-28 at 03:37, Manlio Perillo wrote: > Hi. > > There can be performancs problems in having primary keys of type TEXT? > What about having a primary key of 3 columns (all of type TEXT)? The biggest problem with using text as a primary key or foreign key is that text types are locale dependent, so that you might get one behaviour on one server and another behaviour on another, depending on configuration of the locale and the locale support on that machine. For instance, if you have a locale that says that E and e are equivalent, and another locale that says they aren't...
Michael Glaesemann ha scritto: > > On Jul 28, 2006, at 17:37 , Manlio Perillo wrote: > >> There can be performancs problems in having primary keys of type TEXT? >> What about having a primary key of 3 columns (all of type TEXT)? > > What defines a problem in terms of performance is heavily dependent on > your particular needs and requirements. What are your requirements? What > profiling have you done to see where your performance bottlenecks may be? > I still don't have done profiling. Simply in the first version of my schema I used serial keys but the result is ugly and it force me to do a lot of joins. Thanks and regards Manlio Perillo
On 7/28/06, Manlio Perillo <manlio_perillo@libero.it> wrote:
Ugly? Not sure what you mean by that.
I do understand the problem with so many joins. I use views so that the joins are only delt with once (in the database) and then all my applications run off the views. That way, the applications use very simple queries. The views also allow me to change the table structure (column names, more table normalization, etc.) without having to make changes to the application.
I am even getting ready to start using updatable views so my applications never touch the tables directly - it ads another layer of abstraction between the tables and the application. But that may be more complicated than you are ready for - I have yet to decide if it will be worth the effort but I won't know until I experiment with it more.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================
Michael Glaesemann ha scritto:
>
> On Jul 28, 2006, at 17:37 , Manlio Perillo wrote:
>
>> There can be performancs problems in having primary keys of type TEXT?
>> What about having a primary key of 3 columns (all of type TEXT)?
>
> What defines a problem in terms of performance is heavily dependent on
> your particular needs and requirements. What are your requirements? What
> profiling have you done to see where your performance bottlenecks may be?
>
I still don't have done profiling.
Simply in the first version of my schema I used serial keys but the
result is ugly and it force me to do a lot of joins.
I do understand the problem with so many joins. I use views so that the joins are only delt with once (in the database) and then all my applications run off the views. That way, the applications use very simple queries. The views also allow me to change the table structure (column names, more table normalization, etc.) without having to make changes to the application.
I am even getting ready to start using updatable views so my applications never touch the tables directly - it ads another layer of abstraction between the tables and the application. But that may be more complicated than you are ready for - I have yet to decide if it will be worth the effort but I won't know until I experiment with it more.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================
Aaron Bono ha scritto: > On 7/28/06, *Manlio Perillo* <manlio_perillo@libero.it > <mailto:manlio_perillo@libero.it>> wrote: > > Michael Glaesemann ha scritto: > > > > On Jul 28, 2006, at 17:37 , Manlio Perillo wrote: > > > >> There can be performancs problems in having primary keys of type > TEXT? > >> What about having a primary key of 3 columns (all of type TEXT)? > > > > What defines a problem in terms of performance is heavily dependent on > > your particular needs and requirements. What are your > requirements? What > > profiling have you done to see where your performance bottlenecks > may be? > > > > I still don't have done profiling. > > Simply in the first version of my schema I used serial keys but the > result is ugly and it force me to do a lot of joins. > > > > Ugly? Not sure what you mean by that. > Because serial ids are only surrogate keys. My tables have well definited primary keys, the only problem is that they are of type TEXT (and spawn up to 3 columns). My concern is: how bad can be performance? > I do understand the problem with so many joins. I use views so that the > joins are only delt with once (in the database) and then all my > applications run off the views. That way, the applications use very > simple queries. The views also allow me to change the table structure > (column names, more table normalization, etc.) without having to make > changes to the application. > View are a good idea, thanks. Regards Manlio Perillo
On 7/31/06, Manlio Perillo <manlio_perillo@libero.it> wrote:
My guess is that the performance difference is not going to be noticable unless you are dealing with huge amounts of data and even then may account for such a small hit that there will be other issues that are more pressing like writing better queries, creating a data warehouse or adding proper indexes and keeping the indexes well maintained.
Anyone care to disagree?
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================
Because serial ids are only surrogate keys.
My tables have well definited primary keys, the only problem is that
they are of type TEXT (and spawn up to 3 columns).
My concern is: how bad can be performance?
Anyone care to disagree?
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================