Optimering af ASP og MS-SQL
Hej experter,har I nogle goder ideer til hvordan jeg kan få nedenstående script til at køre hurtigere? Med ca. 1000 records i Customers tager det næsten 5 minutter at køre. Jeg tør næsten ikke tænke på hvor langsomt det kommer til at køre med indhold i Customer_Products...(!)
Nå her er koden:
<!-- #include file="DB_Connect.asp" -->
<%
server.ScriptTimeout = 500
Response.Write("script startet: " & Now() & "<br><br>")
Query = "SELECT ID, Navn, Email FROM Customers"
Set Customers = Server.CreateObject("ADODB.RecordSet")
Customers.Open Query, Connect, 0
if not Customers.EOF then
do until Customers.EOF
Query = "SELECT * FROM Customer_Product WHERE Customer_ID = '" & Customers("ID") & "'"
Set Customer_Products = Server.CreateObject("ADODB.RecordSet")
Customer_Products.Open Query, Connect, 0
if not Customer_Products.EOF then
SQL_String = ""
i = 1
do until Customer_Products.EOF
Query = "SELECT * FROM Products_ProductCodes WHERE Product_Code_ID = '" & Customer_Products("Product_Code_ID") & "'"
Set Products_ProductCodes = server.CreateObject("ADODB.RecordSet")
Products_ProductCodes.Open Query, Connect, 0
if not Products_ProductCodes.EOF then
do until Products_ProductCodes.EOF
Query = "SELECT * FROM Email_Sendt WHERE Product_ID = '" & Products_ProductCodes("Product_ID") & "' AND Customer_ID = '" & Customers("ID") & "'"
Set xCheck = Server.CreateObject("ADODB.RecordSet")
xCheck.Open Query, Connect, 0
if xCheck.EOF then
if i = 1 then
SQL_String = SQL_String & " ID = '" & Products_ProductCodes("Product_ID") & "'"
else
SQL_String = SQL_String & " OR ID = '" & Products_ProductCodes("Product_ID") & "'"
end if
i = i + 1
end if
xCheck.Close
Set xCheck = Nothing
Products_ProductCodes.MoveNext
Loop
end if
Products_ProductCodes.Close
Set Products_ProductCodes = Nothing
Customer_Products.MoveNext
Loop
end if
Customer_Products.Close
Set Customer_Products = Nothing
if not SQL_String = "" then
Response.Write("<h3>" & Customers("Navn") & "</h3>")
Query = "SELECT * FROM Products WHERE " & SQL_String & " ORDER BY Prisgruppe_ID ASC"
Set Products = Server.CreateObject("ADODB.RecordSet")
Products.Open Query, Connect, 0
if Products.EOF then
else
PrisA = 0
PrisB = 0
PrisC = 0
do until Products.EOF
if Products("Prisgruppe_ID") = "1" then
ThisPrisGruppe = "1"
end if
if Products("Prisgruppe_ID") = "2" then
ThisPrisGruppe = "2"
end if
if Products("Prisgruppe_ID") = "3" then
ThisPrisGruppe = "3"
end if
If ThisPrisGruppe = "1" then
if PrisA < 5 then
PrisAbodyHTML = PrisAbodyHTML & "<table cellpadding=0 cellspacing=0><tr><td><b>" & Products("Navn") & "</b></td></tr></table>"
Query = "INSERT INTO Email_Sendt (Product_ID, Customer_ID, DateTime, Prisgruppe_ID) VALUES ('" & Products("ID") & "', '" & Customers("ID") & "', GetDate(),'" & ThisPrisGruppe & "')"
Connect.Execute Query
PrisA = PrisA + 1
else
ThisPrisGruppe = "2"
end if
end if
If ThisPrisGruppe = "2" then
if PrisB < 5 then
PrisBbodyHTML = PrisBbodyHTML & "<table cellpadding=0 cellspacing=0><tr><td>" & Products("Navn") & "</td></tr></table>"
Query = "INSERT INTO Email_Sendt (Product_ID, Customer_ID, DateTime, Prisgruppe_ID) VALUES ('" & Products("ID") & "', '" & Customers("ID") & "', GetDate(),'" & ThisPrisGruppe & "')"
Connect.Execute Query
PrisB = PrisB + 1
else
ThisPrisGruppe = "3"
end if
end if
If ThisPrisGruppe = "3" then
PrisCbodyHTML = PrisCbodyHTML & "<table cellpadding=0 cellspacing=0><tr><td><i>" & Products("Navn") & "</i></td></tr></table>"
Query = "INSERT INTO Email_Sendt (Product_ID, Customer_ID, DateTime, Prisgruppe_ID) VALUES ('" & Products("ID") & "', '" & Customers("ID") & "', GetDate(),'" & ThisPrisGruppe & "')"
Connect.Execute Query
end if
Products.MoveNext
Loop
end if
Products.Close
Set Products = Nothing
Response.Write PrisAbodyHTML & "<br>" & PrisBbodyHTML & "<br>" & PrisCbodyHTML & "<br><br>"
end if
Customers.MoveNext
Loop
end if
Customers.Close
Set Customers = Nothing
Response.Write("script sluttet: " & Now() & "<br><br>")
%>
<!-- #include file="DB_close.asp" -->
