Loading the data and process them programmatically is faster than SQL queries? [closed]

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, vi开发者_JS百科sit the help center. Closed 11 years ago.

Is it true that loading the data and process them programmatically is faster than SQL queries ? I am supposed to optimized the legacy applications which are running slow because of the growing size of data. Any kind help is appreciated.

---

This might be true in certain cases, but generally you would be able to resolve slow queries by tweaking the queries or adding indexes in the database so that they run more efficiently. There will be tools for your database which will help you to understand how your queries are executed in the database, and this will help you to optimize them. Like Oracle's Explain Plan.

---

As Sam suggests, a lot depends on what you are trying to do. A simple custom solution can be much faster than general purpose database. However, a database can out perform a poorly tuned solution. (And do much much more besides)

---

Most of the overhead involved in querying the database is involved in setting up the connection, preparing the statements, etc. Actually processing your result set once it's been returned doesn't take long at all. The fewer times you're performing those expensive actions, the faster your code is going to run.

This is particularly noticeable where you have N + 1 queries, where you have an initial query that returns N rows, and then you execute one query for each of those N rows.

To take a simple example, say you have a table that contains Invoices, and a table that contains Items on those Invoices. You may have your initial query that simply returns the list of Invoices, and you create Invoice objects to store the data for each of the rows. Then for each of those Invoice objects, you run a query to populate the List of Item objects.

It would be more efficient to write a single query that returns all of that data in one result set, and then use Java to process it and break it out into the relevant objects.

---

An SQL query will be faster.

Yes, it depends. But if the data is already in the database, and the processing does not involve resources outside the database, or particularly complex algorithms (eg a shortest-path search through a graph), i would expect processing inside the database with an SQL query to be faster. Firstly, because you don't incur the overhead of moving the data out of the database and back in again, and secondly, because a properly-written query can be executed very efficiently, taking advantage of indexes, order in the data, etc.

Note that i am specifically talking about doing the processing with a query, not a stored procedure. Stored procedures are not very good at taking advantage of indexes and so on, and so are typically slower than an equivalent query. They're still useful, because there are lots of things you can't express in a query, but where the two go head to head, queries usually win.

---

The answer to your question is... complicated, and depends on the data and a correct reading of the question. I'm assuming the correct reading of your question is:

Given data that is already in a database, is it faster to load the data from the database and process it in code OR to have the SQL query do the processing and return results.

Given the above, the answer still depends on the data and the queries you are trying to run. It's worth noting that most databases are highly optimized code and, for things they can do fast, it's generally a better idea to let them do that work. For example, it's almost certainly faster to ask the database What rows have a value of 1 for the field X than to pull down all the rows and find the ones that meet that criteria. Obviously, that's a very simple example.

The database can do some very complex things very efficiently. That being said, there are some things you can do to help it perform well:

• Use appropriate indexes where you can
• When doing joins across multiple tables, make sure you're doing them correctly (ex, a cross join/cartesian product and a distinct modifier is probably not what you want)
• Using the LIKE operator can make taking advantage of indexes problematic, resulting in full table scans. Be careful with them.

There's obviously a lot more things to know about efficient database queries. However, the main thing to keep in mind is that the database code is probably better than you would write on your own. Figure out how to take advantage of it's wonderfulness, if you can.