Data Access from single table in sql server 2005 is too slow
- by Muhammad Kashif Nadeem
Following is the script of table. Accessing data from this table is too slow.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Emails](
[id] [int] IDENTITY(1,1) NOT NULL,
[datecreated] [datetime] NULL CONSTRAINT [DF_Emails_datecreated]
DEFAULT (getdate()),
[UID] [nvarchar](250) COLLATE Latin1_General_CI_AS NULL,
[From] [nvarchar](100) COLLATE Latin1_General_CI_AS NULL,
[To] [nvarchar](100) COLLATE Latin1_General_CI_AS NULL,
[Subject] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Body] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[HTML] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[AttachmentCount] [int] NULL,
[Dated] [datetime] NULL
) ON [PRIMARY]
Following query takes 50 seconds to fetch data.
select id, datecreated, UID, [From], [To], Subject, AttachmentCount,
Dated from emails
If I include Body and Html in select then time is event worse.
indexes are on:
id unique clustered
From Non unique non clustered
To Non unique non clustered
Tabls has currently 180000+ records.
There might be 100,000 records each month so this will become more slow as time will pass.
Does splitting data into two table will solve the problem?
What other indexes should be there?
