SQL Query becomes too much

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Hamayun Khan
    New Member
    • Aug 2007
    • 106

    SQL Query becomes too much

    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

    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')))
    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.
    Last edited by Frinavale; Mar 31 '09, 01:51 PM. Reason: Changed [quote] tags to [code] tags.
  • gits
    Recognized Expert Moderator Expert
    • May 2007
    • 5388

    #2
    wtf ;) ... that is quite a lot of conditions ;) ... as far as i see there are some joins and a union ... and those bunch of conditions ... so you could check whether the fields to search for are all indexed in the DB to avoid full table-scans and/or in case it would be possible you could create an 'aggregation'-table that contains the data ... what would be a kind of datawarehouse for this kind of queries to avoid the slow joins. so to say this is a DB-question ... may be the query could be optimized too.

    kind regards

    Comment

    • Frinavale
      Recognized Expert Expert
      • Oct 2006
      • 9749

      #3
      I've moved this thread to the SQL Server forum. I think they'll have a better idea about how to help you with this. If you're not using SQL Server as your database, let me know and I'll move it accordingly.

      -Moderator Frinny

      Comment

      • ck9663
        Recognized Expert Specialist
        • Jun 2007
        • 2878

        #4
        Some notes...

        1. I assume you don't need all these fields immediately on the requesting page. So retrieve those you only need.
        2. Create a view that will handle the UNION.
        3. UNION vs UNION ALL. Union filters duplicate records which makes your entire query slow. UNION ALL returns all rows.
        4. Create all the necessary index on your table


        --- CK

        Comment

        • Hamayun Khan
          New Member
          • Aug 2007
          • 106

          #5
          Originally posted by Frinavale
          I've moved this thread to the SQL Server forum. I think they'll have a better idea about how to help you with this. If you're not using SQL Server as your database, let me know and I'll move it accordingly.

          -Moderator Frinny
          Thanks

          I m using SQL Server 2005

          Comment

          • Hamayun Khan
            New Member
            • Aug 2007
            • 106

            #6
            Originally posted by ck9663
            Some notes...

            1. I assume you don't need all these fields immediately on the requesting page. So retrieve those you only need.
            2. Create a view that will handle the UNION.
            3. UNION vs UNION ALL. Union filters duplicate records which makes your entire query slow. UNION ALL returns all rows.
            4. Create all the necessary index on your table


            --- CK
            Thanks for reply

            1 I need All the fields I m currently retrieving.

            2 I have already used Union All in my origional Query

            3 I m not SQL Expert. But I m SQL Beginner. If you give me an example of how to create index on table for the given query.

            Keep in mind each time the query is different. (means "Where" Clause is each time different)

            Thanks

            Comment

            • Uncle Dickie
              New Member
              • Nov 2008
              • 67

              #7
              Are you repeating searches unnecessarily in the WHERE statement?

              e.g.

              Code:
              WHERE (Region like '%London%' OR Region like 'London %' OR Region like '% London')

              Are 'London %' and '% London' not subsets of '%London%' ?
              I'm not sure if it will make any difference to the speed (CK can probably advise on that!)

              Comment

              • Hamayun Khan
                New Member
                • Aug 2007
                • 106

                #8
                Originally posted by Uncle Dickie
                Are you repeating searches unnecessarily in the WHERE statement?

                e.g.

                Code:
                WHERE (Region like '%London%' OR Region like 'London %' OR Region like '% London')

                Are 'London %' and '% London' not subsets of '%London%' ?
                I'm not sure if it will make any difference to the speed (CK can probably advise on that!)

                Thanks

                '%London %' does not fulfill my requirements.

                Comment

                • gits
                  Recognized Expert Moderator Expert
                  • May 2007
                  • 5388

                  #9
                  but as above mentioned '%London%' should do ... ?

                  kind regards

                  Comment

                  • ck9663
                    Recognized Expert Specialist
                    • Jun 2007
                    • 2878

                    #10
                    Here's the link to create index. Create an index on the columns that you mostly used for searching.

                    The dollar sign is a wild character. So your '%London%' can capture '% London' and 'London %' as well.


                    --- CK

                    Comment

                    • Delerna
                      Recognized Expert Top Contributor
                      • Jan 2008
                      • 1134

                      #11
                      Just a side note, and I could be wrong

                      but I believe that using like on a field nullifies any benefit achieved by indexing that field.

                      The way I understand it is, an index gives speed improvements by reducing the number of records that the query must scan.
                      Using like forces the query to have to scan EVERY record (I believe) in order to find out whether the value in the field is like the criteria or not.

                      I am in no way criticising the use of LIKE here, just highlighting something that I believe needs to be carefully considered when using it.

                      Comment

                      • ck9663
                        Recognized Expert Specialist
                        • Jun 2007
                        • 2878

                        #12
                        Delerna is right.

                        In some instance, SQL Server will use index if you use as much character on the right side of the string before the wildcard. A WHERE column like 'AAA%' will use an index, but a WHERE column like '%AAAA' will not.


                        --- CK

                        Comment

                        Working...