|
Hi,
I am using VB.Net for my program where I have designed a form.
On the left side, I have a list of 100,000+ Items which load in the CheckListBox control on the form load. On the right hand side, I have a ListView control which has multiple columns and shows values related to the Items selected in CheckListBox.
I have created a string variable called ITEMS like this:
Dim ITEMS as String = "'" & CheckListBox.Item(0) & "'"
For int i = 1 To CheckListBox.Count - 1
ITEMS = ITEMS & "'" & CheckListBox(i) & "'"
Next
This stores all the item names in my ITEMS string variable
Now, my Problem here is that when I use following query:
Select * from TableA where Item in(ITEMS)
This query runs for each of the selected items and takes a very long time (perhaps a couple of minutes) to display result in ListView control. I know, the reason here is that I have 100,000+ Items in my list which I am using in IN operator of SQL Query but I am not sure what alternate way I can use to reduce the output time for these many Items.
If there is any other work around to get quicker results, can anyone please help me?
arunpeswani@gmail.com
|
|
|
|
|
Arun Peswani wrote: If there is any other work around to get quicker results,
Fewer items in your CheckListBox, that amount is impossible to navigate. Check out the TreeView - you can add checkboxes there too, and it'd be easy to categorize.
There are two points you can optimize; creating the string that contains the selection, and executing the query. If you're going to build a large string, consider a StringBuilder.
Optimizing the query will take some experimenting; perhaps it is faster if you create select statements that are glued together with a UNION, or if you insert the keys you'd like to select into a temp-table and join against that.
..nearly forgot the most obvious; don't select every column, select what you actually use! You don't want to pull a large blob over the network if you're not using it 
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks for reply dear.
1) I am not exactly using SELECT * FROM TABLE, Actually I have 3 tables to fetch the data from, and I am using OUTER JOINT as well to get the data from selected columns. I doubt if UNION will help me increasing the speed where I want to pull records of my 100,000 Items in my store. Can you share an example query with UNION for my issue?
2) I have never used StringBuilder. Will surely look at this and will give a shot if it is of some help in order to increase the speed.
3) I think TreeView will also give me the same slow speed because in this control also I will have to find the CHECKED items first and then run the query based on those Items.
Please advise if there is something else which can help me in this challenging situation of retrieving the details of 100,000+ items in my application.
I am wondering how does't it work for those professional Inventory applications available in market, how do they manage to maintain the speed for such bulk items' queries?
Any help in order to increase speed of this particular query will be a great help.
arun_peswani@yahoo.co.in
modified 20-Feb-14 0:31am.
|
|
|
|
|
Arun Peswani wrote: I am not exactly using SELECT * FROM TABLE
Then you'd need to check if that query can be optimized.
Arun Peswani wrote: Can you share an example query with UNION for my issue?
No. There are enough examples on the internet.
Arun Peswani wrote: <layer>I think TreeView will also give me the same slow speed because in this control also I will have to find the CHECKED items first and then run the query based on those Items.
You don't need to "find" the selected items; the CheckedListBox has a CheckedItem[^] property, and the treeview has a CheckedNodes[^] property. Both controls can tell you which items have been selected - you'll only need to convert that selection into something that your query accepts. That's the query with the joins that we did not see.
Also, the TreeNode does not have to be loaded completely; one could load everything for the current category, and not load any other (non-expanded) categories.
Arun Peswani wrote: Any help in order to increase speed of this particular query will be a great help.
You didn't post the query; if you're going to, I'd suggest using the database-forum.
Arun Peswani wrote: I am wondering how does't it work for those professional Inventory applications available in market, how do they manage to maintain the speed for such bulk items' queries?
They don't use a WinControl to show thousands of items and they don't fetch all items on a select.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi Eddy,
Thanks for replying point by point for all my questions
Here is my main query for fetching the records from 3 tables after collecting all the checked Items from my CheckedListBox control in a string called ITEMS:
select Table1.Column2, Table2.Column2, Table3.Column2 from Table2
full outer join Table1 on Table1.Column1 = Table2.Column1
full outer join Table3 on Table3.Column1 = Table2.Column2
where Table1.Column1 in (ITEMS) and Table2.Dates between 'FromDate' and 'ToDate'
Order By Table1.Column2
This is exactly what I am using.
Does it help you to help me further?
I noticed one more thing today, creating a long string ITEMS, which I am using in above query, is also taking a very long time.
As you explained in the third portion or your reply about the checked items in CheckedListBox, "you'll only need to convert that selection into something that your query accepts." can you please help me with this part. How do I need to convert the selected items which can be used in above query? Sorry but I could not think solution for this part  pls help.
arun_peswani@yahoo.co.in
modified 20-Feb-14 13:30pm.
|
|
|
|
|
If you got a few days, we'll take it in a few steps.
Arun Peswani wrote: Does it help you to help me further?
Yes, familiar pattern, might actually have something similar - without dates. Can you also tell us the column-types used? I'd also like to know if there's any indexes defined on that. If yes, then we'll look at optimizing the query tomorrow.
Arun Peswani wrote: I noticed one more thing today, creating a long string ITEMS, which I am using in above query, is also taking a very long time.
Doing a lot costs a lot. Let's see;
Dim ITEMS as String = "'" & CheckListBox.Item(0) & "'"
For int i = 1 To CheckListBox.Count - 1
ITEMS = ITEMS & "'" & CheckListBox(i) & "'"
Next
Private CheckListBox As New CheckedListBox
Sub method1()
Dim sb = New System.Text.StringBuilder()
For Each checkedThingy In CheckListBox.CheckedItems
sb.AppendFormat("'{0}',", checkedThingy)
Next
Dim ITEMS As String = sb.ToString()
End Sub
Arun Peswani wrote: How do I need to convert the selected items which can be used in above query?
Ideally, we'd have a list of the keys (Id's) coming from that selection, not words. You're selecting on a varchar field?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi Eddy,
Thanks for nice replies
Though I am not able to post my replies in a format the way you do but I am trying to explain as much as I can.
1) All my columns are either 'int' or 'varchar', moreover unfortunately there are no indexes defined as well , perhaps I should try if it helps improving the speed.
2) String builder looks better for sure and I am going to try it today.
Table1 has details of Suppliers (Column1 = Supplier ID, int, not null and no index),
Table2 has details of Products (Column1 = Product ID, int, not null and no index),
Table3 is PurchaseEntry table which has details of all the products' purchases and joins other two tables on their ID columns as per my query above.
Time is not a constrain for me as this is my own project, so I have enough time
You may reply as per convenience with your valuable suggestions.
Thanks a lot for all your help so far
arun_peswani@yahoo.co.in
|
|
|
|
|
Arun Peswani wrote: 1) All my columns are either 'int' or 'varchar', moreover unfortunately there are no indexes defined as well , perhaps I should try if it helps improving the speed.
That will indeed improve performance. The keys need one, and the To- and From-date might help too.
Arun Peswani wrote: 2) String builder looks better for sure and I am going to try it today.
I expect it to help, but optimizing the database will have a larger impact.
Having integers as the primary key is sweet 
Theoretically, one could save the selection as a datatable; you now have an ITEMS string containing "11, 17, 321, 205", right? If you'd insert those into a temporary table (Table0), then you could use another join to limit your selection by changing the Sql statement;
select Table1.Column2, Table2.Column2, Table3.Column2 from Table2
full outer join Table1 on Table1.Column1 = Table2.Column1
full outer join Table3 on Table3.Column1 = Table2.Column2
JOIN Table0 on Table1.Column1 = Table0.Column0
where Table2.Dates between 'FromDate' and 'ToDate'
Order By Table1.Column2 The products table, does it hold a column called 'category' or something similar?
Are you familiar with the stopwatch class? If yes, try timing the code that does the loading, the part doing the actual query, and the part that shows it on screen. We can look into optimizing that last part once these two are done
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
|
Arun Peswani wrote: 1) I tried creating ITEMS string as you explained however it required a little extra work on formatting but all went well.
Arun Peswani wrote: 2) I liked the concept of "JOIN Table0 on Table1.Column1 = Table0.Column0" I never thought about this one 
Dumping integers might be faster than creating a lot of parameters, but then again, there won't be much to gain here - people rarely select 50 000 items by placing checkboxes. Perhaps it'd be wiser to not optimize this bit and go for readability.
Arun Peswani wrote: 3) My product table doesn't have category column however I have a separate table for product categories.
My PURCHASE table uses data from Product, Supplier and Category.
Wouldn't it be more efficient to have the products grouped by category when you ask for a selection? Expand a category before it shows the products it contains?
Arun Peswani wrote: 4) Moreover, the stopwatch class which you are asking about, is this similar to progress-bar? If yes then I have used it but if it is something different then please give me some hint. What is it and how to use it.
A stopwatch is a dandy way of timing your code;
Stopwatch sw = new Stopwatch();
sw.Start();
[..bunch of code..].sw.Stop();
Debugger.WriteLine("Your code took {0} ms", sw.ElapsedMilliseconds);It's on MSDN[^] - there's an example near the bottom of the page.
Arun Peswani wrote: You are truly superb
Nah, just trying to help in the same way others helped me. That's what most of us do here, and sometimes there even goes a little effort into an answer. That'd also be the reason for the reaction on a duplicate question; have I been 'wasting' time explaining something that Dave already explained five minutes ago? 
You owe the Q&A guys a ![Beer | [beer]](https://codeproject.freetls.fastly.net/script/Forums/Images/beer.gif)
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Select * ....... very bad
|
|
|
|
|
Adding 100,000 items to a list is just bad design, and there is nothing you can do to improve its speed. It is also extermely annoying for a user trying to navigate such a list. You need to find ways of categorising your data so you have smaller lists and create them dynamically in response to the user's actions.
|
|
|
|
|
Reported as "abusive" for your responses to me and others in the copy of this post over in Q&A.
|
|
|
|
|
I am trying to populate a ComboBox thru my business and data layer and it's not working for some reason. The data is there but the combobox isn't being populated. I'm using a Class that controls all passing of data from my UI to my Business Layer to my Data Layer. In my Class, I have a ComboBox control along with other controls and Data Types.
Below is the code that pertains to this functionality.
Private Sub frmEquipmentSetup_Load(sender As Object, e As EventArgs) Handles MyBase.Load
LoadIntervalComboBox()
.
.
.
End Sub
Private Sub LoadIntervalComboBox()
Try
InitializeErrorClass(EH)
BL.LoadIntervalComboBox(EH)
If EH.ErrorMessage = "" Then
cmbCDInterval = EH.Combo
End If
EH.ErrorMessage = ""
Catch ex As Exception
EH.ErrorMessage = "LoadIntervalComboBox() - " & ex.Message & "~E"
End Try
EH.ProcessMessages(Me, sbr, EH.ErrorMessage)
End Sub
Public Sub InitializeErrorClass(ByRef EH As ErrorHandling.ErrorHandler)
Try
EH.DataSet = New DataSet
EH.DataTable = New DataTable
EH.ErrorMessage = ""
EH.Character = ""
EH.Bool = False
EH.Number = 0
EH.Combo = New ComboBox
EH.Exception = Nothing
Catch ex As Exception
EH.ErrorMessage = "cmbInitializeErrorClass() - " & ex.Message & "~E"
End Try
End Sub
Public Function LoadIntervalComboBox(ByRef EH As ErrorHandling.ErrorHandler)
Try
Dim strFields As String = "interval_ID,interval"
Dim strTblID As String = "LKUP_CalInterval"
Dim strCriteria As String = "active <> 0"
Dim strOrderBy As String = ""
DL.GetData(strTblID, strFields, strCriteria, strOrderBy, EH)
If EH.DataSet.Tables(0).Rows.Count > 0 Then
EH.Combo.DataSource = Nothing
EH.Combo.DataSource = EH.DataSet.Tables(0)
EH.Combo.DisplayMember = "interval"
EH.Combo.ValueMember = "interval_ID"
EH.Combo.SelectedIndex = -1
End If
Catch ex As Exception
EH.ErrorMessage = "LoadIntervalComboBox() - " & ex.Message & "~E"
End Try
Return EH
End Function
Imports System.Windows.Forms
Public Class ErrorHandler
Public gTimeOut As Integer = 0
Public gSplashTimer As Integer = 0
Public gMessageTimer As Integer = 0
Public gblnHalt As Boolean = False
Public gblnEscape As Boolean = False
Public gblnPause As Boolean = False
Public gblnMessageTimer As Boolean = False
Public gblnFlashMessage As Boolean = False
Public gblnHideMenuIcons As Boolean = False
Public WithEvents tmrMessage As New Timer
Public gSBR As New StatusStrip
Public Property ErrorMessage As String
Public Property Number As Integer
Public Property Character As String
Public Property DataTable As DataTable
Public Property DataSet As DataSet
Public Property Exception As Exception
Public Property Bool As Boolean
Public Property Miscellaneous As String
Public Combo As ComboBox
.
.
.
Blake McKenna
modified 18-Feb-14 18:23pm.
|
|
|
|
|
Where is the form created? And where is the ErrorHandler class instantiated?
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Dear All,
I have a spreadsheet with a list of names and e-mail addresses. I have created a code that when clicked will open my default mail program. I can then click send. Start of the code is below (the full code works but I must manually click each time)
Quote: ="mailto:"&E2&"?subject="&B2&
I would like to know if it's possible to create a VBA code or similar within NeoOffice that will automatically load up these 30 hyperlinks so I don't have to click 30 times.
Please help!
|
|
|
|
|
If the cells containing the links are in sequence then you can just code a simple loop which repeats the same code through all the relevant cells.
|
|
|
|
|
Hey Richard! It's great to hear from you! Thank you for the reply.
I probably should have added - I'm a complete beginner to Visual Basic and have never tried coding anything. Is there a tutorial for how to start coding / implement the code you suggested?
Yes all the links are in order. Any help would be amazing.
|
|
|
|
|
Member 10603840 wrote: I'm a complete beginner to Visual Basic
But your question is about using VBA which is a slightly different beast. If you want to learn it then you can use the help system in Microsoft Office, or use Google to find tutorials. I know nothing abot NeoOffice, so I cannot comment further.
|
|
|
|
|
How to declare a global variable and access it's value anywhere..
|
|
|
|
|
|
A couple of ways
1. use a module not my favourite suggestion
public module GlobalVariables
public foo as string
end module
2. shared class with shared variables
public shared class FooBar
public shared SomeValue as integer =1
end class
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
I have one using excel 2000 vba.now we upgrade use window 8 64 bit and excel 2010 so
when I add control listview, it show message "Library not registered"
anybody has idea?
Best regards
|
|
|
|
|
Samoeurn wrote: I have one using excel 2000
One what?
Samoeurn wrote: when I add control listview, it show message "Library not registered"
What library, where does the message appear, what line of code ... ?
Please be clear and specific with the details of your problem. Remember, we cannot see your screen.
Veni, vidi, abiit domum
|
|
|
|
|
I have One VBAProject that is excel add in. If I use window 8 x86, it works perfectly but now our company upgrade using window x64.it has one problem,one control is not support "Listview" and shows error message below:
1.Library not registered
2.Compile error in hidden module: frmChart
this error commonly occurs when code is incompatible with the version,platform, or architecure
of this application. Click "help" for information on how to correct this error.
please kindly help appreciate
Thanks
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
