Max Effiency in Query 1M Rows of data

gluelu · #1 (**permalink**) 02-13-13, 12:21

Hi Guys,

I'm new to dBforums and hope someone can help with this project I'm working on for work.

The gist of it is: I'm querying a table of 1M rows (1 million rows)
Question: Is there an efficient way to query the 1M rows, if you disregard the query code (it is a very straightforward and simple code). What I want to know is, which is faster: To query all 1M rows with one query, or break it down and query perhaps 1000 rows at a time and repeat for 1000 times?

On a side note, does querying scale linearly? (I.e. if it takes 1 min to query 10 rows, does it take 10 min to query 100 rows?)

Do you guys have any recommended ideal query subsize (eg. query 1567 rows at a time) that maximizes the efficiency of this query.

Note: By max efficiency, I mean to minimize the amount of time it takes to generate the table of data using the query.

Thanks guys for all your help.

Cheers

blindman · #2 (**permalink**) 02-13-13, 13:12

A million rows is chicken feed.

It is (almost) never better to break a query into subqueries. Looping through data consumes much more time and resources.

On an indexed table, queries time does not increase linearly with record counts. A table with 10 times as many rows does not take 10 times as long to process as a table with one tenth the rows.

Pat Phelan · #3 (**permalink**) 02-13-13, 13:16

In MOST cases, queries scale roughly as a function of log(n). More depends on your database engine of choice, hardware, etc than it does on pure row count.

For most of my clients running well designed schemas and reasonable queries (less than 100 rows returned to the client) the runtime is around one second. Most of those queries are running against relational database engines (Microsoft SQL, MySQL, DB2 on zOS, Oracle, etc) but a few are running various Big data tools/queries too.

The short answer is that there isn't a short answer. With only a few million rows to query, you ought to be able to get sub-second response for most cases.

-PatP