{"id":529,"date":"2004-03-20T00:44:19","date_gmt":"2004-03-20T03:44:19","guid":{"rendered":"http:\/\/www.hoogervorst.ca\/arthur\/?p=529"},"modified":"2004-03-20T00:44:19","modified_gmt":"2004-03-20T03:44:19","slug":"on-select-go-slow","status":"publish","type":"post","link":"http:\/\/www.hoogervorst.ca\/arthur\/?p=529","title":{"rendered":"On Select Go Slow"},"content":{"rendered":"<p><span class=dropcap>A<\/span>t work, I rolled out a crucial test for the Postgres database: Originally the Invoicing program was never meant to work in a real multi-user environment. This is particularly due to a couple of statements used to generate a <em>unique<\/em> number for tables that have a primary index on that number. One of them looked like this:\n<\/p>\n<p><code>SELECT * FROM atable<br \/>\nORDER BY uniquenumber DESC<br \/>\n<\/code>\n<\/p>\n<p>In the program itself, the &#8216;uniquenumber&#8217; field&#8217;s value was then extracted (and incremented by one) and passed to an INSERT statement that added a new row to that same table.\n<\/p>\n<p><!--more--><\/p>\n<p>Think about it. The table is slowly growing. The larger the table the longer it takes to get that new unique number value. Secondly, trying to retrieve a complete table (ASTERISK, dammit) and pump it in memory doesn&#8217;t really help either. Imagine two users around the same time, trying to add a new row. Who gets which number and is it really unique?\n<\/p>\n<p>We ended up with &#8216;primary index errors&#8217;, that is, and recently they appeared frequently. I took this problem seriously and have started to roll out the use of Postgres &#8216;sequences&#8217;. This added two advantages: Sequences are &#8216;multi-user&#8217; safe: the database takes care of it all. And, I got rid of complete SELECTS, which eventually made a couple of processes ridiculously fast.\n<\/p>\n<p>On a related note<\/p>\n","protected":false},"excerpt":{"rendered":"<p>At work, I rolled out a crucial test for the Postgres database: Originally the Invoicing program was never meant to work in a real multi-user environment. This is particularly due to a couple of statements used to generate a unique &hellip; <a href=\"http:\/\/www.hoogervorst.ca\/arthur\/?p=529\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[4],"tags":[],"_links":{"self":[{"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/posts\/529"}],"collection":[{"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=529"}],"version-history":[{"count":0,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/posts\/529\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=529"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=529"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=529"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}