Re: DB Question, speed is the answer!

The only thing that stands out as slow in your code is the use of the LIKE predicate - you are performing a full-text search with wildcards and, depending on the number of records and the size of the fields, that could take a while. Yes, you should be using placeholders, but they won't speed up this particular snippet any. I am assuming that your database table is properly indexed, if not, "talk to the database people" :-). It's also possible that it's the database connection itself that is slowing you down, in which case you may want to explore persistence via mod_perl or something. If you have thousands of rows in the resultset, you might gain some speed by using DBI's bind_columns.

Comment on Re: DB Question, speed is the answer!

Replies are listed 'Best First'.
Re^2: DB Question, speed is the answer! by Anonymous Monk on Jan 14, 2005 at 15:20 UTC
Actually the results are not big at all, it should return around 10 rows with account numbers, that's all.	[reply]
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]