Which is faster (Database searching)

[Drakkon]

Ohkay whcih would be faster...
Using PHP to access an Oracle database I am drawing out the data from 4 different columns and searchign via keywords...
would it be faster to use oracle and its "contains" statement along with indexes to search these colums?
or would it be faster to use PHP reg expressions?
I know whcih would be simpler but we are going for speed here since the 4 columns have close to 10,000 rows....

 

[bunker]

It's always going to be faster doing the work on the database server vs. doing it in code.

That said, 10k records isn't all that much. You may notice some difference in speed but I wouldn't think it would be that much.


Take this with a grain of salt as I work with asp and MS SQL server.

 

[Haircut]

I would always do any searching on the database side.

As bunker says, 10K records is nothing, as you as you have decent indexes on the table it will hardly take any time at all.

 

[Drakkon]

thats what i thought...now the problem is figuring out the SQL queries and how to create the right indexes thanx guys

 

[Haircut]

If you will always be using the four columns in the where clause of the SQL statement then you will probably be best creating a concatenated index incorporating all four columns.

 

[oog]

Originally posted by: bunker
It's always going to be faster doing the work on the database server vs. doing it in code.

while its true that doing the work on the database server will almost always be faster, it depends on if this is a scenario where you need to scale to a large number of web servers and users. for high scale situations, you would prefer to preserve the processing power of the database and spend a little more time on the web server. it's easier and cheaper to add capacity at the web server than to the database.

 

[Drakkon]

now heres an interesting scenario...
say the oracle DB is running on a close to bare minimum (pentium 3 @ 500 Mhz /w 1 GB of ram)...
While PHP/Web server is running on a Dual Xeon @ 2.5Ghz /w 4 GB of ram...
now i know the load on the DB isn't that bad...but then again if these records do increase in size...and multiple searches will be querying the DB...would it be better off just grabbing all records and sending them off to the web server than letting the DB do all the work?

 

[Barnaby W. Füi]

Originally posted by: Drakkon
now heres an interesting scenario...
say the oracle DB is running on a close to bare minimum (pentium 3 @ 500 Mhz /w 1 GB of ram)...
While PHP/Web server is running on a Dual Xeon @ 2.5Ghz /w 4 GB of ram...
now i know the load on the DB isn't that bad...but then again if these records do increase in size...and multiple searches will be querying the DB...would it be better off just grabbing all records and sending them off to the web server than letting the DB do all the work?

Hard to say.. try doing some tests, and see how it turns out.

 

[jonmullen]

Originally posted by: Drakkon
now heres an interesting scenario...
say the oracle DB is running on a close to bare minimum (pentium 3 @ 500 Mhz /w 1 GB of ram)...
While PHP/Web server is running on a Dual Xeon @ 2.5Ghz /w 4 GB of ram...
now i know the load on the DB isn't that bad...but then again if these records do increase in size...and multiple searches will be querying the DB...would it be better off just grabbing all records and sending them off to the web server than letting the DB do all the work?

Under that situation bandwidth and latency to send all the records may end up being a problem, but like BingBongWongFooey said they best way it to test just something to keep in main as the DB grows your tests might not be the same when your fetching 100,000 records each time.