Optimere SQL output
Nogen som måske har en idé til at optimere dette script, det tager en rum tid at loade alle de data.Det er data som dykker ned i MS SQL database og henter ordrer fra Navision Attain.
men denne måde jeg har lavet det, tvinger jeg til at kigge dybt ned i databasen ca 30 gange, fordi jeg loper på den måde jeg gør.
Databasen indeholder over 200.000 poster, så det er jo mange poster der skal kigges gennem.
Nogen idéer til hvordan jeg klarer det bedre?
<table cellpadding="2" cellspacing="1" class="countertable">
<tr>
<th style="">Denne uge</th>
<th style="">DK</th>
<th style="">SE</th>
<th style="">NO</th>
<th style="">KAS</th>
<th style="">I alt</th>
</tr>
<%
strTaelDK = 0
strTaelSE = 0
strTaelNO = 0
strTaelKAS = 0
strTaelAlle = 0
for i = 0 to 6
usedate = DateAdd("d",i,thisweek)
dbDato = Replace(dbDato,"-","/")
strDateFormatted = Year(usedate) & "-" & Month(usedate) & "-" & Day(usedate)
if i = 0 then
Ugenavn = "Mandag"
elseif i = 1 then
Ugenavn = "Tirsdag"
elseif i = 2 then
Ugenavn = "Onsdag"
elseif i = 3 then
Ugenavn = "Torsdag"
elseif i = 4 then
Ugenavn = "Fredag"
elseif i = 5 then
Ugenavn = "Lørdag"
elseif i = 6 then
Ugenavn = "Søndag"
end if
strSQLDK = "SELECT TOP 100 PERCENT COUNT_BIG (No_) AS [Antall ordre], [Document Date] FROM dbo.[3S Company A_S$Sales Invoice Header] [3S Company A_S$Sales Invoice Header] WHERE ([Sell-to Customer No_] = '45') GROUP BY [Document Date] HAVING ([Document Date] = '" & strDateFormatted & "' ) ORDER BY [Document Date] desc"
Set RsDK = Conn.Execute(strSQLDK)
if rsDK.EOF or rsDK.BOF then
DKAntal = "0"
else
DKantal = RSdk("Antall Ordre")
end if
strSQLSE = "SELECT TOP 100 PERCENT COUNT_BIG (No_) AS [Antall ordre], [Document Date] FROM dbo.[3S Company A_S$Sales Invoice Header] [3S Company A_S$Sales Invoice Header] WHERE ([Sell-to Customer No_] = '46') GROUP BY [Document Date] HAVING ([Document Date] = '" & strDateFormatted & "' ) ORDER BY [Document Date] desc"
Set RsSE = Conn.Execute(strSQLSE)
if rsSE.EOF or rsSE.BOF then
SEAntal = "0"
else
SEantal = RSSE("Antall Ordre")
end if
strSQLNO = "SELECT TOP 100 PERCENT COUNT_BIG (No_) AS [Antall ordre], [Document Date] FROM dbo.[3S Company A_S$Sales Invoice Header] [3S Company A_S$Sales Invoice Header] WHERE ([Sell-to Customer No_] = '47') GROUP BY [Document Date] HAVING ([Document Date] = '" & strDateFormatted & "' ) ORDER BY [Document Date] desc"
Set RsNO = Conn.Execute(strSQLNO)
if rsNO.EOF or rsNO.BOF then
NOAntal = "0"
else
NOantal = rsNO("Antall Ordre")
end if
strSQLKAS = "SELECT TOP 100 PERCENT COUNT_BIG (No_) AS [Antall ordre], [Document Date] FROM dbo.[3S Company A_S$Sales Invoice Header] [3S Company A_S$Sales Invoice Header] WHERE ([Sell-to Customer No_] = '48') GROUP BY [Document Date] HAVING ([Document Date] = '" & strDateFormatted & "' ) ORDER BY [Document Date] desc"
Set RsKAS = Conn.Execute(strSQLKAS)
if rsKAS.EOF or rsKAS.BOF then
KASAntal = "0"
else
KASantal = RSKAS("Antall Ordre")
end if
strSQLAll = "SELECT TOP 100 PERCENT COUNT_BIG (No_) AS [Antall ordre], [Document Date] FROM dbo.[3S Company A_S$Sales Invoice Header] [3S Company A_S$Sales Invoice Header] WHERE ([Sell-to Customer No_] = '45') OR([Sell-to Customer No_] = '46') OR ([Sell-to Customer No_] = '47') OR ([Sell-to Customer No_] = '48')GROUP BY [Document Date] HAVING ([Document Date] = '" & strDateFormatted & "' ) ORDER BY [Document Date] desc"
Set RsAll = Conn.Execute(strSQLAll)
if rsAll.EOF or rsAll.BOF then
AllAntal = "0"
else
Allantal = formatnumber(RSAll("Antall Ordre"),0)
end if
if not AllAntal = "0" then
DKAntal = formatnumber(DKAntal,0)
SEAntal = formatnumber(SEAntal,0)
NOAntal = formatnumber(NOAntal,0)
KASAntal = formatnumber(KASAntal,0)
AllAntal = formatnumber(AllAntal,0)
strTaelDK = StrTaelDK + DKAntal
strTaelSE = StrTaelSE + SEAntal
strTaelNo = StrTaelNO + NoAntal
strTaelKAS = StrTaelKAS + KASAntal
strTaelAlle = StrTaelAlle + AllAntal
%>
<tr >
<td class="countercel" <%if strDateFormatted = strToday then%>style="background-color:#89A4ED;"<%end if%>><%=ugenavn%></td>
<td class="countercel" <%if strDateFormatted = strToday then%>style="background-color:#89A4ED;"<%end if%>><%=DKAntal%></td>
<td class="countercel" <%if strDateFormatted = strToday then%>style="background-color:#89A4ED;"<%end if%>><%=SEAntal%></td>
<td class="countercel" <%if strDateFormatted = strToday then%>style="background-color:#89A4ED;"<%end if%>><%=NOantal%></td>
<td class="countercel" <%if strDateFormatted = strToday then%>style="background-color:#89A4ED;"<%end if%>><%=KASantal%></td>
<td class="countercel" <%if strDateFormatted = strToday then%>style="background-color:#89A4ED;"<%end if%>><%=Allantal%></td>
</tr>
<%
end if
RsDK.Close
Set RsDK = Nothing
RsSE.Close
Set RsSE = Nothing
RsNO.Close
Set RsNO = Nothing
RsKAS.Close
Set RsKAS = Nothing
RsAll.Close
Set RsAll =Nothing
next
%>
<tr>
<td class="sumbund">I alt</td>
<td class="sumbund"><%=strTaelDK%></td>
<td class="sumbund"><%=strTaelSE%></td>
<td class="sumbund"><%=strTaelNO%></td>
<td class="sumbund"><%=strTaelKas%></td>
<td class="sumbund"><%=strTaelAlle%></td>
</tr>
</table>
