none
Expecting to deal with 500k data. Need some Expert advice. RRS feed

  • Question

  • Hi There,

    I am in a situation, like i have to deal data almost 500k. At the same time, result should get within few minutes. I able to do 100K within few minutes but when data increase it takes long time, e.g., when my program can handle 211k data, but it takes 32 min. This is one of the challenge i am facing right now. One of the portion in my program, like

    say you know rowNum1.

                                

                                Range("A1:K" & rowNum1 + 1).AutoFilter Field:=5, Criteria1:="=ASK"
                                Range("A1:K" & rowNum1 + 1).AutoFilter Field:=7, Criteria1:="=FILLED" _
                                    , Operator:=xlOr, Criteria2:="=PARTIALLY_FILLED"
                                R1 = ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Count / ActiveSheet.AutoFilter.Range.Columns.Count - 1

                                ActiveSheet.AutoFilterMode = False
                                Range("A1:K" & rowNum1 + 1).AutoFilter Field:=5, Criteria1:="=BID"
                                Range("A1:K" & rowNum1 + 1).AutoFilter Field:=7, Criteria1:="=CANCELED"
                                R2 = ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Count / ActiveSheet.AutoFilter.Range.Columns.Count - 1

                                ActiveSheet.AutoFilterMode = False
                                Range("A1:K" & rowNum1 + 1).AutoFilter Field:=5, Criteria1:="=BID"
                                Range("A1:K" & rowNum1 + 1).AutoFilter Field:=7, Criteria1:="=FILLED" _
                                    , Operator:=xlOr, Criteria2:="=PARTIALLY_FILLED"
                                R3 = ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Count / ActiveSheet.AutoFilter.Range.Columns.Count - 1
                                ActiveSheet.AutoFilterMode = False
                                Range("A1:K" & rowNum1 + 1).AutoFilter Field:=5, Criteria1:="=ASK"
                                Range("A1:K" & rowNum1 + 1).AutoFilter Field:=7, Criteria1:="=CANCELED"
                                R4 = ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Count / ActiveSheet.AutoFilter.Range.Columns.Count - 1
                                ActiveSheet.AutoFilterMode = False

    For clarification, from above portion I just need to find out R1, R2, R3, R4 to take a decision.

    Is there any other way, I can think so that it will save my time for getting R1, R2, R3, R4. I am expecting to some times from the above portion because they are under a loop.

    Expert advice or opinion is expected. 


    • Edited by Rasel Biswas Thursday, September 20, 2018 6:20 PM Calirification
    • Moved by KareninstructorMVP Friday, September 21, 2018 3:31 PM moved from vb.net forum
    Thursday, September 20, 2018 6:17 PM

All replies

  • The following would require SQL-Server or SQL-Express so if you don't have them there is no need to continue. Also having SSMS (SQL-Server Management Studio) is helpful.

    • Export WorkSheet data to database.
    • Open SSMS, Write SQL SELECT statements to match your Excel filtering. Test for proper results from exported data.
    • Once the above is done, truncate the SQL-Server database table.

    Using the SQL in bullet two, use that code for creating SqlClient connection and command. You can place all SELECT statements in the command object CommandText where each SELECT is separated with a semi-colon. Using a SqlClient.SqlDataReader read back the results for each SQL statement via SqlDataReader.NextResult. Store the results.

    If the above is not an option, consider using a free library that does not use automation e.g. SpreadSheetLight installable via NuGet.

    Summary:

    Working from a database will be very fast while working directly from Excel will be time consuming. Been down both roads.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, September 20, 2018 6:41 PM
  • Hi Karen Payne,

    Thanks for your opinion. Unfortunately I do not have server.

    Regards

    Rasel

    Thursday, September 20, 2018 6:44 PM
  • Hi Karen Payne,

    Thanks for your opinion. Unfortunately I do not have server.

    Regards

    Rasel

    SQL Server Express is 100 % free

    Read up on the install quirks regarding Visual C++ Redistributable package first.

    Thursday, September 20, 2018 8:43 PM
  • Hi Karen Payne,

    Thanks for your opinion. Unfortunately I do not have server.

    Regards

    Rasel

    You don't need a full server, you can download SQL Express it's free.

    https://www.microsoft.com/en-us/sql-server/sql-server-editions-express

    SSMS is here 

    https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, September 20, 2018 9:38 PM
  • Hi Karen Payne,

    Thanks for the valuable information. But, if you would not mind, i have a question: as you know, my above portion code is a very small part of my VBA program. In this situation, how can I implement your suggestion to my VBA program? Any advice is appreciated. Thanks in advance.

    Regards

    Rasel Biswas

    Friday, September 21, 2018 2:03 PM
  • Hi Karen Payne,

    Thanks for the valuable information. But, if you would not mind, i have a question: as you know, my above portion code is a very small part of my VBA program. In this situation, how can I implement your suggestion to my VBA program? Any advice is appreciated. Thanks in advance.

    Regards

    Rasel Biswas

    Hello,

    This forum is solely for VB.NET and not VBA so this means I have been under the impression you were doing this in VB.NET. I'm moving your question to the VBA forum. Note VBA will have not nearly the options as VB.NET or C#.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, September 21, 2018 3:30 PM