Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2013
    Posts
    2

    Question Max Effiency in Query 1M Rows of data

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,564
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,843
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Feb 2013
    Posts
    2
    Thanks for the quick response. Appreciate it.

  5. #5
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,844
    Do you need to retrieve all the records to the client? If you do, performance is going to suffer, because of sheer data transport.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •