|
Hi,
I looked somewhat closer, and I now think the bottleneck is you store your data as text inside TextBoxes, so all filtering requires a continuous stream of string-to-long conversions.
I performed an experiment (in C#) with a proper DataTable+DataGridView+BindingSource setup, and it works fluently. Here is the gist of it:
namespace WindowsFormsApplication1 {
public partial class Form1 : Form {
BindingSource binding;
public Form1() {
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e) {
DataTable table=new DataTable();
table.Columns.Add("Date", typeof(DateTime));
table.Columns.Add("Long", typeof(long));
Random r=new Random();
for(int i=0; i<5000; i++) {
table.Rows.Add(new DateTime(2011, r.Next(1,12), r.Next(1,28)), r.Next(0,40));
}
binding=new BindingSource();
binding.DataSource=table;
dgv.DataSource=binding;
}
private void trb_ValueChanged(object sender, EventArgs e) {
long min=trb.Value;
binding.Filter="Long >= "+min;
label1.Text = "Only rows with Long >= " + min + " are visible";
}
}
}
Also, by using the BindingSource, it is Microsoft's responsibility to make it snappy, and I guess they did it right.
Luc Pattyn [My Articles] Nil Volentibus Arduum
Fed up by FireFox memory leaks I switched to Opera and now CP doesn't perform its paste magic, so links will not be offered. Sorry.
|
|
|
|
|
Hi Luc!
Yes, this is much better! Although for extremely large number of rows it is still freezing like before but it works ok for 50 000 row, even 100 000 rows can be manageable which is much better than situation I got till now (much much better!).
One question. What did you mean by that I store my data as text inside TextBoxes? I haven't do that. But if something like that (string-long, long-string conversions) can cause slower performance maybe that's the reason why it is still slower for over 50 000 rows because I had to modify one of your lines from long min=trb.Value; i.e. Dim min As Long = trackbar1.Value to Dim min As String = trackbar1.Value.ToString because I got errors in two lines which comes after that. If your c# code worked for you than it seems that c# can combine string+long and VB needs to same data type while combining.
I must now try to implement your suggestion to my real project. In my real project I populate my datagridview from large number of structured txt files. Once my datagridview is populatetd it doesn't use those txt files any more so I thought that after this point the performance will be the same as when using databinding. I still don't understand why is it faster while using DataTable+DataGridView+BindingSource but I see it is and that's enough for now, I'll deal with theories later
I just hope I'll succeed in adding data from txt files to DataTable and than populate my datagridview from DataTable using BindingSource like you did - for now I don't see why it wouldn't work...
Thanks again!
|
|
|
|
|
Hi again,
1.
don't do Dim min As String, the minimum value IS a number, so keep it a number. Always try and use the most appropriate data type. Strings exist for humans (to visualize data that probably is not textual at all), not for computers.
2.
C# automatically applies .ToString() when you write a concatenation, as in "text"+number, it treats that like "text"+number.ToString()
As VB.NET doesn't do that, you have to add an explicit .ToString()
3.
My approach works on the DataTable where the "Long" column contains numbers, therefore the filtering is done based on numbers, not strings (even when you pass the minimum value as a string to the BindingSource); yours had just a DGV with two TextBoxColumns, and whatever sits in a TextBox is a string, so it was converting every TextBox's content to a number in order to do the comparison.
4.
It makes no sense to me to have 50,000 rows in a DGV, noone is interested in browsing such a list. Apply a filter, apply pagination, or any other scheme that limits the amount of data presented to the user, so things stay small and agile. Example: this web page only shows 10 or 100 messages, not the full million of messages CP is currently holding in its database.
Luc Pattyn [My Articles] Nil Volentibus Arduum
Fed up by FireFox memory leaks I switched to Opera and now CP doesn't perform its paste magic, so links will not be offered. Sorry.
modified 4-Feb-12 13:06pm.
|
|
|
|
|
1. OK
2. OK
3. Now I understood what you have meant. With this lines I set my columns as Textboxes:
Dim column1 = New DataGridViewTextBoxColumn()
Dim column2 = New DataGridViewTextBoxColumn()
I actually forgot about those lines and thought that next two lines are determining the datatype of my columns
column1.ValueType = GetType(System.DateTime)
column2.ValueType = GetType(System.Int64)
for example when I sort column2 it is sorted like number, not like text, i.e. it is not sorted 1,10,11,2... but it is sorted like number 1,2,10,11... so that's probably why I forgot about those are actually textboxcolumns
4. I agree no one likes to scroll through 50 000 rows. In my application, no one has to because user first use several filters (trackbar is only one of them), and then in the end scroll among 20-30 rows. But all those data (rows) must be in datagridview because there are some calculations made that involves whole dataset. I probably could do it in a better way, but now is to late. I'm working on this for a long time, and changing the methodology now would be a big step back... I haven't anticipate some thing in the beginning so now I must struggle with it  But as I said, it works ok till 50 000 rows, and I expect that number of rows will actually never be reached.
|
|
|
|
|
3. you may be right, I would have to investigate further.
4. what you really should do then is keep the data in a DataTable, not in a DataGridView. A DataTable holds data, a DataGridView holds AND displays data, which is much more expensive. I suggest you still reconsider, switching from DGV to DT probably isn't that hard.
Luc Pattyn [My Articles] Nil Volentibus Arduum
Fed up by FireFox memory leaks I switched to Opera and now CP doesn't perform its paste magic, so links will not be offered. Sorry.
|
|
|
|
|
I have already started DGV to DT boring job...
Now I'm sure that if i succeed in this, my application will work faster.
TNX!
|
|
|
|
|
You're welcome.
Luc Pattyn [My Articles] Nil Volentibus Arduum
Fed up by FireFox memory leaks I switched to Opera and now CP doesn't perform its paste magic, so links will not be offered. Sorry.
|
|
|
|
|
I'm trying to remove the ® and ™ symbols in a string using Regex and it doesnt work for the ™ symbol.
Here is some example code:
Dim s1, s2, s3 As String
Dim i As Integer
s1 = "Trademark" + Chr(153) + " Registered" + Chr(174)
s2 = System.Text.RegularExpressions.Regex.Replace(s1, "[\x99]", "(TM)")
s3 = System.Text.RegularExpressions.Regex.Replace(s1, "[\xAE]", "(R)")
Debug.Print("Orig: " + s1)
For i = 0 To s1.Length - 1
Debug.Print(" " + s1.Substring(i, 1) + " " + Asc(s1.Substring(i, 1)).ToString("X") + " " + Asc(s1.Substring(i, 1)).ToString)
Next
Debug.Print("S2 : " + s2)
Debug.Print("S3 : " + s3)
This code seems to work for the (R) symbol, but not the (TM).
However, if I use Chr(153) instead of \x99 in the regex call, the code works fine.
What am I doing wrong?
Thanks.
|
|
|
|
|
chr() and asc() are ancient ANSI-oriented functions.
A little experiment taught me chr(153) generates unicode character x2122.
Using AscW() rather than Asc() would reveal the truth about your strings.
Luc Pattyn [My Articles] Nil Volentibus Arduum
Fed up by FireFox memory leaks I switched to Opera and now CP doesn't perform its paste magic, so links will not be offered. Sorry.
|
|
|
|
|
Hi,
I have data in Access Database which I am filling in the DataGridView to QA/QC raw data downloaded from a climate station. I am having trouble updating the database using update command. I am using following steps:
1. Locating access database:
Private Sub AccessDataFileButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AccessDataFileButton.Click
OpenFileDialog2.Filter = "Microsoft Access File(*.mdb)|*.mdb|Microsoft Access File(*.accdb)|*.accdb|All files (*.*)|*.*"
OpenFileDialog2.Title = "Input Data File with Real-time Water Quality Time Series"
If OpenFileDialog2.ShowDialog = DialogResult.OK Then
accessDataFile = OpenFileDialog2.FileName
'MsgBox("accessFile= " & OpenFileDialog2.FileName)
Me.AccessFileTextBox.Text = accessDataFile
End If
End Sub
2. Filling DataGridView:
Sub fillDataGrid1()
' Read Access Table to find date and time of the last data
Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & accessDataFile
Dim myConnection As OleDbConnection = New OleDbConnection
myConnection.ConnectionString = connString
myConnection.Open()
Dim inpTable As String = "rawClimateData" 'Access database table
Dim cmd As String = "Select * from " & inpTable '& " ORDER BY mdateTime"
Dim dbCmd As OleDbCommand = New OleDbCommand(cmd, myConnection)
' create a data adapter
Try
da = New OleDbDataAdapter(cmd, myConnection)
Catch ex As Exception ' Catch the error.
MsgBox(ex.ToString) ' Show friendly error message.
End Try
' create a new dataset
Dim table As New DataTable
myDataset = New DataSet()
Try
Me.da.Fill(table)
da.Fill(myDataset, inpTable)
Me.DataGridView1.DataSource = table
Catch ex As Exception
MsgBox(ex.ToString)
End Try
myConnection.Close()
End Sub
3. Updating database (update button)
Private Sub TryTry_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles upDate.Click
Try
Me.da.Update(Me.myDataset.Tables("rawClimateData"))
Me.myDataset.AcceptChanges()
Catch ex As Exception
MsgBox(ex.Message)
End Try
MsgBox("upDateComplete")
End Sub
In some examples I have seen connecting database file while Form is loading; however, I have an option of browsing the access database file.
I know it should be simple, however, I am not a pro. Therefore, any tips/help would be greatly appreciated.
Cheers,
Amanjot
|
|
|
|
|
"AcceptChanges[^]" only updates the object in memory, not the database itself.
I'd suggest you fire an UPDATE statement whenever you want to change something, or an INSERT if you want to add a tuple.
Bastard Programmer from Hell 
|
|
|
|
|
I have a table. It desn't have any Id which will be generated automatically.
my table (Table1)has 30 some rows and 3 columns(FirstName, LastName, Occupation).
I want to read each row one by one and wanted to create a unique ID which is of type uniqueIdentifier and fill another table with all these coulmns and newly created ID.
How can I do that
|
|
|
|
|
you haven't said what the database is
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
Either Paradox 5.0 or DBaseIV format - which would you recommend?
Bastard Programmer from Hell
|
|
|
|
|
Daffodil database[^] 
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
EDIT: Sorry I misread the question.
Could you iterate through the collection
For i As Integer = 0 To table.count - 1
Dim selected As Row = table.row(i)
newTable.add(New Row(i, selected.FirstName, selected.LastName, Occupation))
Next
Alternatively if you are adding to existing table you could use a GUID
For i As Integer = 0 To table.count - 1
Dim selected As Row = table.row(i)
newTable.add(New Row(Guid.NewGuid(), forstName, LastName, Occupation))
Next
- Use the database primary key and store in a column those width is 0.
- Some table rows have a tag property you can use to store the entity representing the record
- Some tables are bound directly to a data source, so by querying the selected row, you can access the unique Id.
For a more specific answer, you'll need to detail the scenario. At this stage we don't even know if your talking about a DataGrid, a DataTable or some other matrix type object.
"You get that on the big jobs."
|
|
|
|
|
I have a table. It desn't have any Id which will be generated automatically.
my table (Table1)has 30 some rows and 3 columns(FirstName, LastName, Occupation).
I want to read each row one by one and wanted to create a unique ID which is of type uniqueIdentifier and fill another table with all these coulmns and newly created ID.
How can I do that
|
|
|
|
|
Why row by row, and not all at once?
SELECT NEWID()
, [FirstName]
, [LastName]
, [Occupation]
INTO MyNewTable
FROM MyExistingTable
--edit;
You would have received more answers if you posted this in the Database[^]-forum.
Bastard Programmer from Hell
|
|
|
|
|
That is according to MSDN[^]
I don't understand is... why? I am sure the answer is probably very simple, but for some reason my mind can't come up with the answer. Maybe it's cuz I am so frustrated with this error. Well, who knows.
What do I want to achieve?
Suppose the following...
Public Sub ChangeThePictureBoxBC(Optional newColor as System.Drawing.Color = Drawing.Color.Black)
Me.SomePictureBox.BackColor = newColor
End Sub
This does not seem wrong to me. Can someone please explain A) why the above sub is wrong without re-quoting the unhelpful MSDN document and B) what suggestion/advice can you give me to make an optional parameter that involves color.
Thanks in advance.
|
|
|
|
|
Clark Kent123 wrote: A) why the above sub is wrong
Only nullable types can be used as optional parameters; a struct is a value-type and can't be empty.
Clark Kent123 wrote: B) what suggestion/advice can you give me to make an optional parameter that involves color.
Overloading[^], instead of optional parameters;
Public Overloads Sub ChangeThePictureBoxBC(newColor as System.Drawing.Color)
Me.SomePictureBox.BackColor = newColor
End Sub
Public Overloads Sub ChangeThePictureBoxBC()
Me.SomePictureBox.BackColor = Drawing.Color.Black
End Sub
Bastard Programmer from Hell
|
|
|
|
|
Overloading! Yeah, that's probably the direction that I will go in. Great suggestion! 
|
|
|
|
|
Thanks
|
|
|
|
|
I'm reading a range from VBE code in a spreadsheet.
data = mySheet.Range(a1:a8).value
but I need to get data as Double() and not as Variant
I need something like:
Dim data() as Double
data = mySheet.Range(a1:a8).value
this because I will use data as c# arg of Com addins (double[])
Thanks for your time
|
|
|
|
|
TheGermoz wrote: this because I will use data as c# arg of Com addins (double[])
I haven't scripted that much yet, but wouldn't it be easier to change the params of the Com addins to (object[])?
Put a breakpoint on it, and see if you can cast/convert it to double's.
Bastard Programmer from Hell 
|
|
|
|
|
thanks you for your replay. Com addin is shared with other application. so not so easy.
It is surprising that from VBE code in spreadsheet it is not possible to copy an Excel range into an array of double() with one dimension.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
