CODEWITHSUNDEEP
sqlperformance
Petras
Petras

Reputation: 4759

Is there efficient SQL to query a portion of a large table

The typical way of selecting data is:

select * from my_table

But what if the table contains 10 million records and you only want records 300,010 to 300,020

Is there a way to create a SQL statement on Microsoft SQL that only gets 10 records at once?

E.g. 

select * from my_table from records 300,010 to 300,020

This would be way more efficient than retrieving 10 million records across the network, storing them in the IIS server and then counting to the records you want.

Upvotes: 1

Views: 5238

Answers (6)

Sophie Alpert
Sophie Alpert

Reputation: 143134

Try looking at info about pagination. Here's a short summary of it for SQL Server.

Upvotes: 2

Robert Pender
Robert Pender

Reputation: 21

I use wrapper queries to select the core query and then just isolate the ROW numbers that i wish to take from the query - this allows the SQL server to do all the heavy lifting inside the CORE query and just pass out the small amount of the table that i have requested. All you need to do is pass the [start_row_variable] and the [end_row_variable] into the SQL query.

NOTE: The order clause is specified OUTSIDE the core query [sql_order_clause]

w1 and w2 are TEMPORARY table created by the SQL server as the wrapper tables.

SELECT
    w1.*
FROM(   
    SELECT w2.*, 
    ROW_NUMBER() OVER ([sql_order_clause]) AS ROW
    FROM (

        <!--- CORE QUERY START --->
        SELECT [columns]
        FROM [table_name]
        WHERE [sql_string]
        <!--- CORE QUERY END --->

   ) AS w2
) AS w1
WHERE ROW BETWEEN [start_row_variable] AND [end_row_variable]

This method has hugely optimized my database systems. It works very well.

IMPORTANT: Be sure to always explicitly specify only the exact columns you wish to retrieve in the core query as fetching unnecessary data in these CORE queries can cost you serious overhead

Upvotes: 1

John Sansom
John Sansom

Reputation: 41819

When working with large tables, it is often a good idea to make use of Partitioning techniques available in SQL Server.

The rules of your partitition function typically dictate that only a range of data can reside within a given partition. You could split your partitions by date range or ID for example.

In order to select from a particular partition you would use a query similar to the following.

SELECT <Column Name1>…/* 
FROM <Table Name> 
WHERE $PARTITION.<Partition Function Name>(<Column Name>) = <Partition Number>

Take a look at the following white paper for more detailed infromation on partitioning in SQL Server 2005.

http://msdn.microsoft.com/en-us/library/ms345146.aspx

I hope this helps however please feel free to pose further questions.

Cheers, John

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 415735

SELECT * FROM my_table is just the tip of the iceberg. Assuming you're talking a table with an identity field for the primary key, you can just say:

SELECT * FROM my_table WHERE ID >= 300010 AND ID <= 300020

You should also know that selecting * is considered poor practice in many circles. They want you specify the exact column list.

Upvotes: 4

BTB
BTB

Reputation: 2146

Use TOP to select only a limited amont of rows like:

SELECT TOP 10 * FROM my_table WHERE ID >= 300010

Add an ORDER BY if you want the results in a particular order.

To be efficient there has to be an index on the ID column.

Upvotes: -2

kquinn
kquinn

Reputation: 10740

Absolutely. On MySQL and PostgreSQL (the two databases I've used), the syntax would be

SELECT [columns] FROM table LIMIT 10 OFFSET 300010;

On MS SQL, it's something like SELECT TOP 10 ...; I don't know the syntax for offsetting the record list.

Note that you never want to use SELECT *; it's a maintenance nightmare if anything ever changes. This query, though, is going to be incredibly slow since your database will have to scan through and throw away the first 300,010 records to get to the 10 you want. It'll also be unpredictable, since you haven't told the database which order you want the records in.

This is the core of SQL: tell it which 10 records you want, identified by a key in a specific range, and the database will do its best to grab and return those records with minimal work. Look up any tutorial on SQL for more information on how it works.

Upvotes: 1

Related Questions