[RESOLVED] Optimize program run speed-VBForums
Results 1 to 12 of 12

Thread: [RESOLVED] Optimize program run speed

  1. #1

    Thread Starter
    Member
    Join Date
    May 2017
    Posts
    43

    Resolved [RESOLVED] Optimize program run speed

    I've got a program that I have been working on whose primary purpose is to display data to a central location, which is being pulled via HTTPwebrequest and and a small segment of SQL. As a side note, this is all being done with VB.net in Visual Studio 2015.

    I'm trying to add a new set of data to be displayed as well, but this task is taking much longer than the original requests I was doing, which is resulting in a heavy resource toll on the program.

    Brief Overview of original program format:

    MainCode()
    HTTPwebrequest of 11 devices
    Display results into 44 different labels
    Refresh all of this data every 12.5 seconds (utilizing a timer tick event)
    End Sub

    All data from the 11 devices is pulled and the 44 labels are populated in approx 1 second total.

    Now, I need to pull one more set of data for each of these devices, but this is taking significantly longer than the original requests, for the reason I am assuming to be due to it pulling the data from a much larger table. Each device for this section takes approximately 2.7 seconds each, which results in approx. 30 seconds for all 11 devices vs taking only 1 second in my MainCode().

    Having this SecondDataPull() in the MainCode() resulted in refreshing of the original data taking too long, so I removed the SecondDataPull() code from the MainCode() and place it in its own Sub, being triggered by a timer as well in hopes that this would resolve this issue. Even with having its own sub, the resource usage for this portion takes so long, that it causes my MainCode() to not pull data as reliably as it should on its' 12.5 second intervals.

    Am I approaching this issue in the most efficient way as possible? Or is there a better way to run these data pulls on a loop of a given cycle, which wouldn't take as big of a resource toll as the timer would? Or maybe a better way to place my code to optimize my speed?

    I'm open to all thoughts and/or suggestions and it is much appreciated. If you need me to elaborate, let me know. Thanks!

  2. #2
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,376

    Re: Optimize program run speed

    Now, I need to pull one more set of data for each of these devices, but this is taking significantly longer than the original requests, for the reason I am assuming to be due to it pulling the data from a much larger table. Each device for this section takes approximately 2.7 seconds each
    So It sounds like your SQL is your bottle neck here and quite a big one.

    If you run the same request just in a script in SQL Server Management studio, does it still take 2.7 second for each?

    If it does - then the first thing i would be doing would be to look to optimise my Select Statement, and to look at adding some Indexes on the tables i am pulling data from.

    2.7 seconds sounds like a long time, how many rows of data are you returning?, and are you able to post your SQL so we can see how complex it is?
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  3. #3
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    9,157

    Re: Optimize program run speed

    Thread moved to the code it better forum.

  4. #4

    Thread Starter
    Member
    Join Date
    May 2017
    Posts
    43

    Re: Optimize program run speed

    Quote Originally Posted by NeedSomeAnswers View Post
    So It sounds like your SQL is your bottle neck here and quite a big one.

    If you run the same request just in a script in SQL Server Management studio, does it still take 2.7 second for each?

    If it does - then the first thing i would be doing would be to look to optimise my Select Statement, and to look at adding some Indexes on the tables i am pulling data from.

    2.7 seconds sounds like a long time, how many rows of data are you returning?, and are you able to post your SQL so we can see how complex it is?
    I haven't tried running the SQL portion elsewhere, but that is something I can try. The devices that I pull the data from are set in stone from the factory, so that portion would not be able to be changed by me.

    I am only returning 1 row of data with 2 columns. As for how many it looks through to pull that, it is currently looking through about 6000 records to pull those 2 records out for me ( I determined this by removing the max() statement and returning all of the event_numbers, and it still took the same amount of time - the only bit of data that I am interested in is the most recent event_number that has occurred, which is why I have been using the max()).

    My SQL statement is:

    Code:
    SELECT max(event_number),vorne_enum_lookup('timeline_stream','reason',reason) FROM timeline_stream
    The full line with the VB involved is:
    Code:
    Dim postData2 As String = "response_type=application/json&sql_statement=SELECT max(event_number),vorne_enum_lookup('timeline_stream','reason',reason) FROM timeline_stream"
    NOTE: vorne_enum_lookup is a section of code specific to the device I am using, but removing that section does nothing to speed up the process. I might also add this is technically LESQL that I am using, the devices do not accept full SQL. One last note too - event_number is an Indexed Field
    Last edited by Mearntain; Sep 11th, 2017 at 12:29 PM. Reason: Elaborated

  5. #5
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,376

    Re: Optimize program run speed

    I am only returning 1 row of data with 2 columns. As for how many it looks through to pull that, it is currently looking through about 6000 records to pull those 2 records out for me
    Thats not a lot of data, in fact its a very small amount of data to be taking 2.7 seconds to run.

    Have you thought about changing your statement so that your limiting the number of rows returned wither via a where clause or a Limit? and testing the speed difference?

    something like -

    Code:
    SELECT event_number,vorne_enum_lookup('timeline_stream','reason',reason) FROM timeline_stream ORDER BY event_number DESC LIMIT 1;
    or

    Code:
    SELECT event_number,vorne_enum_lookup('timeline_stream','reason',reason) FROM timeline_stream WHERE event_number= (SELECT MAX(event_number) FROM table);
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  6. #6
    PowerPoster
    Join Date
    Oct 2013
    Posts
    2,846

    Re: Optimize program run speed

    Maybe it's not the performance of the actual query which is executed on a server, but a delay in the post and/or json building.
    Dim postData2 As String = "response_type=application/ ...."
    Do you have direct access to server to check the performance?

  7. #7
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,376

    Re: Optimize program run speed

    Maybe it's not the performance of the actual query
    I agree it might not be, but its one of the easiest thing to check and so rule in or out.

    In the OP's earlier post they say that they are getting data from Devices rather than directly from there own web services, so i dont think they have much control over the back-end if any, and so i dont think they can change the web service.

    One thing that would be helpful would be to see the full code for the web service calls
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  8. #8

    Thread Starter
    Member
    Join Date
    May 2017
    Posts
    43

    Re: Optimize program run speed

    Below is a copy of the code for pulling the data. I feel pretty confident that the delay is a result of the query, but I may be wrong. When pulling the data, it is returned to me in the forum of an array in this format: [data1, data2, data3, ....,error,statement_time0.00085800000000000004,total_time0.00102000000000000010] - That is an actual statement, with data points removed, that I received from pulling a different set of data, so that's where I am getting the times from.

    Code:
    Private Sub DTpull()
    
            Dim tempRead2 As String = Nothing
            Dim request2 As HttpWebRequest = Nothing
            Dim response2 As HttpWebResponse = Nothing
            Dim commaCount2 As Integer = 0
            Dim DTeventnum As String = Nothing
            Dim DTreason As String = Nothing
    
            request2 = HttpWebRequest.CreateHttp(“http://192.168.100.223/sql-request.do")
    
            'selects columns and from what table the data is to be pulled
            Dim postData2 As String = "response_type=application/json&sql_statement=SELECT max(event_number),vorne_enum_lookup('timeline_stream','reason',reason) FROM timeline_stream"
            'Dim postData2 As String = "response_type=application/json&sql_statement=SELECT max(event_number),vorne_enum_lookup('timeline_stream','reason',reason) FROM timeline_stream"
            Dim data2() As Byte = UTF8Encoding.UTF8.GetBytes(postData2)
            request2.ContentLength = data2.Length
            request2.Method = "POST"
            request2.Timeout = 10000
    
            Using requestStream2 As Stream = request2.GetRequestStream
                requestStream2.Write(data2, 0, data2.Length)
            End Using
    
            'reads response to the tempRead string and removes all the extra bits that we dont need
            response2 = CType(request2.GetResponse(), HttpWebResponse)
    
            Using rsr2 As New StreamReader(response2.GetResponseStream)
                tempRead2 = rsr2.ReadToEnd()
                tempRead2 = tempRead2.Replace("[", "").Replace(" ", "").Replace("{", "").Replace("}", "").Replace("]", "").Replace("""", "").Replace("data", "").Replace(":", "")
            End Using
    
            'for statement to read the entire string
            For i As Integer = 1 To 30 Step 1
                'reads for commas, if one is found it marks the location as the end of one piece of data, and the start of another
                If (GetChar(tempRead2, i).Equals(","c)) Then
                    commaCount2 += 1
                    'reads for the _ character, this signifies the end of the data stream
                ElseIf (GetChar(tempRead2, i).Equals("_"c)) Then
                    Exit For
    
                    'reads the numbers from each data point
                Else
                    'using the comma count this decides which data point is currently being read, and stores it to a temp variable
                    Select Case commaCount2
                        Case 0
                            DTeventnum += GetChar(tempRead2, i)
                        Case 1
                            DTreason += GetChar(tempRead2, i)
                    End Select
                End If
            Next
    
            C1reason.Text = DTreason
    
        End Sub

  9. #9
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,376

    Re: Optimize program run speed

    So have you tried it with either of the queries i posted above? do they make any difference?
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  10. #10

    Thread Starter
    Member
    Join Date
    May 2017
    Posts
    43

    Re: Optimize program run speed

    Quote Originally Posted by NeedSomeAnswers View Post
    So have you tried it with either of the queries i posted above? do they make any difference?
    I completely overlooked your earlier post and just saw it now.


    Using the below code, I did see improvement, and my statement time improved from 2.7 seconds to 2.2 seconds.
    Code:
    SELECT event_number,vorne_enum_lookup('timeline_stream','reason',reason) FROM timeline_stream ORDER BY event_number DESC LIMIT 1
    Using the second code you posted, I am getting an error and can't get that to run properly yet; however, I feel as if this route may yield the fastest time if I can get it typed right.

    Using the below, I get a LESQLerror.
    Code:
    SELECT event_number,vorne_enum_lookup('timeline_stream','reason',reason) FROM timeline_stream WHERE event_number= (SELECT MAX(event_number) FROM table);
    I also tried this, and get the same LESQLerror as well.
    Code:
    SELECT event_number,vorne_enum_lookup('timeline_stream','reason',reason) FROM timeline_stream WHERE event_number= (SELECT MAX(event_number) FROM timeline_stream);
    If I edit it to below, I get an error saying misuse of aggregate function MAX()
    Code:
    SELECT event_number,vorne_enum_lookup('timeline_stream','reason',reason) FROM timeline_stream WHERE event_number= MAX(event_number)

  11. #11
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,376

    Re: Optimize program run speed

    If EventNumber is your primary key or is Indexed, then the first statement with the LIMIT will run quicker

    For the second option;

    Your second try was the correct syntax, and should work really, although you should remove the semi colon ";"

    Code:
    SELECT event_number,vorne_enum_lookup('timeline_stream','reason',reason) FROM timeline_stream WHERE event_number= (SELECT MAX(event_number) FROM timeline_stream)
    Last edited by NeedSomeAnswers; Sep 12th, 2017 at 10:17 AM.
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  12. #12

    Thread Starter
    Member
    Join Date
    May 2017
    Posts
    43

    Re: Optimize program run speed

    Sounds good, I do appreciate it. It sounds as if where I am at now is as fast as Ill be able to make it go. Atleast you helped me shave a half second on the process, which any amount saved was helpful.

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

Survey posted by VBForums.