Re: [SQL] Pls Hlp: SQL Problem
On Mon, Sep 15, 2008 at 2:14 PM, Fernando Hevia <[EMAIL PROTECTED]> wrote: > > >> -Mensaje original- >> De: Scott Marlowe [mailto:[EMAIL PROTECTED] > >> > >> > Consider that if you are NOT going to use the decimals you should >> > really use integer or bigint datatypes. The numeric type >> compute much >> > slower than integer datatypes. >> >> Note that if you're just converting the output the cost is >> minimal compared to if you're doing all your math in numeric. >> It's when you force math to happen that numeric is slower, >> but numeric's gotten a lot of tuning in the last few years >> and it's withing a few percentage >> of integer for most measurements. Definitely not twice as slow or >> anything like they once were. >> > > Well, in that case the manual should be revised. > > 8.1.2. Arbitrary Precision Numbers > > The type numeric can store numbers with up to 1000 digits of precision and > perform calculations exactly. It is especially recommended for storing > monetary amounts and other quantities where exactness is required. However, > arithmetic on numeric values is __very slow__ compared to the integer types, > or to the floating-point types described in the next section. > > Ref: http://www.postgresql.org/docs/current/static/datatype-numeric.html > > The explicit "very slow" assertion scared me quite enough so to avoid > numeric types where possible. yeah, I agree. In the days of 7.2 it was horrifically slow (like 10 to 20 times slower most the time) to do numeric math on similar sized numbers as int math. Note that if you are working with numbers of 500 digits it's gonna be a lot slower than numbers with 10 digits, but if the numbers are similar in size, numeric has had a lot of optimization that makes it quite usable now. which is handy when porting from oracle, where everything is a sort-of numeric. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Pls Hlp: SQL Problem
> -Mensaje original- > De: Scott Marlowe [mailto:[EMAIL PROTECTED] > > > > Consider that if you are NOT going to use the decimals you should > > really use integer or bigint datatypes. The numeric type > compute much > > slower than integer datatypes. > > Note that if you're just converting the output the cost is > minimal compared to if you're doing all your math in numeric. > It's when you force math to happen that numeric is slower, > but numeric's gotten a lot of tuning in the last few years > and it's withing a few percentage > of integer for most measurements. Definitely not twice as slow or > anything like they once were. > Well, in that case the manual should be revised. 8.1.2. Arbitrary Precision Numbers The type numeric can store numbers with up to 1000 digits of precision and perform calculations exactly. It is especially recommended for storing monetary amounts and other quantities where exactness is required. However, arithmetic on numeric values is __very slow__ compared to the integer types, or to the floating-point types described in the next section. Ref: http://www.postgresql.org/docs/current/static/datatype-numeric.html The explicit "very slow" assertion scared me quite enough so to avoid numeric types where possible. Regards, Fernando. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] surrogate vs natural primary keys
Hi, I've been reading several articles on this hotly debated issue and still can't find proper criteria to select one or the other approach for the database I'm currently designing. I'd appreciate any pointers. Thanks. Cheers, -- Seb -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] surrogate vs natural primary keys
On Mon, Sep 15, 2008 at 4:02 PM, Seb <[EMAIL PROTECTED]> wrote: > Hi, > > I've been reading several articles on this hotly debated issue and still > can't find proper criteria to select one or the other approach for the > database I'm currently designing. I'd appreciate any pointers. Thanks. You'll find lots of arguments from both sides, some more strident than others. In most big transactional systems you'll find surrogate keys used for performance reasons, as well as design choices. for instance, when you book a flight with an airline, you'll get a locator code like A89JK3 that is unique to any other locator code in the system. Sure, you could make a natural key of first name, last name, address, phone number, flight number, departure / arrival and day and time, but there's no way that's going to perform as well as a single char(6). The problem with natural keys is that you can never be sure they won't change on you. I like using them, but have been caught out on many occasions where things changed halfway through development and required a lot of redesign. I think this question is a lot like "how large should I set shared_buffers?" There's lots of different answers based on how you are using your data. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] surrogate vs natural primary keys
On Mon, 15 Sep 2008 16:45:08 -0600, "Scott Marlowe" <[EMAIL PROTECTED]> wrote: [...] > I think this question is a lot like "how large should I set > shared_buffers?" There's lots of different answers based on how you > are using your data. Yes, this is precisely what I'm after: *criteria* to help me decide which approach to take for different scenarios. Such guidance is what seems to be lacking from most of the discussions I've seen on the subject. It's hard to distill this information when most of the discussion is centered on advocating one or the other approach. Thanks, -- Seb -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Simple Problem ?
Dear friends, I really amazed with this problem and need your help. I run simple query from Query window of PgAdmin3: SELECT * FROM mytable limit 10; and the result are NON UPDATABLE QUERY, while i need the result are updatable. I run the same query from Passthrough SQL in Microsoft Access, the result are the same (non updatable). But when i run this query from EMS SQLMANAGER for postgre, the result are UPDATABLE. I really amazed. Could someone help me ? Thanks a lot -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Simple Problem ?
Hengky Lie a écrit : > [...] > I really amazed with this problem and need your help. > > I run simple query from Query window of PgAdmin3: SELECT * FROM mytable > limit 10; > > and the result are NON UPDATABLE QUERY, while i need the result are > updatable. > > I run the same query from Passthrough SQL in Microsoft Access, the > result are the same (non updatable). But when i run this query from EMS > SQLMANAGER for postgre, the result are UPDATABLE. I really amazed. > > Could someone help me ? > Do you have a primary key on mytable or OID ? If you don't, pgAdmin3 can't allow modification because it doesn't know how to select a line uniquely. I suppose it is the same for Access. And it is a really bad behaviour of EMS SQL Manager. Regards. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
