VS 2015 How to get faster loop-VBForums
Results 1 to 11 of 11

Thread: How to get faster loop

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jan 2018
    Posts
    18

    How to get faster loop

    First : single data i got from socket , so i can not use paralel for and I use SQL database 2008R2 and only 1 record per incoming ,i thing in 1 second I should write/edit to sql database more than 100 record in peak hour

    i try to make example
    1. main program like data incoming from socket ( without delay)
    Code:
      Sub Main()
            Dim startTime As DateTime = DateTime.Now
            Dim EndTime As DateTime = DateTime.Now
            Dim Difftime As String
    
            Console.WriteLine(startTime.ToString)
            For i = 0 To 100
                Console.WriteLine("----> " & test(i))
            Next
            EndTime = DateTime.Now
            Console.WriteLine(EndTime.ToString)
            Difftime = (EndTime - startTime).ToString
            Console.WriteLine(Difftime)
            Console.ReadLine()
        End Sub
    2. function to save data to sql database ( i make it delay 1000)
    Every time I receive single record data from main program, I need to writes/edit to a SQL database. However, those writes all take some amount of time and data incoming from main program is so fast .
    I can not use SqlBulkCopy because the incoming data only 1 single record.
    the Sleep() is simulating the database writes that take a long time.
    Code:
       Function test(ByVal i As Integer)
            Dim multi As Integer
            multi = i * 5
            test = multi
            Threading.Thread.Sleep(1000)
        End Function
    my question is how to make faster the function? thx ==> i hope someone can make an example to use thread for my question
    Last edited by esugiawan; Feb 3rd, 2018 at 09:05 PM. Reason: to clear my question after friend in here make an answer

  2. #2

    Thread Starter
    Junior Member
    Join Date
    Jan 2018
    Posts
    18

    Re: How to get faster loop

    01/02/2018 17:17:35
    ----> 0
    ----> 5
    ----> ....
    ----> 495
    ----> 500
    01/02/2018 17:19:16
    00:01:41.0157778

  3. #3
    Sinecure devotee
    Join Date
    Aug 2013
    Location
    Southern Tier NY
    Posts
    4,169

    Re: How to get faster loop

    I don't understand the question.
    It seems like it would be faster is you had a shorter sleep, e.g. sleeping for 500 should be twice as fast.
    If you didn't Thread.Sleep at all, then it should be quite fast.

    Perhaps, for the real unasked question, you just need to push the data received into a concurrent queue and process the queued items in a separate thread so your socket code and process code can run independently without interference.

    The times you're showing is what I would expect from the code you posted. You're delaying 1000ms (1 second) in the test function. You call the function 101 times (0 to 100), so I would expect the execution to take around 101 seconds.
    Your delta time, 00:01:41, is 101 seconds.
    Last edited by passel; Feb 1st, 2018 at 10:19 AM.

  4. #4
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    31,562

    Re: How to get faster loop

    Yeah, at this point, this is a self inflicted problem. The loop would take far less than a millisecond without the sleep, so what is the point of the sleep?
    My usual boring signature: Nothing

  5. #5
    You don't want to know.
    Join Date
    Aug 2010
    Posts
    4,455

    Re: How to get faster loop

    It looks like what he's saying is:

    Every time I receive data, I need to make many writes to a database. However, those writes all take some amount of time, and that means my program ends up missing some data. How can I resolve this?
    So the Sleep() is simulating the database writes that take a long time.

    I think the only solution is to store data in a buffer and use threading to try and empty the buffer into the database. But if data consistently comes in faster than it can be written, you're going to run out of memory, so there needs to be a guarantee there comes a point where you can ask for data to come in more slowly.

    You could, perhaps, make two threads that write two sets of data to the database in parallel, but whether that can or will work depends on if the database you choose supports concurrent writes. This could expand to n threads. But writing about how to pull it off is sort of complex, so it is worht making sure we understand hte problem!
    This answer is wrong. You should be using TableAdapter and Dictionaries instead.

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,581

    Re: How to get faster loop

    An alternative to multiple database-writer threads is to have one of them that does batch updates, rather than a single record at a time.

    If SQL Server is being used then the SqlBulkCopy class is a good way to go (it is dramatically faster than writing one record at a time), and some other database systems have similar things available. There are other options too, but what they are depends at least in part on the database system involved.


    It would help a lot to know what database system you are using, how many records you need to write to it (and how often), and whether there are any "quiet" periods when you can catch up with a backlog.

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Jan 2018
    Posts
    18

    Re: How to get faster loop

    It would help a lot to know what database system you are using, how many records you need to write to it (and how often), and whether there are any "quiet" periods when you can catch up with a backlog.
    I use SQL database 2008R2 and only 1 record per incoming ,i thing in 1 second I should write more than 100 record in peak hour

    I think the only solution is to store data in a buffer and use threading to try and empty the buffer into the database. But if data consistently comes in faster than it can be written, you're going to run out of memory, so there needs to be a guarantee there comes a point where you can ask for data to come in more slowly.

    You could, perhaps, make two threads that write two sets of data to the database in parallel, but whether that can or will work depends on if the database you choose supports concurrent writes. This could expand to n threads. But writing about how to pull it off is sort of complex, so it is worht making sure we understand hte problem!
    could you help give me some example how to use threads in this situation

  8. #8
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    97,669

    Re: How to get faster loop

    It sounds like what might be needed here is a BlockingCollection(Of T). I've never used one but the documentation describes it like so:
    Provides blocking and bounding capabilities for thread-safe collections that implement IProducerConsumerCollection(Of T).
    What you can do is create a ConcurrentQueue(Of T) and then pass that to the constructor of the BlockingCollection(Of T). The Add method of the BlockingCollection(Of T) will then call the Enqueue method of the internal ConcurrentQueue(Of T) and the Take method will call the Dequeue method. You won't have any issues with adding and removing on different threads and the BlockingCollection(Of T) can also guard against the overflow that Sitten Spynne described. If a consuming thread tries to take an item from the collection when it's empty then it will block until data becomes available. More importantly though, you can specify an upper bound for the collection and any attempt to add when that capacity has been reached will also block. That means that you can detect when the buffer is full and do whatever is appropriate, e.g. notify an admin that the app is receiving too much data to write efficiently.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  9. #9
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,581

    Re: How to get faster loop

    Quote Originally Posted by esugiawan View Post
    I use SQL database 2008R2 and only 1 record per incoming ,i thing in 1 second I should write more than 100 record in peak hour
    If you want to write 100 records a second then SqlBulkCopy is what I would try first.

    There is a good example in the documentation: https://msdn.microsoft.com/en-us/lib...v=vs.110).aspx

    Note that most of that example isn't needed in a real application (eg: it counts the records in the table first, and it fills a datatable with hard-coded values), the important part is just this:
    Code:
                Using bulkCopy As SqlBulkCopy = _
                  New SqlBulkCopy(connection)
                    bulkCopy.DestinationTableName = "dbo.BulkCopyDemoMatchingColumns"
    
                    Try
                        ' Write from the source to the destination.
                        bulkCopy.WriteToServer(newProducts)
    
                    Catch ex As Exception
                        Console.WriteLine(ex.Message)
                    End Try
                End Using
    newProducts is a DataTable containing the data you want to add.

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Jan 2018
    Posts
    18

    Re: How to get faster loop

    Quote Originally Posted by si_the_geek View Post
    If you want to write 100 records a second then SqlBulkCopy is what I would try first.....
    thx my friend for your answer , fist i want to said sorry because my question is not clear and i edit my thread .. what my wrong is when data incoming from socket, it's not only insert but can be edit too, so i execute storeprocedure with param

  11. #11

    Thread Starter
    Junior Member
    Join Date
    Jan 2018
    Posts
    18

    Solution 1 Re: How to get faster loop

    I thing i found solution for this time, using thread

    Code:
     Sub Main()
            Dim startTime As DateTime = DateTime.Now
            Dim EndTime As DateTime = DateTime.Now
            Dim Difftime As String
    
    
    
            Console.WriteLine(startTime.ToString)
            For i = 0 To 10
                Console.WriteLine("----> " & test(i))
            Next
            EndTime = DateTime.Now
            Console.WriteLine(EndTime.ToString)
            Difftime = (EndTime - startTime).ToString
            Console.WriteLine(Difftime)
            Console.ReadLine()
    
    
            startTime = DateTime.Now
            Console.WriteLine(startTime.ToString)
            For i = 0 To 10
                Dim t As New Threading.Thread(AddressOf test)
                t.IsBackground = True
                ' Global_I = i
                t.Start(i)
            Next
            EndTime = DateTime.Now
            Console.WriteLine(EndTime.ToString)
            Difftime = (EndTime - startTime).ToString
            Console.WriteLine(Difftime)
            Console.ReadLine()
        End Sub
    Function threadProcess(ByVal i As Integer)
    Dim multi As Integer
    multi = Global_I * 5
    threadProcess = multi
    Threading.Thread.Sleep(1000)
    End Function
    Code:
    04/02/2018 21:08:16
    ----> 0
    ----> 5
    ----> 10
    ----> 15
    ----> 20
    ----> 25
    ----> 30
    ----> 35
    ----> 40
    ----> 45
    ----> 50
    04/02/2018 21:08:27
    00:00:11.0446318
    
    04/02/2018 21:08:34
    04/02/2018 21:08:35
    00:00:00.1850106

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width