Harley12
asked on
VB.Net Search Array
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.
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.
For as far as I know you can't search a multidimensional array using indexof or binarysearch, you have to loop through them
ASKER
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.
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.
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.Datarelat
SQL Server, Oracle...?
Bob
Bob
ASKER
Bob, this is SQL Server.
Corey, can you give me a brief example of how to do what you suggest?
Corey, can you give me a brief example of how to do what you suggest?
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.
Bob
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.
Bob
ASKER
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:
Table1
ROWID UPDATESTAMP
1 2005-02-21 16:01:13.000
Table 2
ROWID UPDATESTAMP
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.
Table1
ROWID UPDATESTAMP
1 2005-02-21 16:01:13.000
Table 2
ROWID UPDATESTAMP
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.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Explain more what your app is doing and how you use the array and maybe we can come up with a better solution.