Visual Basic

Re: 100,000 Items in Check ListBox result in slow performance

19-Feb-14 8:11

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 Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Arun Peswani19-Feb-14 16:09

Re: 100,000 Items in Check ListBox result in slow performance

19-Feb-14 16:09

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.

Eddy Vluggen20-Feb-14 0:29

Re: 100,000 Items in Check ListBox result in slow performance

20-Feb-14 0:29

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 Suspicious | :suss:

If you can't read my code, try converting it here[^]

Arun Peswani20-Feb-14 5:35

Re: 100,000 Items in Check ListBox result in slow performance

20-Feb-14 5:35

Hi Eddy,

Thanks for replying point by point for all my questions Smile | :)

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 Frown | :(

pls help.

arun_peswani@yahoo.co.in

modified 20-Feb-14 13:30pm.

Eddy Vluggen20-Feb-14 8:45

Re: 100,000 Items in Check ListBox result in slow performance

20-Feb-14 8:45

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() ' use stringbuilder
        For Each checkedThingy In CheckListBox.CheckedItems ' only add thing you know are checked 
                                                             ' this loop is smaller than "all rows"
            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 Suspicious | :suss:

If you can't read my code, try converting it here[^]

Arun Peswani20-Feb-14 20:55

Re: 100,000 Items in Check ListBox result in slow performance

20-Feb-14 20:55

Hi Eddy,

Thanks for nice replies Smile | :)

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 Frown | :(

, 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 Smile | :)

You may reply as per convenience with your valuable suggestions.

Thanks a lot for all your help so far Smile | :)

arun_peswani@yahoo.co.in

Eddy Vluggen21-Feb-14 6:43

Re: 100,000 Items in Check ListBox result in slow performance

21-Feb-14 6:43

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 Thumbs Up | :thumbsup:

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;

SQL

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 Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Arun Peswani22-Feb-14 6:57

Re: 100,000 Items in Check ListBox result in slow performance

22-Feb-14 6:57

You are truly superb Smile | :)

1) I tried creating ITEMS string as you explained however it required a little extra work on formatting but all went well.

2) I liked the concept of "JOIN Table0 on Table1.Column1 = Table0.Column0" I never thought about this one Poke tongue | ;-P

Anyways, whatever I have done so far (in addition to what you advised), it has optimized the whole thing and speed increased almost 5-6 times. My whole thing now loads in 8-10 seconds which was taking more than 1 minute earlier. That is truly great. Thanks a lot once again Smile | :)

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.

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 brief explanation should help me the way your other replies did.

Thumbs Up | :thumbsup:

arun_peswani@yahoo.co.in

Eddy Vluggen24-Feb-14 7:35

Re: 100,000 Items in Check ListBox result in slow performance

24-Feb-14 7:35

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? Roll eyes | :rolleyes:

You owe the Q&A guys a Beer | [beer]

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

MyOwnBoss7-Mar-14 13:05

MyOwnBoss

7-Mar-14 13:05

Select * ....... very bad

Re: 100,000 Items in Check ListBox result in slow performance

Richard MacCutchan19-Feb-14 22:09

Re: 100,000 Items in Check ListBox result in slow performance

19-Feb-14 22:09

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.

Dave Kreskowiak24-Feb-14 5:10

Dave Kreskowiak

24-Feb-14 5:10

Reported as "abusive" for your responses to me and others in the copy of this post over in Q&A.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

ComboBox not receiving DataSet values?

rsmldmv18-Feb-14 12:17

rsmldmv

18-Feb-14 12:17

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

   'This is from the User Interface
    Private Sub LoadIntervalComboBox()
        Try
            InitializeErrorClass(EH)
            BL.LoadIntervalComboBox(EH)

            If EH.ErrorMessage = "" Then
                cmbCDInterval = EH.Combo                'There is no data now
            End If

            EH.ErrorMessage = ""

        Catch ex As Exception
            EH.ErrorMessage = "LoadIntervalComboBox() - " & ex.Message & "~E"
        End Try

        EH.ProcessMessages(Me, sbr, EH.ErrorMessage)
    End Sub
   '
   '
   'This SubProcedure is in a Module File
    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
   '
   '
   'This Function is in the Business Layer (BL)
    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)     'This Function is in the Data Layer

            If EH.DataSet.Tables(0).Rows.Count > 0 Then
                EH.Combo.DataSource = Nothing
                EH.Combo.DataSource = EH.DataSet.Tables(0)   'At this point there IS data in the table
                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
    'This single variable is used to hold System Warning/Error Messages. It is contained in every Form Module and 
    'Class Module where there is Logic

    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.

Re: ComboBox not receiving DataSet values?

Eddy Vluggen19-Feb-14 8:01

Automatically open Hyperlink in NeoOffice (VBA?)

19-Feb-14 8:01

Where is the form created? And where is the ErrorHandler class instantiated?

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Member 1060384017-Feb-14 9:30

Member 10603840

17-Feb-14 9:30

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!

Re: Automatically open Hyperlink in NeoOffice (VBA?)

Richard MacCutchan17-Feb-14 21:32

Re: Automatically open Hyperlink in NeoOffice (VBA?)

17-Feb-14 21:32

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.

Member 1060384017-Feb-14 22:42

Member 10603840

17-Feb-14 22:42

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.

Re: Automatically open Hyperlink in NeoOffice (VBA?)

Richard MacCutchan18-Feb-14 2:47

18-Feb-14 2:47

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.

vb.net

SURAJIT UPADHYAY15-Feb-14 0:16

SURAJIT UPADHYAY

15-Feb-14 0:16

How to declare a global variable and access it's value anywhere..

Re: vb.net

Richard MacCutchan15-Feb-14 1:42

Library not registered in excel 2013 vba

15-Feb-14 1:42

See http://www.codeproject.com/Messages/1278601/How-to-get-an-answer-to-your-question.aspx[^], and also https://www.google.com/search?q=global+variable+vb.net[^].

Veni, vidi, abiit domum

Re: vb.net

Simon_Whale17-Feb-14 22:05

Simon_Whale

17-Feb-14 22:05

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

Samoeurn13-Feb-14 21:41

Samoeurn

13-Feb-14 21:41

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

Re: Library not registered in excel 2013 vba

Richard MacCutchan13-Feb-14 21:56

Re: Library not registered in excel 2013 vba

13-Feb-14 21:56

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

Samoeurn13-Feb-14 22:27

Samoeurn

13-Feb-14 22:27

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

Re: Library not registered in excel 2013 vba

Richard MacCutchan13-Feb-14 22:59