SQL Server Performance Forum – Threads Archive
Crying need to speed up SQL query in ASP page
I’m a web developer and working on ASP,SQL server 2000.I’ve a specific problem.I’ve 70000 records in a table and I’ve to check all records which satisfy a particular condition based upon some simple mathematical calculation.If the conditions satisfies then I pick up the primary key from that record and by which I retrieve another record from another table which is also around 70000 records and display them in ASP.
I’ve also added indexing to the tables. This is taking about 3 min.Is that ok ? I can predict that is due to
connection.execute("SQL")
–this line which is in the loop.How can I do this in least time ? Right now I’ve little time to experiment with different strategies.
Please give me some sure tips which works.
Thank you.
Using Conn.execute in a lopp is something you *definetly* should not do…I’m positive that there are better ways to solve this but some code would be needed to give some proper advice. —
Frettmaestro
"Real programmers don’t document, if it was hard to write it should be hard to understand"
Thank you frettmaestro,
The code of my problem is something like this.zipcodes and automotive tables has 70000 records.
I need some zip values from zipcodes table based on some mathematical calculation.Against that
zip values I need the records from automotive table.Please help.
————————————————————————————————–
sSQL1 = "Select zip, Lat, Lon from zipcodes"
Set rsUsers = oConnection.Execute(sSQL1) SQL = "Select Lat, Lon from zipcodes WHERE zip=’" + buyer_zip + "’"
set RS = oConnection.execute(SQL) if not RS.eof then
lon1 = RS("Lon")
lat1 = RS("Lat")
end if
lon1 = lon1*0.017453293
lat1 = lat1*0.017453293
If NOT rsUsers.EOF Then
Do While Not rsUsers.EOF ‘loop1 —————————- lon2 =rsUsers("Lon")
lat2 = rsUsers("Lat") if not RS.eof and not rsUsers.eof then
lon2 = lon2*0.017453293
lat2 = lat2*0.017453293 a = pi/2 – lat1
b = pi/2 – lat2
c = Sqr(a^2 + b^2 -2*a*b*Cos(lon2-lon1))
d1 = r*c
d = CInt(d1)
else
d = 3200
end if if d <= preferedDistance1 then
rZIP = rsUsers("zip")
SQL2="select owner_zip, owner_email, owner_fax, owner_name_1, owner_state, id as sas from
automotive where owner_zip =’" & rZIP & "’" set RS1 = oConnection.execute(SQL2)
If NOT RS1.EOF Then
Do While Not RS1.EOF
‘loop2 —————————- <tr class="bc">
<td width=25% align="left"> <%=d%></td>
<td width=50% align="left"> <%=RS1("owner_email")%></td> <td width=15% align="left"> <%=RS1("owner_fax")%></td> <td width=10% align="left"> <%=RS1("owner_zip")%></td>
</tr> RS1.MoveNext() Loop
End If end if rsUsers.MoveNext() Loop
End If
————————————————————————————————-
write the calculation as a user defined function residing on sql server.
Then you might want to wrap a stored procedure around that to take the params.
If youre clever you can probably do it in one hit… dont have time to work this out for you, perhaps tommorrow
I woud second what Chappy has siad, doing server side calculations is always faster than Client side if calculations involve checking data against SQL Serve tables. Remember the golden rule…. SQL round trips are costly. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
if the SQL2 runs a lot in the loop, see if you can move the SQL2="select owner_zip, owner_email, owner_fax, owner_name_1, owner_state, id as sas from
automotive where owner_zip =’" & rZIP & "’" out of the loop , put it at the top with the other 2 query , build it with the sSQL1 = "Select zip, Lat, Lon from zipcodes"
Set rsUsers = oConnection.Execute(sSQL1) at the same time, select owner_zip, owner_email, owner_fax, owner_name_1, owner_state, id as sas ,
zip, Lat, Lon
from
automotive , zipcodes where owner_zip=zip
]]>