-
[RESOLVED] vb.net improve programs
I,i have a little program that retrieves railway reservations for groups.
it downloads from a website getting a json.I store data in a matrix.
The object for the program is to know :
a) how many station are served from that train
b) how many people load and unload that train,selecting a particular station (ex rome,11 loaded,89 unloaded)
c) how many people load a specific car (ex car 5-10 passenger,car 7-11 passenger)
d) how many destinations and people selecting a particular station (ex from rome,11 passenger to florence,18 to milan,etc)
i have a matrix with 14 columns and setted with 5000 rows (i don't think the will be more).
i made a lot of inquiries with a for next to have all this informations,but some need a lot of time to get a response
my progam run in a company computers so i can't install or set anything,i can just place my exe on the computer.
in your opinion,wich would be the best way to improve my program ? datatable ? sql ?
thank for you cooperation
this is my code :
json download to the matrix (stands for bidimensional array)
i use newtonsoft json library
Code:
Public matrice(5000, 15) As String
Public matrice2(5000, 15) As String
Public matrice3(5000, 15) As String
Dim listadest As New List(Of Destinazioni)
Dim listacar As New List(Of paxincarrozza)
Dim listapax As ArrayList = New ArrayList
Dim listapaxcognomi As ArrayList = New ArrayList
Code:
Dim ser As JObject = JObject.Parse(json)
Dim data As List(Of JToken) = ser.Children().ToList
If chiamata = 1 Then
For Each item As JProperty In data
item.CreateReader()
For Each msg As JObject In item.Values
matrice(contatore, 0) = msg("departureLocationName")
matrice(contatore, 1) = msg("arrivalLocationName")
matrice(contatore, 2) = msg("pnrCode")
matrice(contatore, 3) = msg("serviceLevel")
matrice(contatore, 4) = msg("lastName")
matrice(contatore, 5) = msg("wagon")
matrice(contatore, 6) = msg("seat")
matrice(contatore, 7) = msg("firstName")
matrice(contatore, 8) = msg("transportMeanName")
matrice(contatore, 9) = msg("couponId")
contatore = contatore + 1
Next
Next
contatore = 0
Do
If ComboBox1.Items.IndexOf(matrice(contatore, 0)) = -1 Then
ComboBox1.Items.Add(matrice(contatore, 0))
End If
If ComboBox1.Items.IndexOf(matrice(contatore, 1)) = -1 Then
ComboBox1.Items.Add(matrice(contatore, 1))
End If
contatore = contatore + 1
Loop Until matrice(contatore, 0) = Nothing
Label1.Text = "TRENO " & (matrice(0, 8))
then to know passenger destination or passenger load for single car :
Code:
ListBox2.Items.Clear()
Dim i1 As Integer
Dim prima1 As Boolean = True
Dim appoggio1 As paxincarrozza
listacar.Clear()
For i1 = 1 To 4999
If ComboBox1.SelectedItem = (matrice(i1, 0)) Then
If prima1 = True Then
appoggio1.carrozza = (matrice(i1, 5))
appoggio1.quantita = 0
listacar.Add(appoggio1)
prima1 = False
End If
Dim appo1 As New List(Of paxincarrozza)
appo1.AddRange(listacar)
Dim trovato As Boolean = False
Dim a As Integer
For Each element As paxincarrozza In appo1
If element.carrozza = matrice(i1, 5) Then
trovato = True
a = listacar.IndexOf(element)
appoggio1 = element
End If
Next
If trovato = True Then
appoggio1.quantita = appoggio1.quantita + 1
listacar(a) = appoggio1
Else
appoggio1.carrozza = (matrice(i1, 5))
appoggio1.quantita = 1
listacar.Add(appoggio1)
End If
End If
Next
ListBox2.Items.Clear()
For Each element As paxincarrozza In listacar
ListBox2.Items.Add(element.carrozza & " - " & element.quantita)
Next
the point is : a datatable with sql query would be better in your opinion ? or it just load memory without any speed increase ? i'm not sure about it.
we are speaking about 5000 rows nothing more...
thanks to all of you
-
Re: vb.net improve programs
Hello eurostar_italia,
You need to rephrase your post, it says you have a program and you need it to do something?? If english isn't your first language you could try Google Translating your question, I guess.
EDIT:
I reread your post, are you looping through your data with a For/Next loop? Code?
yours,
Peter Swinkels
-
Re: vb.net improve programs
I'd use LINQ... You might find it's significantly quicker... We need to see your code.
-
Re: vb.net improve programs
We'd need to see the code anyways. What's a matrix in this case? I would have expected a datatable, yet you then ask if a datatable would be an improvement. It may well be, if you aren't already using one, because there are much worse alternatives that you might be using. Since we don't know, then there isn't all that much we can suggest.
-
Re: vb.net improve programs
thanks for your suggestion,the first way to be helped is to explain the problem and show the code.
This is my first time in a so technical forum.thanks
-
Re: vb.net improve programs
Quote:
Originally Posted by
Shaggy Hiker
We'd need to see the code anyways. What's a matrix in this case? I would have expected a datatable, yet you then ask if a datatable would be an improvement. It may well be, if you aren't already using one, because there are much worse alternatives that you might be using. Since we don't know, then there isn't all that much we can suggest.
I did it,i'm afraid i didn't do before!
-
Re: vb.net improve programs
i put the code on top.i hope you now you can help me to increase the program performance
-
Re: vb.net improve programs
Quote:
Originally Posted by
eurostar_italia
I,i have a little program that retrieves railway reservations for groups.
it downloads from a website getting a json.I store data in a matrix.
The object for the program is to know :
a) how many station are served from that train
b) how many people load and unload that train,selecting a particular station (ex rome,11 loaded,89 unloaded)
c) how many people load a specific car (ex car 5-10 passenger,car 7-11 passenger)
d) how many destinations and people selecting a particular station (ex from rome,11 passenger to florence,18 to milan,etc)
i have a matrix with 14 columns and setted with 5000 rows (i don't think the will be more).
i made a lot of inquiries with a for next to have all this informations,but some need a lot of time to get a response
my progam run in a company computers so i can't install or set anything,i can just place my exe on the computer.
in your opinion,wich would be the best way to improve my program ? datatable ? sql ?
thank for you cooperation
this is my code :
json download to the matrix (stands for bidimensional array)
i use newtonsoft json library
Code:
Public matrice(5000, 15) As String
Public matrice2(5000, 15) As String
Public matrice3(5000, 15) As String
Dim listadest As New List(Of Destinazioni)
Dim listacar As New List(Of paxincarrozza)
Dim listapax As ArrayList = New ArrayList
Dim listapaxcognomi As ArrayList = New ArrayList
Code:
Dim ser As JObject = JObject.Parse(json)
Dim data As List(Of JToken) = ser.Children().ToList
If chiamata = 1 Then
For Each item As JProperty In data
item.CreateReader()
For Each msg As JObject In item.Values
matrice(contatore, 0) = msg("departureLocationName")
matrice(contatore, 1) = msg("arrivalLocationName")
matrice(contatore, 2) = msg("pnrCode")
matrice(contatore, 3) = msg("serviceLevel")
matrice(contatore, 4) = msg("lastName")
matrice(contatore, 5) = msg("wagon")
matrice(contatore, 6) = msg("seat")
matrice(contatore, 7) = msg("firstName")
matrice(contatore, 8) = msg("transportMeanName")
matrice(contatore, 9) = msg("couponId")
contatore = contatore + 1
Next
Next
contatore = 0
Do
If ComboBox1.Items.IndexOf(matrice(contatore, 0)) = -1 Then
ComboBox1.Items.Add(matrice(contatore, 0))
End If
If ComboBox1.Items.IndexOf(matrice(contatore, 1)) = -1 Then
ComboBox1.Items.Add(matrice(contatore, 1))
End If
contatore = contatore + 1
Loop Until matrice(contatore, 0) = Nothing
Label1.Text = "TRENO " & (matrice(0, 8))
then to know passenger destination or passenger load for single car :
Code:
ListBox2.Items.Clear()
Dim i1 As Integer
Dim prima1 As Boolean = True
Dim appoggio1 As paxincarrozza
listacar.Clear()
For i1 = 1 To 4999
If ComboBox1.SelectedItem = (matrice(i1, 0)) Then
If prima1 = True Then
appoggio1.carrozza = (matrice(i1, 5))
appoggio1.quantita = 0
listacar.Add(appoggio1)
prima1 = False
End If
Dim appo1 As New List(Of paxincarrozza)
appo1.AddRange(listacar)
Dim trovato As Boolean = False
Dim a As Integer
For Each element As paxincarrozza In appo1
If element.carrozza = matrice(i1, 5) Then
trovato = True
a = listacar.IndexOf(element)
appoggio1 = element
End If
Next
If trovato = True Then
appoggio1.quantita = appoggio1.quantita + 1
listacar(a) = appoggio1
Else
appoggio1.carrozza = (matrice(i1, 5))
appoggio1.quantita = 1
listacar.Add(appoggio1)
End If
End If
Next
ListBox2.Items.Clear()
For Each element As paxincarrozza In listacar
ListBox2.Items.Add(element.carrozza & " - " & element.quantita)
Next
the point is : a datatable with sql query would be better in your opinion ? or it just load memory without any speed increase ? i'm not sure about it.
we are speaking about 5000 rows nothing more...
thanks to all of you
Hi eurostar_italia,
Thank you for sharing your code so we can help you out. At the moment I have no access to vb.net but after a quick review of your code I have these tips for you:
1. Try experimenting with the list object in System.Collections.Generic namespace. Personally I prefer that over arrays. Especially arrays with fixed sizes. Remember, in modern Basics you can redimension an array without data loss.
2.
Code:
matrice(contatore, 0) = msg("departureLocationName")
matrice(contatore, 1) = msg("arrivalLocationName")
matrice(contatore, 2) = msg("pnrCode")
matrice(contatore, 3) = msg("serviceLevel")
matrice(contatore, 4) = msg("lastName")
matrice(contatore, 5) = msg("wagon")
matrice(contatore, 6) = msg("seat")
matrice(contatore, 7) = msg("firstName")
matrice(contatore, 8) = msg("transportMeanName")
matrice(contatore, 9) = msg("couponId")
In the above code you might want to use references to an enumeration rather than actual numbers:
Code:
enum columnse
departureLocationName
arrivalLocationName
pnrCode
end enum
matrice(contatore, columnse. departureLocationName) = msg("departureLocationName")
matrice(contatore, columnse.arrivalLocationName) = msg("arrivalLocationName")
matrice(contatore, columnse.pnrCode) = msg("pnrCode")
3. You can shorten "contatore = contatore + 1" to "contatore += 1"
This works for all variables on which addition, subtraction, multiplication and division are performed.
4. I recommend you use meaningful names for your controls rather than List1 and Combo1.
5. You might want to change your loop from:
Code:
Do
...
Loop Until matrice(contatore, 0) = Nothing
to:
Code:
Do Until matrice(contatore, 0) = Nothing
...
Loop
I suspect your code might otherwise throw an error when the very first item contains "Nothing".
If you would like more tips or have any further questions, feel free to ask and post more code,
Hoping this helps.
yours,
Peter Swinkels
-
Re: vb.net improve programs
Quote:
I suspect your code might otherwise throw an error when the very first item contains "Nothing".
If you would like more tips or have any further questions, feel free to ask and post more code,
Hoping this helps.
yours,
Peter Swinkels
thank you for your suggestions....i did them at once.
What about datatables ? do you think they would be better ? somebody told me that they are just a way of wasting memory,some other they're ok for the informations i need (lots of querys)...
-
Re: vb.net improve programs
Hello eurostar_italia,
Thank you for your quick reply. I did a quick Google search for "datatable c# memory leak" and similar searches resulted in a lot of posts concerning memory leaks and usage. My recommendation is to keep using list objects or arrays. Other users who have more experience in these things than I do may have other suggestions for you though. Could you show the changes you made or rather not?
yours,
Peter Swinkels
-
Re: vb.net improve programs
Datatables give you a bunch of ways to filter, sort, and search in the data. If you want to filter out a subset, then they'd certainly be better. If you want to sort, then they'd be better. If you want to search....well, then they may be better.
Another option is the Dictionary. You could certainly switch from your matrices to dictionaries quite easily, but perhaps not all that efficiently. For example, a Dictionary(of Integer, String(15)) would be the same as your matrix. If you were to look up a single item from the dictionary, it would be considerably faster than searching through the matrix that you have, but that will be true ONLY if you know the array you want, and I think you don't. Searching through all the keys in a dictionary is no faster than searching through all the indexes in an array, so a dictionary only makes sense if you can look up items by key without looking through all the keys to find the right one.
Still, that might make sense as long as you don't do it in the trivial way I just described.
What you have is a datatable. You are filling a reader, that could fill a datatable with a single line, at which point you get all the searching advantages of the datatable. I wouldn't worry about the memory leaks. I've never run into any of that. I also wouldn't worry about wasting memory. Memory is abundant, these days, and even back when it wasn't CPU cycles were always more valuable than memory, so people would freely waste memory if it meant faster processes. Only in some embedded systems is memory more valuable than speed.
Still, the search capabilities of datatables may not gain you much. Some of them aren't all that fast, they're just really simple. For example, you can call the .Select() extension method to find a row. That's a single line, but it's 10% to 20% slower than going through the datatable row by row yourself. Using a loop would be six lines, while Select is one, but the loop is slightly faster. Normally, that doesn't matter, so people use Select. When it matters, it matters.
However, your data really fits as a class with 9 members (I'm not sure why your inner array is sized to 15) for the 9 elements you are putting into the inner element of the matrix. If you create a class rather than using a 2D array, you can have a List(of YourClass). That will be easier to work with, just as easy (or easier, but not faster) to search, and will have the same performance.
Better yet, if you do that, you can then improve the performance by wasting memory. Seeking in a Dictionary is FAR faster than iterating through a collection, especially a collection with 5000 items in it. So, for example, once you have built up your List(of YourClass) you could build a DestinationDictionary:
Private destinationDictionary As New Dictionary(of String,List(of YourClass))
Then, for each departureLocationName that you have, add a list that contains just those YourClass that have that departureLocationName. Now you can use the departureLocationName to look up that subset of the YourClass set that has that location name, and that lookup will be considerably faster than going through the list itself.
In theory, you could build a different dictionary for each element in your class, which would mean 9 dictionaries. Yeah, this uses up a bit of memory, but not all that much. The classes are just references. You aren't copying them into the dictionary, so the dictionary entry holds the key (a string), and a reference to a List, which itself just holds some number of references. That's some memory, but not all that much, really, and memory is abundant.
In practice, you probably wouldn't need to build 9 dictionaries, either. You'd probably find that you always start a search on just one or two elements, so you'd build dictionaries for just those elements, and not the rest.
-
Re: vb.net improve programs
Quote:
Originally Posted by
Peter Swinkels
Hello eurostar_italia,
Thank you for your quick reply. I did a quick Google search for "datatable c# memory leak" and similar searches resulted in a lot of posts concerning memory leaks and usage. My recommendation is to keep using list objects or arrays. Other users who have more experience in these things than I do may have other suggestions for you though. Could you show the changes you made or rather not?
yours,
Peter Swinkels
before changes : 10 seconds to load matrix,10 seconds to load listboxes after combobox1 changes its values.
afeter changes :6 seconds to load matrix,8 to loaad listboxes
-
Re: vb.net improve programs
Quote:
Originally Posted by
Shaggy Hiker
Datatables give you a bunch of ways to filter, sort, and search in the data. If you want to filter out a subset, then they'd certainly be better. If you want to sort, then they'd be better. If you want to search....well, then they may be better.
Another option is the Dictionary. You could certainly switch from your matrices to dictionaries quite easily, but perhaps not all that efficiently. For example, a Dictionary(of Integer, String(15)) would be the same as your matrix. If you were to look up a single item from the dictionary, it would be considerably faster than searching through the matrix that you have, but that will be true ONLY if you know the array you want, and I think you don't. Searching through all the keys in a dictionary is no faster than searching through all the indexes in an array, so a dictionary only makes sense if you can look up items by key without looking through all the keys to find the right one.
Still, that might make sense as long as you don't do it in the trivial way I just described.
What you have is a datatable. You are filling a reader, that could fill a datatable with a single line, at which point you get all the searching advantages of the datatable. I wouldn't worry about the memory leaks. I've never run into any of that. I also wouldn't worry about wasting memory. Memory is abundant, these days, and even back when it wasn't CPU cycles were always more valuable than memory, so people would freely waste memory if it meant faster processes. Only in some embedded systems is memory more valuable than speed.
Still, the search capabilities of datatables may not gain you much. Some of them aren't all that fast, they're just really simple. For example, you can call the .Select() extension method to find a row. That's a single line, but it's 10% to 20% slower than going through the datatable row by row yourself. Using a loop would be six lines, while Select is one, but the loop is slightly faster. Normally, that doesn't matter, so people use Select. When it matters, it matters.
However, your data really fits as a class with 9 members (I'm not sure why your inner array is sized to 15) for the 9 elements you are putting into the inner element of the matrix. If you create a class rather than using a 2D array, you can have a List(of YourClass). That will be easier to work with, just as easy (or easier, but not faster) to search, and will have the same performance.
Better yet, if you do that, you can then improve the performance by wasting memory. Seeking in a Dictionary is FAR faster than iterating through a collection, especially a collection with 5000 items in it. So, for example, once you have built up your List(of YourClass) you could build a DestinationDictionary:
Private destinationDictionary As New Dictionary(of String,List(of YourClass))
Then, for each departureLocationName that you have, add a list that contains just those YourClass that have that departureLocationName. Now you can use the departureLocationName to look up that subset of the YourClass set that has that location name, and that lookup will be considerably faster than going through the list itself.
In theory, you could build a different dictionary for each element in your class, which would mean 9 dictionaries. Yeah, this uses up a bit of memory, but not all that much. The classes are just references. You aren't copying them into the dictionary, so the dictionary entry holds the key (a string), and a reference to a List, which itself just holds some number of references. That's some memory, but not all that much, really, and memory is abundant.
In practice, you probably wouldn't need to build 9 dictionaries, either. You'd probably find that you always start a search on just one or two elements, so you'd build dictionaries for just those elements, and not the rest.
@shaggy using datatables i guess would be the best way in my opinion due to several querys i shoud make.an ado connection can be the right solution ? i found a lot of examples but all of them are with external db (access or sql server).
-
Re: vb.net improve programs
Quote:
Originally Posted by
eurostar_italia
before changes : 10 seconds to load matrix,10 seconds to load listboxes after combobox1 changes its values.
afeter changes :6 seconds to load matrix,8 to loaad listboxes
Hello eurostar_italia,
Are you saying the suggested changes made your code faster?
yours,
Peter Swinkels
-
Re: vb.net improve programs
hello Peter,
a little bit faster.
But due i need several querys,I thing i would change the code to a datatable.
but i don't know how does it work the connection...i must read about ado.net
-
Re: vb.net improve programs
There is no connection...at least not in your case. A datatable is an in-memory database table, effectively. Normally, one would create a connection to a database, then use something like a datareader or dataadapter to fill the table from a database. You don't have a database, so that connection followed by fill doesn't need to happen.
In your case, you can create a datatable directly. This link gives an overblown example:
https://docs.microsoft.com/en-us/dot...ew=netcore-3.1
They have this example:
Code:
Private Sub AddColumn()
' Get the DataColumnCollection from a table in a DataSet.
Dim columns As DataColumnCollection = _
DataSet1.Tables("Prices").Columns
Dim column As DataColumn = columns.Add()
With column
.DataType = System.Type.GetType("System.Decimal")
.ColumnName = "Total"
.Expression = "UnitPrice * Quantity"
.ReadOnly = True
.Unique = False
End With
End Sub
That shows some of the options you can use in a datatable, but most of them don't apply to you. What you'd do would be more likely to be like this:
Code:
Private myDT As New Datatable
'Then, in something like form load:
myDT.Columns.Add("arrivalLocationName",GetType("System.String")
myDT.Columns.Add("pnrCode",GetType("System.String")
etc.
There's an even more compact way to write that, but I forget it at the moment, and don't want to steer you wrong.
Later on, when you fill the datatable you'd do something like this:
Code:
Dim nRow = yourDT.NewRow
yourDT.Rows.Add(nRow)
nRow("arrivalLocationName") = msg("arrivalLocationName")
nRow("pnrCode") = msg("pnrCode")
etc...
You can add the row to the Rows collection either before or after you fill in the fields, but it's also very easy to forget it entirely, so I like to add it right away. Forgetting it entirely wouldn't be good.
However, while a datatable can be searched, whether or not it will truly give you all you want in terms of performance remains to be seen. The truly simple means to search, such as the .Select extension method, is compact, but not super efficient. A DataView with a RowFilter may or may not be more efficient. Building dictionaries is more efficient for searching, but takes more work.
-
Re: vb.net improve programs
@shalky but what about sql querys. how can be done without an ado connection ??
-
Re: vb.net improve programs
What I showed has no ADO connection. It has no ADO anything.
You'd create the datatable when the form starts up. They usually get filled from a query, in which case the fields in the datatable come from the SQL, but they don't HAVE to be done that way, and what I showed is making a datatable by hand by adding the fields yourself.
The bit about building the rows would go inside the loop where you are currently filling in your array.
There's no connection involved.
-
Re: vb.net improve programs
Quote:
Originally Posted by
Shaggy Hiker
What I showed has no ADO connection. It has no ADO anything.
You'd create the datatable when the form starts up. They usually get filled from a query, in which case the fields in the datatable come from the SQL, but they don't HAVE to be done that way, and what I showed is making a datatable by hand by adding the fields yourself.
The bit about building the rows would go inside the loop where you are currently filling in your array.
There's no connection involved.
I understand your code to fill up the datatable,but later to make queries to fill up comoboxes o list box: how can i proceed?
-
Re: vb.net improve programs
To fill a combobox is particularly easy, in most cases. Perhaps not, in this case. Normally, you could just set the datasource of the combobox, setting the displayvalue to the column you want. That's probably not going to work, in this case, because you probably have LOADS of duplicates, so you don't want to set the datasource to be any one column in the datatable.
Instead, you probably want to set the datasource to be some set of distinct values from the datatable. There are probably at least two ways to do that. I believe that one would be:
yourDatatable.DefaultView.ToTable(True, "TheColumnYouWantInTheComboBox")
I think that will create a datatable with a single column which you can then set as the datasource for the combobox. If that's the case, then the whole code would look like this:
Code:
yourComboBox.DisplayMember = "TheColumnYouWantInTheComboBox"
yourComboBox.DataSource = yourDatatable.DefaultView.ToTable(True, "TheColumnYouWantInTheComboBox")
When you make a selection in the combobox (SelectedIndexChanged event), you can get the SelectedValue.ToString and use that in a RowFilter for a dataview, which is kind of like a WHERE clause from a SQL statement. So, in the SelectedIndexChanged you might have:
Code:
If yourComboBox.SelectedIndex>-1 then
yourDatatable.DefaultView.RowFilter = "TheColumnYouWantInTheComboBox = '" & yourComboBox.SelectedValue.ToString & "'"
End If
That's both freehand and I haven't used dataviews in a while, so it may be a bit off.
In fact, that might be a bit sketchy, so the alternative would be the Datatable.Select, which can be found here:
https://docs.microsoft.com/en-us/dot...System_String_
It's essentially the same kind of thing. The filter string is the same. What it will do is return an array of datarows which will match your search criteria. This will cut down the set of records you need to look through. There are a variety of ways to improve on that general design, based on what you ultimately need. For example, you might be able to sort the set in some fashion such that the ONE you want is the first one (to find the minimum value, for example). You can also use something like this to build up a filter statement using selections in multiple comboboxes. That's all dependent on what you need, though, so there's no generic solution.
-
Re: vb.net improve programs
well as u rightly understood ,in my case it's a little bit more difficult to load comboxes.
For instance : i need all the stations in "partenza" column as well ones in the Arrivo.
Most of them are duplicates .so probably an sql query wit "inner join " would be greate.
more over to load values in listbox to have "departing from ROMA : 10 people to milan,15 to florence,18 to venice" would be better with sql statement.
i'll try the code u suggest me at once ,as well
thanks for you help!!
-
Re: vb.net improve programs
Quote:
Originally Posted by
eurostar_italia
I,i have a little program that retrieves railway reservations for groups.
it downloads from a website getting a json.I store data in a matrix.
what does you code look like to do that, did you try to import that data right into a Database?
then you could perform the SQL you need to filter the data
-
Re: vb.net improve programs
Quote:
Originally Posted by
eurostar_italia
well as u rightly understood ,in my case it's a little bit more difficult to load comboxes.
For instance : i need all the stations in "partenza" column as well ones in the Arrivo.
Most of them are duplicates .so probably an sql query wit "inner join " would be greate.
more over to load values in listbox to have "departing from ROMA : 10 people to milan,15 to florence,18 to venice" would be better with sql statement.
i'll try the code u suggest me at once ,as well
thanks for you help!!
It's not going to be a SQL query because you're not querying a database. Let's say that you have a DataTable with the names of stations in Partenza and Arrivo columns and you want to list distinct values in alphabetical order. One option is to use a LINQ query:
vb.net Code:
Dim partenzaStations = table.AsEnumerable().Select(Function(row) row.Field(Of String)("Partenza"))
Dim arrivoStations = table.AsEnumerable().Select(Function(row) row.Field(Of String)("Arrivo"))
Dim stations = partenzaStations.Concat(arrivoStations).Distinct().OrderBy(Function(s) s).ToArray()
stationComboBox.DataSource = stations
-
Re: vb.net improve programs
Quote:
Originally Posted by
jmcilhinney
It's not going to be a SQL query because you're not querying a database. Let's say that you have a DataTable with the names of stations in Partenza and Arrivo columns and you want to list distinct values in alphabetical order. One option is to use a LINQ query:
vb.net Code:
Dim partenzaStations = table.AsEnumerable().Select(Function(row) row.Field(Of String)("Partenza"))
Dim arrivoStations = table.AsEnumerable().Select(Function(row) row.Field(Of String)("Arrivo"))
Dim stations = partenzaStations.Concat(arrivoStations).Distinct().OrderBy(Function(s) s).ToArray()
stationComboBox.DataSource = stations
I thought a datatable could be used as a db to make queries.i guess the only solution would be linq to retrieve data without making long loop
-
Re: vb.net improve programs
A database is a specific thing. It's not just anything that stores data. When we say "database" we mean an actual database, not an object that stores data. Even things like Excel or text files might be considered data sources but not actual databases. A DataTable is not a database. It is generally used as a local cache for data from a database but, as has already been indicated, it doesn't have to be used for that. Generally speaking, a DataSet is an in-memory representation of your database or part of your database and a DataTable is an in-memory representation of a table or part of a table from that database. A DataTable only exists while the application is running. If you want the data it contains persisted between sessions then you need to save the data somewhere. That is generally going to be a database but it doesn't have to be. As for queries, LINQ was pretty much based on SQL, being a technology that provides abilities similar to those of SQL but for querying .NET lists instead of database tables.
-
Re: vb.net improve programs
Another point about LINQ is that it is very compact, it is not faster. You save a lot of time writing LINQ, but in every test I have tried, it has run slightly slower than a loop. Others have reported instances where it was faster, and I think it could be faster if there are JOIN statements involved, as loops of that sort get strange. Still, it's not magic. Under the hood, it has to do actual work, and that work can be more than you would do if you wrote the loop yourself.
If you're looking for speed, the dictionary suggestions I made earlier will get you better speed. A datatable will get you better organization, as would a list of classes, but you need to organize the data in different ways if you want to make the speed of a search faster.
-
Re: vb.net improve programs
I tried Shaggy's suggestion, you will have to try how fast it is with more Data
also you would have to adapt to the query(s) you want
here Query (1)
a count of the Wagons which Seat on which Station is occupied
I hardcoded the Seats to 30 for each Wagon
the debug output looks like this..
Code:
Wagon Number.(1):
8xRome 26,67%
1xFlorence 3,33%
1xMilan 3,33%
Seat-1)-Seat-28)-Seat-8)-Seat-2)-Seat-3)-Seat-4)-Seat-11)-Seat-12)-Seat-16)-Seat-22)-occupied
-----------------------------------------------------
Wagon Number.(2):
2xFlorence 6,67%
2xRome 6,67%
Seat-5)-Seat-2)-Seat-15)-Seat-17)-occupied
-----------------------------------------------------
Wagon Number.(5):
1xFlorence 3,33%
Seat-3)-occupied
-----------------------------------------------------
and the code for the Form
add a Datagridview and a Button
Code:
Public Class Train
Dim tb As DataTable = New DataTable
Private Sub Train_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
tb.Columns.Add("Wagon Number")
tb.Columns.Add("Seat Number")
tb.Columns.Add("Total Seats in Wagon")
tb.Columns.Add("Station")
tb.Rows.Add("1", "1", "30", "Rome")
tb.Rows.Add("2", "5", "30", "Florence")
tb.Rows.Add("1", "28", "30", "Florence")
tb.Rows.Add("2", "2", "30", "Rome")
tb.Rows.Add("5", "3", "30", "Florence")
tb.Rows.Add("2", "15", "30", "Florence")
tb.Rows.Add("1", "8", "30", "Milan")
tb.Rows.Add("2", "17", "30", "Rome")
tb.Rows.Add("1", "2", "30", "Rome")
tb.Rows.Add("1", "3", "30", "Rome")
tb.Rows.Add("1", "4", "30", "Rome")
tb.Rows.Add("1", "11", "30", "Rome")
tb.Rows.Add("1", "12", "30", "Rome")
tb.Rows.Add("1", "16", "30", "Rome")
tb.Rows.Add("1", "22", "30", "Rome")
'add the Data
DataGridView1.DataSource = tb
DataGridView1.AllowUserToAddRows = False
End Sub
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
Dim WagonIDs As New List(Of Integer)
For Each row As DataGridViewRow In DataGridView1.Rows
If (Not WagonIDs.Contains(row.Cells("Wagon Number").Value)) Then
WagonIDs.Add(row.Cells("Wagon Number").Value)
End If
Next
For Each Wagon_ID As Integer In WagonIDs
Dim Seats As New Dictionary(Of String, Integer)()
Dim rows As New Dictionary(Of Integer, List(Of Integer))
For Each row As DataGridViewRow In DataGridView1.Rows
If row.Cells("Wagon Number").Value = Wagon_ID Then
If Not Seats.ContainsKey(row.Cells("Station").Value) Then
Seats.Add(row.Cells("Station").Value, 0)
End If
Seats(row.Cells("Station").Value) += 1
If Not rows.ContainsKey(row.Cells("Seat Number").Value) Then
rows(row.Cells("Seat Number").Value) = New List(Of Integer)()
End If
rows(row.Cells("Seat Number").Value).Add(row.Cells("Total Seats in Wagon").Value)
End If
Next
Debug.Write("Wagon Number.(" & Wagon_ID & ")")
Debug.WriteLine(": ")
For Each soldSeatTicket As KeyValuePair(Of String, Integer) In Seats
Debug.Write(String.Format("{0}x{1} ", soldSeatTicket.Value, soldSeatTicket.Key))
Dim TotalSeatsOnWagon As Integer = 30
Dim DisplayPercentage = soldSeatTicket.Value / TotalSeatsOnWagon
Debug.WriteLine(DisplayPercentage.ToString("p"))
Next
For Each row As KeyValuePair(Of Integer, List(Of Integer)) In rows
Debug.Write("Seat-" & row.Key & ")")
Debug.Write("-")
Next
Debug.Write("occupied")
Debug.WriteLine(" ")
Debug.WriteLine("-----------------------------------------------------")
Next
End Sub
End Class
-
Re: vb.net improve programs
Quote:
Originally Posted by
Shaggy Hiker
Another point about LINQ is that it is very compact, it is not faster. You save a lot of time writing LINQ, but in every test I have tried, it has run slightly slower than a loop. Others have reported instances where it was faster,
In some cases PLINQ can speed it up, yet to see any of the long time members ever use it, which makes me wonder "why". Waste of time when it was really tested?
-
Re: vb.net improve programs
Techgnome has reported cases where LINQ was faster, and I think it quite likely could be for JOINS, but I have never had a good test case to time it.
-
Re: vb.net improve programs
@Chris your code look to be great!!!
thank to all of you
-
Re: vb.net improve programs
Quote:
Originally Posted by
eurostar_italia
@Chris your code look to be great!!!
thank to all of you
well it was Shaggy's idea, and I don't know if it's great until you test it with 5000 rows as you said
-
Re: vb.net improve programs
well i don't use datatable to show datas ,i use list boxes...due one it's for destination,one is for car load and so on..
i can arrange the same code in function to be called from the different buttons you think ?
-
Re: vb.net improve programs
Quote:
Originally Posted by
eurostar_italia
well i don't use datatable to show datas ,i use list boxes...due one it's for destination,one is for car load and so on..
i can arrange the same code in function to be called from the different buttons you think ?
Yes, a ListBox is for displaying data and allowing the user to interact with it. A DataTable is for storing data while your application is running. A database is for storing data while your app is not running and to allow multiple users to access the same data. It is quite common for data to be stored in a Datatable and that table to be bound to a ListBox and/or other controls. The two are not mutually exclusive and often work together.
-
Re: vb.net improve programs
Quote:
Originally Posted by
jmcilhinney
Yes, a ListBox is for displaying data and allowing the user to interact with it. A DataTable is for storing data while your application is running. A database is for storing data while your app is not running and to allow multiple users to access the same data. It is quite common for data to be stored in a Datatable and that table to be bound to a ListBox and/or other controls. The two are not mutually exclusive and often work together.
I was wrong,what i want to say was "i dont use datagridview so I can't use that code.but it's a good beginning point.
Code:
With datatable1
.Columns.Add("Partenza", System.Type.GetType("System.String"))
.Columns.Add("Arrivo", System.Type.GetType("System.String"))
.Columns.Add("PNR", System.Type.GetType("System.String"))
.Columns.Add("Classe", System.Type.GetType("System.String"))
.Columns.Add("Cognome", System.Type.GetType("System.String"))
.Columns.Add("Carrozza", System.Type.GetType("System.String"))
.Columns.Add("Posto", System.Type.GetType("System.String"))
.Columns.Add("Nome", System.Type.GetType("System.String"))
.Columns.Add("Treno", System.Type.GetType("System.String"))
.Columns.Add("ID prenotazione", System.Type.GetType("System.String"))
End With
For Each item As JProperty In data
item.CreateReader()
For Each msg As JObject In item.Values
datatable1.Rows.Add(msg("departureLocationName"), msg("arrivalLocationName"), msg("pnrCode"), msg("serviceLevel"), msg("lastName"), msg("wagon"), msg("seat"), msg("firstName"), msg("transportMeanName"), msg("Id"))
Next
Next
-
Re: vb.net improve programs
Code:
Dim WagonIDs As New List(Of String)
For Each row In treno1.Rows
If (Not WagonIDs.Contains(("Carrozza"))) Then
WagonIDs.Add(("Carrozza"))
End If
Next
this code add the word Carrozza,but if i want to find out the real value ????
-
Re: vb.net improve programs
Quote:
Originally Posted by
jmcilhinney
It's not going to be a SQL query because you're not querying a database. Let's say that you have a DataTable with the names of stations in Partenza and Arrivo columns and you want to list distinct values in alphabetical order. One option is to use a LINQ query:
vb.net Code:
Dim partenzaStations = table.AsEnumerable().Select(Function(row) row.Field(Of String)("Partenza"))
Dim arrivoStations = table.AsEnumerable().Select(Function(row) row.Field(Of String)("Arrivo"))
Dim stations = partenzaStations.Concat(arrivoStations).Distinct().OrderBy(Function(s) s).ToArray()
stationComboBox.DataSource = stations
it's strange .. stations it's correctly filled with all stations,but stationcombobox doesn't load anything
stations = {Length=26}
-
Re: vb.net improve programs
Where's that code located?
-
Re: vb.net improve programs
it's in the backgroundworker
station has 26 records as they should be....but the combobox is empty
i must say,25 are stations but record 0 is ""
Code:
Private Sub BackgroundWorker1_RunWorkerCompleted(sender As Object, e As RunWorkerCompletedEventArgs) Handles BackgroundWorker1.RunWorkerCompleted
FormAttesa.Close()
If e.Cancelled = True Then
Exit Sub
Else
Dim ser As JObject = JObject.Parse(json)
Dim data As List(Of JToken) = ser.Children().ToList
If chiamata = 1 Then
With treno1
.Columns.Add("Partenza", System.Type.GetType("System.String"))
.Columns.Add("Arrivo", System.Type.GetType("System.String"))
.Columns.Add("PNR", System.Type.GetType("System.String"))
.Columns.Add("Classe", System.Type.GetType("System.String"))
.Columns.Add("Cognome", System.Type.GetType("System.String"))
.Columns.Add("Carrozza", System.Type.GetType("System.String"))
.Columns.Add("Posto", System.Type.GetType("System.String"))
.Columns.Add("Nome", System.Type.GetType("System.String"))
.Columns.Add("Treno", System.Type.GetType("System.String"))
.Columns.Add("ID prenotazione", System.Type.GetType("System.String"))
End With
For Each item As JProperty In data
item.CreateReader()
For Each msg As JObject In item.Values
treno1.Rows.Add(msg("departureLocationName"), msg("arrivalLocationName"), msg("pnrCode"), msg("serviceLevel"), msg("lastName"), msg("wagon"), msg("seat"), msg("firstName"), msg("transportMeanName"), msg("Id"))
Next
Next
Dim partenzaStations = treno1.AsEnumerable().Select(Function(row) row.Field(Of String)("Partenza"))
Dim arrivoStations = treno1.AsEnumerable().Select(Function(row) row.Field(Of String)("Arrivo"))
Dim stations = partenzaStations.Concat(arrivoStations).Distinct().OrderBy(Function(s) s).ToArray()
ComboBox1.DataSource = stations
-
Re: vb.net improve programs
your making things far to complicated, why don't you supply some Data (see Post#22 you have chosen to ignore)
also the columns you add to your DataTable are all Strings ?
see this
Code:
Protected Function FormatDataTable() As DataTable
Dim dt As DataTable = New DataTable()
' Create Columns
dt.Columns.Add("Date", GetType(Date))
dt.Columns.Add("Week", GetType(Integer))
dt.Columns.Add("Day", GetType(String))
dt.Columns.Add("Day No.", GetType(Integer))
Return dt
End Function
-
Re: vb.net improve programs
Quote:
Originally Posted by
ChrisE
your making things far to complicated, why don't you supply some Data (see Post#22 you have chosen to ignore)
also the columns you add to your DataTable are all Strings ?
see this
Code:
Protected Function FormatDataTable() As DataTable
Dim dt As DataTable = New DataTable()
' Create Columns
dt.Columns.Add("Date", GetType(Date))
dt.Columns.Add("Week", GetType(Integer))
dt.Columns.Add("Day", GetType(String))
dt.Columns.Add("Day No.", GetType(Integer))
Return dt
End Function
it's ok but it's still a datatable,i can only use linq to make queries.I don't want to use an external db cause i cannot install anything on the machine.It's a company machine...so i can only place .exe on desktop....
this code seems to work,but it doesn't load anything even if stations array is correctly populated
Code:
Dim partenzaStations = treno1.AsEnumerable().Select(Function(row) row.Field(Of String)("Partenza"))
Dim arrivoStations = treno1.AsEnumerable().Select(Function(row) row.Field(Of String)("Arrivo"))
Dim stations = partenzaStations.Concat(arrivoStations).Distinct().OrderBy(Function(s) s).ToArray()
ComboBox1.DataSource = stations
-
Re: vb.net improve programs
ok i guess this is too much difficult for me..
stations it's correctly filled but combobox1 is empty.
i keep on matrix.
it takes longer,very hard to inquiry but it works
many thanks to all of you
-
Re: vb.net improve programs
you can create a Access .mdb and Access doesn't have to be Installed
on your Company PC
Code:
Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
'Add the COM Reference, "Microsoft ADO Ext. 6.0 for DLL and Security"
' create the empty DB file
Dim cat As New ADOX.Catalog()
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Berichte\Backup.mdb")
cat = Nothing
End Sub
as easy as that
-
Re: vb.net improve programs
Quote:
Originally Posted by
jmcilhinney
It's not going to be a SQL query because you're not querying a database. Let's say that you have a DataTable with the names of stations in Partenza and Arrivo columns and you want to list distinct values in alphabetical order. One option is to use a LINQ query:
vb.net Code:
Dim partenzaStations = table.AsEnumerable().Select(Function(row) row.Field(Of String)("Partenza"))
Dim arrivoStations = table.AsEnumerable().Select(Function(row) row.Field(Of String)("Arrivo"))
Dim stations = partenzaStations.Concat(arrivoStations).Distinct().OrderBy(Function(s) s).ToArray()
stationComboBox.DataSource = stations
it works only if i don't order the list,otherwise the combobox it wont' be loaded !!
i guess it's due the first value is nothing.
but the where condition can not be applyied in the query.so strange
-
Re: vb.net improve programs
at the end i solved in this way :
Code:
Dim partenzaStations = From n In treno1.AsEnumerable()
Where n.Field(Of String)("Partenza") IsNot Nothing
Select n.Field(Of String)("Partenza")
Dim arrivoStations = From n In treno1.AsEnumerable()
Where n.Field(Of String)("Arrivo") IsNot Nothing
Select n.Field(Of String)("Arrivo")
Dim stations = partenzaStations.Concat(arrivoStations).Distinct().OrderBy(Function(s) s).ToArray()
ComboBox1.DataSource = stations.ToArray
ComboBox1.SelectedIndex = -1
maybe it will help somebody else
