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?

Eg

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.

Answers


Try looking at info about pagination. Here's a short summary of it for SQL Server: http://www.singingeels.com/Articles/Pagination_In_SQL_Server_2005.aspx.


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.


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.


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


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


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.


Need Your Help

Yii file field is empty

file-upload yii

I have file field exactly as this link said in my project , i put exactly it's code and change every thing looks like it but when i want it's value by this code :

Rotating image in pygame

python graphics python-3.x rotation pygame

So I have an arrow as an image. By the looks of it, it seems to be rotating right around the centre. But when it rotates, either way, when it his an angle of around 75 degrees on each side there is...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.