Slow Select query in SQL with table having 2 million records only

Question

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 2.2 million so it should render very fast.

It would be really helpful if you could add your valuable inputs for this.

Selecting records should be very fast and render within seconds as table records are less and database size is just 12 GB.

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.

(1 row(s) affected)

SQL Server Execution Times: CPU time = 2657 ms, elapsed time = 9737 ms."

**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

Answer 1

Two things, that will speed up your query:

1. Create clustered index on Id, so ordering isn't done by the query, the table will be stored ordered already by that column (so ordering will be much, much faster by indexed column, in this case Id).

2. Create non-clustered index on bookedin_date, it will speed up filtering by that column (in WHERE clause).

Answer 2

In SQL Server, the page size is 8 KB. Even if your collation is ASCII, each rows of your table consumes more than that capacity. This means even you create good indexes, and boost the performance on where clauses, SQL‌ Server has delay in extracting data from leaf nodes. on the other hand, when a table has too many columns, you most probably need to redesign it.
Any way you have to solutions:
1) Create an index for your query which all columns you need are attached to it as "included" columns. 2) split your table into 2 or more tables

Answer 3

row count in the table is quite less just 2.2 million so it should render very fast.

Reading 2.2 million rows is not a big deal, but sending them to a client and "rendering" them can be expensive, especially for a table with so many columns. Why are you sending so many rows and columns to the client?

Anyway, to optimize the query processing, try storing this table as a clustered Columnstore instead of an uncompressed heap. Eg:

create clustered columnstore index cci_trackingsHistory on [trackingsHistory]

This will both make the table much smaller, reducing the IO to read it. But it won't make sending the rows over the network or client-side processing any faster.

Another design that would be better is to make the PK clustered, since you're requesting the rows in PK order. With a non-clustered PK SQL has to either sort all the rows, or do bookmark lookups for each row.