Question about how server intensive this method would be

Discussion in 'Database Support' started by zaboss, Nov 7, 2008.

  1. zaboss

    zaboss Perch

    Hi,
    I have a very complex search over an database and the only solution I could found was to select the records, put them in a temporrary table, perform the filtering over them, display the final results to the user and then delete the temporary table.
    We are talking about 100,000 + records at the maximum level of the database, while search results will allways be several hundreds the most.
    I don't expect that at the maximum level, the size of the db would excede 100 MB.
  2. Stephen

    Stephen US Operations Staff Member

    Would a full text index not resolve this?
  3. zaboss

    zaboss Perch

    Nope, because it involves math operations between columns, not just text search. There are 8 search criteria that a user might chose so the first query is generated dinamically, that's why I need a second trip to sort them right.
  4. Stephen

    Stephen US Operations Staff Member

    ah gotcha :(

    It sounds like it may be intensive but 100mb isn't that much.....maybe run it on a local SQL Express and see how it works?
  5. zaboss

    zaboss Perch

    As far as I have tested, the query runs instantly, but I have only few hundreds data to test. Fortunately, it isn't linkely that the query will be run to often since there will be only 3-4 users of the application.
  6. KrisSiegel

    KrisSiegel Perch

    Any way we could see the query? Might be able to do some optimization.
  7. zaboss

    zaboss Perch

    Code:
    <%   
    msql="SELECT tblCandidati.cndID, tblLang.strLimba, tblLang.strNivel, tblCandidati.strNivelStudii, tblCandidati.strNivelCariera, tblCandidati.StrKeyWords, tblCandidati.strAniExperienta, tblCandidati.strNume, tblCandidati.strPspamSpamme, tblCandidati.strIndustry,  tblExperienta.strdataInceperii, tblExperienta.strdataterminarii, tblExperienta.strPozitie, SUM(DateDiff(""m"", strDataInceperii, strDataTerminarii)) AS yearsinposition"
    msql= msql & " INTO tblSearch "
    msql=msql & " FROM tblCandidati, tblLang, tblExperienta WHERE"
    If request.form("indID")="" then
    Else
    msql= msql &" tblCandidati.strIndustry="& request.form("indID") &" AND "
    End if
    If request.form("strLimba")="" then
    Else
    msql= msql &" tblLang.strLimba='"& request.form("strLimba") &"' AND "
    End if
    If request.form("profID")="" then
    Else
    msql= msql &" tblExperienta.strPozitie="& request.form("profID") &" AND " 
    End if
    If request.form("strLocalitate")="" then
    Else
    msql= msql &" tblCandidati.strLocalitate='"& request.form("strLocalitate") &"' AND "
    End if
    If request.form("keywords")="" then
    Else
    msql= msql &" tblCandidati.strKeyWords ='" & request.form("keywords") & "' AND "
    End if
    If request.form("strCarnet")="" then
    Else
    msql= msql &" tblCandidati.strcarnet="& request.form("strCarnet") &" AND "
    End if
    msql=msql & " tblExperienta.cndID=tblCandidati.cndID AND tblLang.cndID=tblCandidati.cndID"
    msql=msql & "  GROUP BY tblExperienta.strDataInceperii, tblExperienta.strDataTerminarii, tblCandidati.cndID, tblLang.cndID, tblLang.strLimba, tblLang.strNivel, tblExperienta.strPozitie, tblCandidati.strNivelStudii, tblCandidati.strNivelCariera, tblCandidati.strAniExperienta, tblCandidati.strNume, tblCandidati.strPspamSpamme, tblCandidati.strIndustry, tblCandidati.StrKeyWords"
    'msql=msql & " HAVING SUM(DateDiff(""yyyy"", strDataInceperii, strDataTerminarii)) >= "& start &" AND SUM(DateDiff(""yyyy"", strDataInceperii, strDataTerminarii)) <= "& strend
    'Response.Write msql
    Set RSS = Server.CreateObject("ADODB.Recordset")
    RSS.Open msql, MyConn, 1, 3
    
    MyConn.Execute ("SELECT cndID, strPozitie, yearsinposition INTO tblNew FROM tblSearch GROUP BY cndID, strPozitie, yearsinposition")
    sql2 = "SELECT cndID, strPozitie, SUM(yearsinposition) AS yearsperposition INTO tblSUM FROM tblNew GROUP BY cndID, strPozitie"
    If request.form("strStart")="" then
    start=0
    Else
    start=request.form("strStart")*12
    End if
    If request.form("strEnd")="" then
    strend=1000
    Else
    strend=request.form("strEnd")*12
    End if
    sql2=sql2 & " HAVING SUM(yearsinposition) >= "& start &" AND SUM(yearsinposition) <= "& strend
    MyConn.Execute (sql2)
    
    sql= "SELECT cndID, SUM(yearsperposition) AS totalExperience FROM tblSUM GROUP BY cndID"
    If request.form("strTStart")="" then
    tstart=0
    Else
    tstart=request.form("strTStart")*12
    End if
    If request.form("strTEnd")="" then
    tstrend=1000
    Else
    tstrend=request.form("strTEnd")*12
    End if
    sql=sql & " HAVING SUM(yearsperposition) >= "& tstart &" AND SUM(yearsperposition) <= "& tstrend
    Set RS = Server.CreateObject("ADODB.Recordset")
    RS.Open sql, MyConn, 1, 3
  8. KrisSiegel

    KrisSiegel Perch

    I think that can be combined into one call to the SQL Server. Any way you could post the query without the string concatenation? It'll be easier to read :)
  9. zaboss

    zaboss Perch

    Actually, there are 3 querries. To simplify here it is the problem:
    I have 3 tables (tblCandidates, tblExperience, tblLanguages, the latter 2 related by one to many relation with tblCandidates based on cndID). I need to perform a search based on:
    tblCandidates
    Location
    Driving licence
    Keywords
    tblExperience
    WorkExperience - years (sum between startdate and enddate)
    Position - (also, years in position)
    Industry
    tblLanguage
    Language
    Level

    All criterias are dynamic, the user might select one or all of them. what I do with the code above
    1. Join the 3 tables, which gives me lots of duplicate records (a candidate is listed for each job experience and each language - so if he is having 4 job experience and knows 2 languages is listed 6 times)
    2 Sum experience on position into a new table
    3 Sum total work experience into a new table
    4 Join the final results with the tblCandidates to get the info I need to display (and got losted on the way :D ).
    Hopes that makes it easier.
  10. SubSpace

    SubSpace Bass

    You can turn this into a query that doesn't have to be dynamically built.. and one that doesn't directly use form input in it so it isn't wide open to SQL injection while you're at it.
    It's been a while, but I believe you need to create a stored procedure for named parameters to work in old-fashioned ADO though, and this would be a pain without named parameters.

    Assuming you turn all your form values into named parameters, you can rewrite

    Code:
    If request.form("indID")="" then
    Else
    msql= msql &" tblCandidati.strIndustry="& request.form("indID") &" AND "
    End if
    to something plain T-SQL like

    Code:
    (@indID IS NULL OR [email protected]) AND
    By the way, it looks like you're not actually using the temporary table functionality of SQL Server, but a regular table? I'm pretty sure the query itself should be rewritable to not need any, if you use subqueries instead of joins, but I have too much of a headache to figure it out right now without an actual database :p
  11. Yash

    Yash Bass

    I learn something new about T-SQL everyday :)
  12. zaboss

    zaboss Perch

    Hi, the code above was for Access, not SQL Server, but the logic is the same. I was too busy to re-write it to take advantage of the SQL Server features (the database is less than 1000 records, so no hurry yet).
    I have tryed with subqueries but I wasn't able to make it return what I wanted. I'll try to digg more with T-SQL.
  13. antic

    antic Perch

    Can you still post the Access db zipped up as an attachment? Would be easier to suggest something with a db to look at.
  14. zaboss

    zaboss Perch

    Hi Antic,
    sorry for the delay, I have missed the follow up email. I have attached the database here, as the forum didn't allowed it.
    Thanks,
    Cristian Banu

Share This Page

JodoHost - 26,000 hosting end-users in 100 countries
Plesk Web Hosting
VPS Hosting
H-Sphere Web Hosting
Other Services