Hi,
I am having a table with 40 columns and it contains 4 million records. I got the results for one year in 40 secs. After tuning, it is retuning in 24 secs( what i have done is i created index on order by fields).
Can you please suggest me in which way I can increase the performance.
Note: I am using only one table with Primary Key.
Thanks
Dinesh
Hi,
I am having a table with 40 columns and it contains 4 million records. I got the results for one year in 40 secs (as date parameter). After tuning, it is retuning in 24 secs( what i have done is i created index on order by fields).
Can you please suggest me in which way I can increase the performance.
Note: I am using only one table with Primary Key.
Thanks
Dinesh
post back the table script and the query ... You might want to create covering index.
Madhu
Hi,
The table script and the query is shown below.
Table Script
------------------
CREATE TABLE [dbo].[ActivityDetailsWithPaymentReport](
[AFTID] [int] NOT NULL,
[ActivityID] [int] NULL,
[FacultyID] [int] NULL,
[ActivityIdentity] [varchar](13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ActivityStatusKey] [int] NULL,
[ActivityStatus] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ActivityTypeKey] [int] NULL,
[ActivityType] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ActivitydescriptionKey] [int] NULL,
[Activitydescription] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BrandKey] [int] NULL,
[BrandDiseaseState] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SupplierKey] [int] NULL,
[SupplierName] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Occuredby] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Occuredwhen] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AddedBy] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AddedWhen] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Cancelledby] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Cancelledwhen] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AssignedTo] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BudgetApprovedWhen] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BudgetStatus] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CheckNumber] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CheckAmount] [money] NULL,
[CheckDate] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CheckStatusKey] [int] NULL,
[CheckStatus] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CheckTypeKey] [int] NULL,
[CheckType] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CostCentre] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CostElement] [int] NULL,
[Country] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DocOnFileChecked] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DocOnFileCheckedWhen] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ActivityEndDate] [datetime] NULL,
[ReportEndDate] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FacultyFirstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FacultyLastName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FacultyName] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IONumber] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LeadRepresentative] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LocationCity] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LocationName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LocationState] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CheckMailDate] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FacultyCount] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OusPayments] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PaymentType] [int] NULL,
[ActivityStartDate] [datetime] NULL,
[ReportStartDate] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TOPIC] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PWInternal] [int] NULL,
[SBStatus] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Credentials1] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Speciality1] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TierLevel] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[W9OnFile] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Affiliation] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ContractOnFile] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LeadRepresentativeVoiceMail] [varchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SBEventNumber] [int] NULL,
[ProductsListedFor] [varchar](8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AnnualContract] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SequenceID] [int] NULL,
CONSTRAINT [PK_ActivityDetailsWithPaymentReport] PRIMARY KEY CLUSTERED
(
[AFTID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Query to retrieve the fields from the table
-----------------------------------------------------------
SELECT ActivityIdentity AS ActivityNumber, ActivityType, ActivityDescription, ActivityStatus,
ReportStartDate AS StartDate, ReportEndDate AS EndDate, LocationName, LocationCity, LocationState,
Country, BrandDiseaseState, SupplierName, PaymentType, CheckNumber, CheckStatus,
FacultyName, FacultyID AS FacultyKey, FacultyCount, CheckDate, CheckAmount, CostCentre AS CostCentres,
CheckMailDate AS MailDate, CheckType, AddedBy, AddedWhen, Occuredby, Occuredwhen,
Cancelledby, Cancelledwhen, DocOnFileChecked, DocOnFileCheckedWhen, AnnualContract,
AssignedTo, OusPayments, LeadRepresentative, BudgetStatus, BudgetApprovedWhen, CostElement,
IONumber, Topic
FROM dbo.ActivityDetailsWithPaymentReport
WHERE ActivityStartDate
>= '01/01/2007' AND ActivityEndDate <= '12/31/2007'
Thanks
Dinesh
Try to make index(es) on the field(s) that participate in WHERE clause (i guess you have WHERE ADateColumn=YourOneYear). And verify if Primary Key have a clustered index.
Yes, i am having date column as parameter and also created index on the date column. but the same result.
Thanks
Dinesh
Did you try with between clause instead of >= and <=
Also using > and < is better performed than >= and <= cluase
Let me know if make any difference.
Good luck
The query that you provided is almost certainly likely to result in a clustered index scan - even if you create nonclustered indexes on the date columns, although this does depend on the distribution of your data.
You might want to consider creating a single composite nonclustered index on your ActivityStartDate and ActivityEndDate columns and 'include' all other columns used in the query in your index. If you do this then experiment with the order of the ActivityStartDate and ActivityEndDate columns within the index to see which order performs best.
Alternatively you could change your clustered index to be a composite clustered index on the two date columns (again, experiment with the ordering of the date columns within the index), however this could have an impact on the performance of inserts, updates and deletes so you should test this out before putting any changes into production.
Chris