HI All
My client requirements are such that I m going to generate query runtime in asp.net.
At some time the query reaches to very much length as below
While executing query like this the page load too much slow. While the other time the query is not to much lengthy but 10 20 or 50 number of queries are to be evaluated. Now the problem is that the site is too much slow for pages in which the above queries are to be evaluated. My client want to speed up the site. He is not happy with the speed of the site. Also he is not ready to change his requirements. If Any one has some recomondations.
Very very Thanks for sugessions and recomondations.
My client requirements are such that I m going to generate query runtime in asp.net.
At some time the query reaches to very much length as below
Code:
SELECT tblJobPost.JobTitle,tblJobPost.JobDesc,Scraped,tblSchools.logoimage,tblJobPost.JobPostID,tblJobPost.SchoolID,tblSchools.web,cast(tblJobPost.MemType as nvarchar(255)) as MemType,tblSchools.InstitutionName,tblSchools.InstitutionName as Empname,tblJobPost.PayScale,cast(tblLEA.LEA as nvarchar(255)) as LEA,tblContract.ContractType,tblHoursAbl.HoursAbl,cast(tblJobPost.StartDate as nvarchar(255)) as StartDate, cast(tblJobPost.ClosingDate as nvarchar(255)) as ClosingDate,cast(tblJobPost.asap as nvarchar(50)) as asap, cast(tblJobPost.postdate as nvarchar(50)) as postdate FROM tblJobPost LEFT Outer JOIN tblpositions ON tblJobPost.position_id = tblpositions.position_id LEFT OUTER JOIN tblMainsubjects ON tblJobPost.SubjectID = tblMainsubjects.MSubjectID LEFT OUTER JOIN tblSchoolPhase ON tblJobPost.SchoolPhaseID = tblSchoolPhase.SchoolPhaseID LEFT OUTER JOIN tblLEA ON tblJobPost.LeaID = tblLEA.LeaID LEFT OUTER JOIN tblHoursAbl ON tblJobPost.HoursID = tblHoursAbl.HoursAblID LEFT OUTER JOIN tblContract ON tblJobPost.ContractID = tblContract.ContractID LEFT OUTER JOIN tblregion ON tblJobPost.Region_id = tblregion.RegionID RIGHT OUTER JOIN tblSchools ON tblJobPost.SchoolID = tblSchools.SchoolID Where (tblregion.region = 'London') and (tblSchoolPhase.SchoolPhase = 'Secondary Education') and (tblpositions.Position_title = 'Classroom Teacher')and (tblMainSubjects.MSubject = 'Science')and (tblLEA.LEA = 'Croydon') and (tblJobPost.ClosingDate >= '3/30/2009 11:14:49 PM') Union Select tblJobScrap.JobTitle,tblJobScrap.JobDesc,tblJobScrap.Scraped,tblSchools.logoimage,tblJobScrap.JobPostID,tblJobScrap.SchoolID,tblSchools.web,tblSchools.MemType,tblSchools.InstitutionName,tblJobScrap.InstitutionName as Empname,tblJobScrap.PayScale,tblJobScrap.LEA,tblJobScrap.Contract as ContractType,tblJobScrap.HoursAbl,tblJobScrap.StartDate,tblJobScrap.ClosingDate,tblJobScrap.asap,tblJobScrap.postdate from tblJobScrap LEFT OUTER JOIN tblSchools ON tblJobScrap.SchoolID = tblSchools.SchoolID Where (((Region like '%London%' OR Region like 'London %' OR Region like '% London'))) AND NOT (((JobTitle like '%Primary%' OR JobTitle like 'Primary %' OR JobTitle like '% Primary') OR (JobTitle like '%Infant%' OR JobTitle like 'Infant %' OR JobTitle like '% Infant') OR (JobTitle like '%Junior%' OR JobTitle like 'Junior %' OR JobTitle like '% Junior') OR (JobTitle like '%Early%' OR JobTitle like 'Early %' OR JobTitle like '% Early') OR (JobTitle like '%Elementary%' OR JobTitle like 'Elementary %' OR JobTitle like '% Elementary') OR (JobTitle like '%Special%' OR JobTitle like 'Special %' OR JobTitle like '% Special') OR (JobTitle like '%University%' OR JobTitle like 'University %' OR JobTitle like '% University') OR (JobTitle like '%Lecturer%' OR JobTitle like 'Lecturer %' OR JobTitle like '% Lecturer') OR (JobTitle like '%tutor%' OR JobTitle like 'tutor %' OR JobTitle like '% tutor') OR (JobTitle like '%Adult%' OR JobTitle like 'Adult %' OR JobTitle like '% Adult') OR (JobTitle like '%Nursery%' OR JobTitle like 'Nursery %' OR JobTitle like '% Nursery') OR (JobTitle like '%Middle%' OR JobTitle like 'Middle %' OR JobTitle like '% Middle') OR (JobTitle like '%Multicultural%' OR JobTitle like 'Multicultural %' OR JobTitle like '% Multicultural') OR (JobTitle like '%Key Stage 1%' OR JobTitle like 'Key Stage 1 %' OR JobTitle like '% Key Stage 1') OR (JobTitle like '%Key Stage 2%' OR JobTitle like 'Key Stage 2 %' OR JobTitle like '% Key Stage 2') OR (JobTitle like '%KS1%' OR JobTitle like 'KS1 %' OR JobTitle like '% KS1') OR (JobTitle like '%KS2%' OR JobTitle like 'KS2 %' OR JobTitle like '% KS2') OR (JobTitle like '%KS 1%' OR JobTitle like 'KS 1 %' OR JobTitle like '% KS 1') OR (JobTitle like '%KS 2%' OR JobTitle like 'KS 2 %' OR JobTitle like '% KS 2') OR (JobTitle like '%Year 1%' OR JobTitle like 'Year 1 %' OR JobTitle like '% Year 1') OR (JobTitle like '%Year 2%' OR JobTitle like 'Year 2 %' OR JobTitle like '% Year 2') OR (JobTitle like '%Year 3%' OR JobTitle like 'Year 3 %' OR JobTitle like '% Year 3') OR (JobTitle like '%Year 4%' OR JobTitle like 'Year 4 %' OR JobTitle like '% Year 4') OR (JobTitle like '%Year 5%' OR JobTitle like 'Year 5 %' OR JobTitle like '% Year 5') OR (JobTitle like '%Year 6%' OR JobTitle like 'Year 6 %' OR JobTitle like '% Year 6') OR (JobTitle like '%social worker%' OR JobTitle like 'social worker %' OR JobTitle like '% social worker')) OR ((tblJobScrap.institutionName like '%Primary%' OR tblJobScrap.institutionName like 'Primary %' OR tblJobScrap.institutionName like '% Primary') OR (tblJobScrap.institutionName like '%Infant%' OR tblJobScrap.institutionName like 'Infant %' OR tblJobScrap.institutionName like '% Infant') OR (tblJobScrap.institutionName like '%Junior%' OR tblJobScrap.institutionName like 'Junior %' OR tblJobScrap.institutionName like '% Junior') OR (tblJobScrap.institutionName like '%Early%' OR tblJobScrap.institutionName like 'Early %' OR tblJobScrap.institutionName like '% Early') OR (tblJobScrap.institutionName like '%Elementary%' OR tblJobScrap.institutionName like 'Elementary %' OR tblJobScrap.institutionName like '% Elementary') OR (tblJobScrap.institutionName like '%Special%' OR tblJobScrap.institutionName like 'Special %' OR tblJobScrap.institutionName like '% Special') OR (tblJobScrap.institutionName like '%University%' OR tblJobScrap.institutionName like 'University %' OR tblJobScrap.institutionName like '% University') OR (tblJobScrap.institutionName like '%Lecturer%' OR tblJobScrap.institutionName like 'Lecturer %' OR tblJobScrap.institutionName like '% Lecturer') OR (tblJobScrap.institutionName like '%tutor%' OR tblJobScrap.institutionName like 'tutor %' OR tblJobScrap.institutionName like '% tutor') OR (tblJobScrap.institutionName like '%Adult%' OR tblJobScrap.institutionName like 'Adult %' OR tblJobScrap.institutionName like '% Adult') OR (tblJobScrap.institutionName like '%Nursery%' OR tblJobScrap.institutionName like 'Nursery %' OR tblJobScrap.institutionName like '% Nursery') OR (tblJobScrap.institutionName like '%Middle%' OR tblJobScrap.institutionName like 'Middle %' OR tblJobScrap.institutionName like '% Middle') OR (tblJobScrap.institutionName like '%Multicultural%' OR tblJobScrap.institutionName like 'Multicultural %' OR tblJobScrap.institutionName like '% Multicultural') OR (tblJobScrap.institutionName like '%Key Stage 1%' OR tblJobScrap.institutionName like 'Key Stage 1 %' OR tblJobScrap.institutionName like '% Key Stage 1') OR (tblJobScrap.institutionName like '%Key Stage 2%' OR tblJobScrap.institutionName like 'Key Stage 2 %' OR tblJobScrap.institutionName like '% Key Stage 2') OR (tblJobScrap.institutionName like '%KS1%' OR tblJobScrap.institutionName like 'KS1 %' OR tblJobScrap.institutionName like '% KS1') OR (tblJobScrap.institutionName like '%KS2%' OR tblJobScrap.institutionName like 'KS2 %' OR tblJobScrap.institutionName like '% KS2') OR (tblJobScrap.institutionName like '%KS 1%' OR tblJobScrap.institutionName like 'KS 1 %' OR tblJobScrap.institutionName like '% KS 1') OR (tblJobScrap.institutionName like '%KS 2%' OR tblJobScrap.institutionName like 'KS 2 %' OR tblJobScrap.institutionName like '% KS 2') OR (tblJobScrap.institutionName like '%Year 1%' OR tblJobScrap.institutionName like 'Year 1 %' OR tblJobScrap.institutionName like '% Year 1') OR (tblJobScrap.institutionName like '%Year 2%' OR tblJobScrap.institutionName like 'Year 2 %' OR tblJobScrap.institutionName like '% Year 2') OR (tblJobScrap.institutionName like '%Year 3%' OR tblJobScrap.institutionName like 'Year 3 %' OR tblJobScrap.institutionName like '% Year 3') OR (tblJobScrap.institutionName like '%Year 4%' OR tblJobScrap.institutionName like 'Year 4 %' OR tblJobScrap.institutionName like '% Year 4') OR (tblJobScrap.institutionName like '%Year 5%' OR tblJobScrap.institutionName like 'Year 5 %' OR tblJobScrap.institutionName like '% Year 5') OR (tblJobScrap.institutionName like '%Year 6%' OR tblJobScrap.institutionName like 'Year 6 %' OR tblJobScrap.institutionName like '% Year 6') OR (tblJobScrap.institutionName like '%social worker%' OR tblJobScrap.institutionName like 'social worker %' OR tblJobScrap.institutionName like '% social worker')))and (((JobTitle like '%Classroom Teacher%' OR JobTitle like 'Classroom Teacher %' OR JobTitle like '% Classroom Teacher') OR (JobTitle like '%teacher%' OR JobTitle like 'teacher %' OR JobTitle like '% teacher') OR (JobTitle like '%ordinator%' OR JobTitle like 'ordinator %' OR JobTitle like '% ordinator') OR (JobTitle like '%position%' OR JobTitle like 'position %' OR JobTitle like '% position'))) AND NOT (((JobTitle like '%Headteacher%' OR JobTitle like 'Headteacher %' OR JobTitle like '% Headteacher') OR (JobTitle like '%Head teacher%' OR JobTitle like 'Head teacher %' OR JobTitle like '% Head teacher') OR (JobTitle like '%Head%' OR JobTitle like 'Head %' OR JobTitle like '% Head') OR (JobTitle like '%lecturer%' OR JobTitle like 'lecturer %' OR JobTitle like '% lecturer') OR (JobTitle like '%assistant%' OR JobTitle like 'assistant %' OR JobTitle like '% assistant') OR (JobTitle like '%support%' OR JobTitle like 'support %' OR JobTitle like '% support')))and (((JobTitle like '%Science%' OR JobTitle like 'Science %' OR JobTitle like '% Science') OR (JobTitle like '%Chemistry%' OR JobTitle like 'Chemistry %' OR JobTitle like '% Chemistry') OR (JobTitle like '%Physics%' OR JobTitle like 'Physics %' OR JobTitle like '% Physics') OR (JobTitle like '%Biology%' OR JobTitle like 'Biology %' OR JobTitle like '% Biology')))AND (((LEA like '%Croydon%' OR LEA like 'Croydon %' OR LEA like '% Croydon')))
Very very Thanks for sugessions and recomondations.
Comment