Results 1 to 29 of 29

Thread: Problem : Generating Unique Number (too slow)

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    May 2013
    Posts
    328

    Problem : Generating Unique Number (too slow)

    I split my Ms Access DB to Front-end to Back-end.
    Total records stored is more than 30 thousand.

    my problem is, when im generating unique number took 10 seconds.

    my code :
    Code:
    Dim i As Long
    
    Dim rsGEN As ADODB.Recordset
    Dim sqlCriteria As String
    Dim cNum As Long
    
    Set rsGEN = New ADODB.Recordset
        rsGEN.CursorLocation = adUseClient
        rsGEN.CursorType = adOpenStatic
        rsGEN.LockType = adLockReadOnly
    
    sqlCriteria = "SELECT MAX(REINPUT) AS CNUMBER FROM TBL_PAYROLL" '
    
        rsGEN.Open sqlCriteria, cn
        
        If Not rsGEN.EOF Then
            rsGEN.MoveLast
            cNum = rsGEN.Fields("CNUMBER").Value + 1
        End If
        
    
     LBLNumber.Caption = Format(cNum, "000000")
    
    Set rsGEN = Nothing

  2. #2
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    Re: Problem : Generating Unique Number (too slow)

    Why do you even have a MoveLast there? It is returning only 1 record so it is not needed. Can you try using adUserServer in your CursorLocation if it will improve the speed?
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    May 2013
    Posts
    328

    Re: Problem : Generating Unique Number (too slow)

    Quote Originally Posted by dee-u View Post
    Why do you even have a MoveLast there? It is returning only 1 record so it is not needed. Can you try using adUserServer in your CursorLocation if it will improve the speed?
    Nothing happens, still slow.

    But when I've try to put the program where the back-end database located, I can filter more than 1000 records
    less than 3 seconds.

    Maybe the link between Back-end and Front-end is the culprit.

    Any suggestion?

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    May 2013
    Posts
    328

    Re: Problem : Generating Unique Number (too slow)

    Is my approach is right, having a Front-end every user?

  5. #5
    Fanatic Member
    Join Date
    Jan 2013
    Posts
    759

    Re: Problem : Generating Unique Number (too slow)

    You could add an index on the REINPUT field. That would allow Access to retrieve the highest value far more quickly.

    If you're doing this a lot, then I'd suggest using a separate table to supply the "last number".
    Code:
    update last_reindex 
    set value = value + 1 ; 
    
    select value from last_reindex ; 
    
    insert 
    into tbl_payroll( ..., reindex, ... ) 
    values ( ..., ?, ... ) ;    /* use value from last_reindex */
    Works perfectly well in just about every DBMS I've used in the last twenty years or so.
    Even Access.

    Regards, Phill W.

  6. #6
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Problem : Generating Unique Number (too slow)

    You need to index the field, possibly even make it the primary key field.

    In the past I worked on a program written by someone else that was pulling a report with over 1 million records on it. The report was taking ~ 30 minutes to build and was timing out about 1/2 the time so they asked me to have a look and see if I could extend the timeout. Instead I indexed the key field and the report took less than 30 seconds. Basically it was a bit more than 70x faster with proper indexing.
    Last edited by DataMiser; Aug 20th, 2014 at 08:30 AM.

  7. #7
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: Problem : Generating Unique Number (too slow)

    Another culprit can be that the Access MDB file is stored on a network location.
    Because just being a file and not a database server, all data need to be pulled to the client to perform even a simple query.
    This would explain the big time difference when performing the query on a computer containing the MDB file on a local disk and doing the query on another computer using a shared/network drive.

  8. #8
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: Problem : Generating Unique Number (too slow)

    I split my Ms Access DB to Front-end to Back-end.
    Total records stored is more than 30 thousand.
    i am a bit confused you talk about a frond-end and a back-end
    are you using vb6 or Ms Access to use the db ?
    also what do you need that unique number for ?
    and what is the datatype of that "CNUMBER" field ?
    and has that unique number to be unique for a specific table(s) ?
    or has it to be unique for every table in the db
    (by that i mean has that number to be unique in the entire db)

    anyway whatever you want an unique number for
    the db should return a unique number immediately after you do an addnew
    i mean immediately not after 10 or 1 second but immediately
    even if there are hundreds thousands records in that table

    edit:
    sqlCriteria = "SELECT MAX(REINPUT) AS CNUMBER FROM TBL_PAYROLL
    also if you select the highest number from a table and add 1 to it
    a lot of other users could have done that a split second before/after you
    and you'l all have generated the same unique ??? number
    Last edited by IkkeEnGij; Aug 20th, 2014 at 08:51 AM. Reason: forgor to mention an important point

  9. #9
    Frenzied Member
    Join Date
    Jan 2009
    Location
    Watch Window(Shift+f9)
    Posts
    1,879

    Re: Problem : Generating Unique Number (too slow)

    another problem is why don't you restrict by proper where condition .it would be too slow .
    Code:
    sqlCriteria = "SELECT MAX(REINPUT) AS CNUMBER FROM TBL_PAYROLL"

  10. #10
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Problem : Generating Unique Number (too slow)

    How would you restrict it? If you restricted it, it would only be the max REINPUT for that subset of the data... which may or may not include the actual max value for all records.

    If it's to get the last ID so you can add 1 to it... then I question whether it should be the ID. If two people request the max id and both add to it, you suddenly have a collision. IDs should avoid that. First, they should be natural. Is there something about the data inherently that makes them unique, and does it makes sense for it to the be the ID? Is it something that runs the risk of being changed later?
    If there isn't a viable natural ID, then you have to go with a surrogate key - IDENTITY or Autonumber ... which is generated by the database when you insert the record. This is nice since you don't need to worry about it. IT does have some drawbacks, where if you don't know how to deal with it, can be a problem (specifically if you then need the ID to use as an FKey in another table). It's not impossible, it just takes some coordination. The alternative is to let the client generate the key using a GUID. then you can generate unlimited IDs on the client and the likelyhood they will collide with existing or other client entries is very, very, very small.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  11. #11
    Frenzied Member
    Join Date
    May 2013
    Posts
    1,126

    Re: Problem : Generating Unique Number (too slow)

    30,000 records are still very little to have problem on querying. i think even without putting index on it, it should still do fast query right?

    i have almost the same code but it generates unique number really fast. maybe its the data type he's using perhaps.

  12. #12
    PowerPoster Nightwalker83's Avatar
    Join Date
    Dec 2001
    Location
    Adelaide, Australia
    Posts
    13,344

    Re: Problem : Generating Unique Number (too slow)

    Quote Originally Posted by codesearcher View Post
    i have almost the same code but it generates unique number really fast. maybe its the data type he's using perhaps.
    Could you post your code?
    when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
    If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
    https://get.cryptobrowser.site/30/4111672

  13. #13
    Frenzied Member
    Join Date
    May 2013
    Posts
    1,126

    Re: Problem : Generating Unique Number (too slow)

    i am willing to post but i am on mobile right now and away from my laptop where my codes are.

  14. #14

    Thread Starter
    Hyperactive Member
    Join Date
    May 2013
    Posts
    328

    Re: Problem : Generating Unique Number (too slow)

    Quote Originally Posted by Nightwalker83 View Post
    Could you post your code?
    Code:
    Dim rsGEN As ADODB.Recordset
    Dim sqlCriteria As String
    Dim cNum As Long
    
    Set rsGEN = New ADODB.Recordset
        rsGEN.CursorLocation = adUseClient
        rsGEN.CursorType = adOpenStatic
        rsGEN.LockType = adLockReadOnly
    
    sqlCriteria = "SELECT MAX(REINPUT) AS CNUMBER FROM TBL_PAYROLL" '
    
        rsGEN.Open sqlCriteria, cn
        
        If Not rsGEN.EOF Then
            rsGEN.MoveLast
            cNum = rsGEN.Fields("CNUMBER").Value + 1
        End If
        
    
     LBLNumber.Caption = Format(cNum, "000000")
    
    Set rsGEN = Nothing

  15. #15
    Frenzied Member
    Join Date
    May 2013
    Posts
    1,126

    Re: Problem : Generating Unique Number (too slow)

    whats the datatype of your field reinput?

  16. #16
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: Problem : Generating Unique Number (too slow)

    the fastest possible way to get an unique number (long)
    using the NWIND.MDB we all have (i suppose)
    this gets an unique ID for the categories table
    if you want an unique number for the entire db
    just create a table with just an autonumber (with a unique index,primary key is preferable)

    Code:
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim UniqueNumber As Long
    
    Private Sub Command1_Click()
       Set db = OpenDatabase("C:\Program Files\Microsoft Visual Studio\VB98\NWIND.MDB")
       Set rs = db.OpenRecordset("Categories")
       'do not lock the page on addnew
       rs.LockEdits = False
       rs.AddNew
       UniqueNumber = rs!CategoryID
       MsgBox UniqueNumber
        'we only need a unique number
        'so there is no need to really add a record
       rs.CancelUpdate
       rs.Close
       Set rs = Nothing
       db.Close
       Set db = Nothing
    End Sub
    of course,in the real world the db and the rs stay open for the entire duration of the program
    except of course when there is a non recoverable error somewhere
    Last edited by IkkeEnGij; Aug 24th, 2014 at 03:32 AM. Reason: added 'when'

  17. #17
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Problem : Generating Unique Number (too slow)

    Not sure that's going to work long term. What happens the next time a number is needed?

    Personally I think the OP is going about this all wrong, and since the questions aren't being answered, there's not much point in me continuing.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  18. #18
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: Problem : Generating Unique Number (too slow)

    Personally I think the OP is going about this all wrong, and since the questions aren't being answered, there's not much point in me continuing.
    me thinks the same. only i havent even started

  19. #19
    PowerPoster Nightwalker83's Avatar
    Join Date
    Dec 2001
    Location
    Adelaide, Australia
    Posts
    13,344

    Re: Problem : Generating Unique Number (too slow)

    morbid.ivan, that question about posting the code was directed at codesearcher.
    when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
    If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
    https://get.cryptobrowser.site/30/4111672

  20. #20
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: Problem : Generating Unique Number (too slow)

    Quote Originally Posted by techgnome View Post
    Not sure that's going to work long term. What happens the next time a number is needed?

    -tg
    may i suggest you think twice, or maybe try it before posting such a remark
    and if you want it really universaly unique (according to MS), just change it to replication-id
    and you'l not even need an index on the table

  21. #21
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Problem : Generating Unique Number (too slow)

    Quote Originally Posted by IkkeEnGij View Post
    the fastest possible way to get an unique number (long)
    using the NWIND.MDB we all have (i suppose)
    this gets an unique ID for the categories table
    if you want an unique number for the entire db
    just create a table with just an autonumber (with a unique index,primary key is preferable)

    Code:
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim UniqueNumber As Long
    
    Private Sub Command1_Click()
       Set db = OpenDatabase("C:\Program Files\Microsoft Visual Studio\VB98\NWIND.MDB")
       Set rs = db.OpenRecordset("Categories")
       'do not lock the page on addnew
       rs.LockEdits = False
       rs.AddNew
       UniqueNumber = rs!CategoryID
       MsgBox UniqueNumber
        'we only need a unique number
        'so there is no need to really add a record
       rs.CancelUpdate
       rs.Close
       Set rs = Nothing
       db.Close
       Set db = Nothing
    End Sub
    of course,in the real world the db and the rs stay open for the entire duration of the program
    except of course when there is a non recoverable error somewhere
    Quote Originally Posted by techgnome View Post
    Not sure that's going to work long term. What happens the next time a number is needed?

    Personally I think the OP is going about this all wrong, and since the questions aren't being answered, there's not much point in me continuing.

    -tg
    Quote Originally Posted by IkkeEnGij View Post
    may i suggest you think twice, or maybe try it before posting such a remark
    and if you want it really universaly unique (according to MS), just change it to replication-id
    and you'l not even need an index on the table
    Huh... Ummm ok... well I don't have Access. Heck I'm not even on a Windows box, nor do I have VB6 installed. All I did was mention that I wasn't sure that would work - I did NOT say difinitvely that it wouldn't, just that I wasn't sure... I know that Access and Autonumbers are a bit weird, but I couldn't remember if it would cause the number to be reused and what would happen when another number is needed - would it end up recycling the last same number.

    Since clearly this isn't a thread where a discussion is wanted, I'm done with it.

    Good lick.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  22. #22
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Problem : Generating Unique Number (too slow)

    Quote Originally Posted by morbid.ivan View Post
    Nothing happens, still slow.

    But when I've try to put the program where the back-end database located, I can filter more than 1000 records
    less than 3 seconds.

    Any suggestion?
    3 seconds to filter 1000 records is pretty slow as well.
    Most of my DB queries complete in less than 1 second even when using Access on a network drive on a slow PC and way more than a few thousand records.

    The only time I have ran into issues where I have saw simple queries take 2 or more seconds was in a case where there was an extreme amount of network traffic eating up all the bandwidth.

  23. #23

    Thread Starter
    Hyperactive Member
    Join Date
    May 2013
    Posts
    328

    Re: Problem : Generating Unique Number (too slow)

    Quote Originally Posted by codesearcher View Post
    whats the datatype of your field reinput?
    (Text Type) Primary field

  24. #24
    Frenzied Member
    Join Date
    May 2014
    Location
    Kallithea Attikis, Greece
    Posts
    1,289

    Re: Problem : Generating Unique Number (too slow)

    I have an idea for you...morbid.ivan
    I think is the top solution...you need a table with one record with one field..CNUMBER. You only find once the CNUMBER, and store it in that table and then you can use that record and lonely field as a variable in your database. Why is a solution? Because if you ever delete a record...even the record with the max CNUMBER you never use that CNUMBER but CNUMBER+1 so you ever use a unique number that happen to not exist. That is your needing, a number that does not exist and not the current top CNUMBER. When you find what you need then you have the top solution.

  25. #25
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: Problem : Generating Unique Number (too slow)

    georgekar
    please take a look at post #8 and post #10
    explains why that could get you into trouble

  26. #26
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Problem : Generating Unique Number (too slow)

    Quote Originally Posted by morbid.ivan View Post
    (Text Type) Primary field
    I think your problem's in the datatype, since you're using MAX on a Textfield (think sorting by string)
    But i'm not sure about it. It's been over 10 years I've done anything serious with Access
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  27. #27
    Frenzied Member
    Join Date
    May 2014
    Location
    Kallithea Attikis, Greece
    Posts
    1,289

    Re: Problem : Generating Unique Number (too slow)

    Quote Originally Posted by IkkeEnGij View Post
    georgekar
    please take a look at post #8 and post #10
    explains why that could get you into trouble
    IkkeEnGij,
    Nothing explains at that posts. My idea is to use the power of a database. Using a table you can lock the record so no one can get that record and change the value. Are you happy with that??

    Read here please: http://www.databasedev.co.uk/multi-u...d-locking.html
    I think adLockPessimistic is the right choice.

  28. #28
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Problem : Generating Unique Number (too slow)

    Quote Originally Posted by georgekar View Post
    Nothing explains at that posts. My idea is to use the power of a database. Using a table you can lock the record so no one can get that record and change the value. Are you happy with that?
    I won't repeat my first thought... my second thought is that clearly you don't understand multiuser environments. Locking tables is bad... especially for something as stupid as a single number. What happens when two people need a number at the same time? One of them is going to have to wait... and for how long? For as long as the other has the table locked. It's a hack solution to a problem that shouldn't exist.

    There is ZERO reason to have a table that has one row, one field, and does nothing but track a number. Zero. The field is being used for an ID (I suspect, I don't really know since the questions I've asked have gone unanswered, and quite personally couldn't care any less if they do get answered at this point) so, yeah, use the power of the database as you put it, but do it right, add an AutoNumber field ...there, there is your incremental number. Make it the PKey. There... now you don't need to worry about locking tables or doing crap hacks for things they aren't intended (you've distorted the meaning behind that article.)

    So am I happy with that?
    Not.
    One.
    Bit.

    Not even a nibble.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  29. #29
    Frenzied Member
    Join Date
    May 2014
    Location
    Kallithea Attikis, Greece
    Posts
    1,289

    Re: Problem : Generating Unique Number (too slow)

    I like the Auto Increment Number too. For some reason this guy didn't used. Locking the table for one field is not a problem if any time one user can put a new record. But all the other records in the "master" table can edited as we wish, by many users. So the locking is for new entry. As you say, we didn't knew the concept of his work. So we going by guessing, and maybe we waste our time..

Posting Permissions

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



Click Here to Expand Forum to Full Width