How not to keep track of ordernumbers



  • While updating on of our webshops I came across the following piece of code to create a unique ordernumber.

    [code]rscounter.setSQLText("Select * from counter order by counter")
    rscounter.requery()
    rscounter.moveFirst()
    ordernr = rscounter.fields.getValue("counter") +1
    call rscounter.addImmediate("counter",ordernr)
    rscounter.moveFirst()
    rscounter.deleteRecord()

    session.Contents("ordernr") = ordernr[/code] 

     While the real WTF is obviously using asp classic, this had me rolling my eyes for a bit. Maybe the worst bit about this, is the fact that this is not done at the time of order creation, but during login.



  •  No locking? Hope the shop isn't Amazon ;-)


  • :belt_onion:

    I'm currently supporting an application using this kind of logic for uploading records from a file:

    • Determine the first free ID from the "Available IDs" table.
    • Increment this with 200 and update the "Available IDs" table with this new number.
    • Insert records 1 through 200 in the data table

    Repeat above steps (inserting the next 200 records) until all records have been inserted.

    Luckely they used a locking mechanism on the database, but this "Available IDs" table is the performance bottleneck of the application. The application vender once told me they don't use the IDENTITY column because it restrics their freedom for manipulating the data as they please. Hence they don't use foreign key relations and other annoying features liked cascade deletes.



  • I am familiar with the "id DEFAULT (SELECT max(id) + 1 FROM tbl)" idiom.  Oh God am I ever...



  • @bjolling said:

    I'm currently supporting an application using this kind of logic for uploading records from a file:

    • Determine the first free ID from the "Available IDs" table.
    • Increment this with 200 and update the "Available IDs" table with this new number.
    • Insert records 1 through 200 in the data table

    Repeat above steps (inserting the next 200 records) until all records have been inserted.

    Luckely they used a locking mechanism on the database, but this "Available IDs" table is the performance bottleneck of the application. The application vender once told me they don't use the IDENTITY column because it restrics their freedom for manipulating the data as they please. Hence they don't use foreign key relations and other annoying features liked cascade deletes.

    Why wouldn't they just use a table with a single row and increment that by 200 instead of storing every available ID?  With exclusive locks they wouldn't have concurrency issues and the performance wouldn't be bad.  Obviously it's still a WTF, but it's actually a functional WTF.



  • That sounds a lot like a product used in a place that went down hard after only 2 years in business.

    Except they also used the "counter" for "number of orders". Looks like these guys had never heard of COUNT(*)


  • :belt_onion:

    @morbiuswilters said:

    Why wouldn't they just use a table with a single row and increment that by 200 instead of storing every available ID?  With exclusive locks they wouldn't have concurrency issues and the performance wouldn't be bad.  Obviously it's still a WTF, but it's actually a functional WTF.
     

    In one place of the application I have replaced their functionality with some code of my own which is about 4 to 5 times faster. 

    • Bulk load the data first in a staging table that has an IDENTITY column and determine the number of inserted rows
    • Check the first free ID from the available IDs table and increment it with the total number of inserted rows
    • Upload the data into the data table while recalculating the new IDs (derived from the IDENTITY and the first available ID)

    They would take an exclusive lock every 200 records on the "Available IDs" table whereas my code only does that once. They also needed some application code to generate their IDs.

    Still, there must be more things that they did wrong to explain the speed difference but I haven't got access to their code :-(


Log in to reply