in reply to Re: DB Question, speed is the answer!
in thread DB Question, speed is the answer!

Actually the results are not big at all, it should return around 10 rows with account numbers, that's all.
  • Comment on Re^2: DB Question, speed is the answer!

Replies are listed 'Best First'.
Re^3: DB Question, speed is the answer!
by TrekNoid (Pilgrim) on Jan 14, 2005 at 16:10 UTC
    I have to agree with jZed here, the predicate: 
    MASTERTABLE.NUMBER LIKE '%1324940%'

    [download]
    is going to perform a full table scan, no matter how many rows your return set has. By wildcarding on the left and right, you're defeating any index that the table would have, so it has no choice but to scan the whole table.

    If it's running faster the second time you run it, it's because the data is likely still cached from your first run.

    You might try re-working this into either two queries, or at least a sub-query, where you do: 

       (   MASTERTABLE.SERIAL = 'FF' 
    OR MASTERTABLE.SERIAL = 'CC' 
    OR MASTERTABLE.SERIAL = 'OO' 
    OR MASTERTABLE.SERIAL = 'NN')

    [download]
    first, which will reduce your initial set, and *then* do the LIKE against that return set.

    That should help some

    TrekNoid

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom