Re: VB6 - searching 500,000 records for 100 values about 10,000 times per day
From: Mark J. McGinty (mmcginty_at_spamfromyou.com)
Date: 08/22/04
- Next message: Veign: "Re: Substring"
- Previous message: Rick: "Re: VB6 - searching 500,000 records for 100 values about 10,000 times per day"
- In reply to: MS Newsgroups: "VB6 - searching 500,000 records for 100 values about 10,000 times per day"
- Next in thread: MS Newsgroups: "Re: VB6 - searching 500,000 records for 100 values about 10,000 times per day"
- Reply: MS Newsgroups: "Re: VB6 - searching 500,000 records for 100 values about 10,000 times per day"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 22 Aug 2004 00:07:58 -0700
If the set of value pairs will approach even 100K, you almost certainly want
to use some decent db iron, like MSDE -- not Jet.
Create a clustered index on the string field in the large table, this will
make lookups very fast. If the number of rows to be looked-up will vary
from call to call, consider populating a second table with the inputs, and
joining it to the large table to derive the result set. Create a clustered
index on the string field in the second table as well. Call TRUNCATE TABLE
[InputsTable] between calls rather than DELETE [InputsTable], it's unlogged,
and quite a bit faster.
Given that you'll only be retrieving 100-200 rows, the lookup should be a
sub-second operation.
It won't be necessary to stop the lookup process while updating the table,
simply take a table lock and let the engine handle concurrency issues for
you.
At least, that's the approach I'd try first.
Hope it Helps,
Mark
"MS Newsgroups" <msnews@mailinator.com> wrote in message
news:%23euhdo9hEHA.1656@TK2MSFTNGP09.phx.gbl...
> Hello all,
>
> I'm trying to determine the best way to lookup about 100 string values in
> a
> dataset that could eventually grow to 500,000 records; the data will need
> to
> be looked up about 10,000 times per day. The records are pretty simple -
> basically a text string and a corresponding integer value between 0 and
> 9999, like this:
>
> string1 6782
> string2 8999
> string3 1
> string4 888
>
> ... and so on.
>
> The strings are in ANSI and will average 8 characters in length but could
> be
> as long as 40 characters or as short as 1 character.
>
> The application does not modify the data - it just reads it. An outside
> program will occasionally rewrite the records but not more than once a
> day.
> It will be possible to stop the analysis routine while the records are
> being
> replaced but other than that the program will run 24x7.
>
> Every time this routine gets called it will have to read in a number of
> constants, parse out the strings to be searched for, do the search and
> read
> the corresponding integers then perform some calculations and spit out a
> result.
>
> The application will run on a Windows 2000 or 2003 server and all data
> will
> reside on the same system. I can install a database engine if I have to,
> though I'd prefer not to because doing so will probably make the
> installation process much more complicated (customers will be installing
> the
> program on their systems). I'll use a database engine if that's really the
> right way to do it but with records this simple I'm thinking that an
> Access
> database file or even a simple text file might be the way to go. Obviously
> I'm hoping to do the searches using ADO but I'm open to suggestions in
> that
> regard as well.
>
> It's been a while since I've done anything along these lines so I'm hoping
> to get some input on what the best way is to search the data.
>
> Thanks in advance and apologies if this is a ramble. I've been thinking
> about it for too long without outside input.
>
>
>
>
>
- Next message: Veign: "Re: Substring"
- Previous message: Rick: "Re: VB6 - searching 500,000 records for 100 values about 10,000 times per day"
- In reply to: MS Newsgroups: "VB6 - searching 500,000 records for 100 values about 10,000 times per day"
- Next in thread: MS Newsgroups: "Re: VB6 - searching 500,000 records for 100 values about 10,000 times per day"
- Reply: MS Newsgroups: "Re: VB6 - searching 500,000 records for 100 values about 10,000 times per day"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
