4

I want to select million records from a table and I am using select query for this.

Currently it is taking a few minutes to get data. Can I get it quickly?

I am using SQL Server 2008 R2.

My query:

SELECT     
   sum(Orders.BusinessVolumeTotal) as  BusinessVolume, 
   sum(Orders.CommissionableVolumeTotal) as CommissionableVolume, 
   OrderTypes.OrderTypeDescription, 
   Orders.OrderTypeID
FROM  
   Orders 
INNER JOIN
   OrderTypes ON Orders.OrderTypeID = OrderTypes.OrderTypeID
WHERE
   Orders.OrderDate > convert(DATETIME, '{0}') 
   and Orders.OrderDate < convert(DATETIME, '{1}') 
GROUP BY
   Orders.OrderTypeID, OrderTypes.OrderTypeDescription
Kate Orlova
  • 3,225
  • 5
  • 11
  • 35
AnandMeena
  • 528
  • 3
  • 11
  • 26
  • 3
    That highly depends on (a) what your table(s) look like, (b) what kind of indexes you have, (c) what your query is like, and (d) what your hardware (servers) supports... this question is **way too broad** to answer.... – marc_s Oct 22 '13 at 07:09
  • That still leaves the table and index structures unanswered..... – marc_s Oct 22 '13 at 07:12
  • I am not using Index and table has 29 columns. – AnandMeena Oct 22 '13 at 07:15
  • Points to look at: try to get rid of the `CONVERT` functions in your `WHERE` clause - that'll kill performance. Also: is your foreign key `Orders.OrderTypeID` indexed? Do your tables have **good** clustering keys? Have you ever even run this query throug the Database Tuning Advisor?? – marc_s Oct 22 '13 at 07:15
  • And what does your current **execution plan** look like? Can you post a screenshot? – marc_s Oct 22 '13 at 07:16
  • sorry what you mean by "execution plan" ? – AnandMeena Oct 22 '13 at 07:17
  • 1
    See: http://technet.microsoft.com/en-us/library/ms178071%28v=sql.105%29.aspx. Before you execute the query in SQL Server Mgmt Studio, go to `Query > Include Actual Execution Plan` and you'll get a graphical view of how the query was executed - that is immensely helpful to see problems in performance – marc_s Oct 22 '13 at 07:20

4 Answers4

9

Use Indexing for your table fields for fetching data fast.

Reference:

http://www.tutorialspoint.com/sql/sql-indexes.htm

Munis Isazade
  • 23
  • 3
  • 4
Zeeshan
  • 1,659
  • 13
  • 17
3

There's a few factors that would go into this. A quick list of things to look at:

  1. The speed of your server. CPU, memory, network connection would all be factors
  2. If you are doing a SELECT statement with conditions (ie. using a WHERE) or one with JOINS, having indexes will improve your performance, especially on a table with millions of rows. Hash tables will do a huge net positive on a large table.
  3. Writing clean queries. For example, if you have a large list of items you need to exclude from a query, perform a LEFT JOIN instead of using a NOT IN condition.

This is really just the tip of the iceberg, but some of the easiest things to implement will also provide you with some of the biggest performance boosts.

Lloyd Banks
  • 35,740
  • 58
  • 156
  • 248
3

Best way Fast Performance Tips for SQL Usiing SELECT Statements

   1:- Check Indexes. 
   2:- There should be indexes on all fields used in the WHERE and JOIN portions of the SQL statement
   3:-  Limit Size of Your Working Data Set.
   4:-  Only Select Fields You select as Need.
   5:- Remove Unnecessary Table and index 
   6:- Remove OUTER JOINS. 
   7:-  Remove Calculated Fields in JOIN and WHERE Clauses.
user6341745
  • 55
  • 4
  • Bro, as a beginner, your Index description was the first time I understood what an index actually does and how to create it. That seems ridiculous, but it's true. Thanks! – sonicblis Jan 25 '23 at 16:36
1

The speed of the query depends on the number of rows but if you do appropriate optimizations taking the performance factors such as:

  1. Indexing Clustered/Non clustered
  2. Data Caching
  3. Table Partitioning
  4. Execution Plan caching
  5. Data Distribution

the query will execute faster.

Shreevardhan
  • 12,233
  • 3
  • 36
  • 50
vhadalgi
  • 7,027
  • 6
  • 38
  • 67