Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2003
    Location
    Tunisia
    Posts
    192

    Thumbs up Unanswered: serial removal results speed gain !

    Hi, as you know I am a programmer that uses VB , with a Postgresl DBMS (on liunx top), we used to use serial type in 99% of our tables, when we've got many problems of slowlines and we've doubted the ODBC , the OS, th DBMS, (more than 90 clients hooked to the server).

    We found it finally, the serial type makes the databse slow, because it speculate the tansaction to make coherant oreder : transaction 1 waits transcation 2 to finish to get inserted when both belongs to the same table !

    We've removed the serial type and replaced it with a serial client side from VB , where the function gets the max(row) and add 1 to it , theorilly server side serials are better, but with this solution we've gained more than 40 % in speed I woumldn't be stupid but that's the fact !

    Anymore I want you experienca and opinion about this !
    Open up
    Take a look to my Blog http://www.rundom.com/karim2k

  2. #2
    Join Date
    Sep 2003
    Posts
    7

    Post Re: serial removal results speed gain !

    Originally posted by karim2k
    We found it finally, the serial type makes the databse slow, because it speculate the tansaction to make coherant oreder : transaction 1 waits transcation 2 to finish to get inserted when both belongs to the same table !

    We've removed the serial type and replaced it with a serial client side from VB , where the function gets the max(row) and add 1 to it , theorilly server side serials are better, but with this solution we've gained more than 40 % in speed I wouldn't be stupid but that's the fact !
    But what about your DB consistency? If I got it right, you make a
    1. SELECT MAX()+1
    2. something outside DB
    3. INSERT whatsoever-with-value-from-1.-as-primary-key

    I dont doubt that this makes a speed up (transactional lokcing as you said), but what if another client makes a SELECT MAX() while one is in the '2.' part? Both clients will try to insert with the same value returned by SELECT MAX()!

    If the SERIAL types are so slow, I'd recommend dropping the SERIAL as primary-key and perhaps better taking a 'real' primary key over all columns that are making your entries unique.

    (I'm omitting the VB jokes here)

  3. #3
    Join Date
    Apr 2003
    Location
    Tunisia
    Posts
    192

    cool

    Anyway , our Dbserver id feeding more than 90 hbungry Clients and the use of serial as prmary key has fallen down ! I thinh that the serial is not optimized when accessing from ODBC dur to the cache managment. thank you anyway !
    Open up
    Take a look to my Blog http://www.rundom.com/karim2k

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •