-
Sep 11th, 2017, 08:31 AM
#1
Thread Starter
Lively Member
[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!
-
Sep 11th, 2017, 09:54 AM
#2
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
-
Sep 11th, 2017, 09:55 AM
#3
Re: Optimize program run speed
Thread moved to the code it better forum.
-
Sep 11th, 2017, 12:23 PM
#4
Thread Starter
Lively Member
Re: Optimize program run speed
Originally Posted by NeedSomeAnswers
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
-
Sep 12th, 2017, 03:24 AM
#5
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
-
Sep 12th, 2017, 04:24 AM
#6
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?
-
Sep 12th, 2017, 05:18 AM
#7
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
-
Sep 12th, 2017, 07:29 AM
#8
Thread Starter
Lively Member
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
-
Sep 12th, 2017, 08:06 AM
#9
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
-
Sep 12th, 2017, 09:11 AM
#10
Thread Starter
Lively Member
Re: Optimize program run speed
Originally Posted by NeedSomeAnswers
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)
-
Sep 12th, 2017, 10:11 AM
#11
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
-
Sep 13th, 2017, 02:11 PM
#12
Thread Starter
Lively Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|