Question about how server intensive this method would be

Z

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.
 
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.
 
zaboss said:
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.
Click to expand...

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?
 
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.
 
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
 
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 :)
 
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.
 
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 tblCandidati.strIndustry=@indID) 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
 
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.
 
Can you still post the Access db zipped up as an attachment? Would be easier to suggest something with a db to look at.
 
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
 
You must log in or register to reply here.
Back
Top