May 9, 2008 at 1:33 am
Hello everyone, I m using SQL Server2005, I have a Complex view which contains lot of tables and Inner, Right and Left Outer Join used, This View Returns the approx 2 lac rows and takes processing time is 36 secs, Kindly tell me what can i do ? i mean to say Create Index View or create small indexes on this view for good performance?
And tell me which type of Index used and on which column?
Reply me asap, Thanx in Advance.
May 9, 2008 at 1:43 am
Can you post little bit more informations, like the View Statement or table structures
w. lengenfeler
May 9, 2008 at 1:58 am
Here is my View:
SELECT TOP (100) PERCENT dbo.TvsRecords.TvsRecordID, dbo.TvsRecords.TvsFormNo, dbo.TvsRecords.SystemFormNo AS ScanID,
dbo.TvsFormStatusCodes.TvsFormStatusName, dbo.TvsRecords.CreateDate, dbo.TvsRecords.LastUpdate,
dbo.SystemUsers.FullName AS OperatorName, dbo.SystemUsers.Address2 AS OperatorShift, SystemUsers_1.FullName AS QCName,
SystemUsers_1.Address2 AS QCShift, dbo.TvsRecords.TvsFormDate, dbo.TvsRecords.CNIC1, dbo.TvsRecords.CNIC2, dbo.TvsRecords.CNIC3,
dbo.TvsRecords.CNIC4, dbo.TvsRecords.CNICFalimyNumber, dbo.TvsRecords.TotalFamilyMembers,
COUNT(dbo.TvsFamilyMembers.TvsFamilyMemberID) AS FamilyMemberRecords, dbo.TvsRecords.AidRcvdErraCashGrant,
dbo.TvsRecords.AidRcvdErraHousingGrant, dbo.TvsRecords.AidRcvdPakBaitulmal, dbo.TvsRecords.AidRcvdZakatProgram,
dbo.TvsRecords.AidRcvdFromGovt, dbo.TvsRecords.AidRcvdFromOrganization, dbo.TvsRecords.AidRcvdForeignAid, dbo.TvsRecords.AccountInfoTitle,
dbo.TvsRecords.AccountInfoAccountNo, dbo.Provinces.ProvinceName, dbo.Districts.DistrictName, dbo.Tehsils.TehsilName, dbo.Villages.VillageName,
dbo.TvsRecords.PostOffice, dbo.TvsRecordBankNames.BankName, dbo.TvsRecordBranchInfo.BranchName, dbo.TvsFamilyMembers.MemberName,
dbo.Gender.Description AS Gender, dbo.TvsFamilyMembers.Age, dbo.Relationships.RelationshipName,
dbo.MaritalStatus.StatusName AS MaritalStatus, dbo.EducationDiscontinued.Description AS EducationDiscontinued,
dbo.QualificationGrades.GradeName AS QualificationGrade, dbo.AidReceiveds.AidReceivedName AS AidReceived,
dbo.VulnerabilityTypes.VulnerabilityName, dbo.DisabilityTypes.DisabilityName AS DisabilityTypeBefore,
DisabilityTypes_1.DisabilityName AS DisabilityTypeAfter, dbo.ParentInformations.ParentInfo AS ParentInfoBefore,
ParentInformations_1.ParentInfo AS ParentInfoAfter, dbo.ResidanceTypes.Name AS ResidenceTypeBefore,
ResidanceTypes_1.Name AS ResidenceTypeAfter, dbo.Skills.SkillName AS HaveSkills, Skills_1.SkillName AS SeekingSkills,
dbo.DependancyTypes.DepandancyName AS DepandancyType, dbo.TvsRecords.OpConfirmDate, dbo.TvsRecords.QcConfirmDate,
dbo.QualificationGrades.QualificationGradeID, dbo.EducationDiscontinued.EducationDiscontinuedID, dbo.MaritalStatus.MaritalStatusID,
dbo.Gender.GenderID, dbo.Relationships.RelationshipID, dbo.AidReceiveds.AidReceivedID, dbo.DependancyTypes.DependancyType,
dbo.VulnerabilityTypes.VulnerabilityType, dbo.ParentInformations.ParentInfoID AS ParentInfoBeforeID,
ParentInformations_1.ParentInfoID AS ParentInfoAfterID, DisabilityTypes_1.DisabilityType AS DisabilityTypeAfterID,
dbo.DisabilityTypes.DisabilityType AS DisabilityTypeBeforeID, dbo.ResidanceTypes.ResidenceType AS ResidenceTypeBeforeID,
ResidanceTypes_1.ResidenceType AS ResidenceTypeAfterID, dbo.Skills.SkillID AS HaveSkillsID, Skills_1.SkillID AS SeekingSkillsID,
dbo.TvsFamilyMembers.MemberNo, dbo.Provinces.ProvinceID, dbo.Districts.DistrictID, dbo.Tehsils.TehsilID, dbo.UnionCouncils.UnionCouncilID,
dbo.UnionCouncils.UnionCouncilName, dbo.Villages.VillageID, dbo.TvsRecordBranchInfo.BranchID, dbo.TvsRecordBankNames.BankID,
dbo.TvsFamilyMembers.TvsFamilyMemberID, dbo.TvsRecords.TvsFormStatusCode, dbo.TvsRecords.CorrectionsCount,
dbo.TvsRecords.TvsRecordBatchID, dbo.TvsRecordBranchInfo.BranchCode, dbo.TvsRecords.EntryOperatorUserID, dbo.TvsRecords.QCUserID
FROM dbo.SystemUsers RIGHT OUTER JOIN
dbo.TvsRecords INNER JOIN
dbo.TvsFormStatusCodes ON dbo.TvsRecords.TvsFormStatusCode = dbo.TvsFormStatusCodes.TvsFormStatusCode ON
dbo.SystemUsers.SystemUserID = dbo.TvsRecords.EntryOperatorUserID LEFT OUTER JOIN
dbo.UnionCouncils ON dbo.TvsRecords.UnionCouncilID = dbo.UnionCouncils.UnionCouncilID LEFT OUTER JOIN
dbo.Provinces ON dbo.TvsRecords.ProvinceID = dbo.Provinces.ProvinceID LEFT OUTER JOIN
dbo.Districts ON dbo.TvsRecords.DistrictID = dbo.Districts.DistrictID LEFT OUTER JOIN
dbo.TvsRecordBranchInfo ON dbo.TvsRecords.BranchID = dbo.TvsRecordBranchInfo.BranchID LEFT OUTER JOIN
dbo.TvsRecordBankNames ON dbo.TvsRecords.BankID = dbo.TvsRecordBankNames.BankID LEFT OUTER JOIN
dbo.Villages ON dbo.TvsRecords.VillageID = dbo.Villages.VillageID LEFT OUTER JOIN
dbo.SystemUsers AS SystemUsers_1 ON dbo.TvsRecords.QCUserID = SystemUsers_1.SystemUserID LEFT OUTER JOIN
dbo.Tehsils ON dbo.TvsRecords.TehsilID = dbo.Tehsils.TehsilID FULL OUTER JOIN
dbo.QualificationGrades RIGHT OUTER JOIN
dbo.TvsFamilyMembers ON dbo.QualificationGrades.QualificationGradeID = dbo.TvsFamilyMembers.QualificationGradeID LEFT OUTER JOIN
dbo.DisabilityTypes AS DisabilityTypes_1 ON dbo.TvsFamilyMembers.DisabilityTypeAfter = DisabilityTypes_1.DisabilityType LEFT OUTER JOIN
dbo.VulnerabilityTypes ON dbo.TvsFamilyMembers.VulnerabilityType = dbo.VulnerabilityTypes.VulnerabilityType LEFT OUTER JOIN
dbo.ParentInformations ON dbo.TvsFamilyMembers.ParentInfoIDBefore = dbo.ParentInformations.ParentInfoID LEFT OUTER JOIN
dbo.Skills ON dbo.TvsFamilyMembers.SkillID = dbo.Skills.SkillID LEFT OUTER JOIN
dbo.ParentInformations AS ParentInformations_1 ON dbo.TvsFamilyMembers.ParentInfoIDAfter = ParentInformations_1.ParentInfoID LEFT OUTER JOIN
dbo.ResidanceTypes ON dbo.TvsFamilyMembers.ResidenceTypeBefore = dbo.ResidanceTypes.ResidenceType LEFT OUTER JOIN
dbo.DependancyTypes ON dbo.TvsFamilyMembers.DependancyType = dbo.DependancyTypes.DependancyType LEFT OUTER JOIN
dbo.Relationships ON dbo.TvsFamilyMembers.RelationshipID = dbo.Relationships.RelationshipID LEFT OUTER JOIN
dbo.Gender ON dbo.TvsFamilyMembers.GenderID = dbo.Gender.GenderID LEFT OUTER JOIN
dbo.ResidanceTypes AS ResidanceTypes_1 ON dbo.TvsFamilyMembers.ResidenceTypeAfter = ResidanceTypes_1.ResidenceType LEFT OUTER JOIN
dbo.Skills AS Skills_1 ON dbo.TvsFamilyMembers.SeekingSkillID = Skills_1.SkillID LEFT OUTER JOIN
dbo.DisabilityTypes ON dbo.TvsFamilyMembers.DisabilityTypeBefore = dbo.DisabilityTypes.DisabilityType LEFT OUTER JOIN
dbo.EducationDiscontinued ON
dbo.TvsFamilyMembers.EducationDiscontinuedID = dbo.EducationDiscontinued.EducationDiscontinuedID LEFT OUTER JOIN
dbo.AidReceiveds ON dbo.TvsFamilyMembers.AidReceivedID = dbo.AidReceiveds.AidReceivedID LEFT OUTER JOIN
dbo.MaritalStatus ON dbo.TvsFamilyMembers.MaritalStatusID = dbo.MaritalStatus.MaritalStatusID ON
dbo.TvsRecords.TvsRecordID = dbo.TvsFamilyMembers.TvsRecordID
GROUP BY dbo.TvsRecords.TvsRecordID, dbo.TvsRecords.TvsFormNo, dbo.TvsRecords.SystemFormNo, dbo.TvsFormStatusCodes.TvsFormStatusName,
dbo.TvsRecords.CreateDate, dbo.TvsRecords.LastUpdate, dbo.SystemUsers.FullName, dbo.SystemUsers.Address2, SystemUsers_1.FullName,
SystemUsers_1.Address2, dbo.TvsRecords.TvsFormDate, dbo.TvsRecords.CNIC1, dbo.TvsRecords.CNIC2, dbo.TvsRecords.CNIC3,
dbo.TvsRecords.CNIC4, dbo.TvsRecords.CNICFalimyNumber, dbo.TvsRecords.TotalFamilyMembers, dbo.TvsRecords.AidRcvdErraCashGrant,
dbo.TvsRecords.AidRcvdErraHousingGrant, dbo.TvsRecords.AidRcvdPakBaitulmal, dbo.TvsRecords.AidRcvdZakatProgram,
dbo.TvsRecords.AidRcvdFromGovt, dbo.TvsRecords.AidRcvdFromOrganization, dbo.TvsRecords.AidRcvdForeignAid, dbo.TvsRecords.AccountInfoTitle,
dbo.TvsRecords.AccountInfoAccountNo, dbo.Provinces.ProvinceName, dbo.Districts.DistrictName, dbo.Tehsils.TehsilName, dbo.Villages.VillageName,
dbo.TvsRecords.PostOffice, dbo.TvsRecordBankNames.BankName, dbo.TvsRecordBranchInfo.BranchName, dbo.TvsFamilyMembers.MemberName,
dbo.Gender.Description, dbo.TvsFamilyMembers.Age, dbo.Relationships.RelationshipName, dbo.MaritalStatus.StatusName,
dbo.EducationDiscontinued.Description, dbo.QualificationGrades.GradeName, dbo.AidReceiveds.AidReceivedName,
dbo.VulnerabilityTypes.VulnerabilityName, dbo.DisabilityTypes.DisabilityName, DisabilityTypes_1.DisabilityName, dbo.ParentInformations.ParentInfo,
ParentInformations_1.ParentInfo, dbo.ResidanceTypes.Name, ResidanceTypes_1.Name, dbo.Skills.SkillName, Skills_1.SkillName,
dbo.DependancyTypes.DepandancyName, dbo.TvsRecords.OpConfirmDate, dbo.TvsRecords.QcConfirmDate,
dbo.QualificationGrades.QualificationGradeID, dbo.EducationDiscontinued.EducationDiscontinuedID, dbo.MaritalStatus.MaritalStatusID,
dbo.Gender.GenderID, dbo.Relationships.RelationshipID, dbo.AidReceiveds.AidReceivedID, dbo.DependancyTypes.DependancyType,
dbo.VulnerabilityTypes.VulnerabilityType, dbo.ParentInformations.ParentInfoID, ParentInformations_1.ParentInfoID, DisabilityTypes_1.DisabilityType,
dbo.DisabilityTypes.DisabilityType, dbo.ResidanceTypes.ResidenceType, ResidanceTypes_1.ResidenceType, dbo.Skills.SkillID, Skills_1.SkillID,
dbo.TvsFamilyMembers.MemberNo, dbo.Provinces.ProvinceID, dbo.Districts.DistrictID, dbo.Tehsils.TehsilID, dbo.UnionCouncils.UnionCouncilID,
dbo.UnionCouncils.UnionCouncilName, dbo.Villages.VillageID, dbo.TvsRecordBranchInfo.BranchID, dbo.TvsRecordBankNames.BankID,
dbo.TvsFamilyMembers.TvsFamilyMemberID, dbo.TvsRecords.TvsFormStatusCode, dbo.TvsRecords.CorrectionsCount,
dbo.TvsRecords.TvsRecordBatchID, dbo.TvsRecordBranchInfo.BranchCode, dbo.TvsRecords.EntryOperatorUserID, dbo.TvsRecords.QCUserID
ORDER BY dbo.TvsRecords.TvsFormNo, ScanID
May 9, 2008 at 3:16 am
Are you pulling our legs? A 202 line query with 28 tables (LEFT JOIN, RIGHT JOIN, INNER JOIN and FULL JOIN). A group by for 85 records?
SELECTTOP (100) PERCENT
dbo.TvsRecords.TvsRecordID,
dbo.TvsRecords.TvsFormNo,
dbo.TvsRecords.SystemFormNo AS ScanID,
dbo.TvsFormStatusCodes.TvsFormStatusName,
dbo.TvsRecords.CreateDate,
dbo.TvsRecords.LastUpdate,
dbo.SystemUsers.FullName AS OperatorName,
dbo.SystemUsers.Address2 AS OperatorShift,
SystemUsers_1.FullName AS QCName,
SystemUsers_1.Address2 AS QCShift,
dbo.TvsRecords.TvsFormDate,
dbo.TvsRecords.CNIC1,
dbo.TvsRecords.CNIC2,
dbo.TvsRecords.CNIC3,
dbo.TvsRecords.CNIC4,
dbo.TvsRecords.CNICFalimyNumber,
dbo.TvsRecords.TotalFamilyMembers,
COUNT(dbo.TvsFamilyMembers.TvsFamilyMemberID) AS FamilyMemberRecords,
dbo.TvsRecords.AidRcvdErraCashGrant,
dbo.TvsRecords.AidRcvdErraHousingGrant,
dbo.TvsRecords.AidRcvdPakBaitulmal,
dbo.TvsRecords.AidRcvdZakatProgram,
dbo.TvsRecords.AidRcvdFromGovt,
dbo.TvsRecords.AidRcvdFromOrganization,
dbo.TvsRecords.AidRcvdForeignAid,
dbo.TvsRecords.AccountInfoTitle,
dbo.TvsRecords.AccountInfoAccountNo,
dbo.Provinces.ProvinceName,
dbo.Districts.DistrictName,
dbo.Tehsils.TehsilName,
dbo.Villages.VillageName,
dbo.TvsRecords.PostOffice,
dbo.TvsRecordBankNames.BankName,
dbo.TvsRecordBranchInfo.BranchName,
dbo.TvsFamilyMembers.MemberName,
dbo.Gender.Description AS Gender,
dbo.TvsFamilyMembers.Age,
dbo.Relationships.RelationshipName,
dbo.MaritalStatus.StatusName AS MaritalStatus,
dbo.EducationDiscontinued.Description AS EducationDiscontinued,
dbo.QualificationGrades.GradeName AS QualificationGrade,
dbo.AidReceiveds.AidReceivedName AS AidReceived,
dbo.VulnerabilityTypes.VulnerabilityName,
dbo.DisabilityTypes.DisabilityName AS DisabilityTypeBefore,
DisabilityTypes_1.DisabilityName AS DisabilityTypeAfter,
dbo.ParentInformations.ParentInfo AS ParentInfoBefore,
ParentInformations_1.ParentInfo AS ParentInfoAfter,
dbo.ResidanceTypes.Name AS ResidenceTypeBefore,
ResidanceTypes_1.Name AS ResidenceTypeAfter,
dbo.Skills.SkillName AS HaveSkills,
Skills_1.SkillName AS SeekingSkills,
dbo.DependancyTypes.DepandancyName AS DepandancyType,
dbo.TvsRecords.OpConfirmDate,
dbo.TvsRecords.QcConfirmDate,
dbo.QualificationGrades.QualificationGradeID,
dbo.EducationDiscontinued.EducationDiscontinuedID,
dbo.MaritalStatus.MaritalStatusID,
dbo.Gender.GenderID,
dbo.Relationships.RelationshipID,
dbo.AidReceiveds.AidReceivedID,
dbo.DependancyTypes.DependancyType,
dbo.VulnerabilityTypes.VulnerabilityType,
dbo.ParentInformations.ParentInfoID AS ParentInfoBeforeID,
ParentInformations_1.ParentInfoID AS ParentInfoAfterID,
DisabilityTypes_1.DisabilityType AS DisabilityTypeAfterID,
dbo.DisabilityTypes.DisabilityType AS DisabilityTypeBeforeID,
dbo.ResidanceTypes.ResidenceType AS ResidenceTypeBeforeID,
ResidanceTypes_1.ResidenceType AS ResidenceTypeAfterID,
dbo.Skills.SkillID AS HaveSkillsID,
Skills_1.SkillID AS SeekingSkillsID,
dbo.TvsFamilyMembers.MemberNo,
dbo.Provinces.ProvinceID,
dbo.Districts.DistrictID,
dbo.Tehsils.TehsilID,
dbo.UnionCouncils.UnionCouncilID,
dbo.UnionCouncils.UnionCouncilName,
dbo.Villages.VillageID,
dbo.TvsRecordBranchInfo.BranchID,
dbo.TvsRecordBankNames.BankID,
dbo.TvsFamilyMembers.TvsFamilyMemberID,
dbo.TvsRecords.TvsFormStatusCode,
dbo.TvsRecords.CorrectionsCount,
dbo.TvsRecords.TvsRecordBatchID,
dbo.TvsRecordBranchInfo.BranchCode,
dbo.TvsRecords.EntryOperatorUserID,
dbo.TvsRecords.QCUserID
FROMdbo.SystemUsers
RIGHT JOINdbo.TvsRecords ON dbo.SystemUsers.SystemUserID = dbo.TvsRecords.EntryOperatorUserID
INNER JOINdbo.TvsFormStatusCodes ON dbo.TvsRecords.TvsFormStatusCode = dbo.TvsFormStatusCodes.TvsFormStatusCode
LEFT JOINdbo.UnionCouncils ON dbo.TvsRecords.UnionCouncilID = dbo.UnionCouncils.UnionCouncilID
LEFT JOINdbo.Provinces ON dbo.TvsRecords.ProvinceID = dbo.Provinces.ProvinceID
LEFT JOINdbo.Districts ON dbo.TvsRecords.DistrictID = dbo.Districts.DistrictID
LEFT JOINdbo.TvsRecordBranchInfo ON dbo.TvsRecords.BranchID = dbo.TvsRecordBranchInfo.BranchID
LEFT JOINdbo.TvsRecordBankNames ON dbo.TvsRecords.BankID = dbo.TvsRecordBankNames.BankID
LEFT JOINdbo.Villages ON dbo.TvsRecords.VillageID = dbo.Villages.VillageID
LEFT JOINdbo.SystemUsers AS SystemUsers_1 ON dbo.TvsRecords.QCUserID = SystemUsers_1.SystemUserID
LEFT JOINdbo.Tehsils ON dbo.TvsRecords.TehsilID = dbo.Tehsils.TehsilID
RIGHT JOINdbo.TvsFamilyMembers ON dbo.TvsRecords.TvsRecordID = dbo.TvsFamilyMembers.TvsRecordID
FULL JOINdbo.QualificationGrades ON dbo.QualificationGrades.QualificationGradeID = dbo.TvsFamilyMembers.QualificationGradeID
LEFT JOINdbo.DisabilityTypes AS DisabilityTypes_1 ON dbo.TvsFamilyMembers.DisabilityTypeAfter = DisabilityTypes_1.DisabilityType
LEFT JOINdbo.VulnerabilityTypes ON dbo.TvsFamilyMembers.VulnerabilityType = dbo.VulnerabilityTypes.VulnerabilityType
LEFT JOINdbo.ParentInformations ON dbo.TvsFamilyMembers.ParentInfoIDBefore = dbo.ParentInformations.ParentInfoID
LEFT JOINdbo.Skills ON dbo.TvsFamilyMembers.SkillID = dbo.Skills.SkillID
LEFT JOINdbo.ParentInformations AS ParentInformations_1 ON dbo.TvsFamilyMembers.ParentInfoIDAfter = ParentInformations_1.ParentInfoID
LEFT JOINdbo.ResidanceTypes ON dbo.TvsFamilyMembers.ResidenceTypeBefore = dbo.ResidanceTypes.ResidenceType
LEFT JOINdbo.DependancyTypes ON dbo.TvsFamilyMembers.DependancyType = dbo.DependancyTypes.DependancyType
LEFT JOINdbo.Relationships ON dbo.TvsFamilyMembers.RelationshipID = dbo.Relationships.RelationshipID
LEFT JOINdbo.Gender ON dbo.TvsFamilyMembers.GenderID = dbo.Gender.GenderID
LEFT JOINdbo.ResidanceTypes AS ResidanceTypes_1 ON dbo.TvsFamilyMembers.ResidenceTypeAfter = ResidanceTypes_1.ResidenceType
LEFT JOINdbo.Skills AS Skills_1 ON dbo.TvsFamilyMembers.SeekingSkillID = Skills_1.SkillID
LEFT JOINdbo.DisabilityTypes ON dbo.TvsFamilyMembers.DisabilityTypeBefore = dbo.DisabilityTypes.DisabilityType
LEFT JOINdbo.EducationDiscontinued ON dbo.TvsFamilyMembers.EducationDiscontinuedID = dbo.EducationDiscontinued.EducationDiscontinuedID
LEFT JOINdbo.AidReceiveds ON dbo.TvsFamilyMembers.AidReceivedID = dbo.AidReceiveds.AidReceivedID
LEFT JOINdbo.MaritalStatus ON dbo.TvsFamilyMembers.MaritalStatusID = dbo.MaritalStatus.MaritalStatusID
GROUP BYdbo.TvsRecords.TvsRecordID,
dbo.TvsRecords.TvsFormNo,
dbo.TvsRecords.SystemFormNo,
dbo.TvsFormStatusCodes.TvsFormStatusName,
dbo.TvsRecords.CreateDate,
dbo.TvsRecords.LastUpdate,
dbo.SystemUsers.FullName,
dbo.SystemUsers.Address2,
SystemUsers_1.FullName,
SystemUsers_1.Address2,
dbo.TvsRecords.TvsFormDate,
dbo.TvsRecords.CNIC1,
dbo.TvsRecords.CNIC2,
dbo.TvsRecords.CNIC3,
dbo.TvsRecords.CNIC4,
dbo.TvsRecords.CNICFalimyNumber,
dbo.TvsRecords.TotalFamilyMembers,
dbo.TvsRecords.AidRcvdErraCashGrant,
dbo.TvsRecords.AidRcvdErraHousingGrant,
dbo.TvsRecords.AidRcvdPakBaitulmal,
dbo.TvsRecords.AidRcvdZakatProgram,
dbo.TvsRecords.AidRcvdFromGovt,
dbo.TvsRecords.AidRcvdFromOrganization,
dbo.TvsRecords.AidRcvdForeignAid,
dbo.TvsRecords.AccountInfoTitle,
dbo.TvsRecords.AccountInfoAccountNo,
dbo.Provinces.ProvinceName,
dbo.Districts.DistrictName,
dbo.Tehsils.TehsilName,
dbo.Villages.VillageName,
dbo.TvsRecords.PostOffice,
dbo.TvsRecordBankNames.BankName,
dbo.TvsRecordBranchInfo.BranchName,
dbo.TvsFamilyMembers.MemberName,
dbo.Gender.Description,
dbo.TvsFamilyMembers.Age,
dbo.Relationships.RelationshipName,
dbo.MaritalStatus.StatusName,
dbo.EducationDiscontinued.Description,
dbo.QualificationGrades.GradeName,
dbo.AidReceiveds.AidReceivedName,
dbo.VulnerabilityTypes.VulnerabilityName,
dbo.DisabilityTypes.DisabilityName,
DisabilityTypes_1.DisabilityName,
dbo.ParentInformations.ParentInfo,
ParentInformations_1.ParentInfo,
dbo.ResidanceTypes.Name,
ResidanceTypes_1.Name,
dbo.Skills.SkillName,
Skills_1.SkillName,
dbo.DependancyTypes.DepandancyName,
dbo.TvsRecords.OpConfirmDate,
dbo.TvsRecords.QcConfirmDate,
dbo.QualificationGrades.QualificationGradeID,
dbo.EducationDiscontinued.EducationDiscontinuedID,
dbo.MaritalStatus.MaritalStatusID,
dbo.Gender.GenderID,
dbo.Relationships.RelationshipID,
dbo.AidReceiveds.AidReceivedID,
dbo.DependancyTypes.DependancyType,
dbo.VulnerabilityTypes.VulnerabilityType,
dbo.ParentInformations.ParentInfoID,
ParentInformations_1.ParentInfoID,
DisabilityTypes_1.DisabilityType,
dbo.DisabilityTypes.DisabilityType,
dbo.ResidanceTypes.ResidenceType,
ResidanceTypes_1.ResidenceType,
dbo.Skills.SkillID,
Skills_1.SkillID,
dbo.TvsFamilyMembers.MemberNo,
dbo.Provinces.ProvinceID,
dbo.Districts.DistrictID,
dbo.Tehsils.TehsilID,
dbo.UnionCouncils.UnionCouncilID,
dbo.UnionCouncils.UnionCouncilName,
dbo.Villages.VillageID,
dbo.TvsRecordBranchInfo.BranchID,
dbo.TvsRecordBankNames.BankID,
dbo.TvsFamilyMembers.TvsFamilyMemberID,
dbo.TvsRecords.TvsFormStatusCode,
dbo.TvsRecords.CorrectionsCount,
dbo.TvsRecords.TvsRecordBatchID,
dbo.TvsRecordBranchInfo.BranchCode,
dbo.TvsRecords.EntryOperatorUserID,
dbo.TvsRecords.QCUserID
ORDER BYdbo.TvsRecords.TvsFormNo,
ScanID
N 56°04'39.16"
E 12°55'05.25"
May 9, 2008 at 5:43 am
Huh, I wonder why that's running slow. 😀
Sorry, that's not nice. Actually, except for the enormous set of columns in the GROUP BY, this isn't too bad a query based on structure alone. Do you have an execution plan? That will tell you where your bottlenecks are. Start there, identifying the most costly processes and seeing if there are ways to index the tables such that it gets you what you want.
That might not work. You can't simply take this same query and break it down into smaller parts to increase performance. Probably the better choice, assuming a bunch of indexes won't work, is to pre-aggregate some or all of this data. That means creating a process to pull the data from the structures containing it now and do smaller sets of aggregations into new structures. Then join those structures using a query that only has to pull the data together, not aggregate it.
One other thought, the code is a bit hard to read formatted that way, but I only see a single aggregate function, COUNT(dbo.TvsFamilyMembers.TvsFamilyMemberID) AS FamilyMemberRecords. Why not put that one function in a sub-select that aggregates what you need and then join against that with the other columns?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply