Gossamer Forum
Home : Products : Gossamer Links : Version 1.x :

best way to search records?

Quote Reply
best way to search records?
I need to do a search for records, such that:

Field_A=x and Field_B=y

Then, I need to run through each of the results, hash them, and do some manipulation of a few other columns, then write it back.

What's the most efficient way to call up the list, hash it, then iterate through it in a loop that I can add the other processes to?

I can see several (at least 2) different ways to do this, and wonder what would be the most efficient?
Quote Reply
Re: best way to search records? In reply to
i'd put it into the prepare statement..
Quote Reply
Re: best way to search records? In reply to
You can use DBSQL:

Code:
my $hits = $db->query ( { Field_A => 'x', Field_B => 'y' } );
if ($db->hits) {
foreach my $hit (@{$hits}) {
$hash = $db->array_to_hash($hit);
...
}
}

You can pass in things like mh = 25 to only get 25 results to save yourself from getting a ton of results, or set it really high if you want all the hits. You can also use other options like and matching, or matching etc.

This isn't the fastest way as it is still a wrapper, and you are using a perl subroutine to convert an array to a hash, rather then the DBD driver. However, I think the performance hit is pretty minimal, and it will save you a lot of code, especially if you need to do paging.

Cheers,

Alex

Quote Reply
Re: best way to search records? In reply to
That sounds good. This isn't going to process a lot of records at any one time. You read me right in that I was looking for code-efficiency not CPU efficiency, since if this routine is going to slow down anywhere, it's in what I do to the records, not the initial search and seek. If this routine ever pulls up 100 records I'll probably be retired, and living on an Island in the Tropics <G>

I can pass QUERY a '<' such that DATE<$TODAY, if I remember a previous thread correctly.

In actuality that would only happen if I had a system go down for more than 24 hours, since I'm really only looking for certain records with a flag set, and with a date equal to $TODAY