[Last Call] Learn how to a build a cloud-first strategyRegister Now


VB.Net Search Array

Posted on 2005-04-20
Medium Priority
Last Modified: 2010-04-23
I have a two dimensional string array defined as (2,5).  EX:

This(0,0) is(0,1) an(0,2) example(0,3) of(0,4)
records(1,0) that(1,1) I(1,2) have(1,3) there(1,4)

If I wanted to find the word 'example' in this array how would I do this without having to loop through all the items in the array?  My actual array has about 600,000 records in it so looping through would be ridiculously slow.  I tried using the binarysearch method but this only works with one dimensional arrays.
Question by:Harley12
LVL 86

Expert Comment

by:Mike Tomlinson
ID: 13824034
A binary search is only useful if your data is sorted anyway.

Explain more what your app is doing and how you use the array and maybe we can come up with a better solution.
LVL 25

Expert Comment

ID: 13824037
For as far as I know you can't search a multidimensional array using indexof or binarysearch, you have to loop through them

Author Comment

ID: 13824177
Ok, maybe there is an alternative. Here is my delimma.  Currently I have a recordset that has about 600,000 records each with about 20 fields.  I need to compare a field value in each one of these records with a field in another table(joining on a primary key) and if the fields don't match then perform one operation and if they do match then perform another operation.  It is working now, however, I am looping through the 600,000 records and for each one I am performing a select against this other table to do the comparison.  This is 600,000 trips to the db and seems to be slowing my app down.  

I think the best thing may be for me just to write a database side cursor that will populate a temp table with all the records that I need to use.  I can then just create one recordset based on this temp table and perform the necessary operations.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 13

Expert Comment

by:Corey Scheich
ID: 13824739
Will the db be changing while your app is running or do you have sole access to it.  If it isn't changing you can create a dataset that contains only the relevent info you need to compare with.  Then call the dataset that is in memory for your array comparison.  It seems that you have to loop through your array anyway since you have to do an action on each field nomatter what the outcome of your comparison is.  If you have everyting in a single dataset you can grab a colum using the datarelation between the tables.  "SomeParentTable.Datarelation.ColumninChildTable"
LVL 96

Expert Comment

by:Bob Learned
ID: 13827024
SQL Server, Oracle...?


Author Comment

ID: 13828521
Bob, this is SQL Server.

Corey, can you give me a brief example of how to do what you suggest?
LVL 96

Expert Comment

by:Bob Learned
ID: 13832621
I am trying to understand your requirement, so a question, then some proposals.

Question:  Is this comparison operation based on records that are pulled from the database, processed, and then compared, or are you trying to compare two tables.

Proposal #1 (Records in memory compared to database):  Create a stored procedure, and hand the records to the procedure, and do it on that side.

Proposal #2 (Compare two tables):  Create a query that will compare and pull records based on differences.

If I missed with this, please let us know just how far I missed.


Author Comment

ID: 13834140
I need to loop through all the records in one table and for each record compare a field in that table to a field in another table.  I created a db side cursor yesterday to do this.  It works, but is slow.  My main objective at this point is to get this process to finish as quickly as possible.  Ideally I would be able to create some type of SQL query to do this in one statement.  I could build my recordset based on this one query, however, I don't think it is possible.  To be more specific, here are the two tables:


1               2005-02-21 16:01:13.000

Table 2

1            2005-02-21 14:42:56.000

I will be comparing the updatestamp field in table1 to the updatestamp in table 2 where the rowid's are equal.   If they are the same then I won't need to do anything, if they are different then I will be performing an update and if the rowid from table 1 does not exist in table 2 then I will need to do an insert into table 2.

Author Comment

ID: 13959419
I found a workaround for this problem.  No matter what I tried, looping through 600k records was just not going to be fast enough.  I was able to narrow down my results set.  Thanks for everyones input.

Accepted Solution

GhostMod earned 0 total points
ID: 14000653
PAQd, 125 points refunded.

Community Support Moderator

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month18 days, 2 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question