Use array or sql commands to do lookup

Is it better to download a small table (about 3500 records) to an array to look-up something, or create sql commands to look-up the value?

I have a table with station numbers and corresponding links.  I am converting an older table that just has the station number.  I need to look up the old station number and find the new link before I convert the record. I wondered if it was better to create an array and download the table, or just create sql commands to find the corresponding link.
jsaacsonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> about 3500 records

Kindly think it in the long term perspective whether this would be 3500 records all the time or will it grow over the period of time..
And do think about the size of your array, length of both columns( if if is varchar column consider the max size of it) and if it would be very less, then take it to your array else do it in the SQL table itself.
0
jsaacsonAuthor Commented:
Actually, this program will only run 3 or 4 times, as it is part of a conversion.

The array would consist of 2 fields, one 7 characters and 1 integer.

So based on your response, it seems that an array would be the better solution.

Thanks
0
Jorge PaulinoIT Pro/DeveloperCommented:
You can load the table into a Dataset/DataTable and then you can look in the Datable very easily. But you 3500 are not too many records.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jsaacsonAuthor Commented:
In reviewing options for this, I saw some comments on using a dictionary.  I think I might best use the dictionary, as I want to use this data for look-ups.  When I read another table I am converting, I need to find the new station number for each record.  It looks like using the dictionary would work well as I will feed it the old station number, which is the first field, and pull the new station number which is the second field.

Are there any caveats about doing this?

0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> I want to use this data for look-ups.

If you are going to use this data only for look-ups, then no issues in having either dataset / data table or in your array unless otherwise it would grow to huge no. of records ( 3500 is somewhat huge).
Alternatively, you can create an XML for the required set of records when your application is being started and then reference that XML as a lookup which should be comparatively faster and reduce the memory load for your application too for your 3500 records..

PS: Make sure you save that as an XML file instead of creating an XML and having it in memory which might again impact.
0
CodeCruiserCommented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.