title: Slow Select query in SQL with table having 2 million records only
body: <p>I need your valuable suggestion if possible for you, I have a table tracking history (script for the table below). It has approx. 2.2 million records. But when I fetch all the records the table rendering is too slow and take about 4-5 minutes for fetching all records. I assume the row count in the table is quite less just <strong>2.2 million</strong> so it should render very fast.</p>
  
  <p>It would be really helpful if you could add your valuable inputs for this. </p>
  
  <p>Selecting records should be very fast and render within seconds as table records are less and database size is just 12 GB. </p>
  
  <p>Statistics IO and time for 100,000 records - 
  "(100000 row(s) affected)
  Table 'trackingsHistory'. Scan count 1, logical reads 21036, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.</p>
  
  <p>(1 row(s) affected)</p>
  
  <p>SQL Server Execution Times:
   CPU time = 2657 ms,  elapsed time = 9737 ms."</p>
  
  <p><a href="https://i.stack.imgur.com/rrpvn.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/rrpvn.png" alt="Client Statistics attached"></a></p>
  
  <pre><code>**Details Below**
  
  **Exact query** - SELECT * FROM TRACKINGSHISTORY WHERE bookedin_date BETWEEN '2018-01-01 07:03:58.700' AND '2018-12-31 07:03:58.700' ORDER BY ID DESC
  
  **Table Structure**
  /****** Object:  Table [dbo].[trackingsHistory]    Script Date: 07/25/2018 13:22:40 ******/
  SET ANSI_NULLS ON
  GO
  
  SET QUOTED_IDENTIFIER ON
  GO
  
  CREATE TABLE [dbo].[trackingsHistory](
        [id] [int] IDENTITY(1,1) NOT NULL,
        [customer_id] [int] NULL,
        [master_tracking_no] [nvarchar](30) NULL,
        [carrier] [nvarchar](50) NOT NULL,
        [voided] [bit] NOT NULL,
        [origin] [nvarchar](250) NULL,
        [destination] [nvarchar](250) NOT NULL,
        [service_type] [nvarchar](50) NULL,
        [bookedin_date] [datetime] NOT NULL,
        [package_type] [nvarchar](50) NOT NULL,
        [pieces] [tinyint] NOT NULL,
        [total_weight] [decimal](18, 2) NOT NULL,
        [weight_unit] [int] NULL,
        [total_dim_weight] [decimal](18, 2) NOT NULL,
        [scheduled] [nvarchar](50) NULL,
        [quoted_base] [decimal](18, 2) NOT NULL,
        [quoted_fuel] [decimal](18, 2) NOT NULL,
        [quoted_vat] [decimal](18, 2) NOT NULL,
        [quoted_total] [decimal](18, 2) NOT NULL,
        [quoted_insurance] [decimal](18, 2) NOT NULL,
        [quoted_additionalhandlingsurcharge] [decimal](18, 2) NOT NULL,
        [quoted_administrationcharge] [decimal](18, 2) NOT NULL,
        [quoted_reseller_markup_percentage] [decimal](18, 2) NOT NULL,
        [quoted_reseller_markup] [decimal](18, 2) NOT NULL,
        [insurance] [nvarchar](50) NULL,
        [shipper_company] [nvarchar](50) NULL,
        [shipper_contact] [nvarchar](50) NULL,
        [shipper_vat_no] [nvarchar](50) NULL,
        [terms_of_trade] [nvarchar](50) NULL,
        [destination_country] [nvarchar](50) NULL,
        [receiver_company] [nvarchar](50) NULL,
        [receiver_contact] [nvarchar](50) NULL,
        [receiver_vat_no] [nvarchar](50) NULL,
        [shipment_contents] [nvarchar](2000) NULL,
        [reason_for_export] [nvarchar](50) NULL,
        [commercial_invoice] [bit] NOT NULL,
        [shipment_reference] [nvarchar](50) NULL,
        [currency_sign] [nvarchar](10) NULL,
        [dispatch_id] [int] NULL,
        [payment_transaction_id] [nvarchar](100) NULL,
        [service_id] [int] NULL,
        [importTracking_id] [int] NULL,
        [origin_email] [nvarchar](50) NULL,
        [origin_phone] [nvarchar](50) NULL,
        [dest_email] [nvarchar](50) NULL,
        [dest_phone] [nvarchar](50) NULL,
        [quoted_extareasurcharge] [decimal](18, 2) NOT NULL,
        [quoted_extarea_fuel_surcharge] [decimal](18, 2) NOT NULL,
        [payment_method_used] [nvarchar](10) NULL,
        [paymentmode] [nvarchar](10) NULL,
        [WORef] [nvarchar](50) NULL,
        [quoted_residential_charge] [decimal](18, 2) NOT NULL,
        [quoted_residential_fuel_surcharge] [decimal](18, 2) NOT NULL,
        [transportation_payer] [int] NULL,
        [subscription_type] [tinyint] NULL,
        [quoted_markup] [decimal](18, 2) NOT NULL,
        [shipment_status] [nvarchar](500) NULL,
        [shipment_status_error] [nvarchar](500) NULL,
        [is_shipment_delivered] [bit] NULL,
        [is_thermal_print] [bit] NULL,
        [watch_status] [bit] NULL,
        [account_no] [nvarchar](10) NULL,
        [quoted_largepackagesurcharge] [decimal](18, 2) NOT NULL,
        [quoted_overmax_size] [decimal](18, 2) NOT NULL,
        [quoted_overmax_weight] [decimal](18, 2) NOT NULL,
        [origin_country] [nvarchar](125) NULL,
        [quoted_largepackage_fuel_surcharge] [decimal](18, 2) NOT NULL,
        [quoted_premium_timed_charge] [decimal](18, 2) NOT NULL,
        [quoted_restricted_destination] [decimal](18, 2) NOT NULL,
        [quoted_exporter_validation] [decimal](18, 2) NOT NULL,
        [quoted_elevated_risk] [decimal](18, 2) NOT NULL,
        [shipment_type] [tinyint] NOT NULL,
        [quoted_misc_charge] [decimal](18, 2) NOT NULL,
        [last_updated_date] [datetime] NULL,
        [reseller_id] [int] NULL,
        [sales_person_id] [int] NULL,
        [multi_site_id] [int] NULL,
        [acs_group_id] [int] NULL,
        [franchise_acs_group_id] [int] NULL,
        [sk_commission] [decimal](18, 2) NULL,
  CONSTRAINT [PK_tracking] PRIMARY KEY CLUSTERED 
  (
        [id] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]
  )
  
  GO
  
  EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'True value will add shipment to watch list and false will remove from watch list.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'trackingsHistory', @level2type=N'COLUMN',@level2name=N'watch_status'
  GO
  
  ALTER TABLE [dbo].[trackingsHistory]  WITH CHECK ADD  CONSTRAINT [FK_trackingsHistory_customers] FOREIGN KEY([customer_id])
  REFERENCES [dbo].[customers] ([id])
  GO
  
  ALTER TABLE [dbo].[trackingsHistory] CHECK CONSTRAINT [FK_trackingsHistory_customers]
  GO
  
  ALTER TABLE [dbo].[trackingsHistory]  WITH CHECK ADD  CONSTRAINT [FK_trackingsHistory_dispatches] FOREIGN KEY([dispatch_id])
  REFERENCES [dbo].[dispatches] ([id])
  GO
  
  ALTER TABLE [dbo].[trackingsHistory] CHECK CONSTRAINT [FK_trackingsHistory_dispatches]
  GO
  
  ALTER TABLE [dbo].[trackingsHistory]  WITH CHECK ADD  CONSTRAINT [FK_trackingsHistory_importTrackings] FOREIGN KEY([importTracking_id])
  REFERENCES [dbo].[importTrackings] ([id])
  GO
  
  ALTER TABLE [dbo].[trackingsHistory] CHECK CONSTRAINT [FK_trackingsHistory_importTrackings]
  GO
  
  ALTER TABLE [dbo].[trackingsHistory]  WITH CHECK ADD  CONSTRAINT [FK_trackingsHistory_multisite] FOREIGN KEY([multi_site_id])
  REFERENCES [dbo].[customers] ([id])
  GO
  
  ALTER TABLE [dbo].[trackingsHistory] CHECK CONSTRAINT [FK_trackingsHistory_multisite]
  GO
  
  ALTER TABLE [dbo].[trackingsHistory]  WITH CHECK ADD  CONSTRAINT [FK_trackingsHistory_reseller] FOREIGN KEY([reseller_id])
  REFERENCES [dbo].[customers] ([id])
  GO
  
  ALTER TABLE [dbo].[trackingsHistory] CHECK CONSTRAINT [FK_trackingsHistory_reseller]
  GO
  
  ALTER TABLE [dbo].[trackingsHistory]  WITH CHECK ADD  CONSTRAINT [FK_trackingsHistory_sales] FOREIGN KEY([sales_person_id])
  REFERENCES [dbo].[customers] ([id])
  GO
  
  ALTER TABLE [dbo].[trackingsHistory] CHECK CONSTRAINT [FK_trackingsHistory_sales]
  GO
  
  ALTER TABLE [dbo].[trackingsHistory] ADD  CONSTRAINT [DF_trackingsHistory_quoted_base]  DEFAULT ((0)) FOR [quoted_base]
  GO
  
  ALTER TABLE [dbo].[trackingsHistory] ADD  CONSTRAINT [DF_trackingsHistory_quoted_fuel]  DEFAULT ((0)) FOR [quoted_fuel]
  GO
  
  ALTER TABLE [dbo].[trackingsHistory] ADD  CONSTRAINT [DF_trackingsHistory_quoted_vat]  DEFAULT ((0)) FOR [quoted_vat]
  GO
  
  ALTER TABLE [dbo].[trackingsHistory] ADD  CONSTRAINT [DF_trackingsHistory_quoted_total]  DEFAULT ((0)) FOR [quoted_total]
  GO
  
  ALTER TABLE [dbo].[trackingsHistory] ADD  CONSTRAINT [DF_trackingsHistory_quoted_insurance]  DEFAULT ((0)) FOR [quoted_insurance]
  GO
  
  ALTER TABLE [dbo].[trackingsHistory] ADD  CONSTRAINT [DF_trackingsHistory_quoted_additionalhandlingsurcharge]  DEFAULT ((0)) FOR [quoted_additionalhandlingsurcharge]
  GO
  
  ALTER TABLE [dbo].[trackingsHistory] ADD  CONSTRAINT [DF_trackingsHistory_quoted_additionalhandlingsurcharge1]  DEFAULT ((0)) FOR [quoted_administrationcharge]
  GO
  
  ALTER TABLE [dbo].[trackingsHistory] ADD  CONSTRAINT [DF_trackingsHistory_quoted_reseller_markup_percentage]  DEFAULT ((0)) FOR [quoted_reseller_markup_percentage]
  GO
  
  ALTER TABLE [dbo].[trackingsHistory] ADD  CONSTRAINT [DF_trackingsHistory_quoted_reseller_markup]  DEFAULT ((0)) FOR [quoted_reseller_markup]
  GO
  
  ALTER TABLE [dbo].[trackingsHistory] ADD  CONSTRAINT [DF__trackings__quote__14270015]  DEFAULT ((0)) FOR [quoted_extareasurcharge]
  GO
  
  ALTER TABLE [dbo].[trackingsHistory] ADD  CONSTRAINT [DF_trackingsHistory_quoted_extarea_fuel_surcharge]  DEFAULT ((0)) FOR [quoted_extarea_fuel_surcharge]
  GO
  
  ALTER TABLE [dbo].[trackingsHistory] ADD  CONSTRAINT [DF__trackings__quote__17036CC0]  DEFAULT ((0)) FOR [quoted_residential_charge]
  GO
  
  ALTER TABLE [dbo].[trackingsHistory] ADD  CONSTRAINT [DF_trackingsHistory_quoted_residential_fuel_surcharge]  DEFAULT ((0)) FOR [quoted_residential_fuel_surcharge]
  GO
  
  ALTER TABLE [dbo].[trackingsHistory] ADD  CONSTRAINT [DF_trackingsHistory_quoted_markup]  DEFAULT ((0)) FOR [quoted_markup]
  GO
  
  ALTER TABLE [dbo].[trackingsHistory] ADD  CONSTRAINT [DF__trackings__quote__55FFB06A]  DEFAULT ((0)) FOR [quoted_largepackagesurcharge]
  GO
  
  ALTER TABLE [dbo].[trackingsHistory] ADD  CONSTRAINT [DF__trackings__quote__25E688F4]  DEFAULT ((0)) FOR [quoted_overmax_size]
  GO
  
  ALTER TABLE [dbo].[trackingsHistory] ADD  CONSTRAINT [DF__trackings__quote__26DAAD2D]  DEFAULT ((0)) FOR [quoted_overmax_weight]
  GO
  
  ALTER TABLE [dbo].[trackingsHistory] ADD  CONSTRAINT [DF__trackings__quote__5F3F01E1]  DEFAULT ((0)) FOR [quoted_largepackage_fuel_surcharge]
  GO
  
  ALTER TABLE [dbo].[trackingsHistory] ADD  CONSTRAINT [DF_trackingsHistory_quoted_premium_timed_charge]  DEFAULT ((0)) FOR [quoted_premium_timed_charge]
  GO
  
  ALTER TABLE [dbo].[trackingsHistory] ADD  CONSTRAINT [DF__trackings__quote__22DFFF17]  DEFAULT ((0)) FOR [quoted_restricted_destination]
  GO
  
  ALTER TABLE [dbo].[trackingsHistory] ADD  CONSTRAINT [DF__trackings__quote__23D42350]  DEFAULT ((0)) FOR [quoted_exporter_validation]
  GO
  
  ALTER TABLE [dbo].[trackingsHistory] ADD  CONSTRAINT [DF__trackings__quote__6A669BCA]  DEFAULT ((0.00)) FOR [quoted_elevated_risk]
  GO
  
  ALTER TABLE [dbo].[trackingsHistory] ADD  CONSTRAINT [DF_Constraint]  DEFAULT ((0)) FOR [shipment_type]
  GO
  
  ALTER TABLE [dbo].[trackingsHistory] ADD  DEFAULT ((0)) FOR [quoted_misc_charge]
  GO
  
  ALTER TABLE [dbo].[trackingsHistory] ADD  DEFAULT (getdate()) FOR [last_updated_date]
  GO
  </code></pre>