September 12, 2014 at 5:57 am
Hi Friends,
In my table have a 5000000 records and table have 60 columus and i created one primary key and 30 columns non-clusterd index.but i run the store procedure it take 20min like my sp is
create PROCEDURE sp_members
@fromDate datetime = null,
@toDate datetime = null
AS
BEGIN
SELECT *
FROM td_member J
where J.CreatedDateTime between @fromDate and @toDate
and (J.IsDeleted=0 OR J.ISDELETED IS Null)and j.IsActive = '1'
END
i pass the date between 2 days it run past.when pass the last one month it take 30 min.please help me
September 12, 2014 at 6:00 am
Please post table definitions, index definitions and execution plan.
Is that SELECT * really necessary or do you just need a subset of the columns?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 12, 2014 at 6:17 am
Table
Idintno410 0 no(n/a)(n/a)NULL
Titlenvarcharno1000 no(n/a)(n/a)Latin1_General_CI_AS
CompanyNamenvarcharno1000 no(n/a)(n/a)Latin1_General_CI_AS
Salaryintno410 0 no(n/a)(n/a)NULL
CountryIdintno410 0 no(n/a)(n/a)NULL
JobDescriptionnvarcharno-1 no(n/a)(n/a)Latin1_General_CI_AS
MinExperienceintno410 0 no(n/a)(n/a)NULL
MaxExperienceintno410 0 yes(n/a)(n/a)NULL
JobTypeIdintno410 0 yes(n/a)(n/a)NULL
IndustryIdintno410 0 yes(n/a)(n/a)NULL
SectorIdintno410 0 yes(n/a)(n/a)NULL
DesiredCandidateProfilevarcharno5000 nononoLatin1_General_CI_AS
AboutYourCompanynvarcharno-1 yes(n/a)(n/a)Latin1_General_CI_AS
IsCompanyLogobitno1 no(n/a)(n/a)NULL
UserIdintno410 0 no(n/a)(n/a)NULL
PhotoPath1intno410 0 yes(n/a)(n/a)NULL
PhotoPath2intno410 0 yes(n/a)(n/a)NULL
PhotoPath3intno410 0 yes(n/a)(n/a)NULL
PhotoPath4intno410 0 yes(n/a)(n/a)NULL
PhotoPath5intno410 0 yes(n/a)(n/a)NULL
PhotoThumbPath1intno410 0 yes(n/a)(n/a)NULL
PhotoThumbPath2intno410 0 yes(n/a)(n/a)NULL
PhotoThumbPath3intno410 0 yes(n/a)(n/a)NULL
PhotoThumbPath4intno410 0 yes(n/a)(n/a)NULL
PhotoThumbPath5intno410 0 yes(n/a)(n/a)NULL
ReceiveApplicantsvarcharno5000 yesnoyesLatin1_General_CI_AS
ExternalSiteApplicantvarcharno1000 yesnoyesLatin1_General_CI_AS
TotalViewsintno410 0 yes(n/a)(n/a)NULL
CreatedDateTimedatetimeno8 yes(n/a)(n/a)NULL
SkillIdintno410 0 yes(n/a)(n/a)NULL
IsActivebitno1 yes(n/a)(n/a)NULL
LocationNamevarcharno200 yesnoyesLatin1_General_CI_AS
SalaryCurrencyIdintno410 0 yes(n/a)(n/a)NULL
IsDeletedbitno1 yes(n/a)(n/a)NULL
IsRssJobbitno1 yes(n/a)(n/a)NULL
ReferenceNumbernvarcharno-1 yes(n/a)(n/a)Latin1_General_CI_AS
Urlnvarcharno2000 yes(n/a)(n/a)Latin1_General_CI_AS
IsShinebitno1 yes(n/a)(n/a)NULL
IsNaukribitno1 yes(n/a)(n/a)NULL
IsTotalbitno1 yes(n/a)(n/a)NULL
IsReedbitno1 yes(n/a)(n/a)NULL
IsSeekbitno1 yes(n/a)(n/a)NULL
CompanyLogoIdintno410 0 yes(n/a)(n/a)NULL
IsOdeskbitno1 yes(n/a)(n/a)NULL
IsCompanyRssbitno1 yes(n/a)(n/a)NULL
IsCatererbitno1 yes(n/a)(n/a)NULL
IsDicebitno1 yes(n/a)(n/a)NULL
IsDirectbitno1 yes(n/a)(n/a)NULL
IsCareesmabitno1 yes(n/a)(n/a)NULL
IsRezidorbitno1 yes(n/a)(n/a)NULL
IsIhgbitno1 yes(n/a)(n/a)NULL
IsIndeedbitno1 yes(n/a)(n/a)NULL
IsKempinskibitno1 yes(n/a)(n/a)NULL
IsSercobitno1 yes(n/a)(n/a)NULL
IsTajbitno1 yes(n/a)(n/a)NULL
Index
IX_CompanyNamenonclustered located on PRIMARYCompanyName
IX_CreatedDateTimenonclustered located on PRIMARYCreatedDateTime
IX_IsActivenonclustered located on PRIMARYIsActive
IX_IsCareesmanonclustered located on PRIMARYIsCareesma
IX_IsCaterernonclustered located on PRIMARYIsCaterer
IX_IsCompanyRssnonclustered located on PRIMARYIsCompanyRss
IX_IsDicenonclustered located on PRIMARYIsDice
IX_IsDirectnonclustered located on PRIMARYIsDirect
IX_IsIhgnonclustered located on PRIMARYIsIhg
IX_IsIndeednonclustered located on PRIMARYIsIndeed
IX_IsKempinskinonclustered located on PRIMARYIsKempinski
IX_IsNaukrinonclustered located on PRIMARYIsNaukri
IX_IsOdesknonclustered located on PRIMARYIsOdesk
IX_IsReednonclustered located on PRIMARYIsReed
IX_IsRezidornonclustered located on PRIMARYIsRezidor
IX_IsRssJobnonclustered located on PRIMARYIsRssJob
IX_IsSeeknonclustered located on PRIMARYIsSeek
IX_IsSercononclustered located on PRIMARYIsSerco
IX_IsShinenonclustered located on PRIMARYIsShine
IX_IsTajnonclustered located on PRIMARYIsTaj
IX_IsTotalnonclustered located on PRIMARYIsTotal
IX_Jobnonclustered located on PRIMARYTitle
IX_Job_CompanyNamenonclustered located on PRIMARYCompanyName
IX_Job_CountryIdnonclustered located on PRIMARYCountryId
IX_JOB_Industrynonclustered located on PRIMARYIndustryId
IX_Job_IsActivenonclustered located on PRIMARYIsActive
IX_Job_IsDeletednonclustered located on PRIMARYIsDeleted
IX_JOB_JobTypeIdnonclustered located on PRIMARYJobTypeId
IX_JOB_Locationnonclustered located on PRIMARYLocationName
IX_Job_LocationNamenonclustered located on PRIMARYLocationName
IX_JOB_MaxExperiencenonclustered located on PRIMARYMaxExperience
IX_JOB_MinExperiencenonclustered located on PRIMARYMinExperience
IX_JOB_SKILLnonclustered located on PRIMARYSkillId
IX_JOB_Titlenonclustered located on PRIMARYTitle
IX_Job_UserIdnonclustered located on PRIMARYUserId
PK_Jobclustered, unique, primary key located on PRIMARYId
September 12, 2014 at 6:34 am
polo.csit (9/12/2014)
TableIdintno410 0 no(n/a)(n/a)NULL
Titlenvarcharno1000 no(n/a)(n/a)Latin1_General_CI_AS
CompanyNamenvarcharno1000 no(n/a)(n/a)Latin1_General_CI_AS
.
.
.
Index
IX_CompanyNamenonclustered located on PRIMARYCompanyName
IX_CreatedDateTimenonclustered located on PRIMARYCreatedDateTime
IX_IsActivenonclustered located on PRIMARYIsActive
IX_IsCareesmanonclustered located on PRIMARYIsCareesma
IX_IsCaterernonclustered located on PRIMARYIsCaterer
.
.
.
These aren't index and table definitions. Please read the article linked in my sig "please read this".
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 12, 2014 at 8:38 am
You need to look to your execution plan to understand how it's using the indexes you've created.
From the looks of things, you have an index on a bit column, IsActive. Throw it away. On a guess, an index with a compound key of CreatedDateTime, IsActive, and IsDeleted would work better than most of your other indexes. But that's a total guess.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply