Access -> SQL Server = Rigtig langsomt
Hejsa,jeg har netop upsized min Access 2000 database til SQL Server 2000. Det drejer sig om en testdatabase - et message board. Men det kører EKSTREMT LANGSOMT lige pludseligt, når det skal vise oversigt over beskederne.
Jeg koder i ASP, og det er kun her den er langsom. I Query Analyzer kører den lynhurtigt. Access i ASP er tilsvarende hurtigt.
Min kode, fordelt på 2 subs:
- CONNECTION -
Sub sub_ADODB_CONNECTION_OPEN
Set obj_ADODB_CONNECTION = Server.CreateObject("ADODB.Connection")
With obj_ADODB_CONNECTION
.ConnectionString = "DRIVER={SQL Server};SERVER=MAIN;DATABASE=bnmbSQL"
.Open
End With
End Sub
- VIS BESKEDER (pas på, den her er lang) -
Sub sub_LIST_THREADS(M, pageNum)
Set obj_ADODB_RECORDSET_LIST_THREADS =
Server.CreateObject("ADODB.Recordset")
With obj_ADODB_RECORDSET_LIST_THREADS
If M = 0 Then
If bln_FILTER_THREADS Then
str_SOURCE = str_SOURCE & "SELECT M, P, T, str_NAME, str_E_MAIL,
str_SUBJECT, dat_DATE, str_REMOTE_ADDR FROM tbl_MESSAGES WHERE P = 0"
If bln_FILTER_THREADS_YEAR And Session("str_FILTER_THREADS_YEAR") <>
str_FILTER_THREADS_YEAR_ANY Then
str_SOURCE = str_SOURCE & " AND Year(dat_DATE) = '" &
Session("str_FILTER_THREADS_YEAR") & "'"
End If
If bln_FILTER_THREADS_MONTH And Session("str_FILTER_THREADS_MONTH") <>
str_FILTER_THREADS_MONTH_ANY Then
str_SOURCE = str_SOURCE & " AND Month(dat_DATE) = '" &
Session("str_FILTER_THREADS_MONTH") & "'"
End If
If bln_FILTER_THREADS_DAY And Session("str_FILTER_THREADS_DAY") <>
str_FILTER_THREADS_DAY_ANY Then
str_SOURCE = str_SOURCE & " AND Day(dat_DATE) = " &
Session("str_FILTER_THREADS_DAY")
End If
If bln_FILTER_THREADS_TEXT Then
str_SOURCE = str_SOURCE & " AND (str_NAME LIKE '%" &
Session("str_FILTER_THREADS_TEXT") & "%'"
str_SOURCE = str_SOURCE & " OR str_E_MAIL LIKE '%" &
Session("str_FILTER_THREADS_TEXT") & "%'"
str_SOURCE = str_SOURCE & " OR str_SUBJECT LIKE '%" &
Session("str_FILTER_THREADS_TEXT") & "%'"
str_SOURCE = str_SOURCE & " OR dat_DATE LIKE '%" &
Session("str_FILTER_THREADS_TEXT") & "%'"
str_SOURCE = str_SOURCE & " OR str_REMOTE_ADDR LIKE '%" &
Session("str_FILTER_THREADS_TEXT") & "%'"
str_SOURCE = str_SOURCE & " OR str_MESSAGE LIKE '%" &
Session("str_FILTER_THREADS_TEXT") & "%')"
End If
str_SOURCE = str_SOURCE & " ORDER BY M DESC"
.Source = str_SOURCE
Else
''''''''''''''''''' MIN SQL-STATEMENT HER ''''''''''''''''''''
.Source = "SELECT M, P, T, str_NAME, str_E_MAIL, str_SUBJECT, dat_DATE,
str_REMOTE_ADDR FROM tbl_MESSAGES WHERE P = 0 ORDER BY M DESC"
End If
Else
.Source = "SELECT M, P, T, str_NAME, str_E_MAIL, str_SUBJECT, dat_DATE,
str_REMOTE_ADDR FROM tbl_MESSAGES WHERE M = " & M & " ORDER BY M DESC"
End If
.ActiveConnection = obj_ADODB_CONNECTION
.CursorType = 1
.LockType = 1
.PageSize = 50
.Open
If Not .BOF Or Not .EOF Then
Response.Write("<ul>" & vbCrLf)
.AbsolutePage = pageNum
For ib = 1 to .PageSize
'While Not .EOF
sub_HTML_LIST_ITEMS .Fields("M").Value, .Fields("P").Value,
.Fields("T").Value, .Fields("str_NAME").Value, .Fields("str_E_MAIL").Value,
.Fields("str_SUBJECT").Value, .Fields("dat_DATE").Value,
.Fields("str_REMOTE_ADDR").Value
Response.Write "<br />"
sub_LIST_FOLLOW_UPS .Fields("M").Value, 0
Response.Write "<br />"
.MoveNext
if .EOF then Exit For
Next
'Wend
Response.Write("</ul>" & vbCrLf)
End If
if not bln_REQUEST_METHOD_GET then
displayPageLinks obj_ADODB_RECORDSET_LIST_THREADS, pageNum
end if
.Close
End With
Set obj_ADODB_RECORDSET_LIST_THREADS = Nothing
End Sub
