Database update taking too long. Help quick.

[qwert231]

This is a biggy! I need it quick... and appreciate any help.

K, I got this page which dynamically lists/shows a number of images. There are 2 options to be set for each image. Whether or not it is shown (HIDE) and what sequence it should be shown in (SORT). Sometimes a job has as many as 500 images.

When the studio (Photographer) goes on and makes changes, to all of the images, my script goes, sees if the options has changed and if it has, updates the database. I am wondering, should I open and close the database for each change? Or can I open it and leave it... here is the code I have right now. Please, any questions, let me know. I kinda gotta work this out soon.

RecSelect("SELECT ID, Sort, Hide FROM Photos WHERE JobNumber LIKE '" & job & "' ORDER BY Sort&quot
aRecSet = recSet.GetRows '-->I built this into an array, so I don't have 2 connections to the database at the same time.
If Err.number <> 0 then
TrapError &quot;GetRows Line 23&quot;, Err.number, Err.Source, Err.description, bsktNum
End If
RecClose
x = UBound(aRecSet, 1)
y = UBound(aRecSet, 2)
j = 0
'Response.Write x & &quot;, &quot; & y
for j = 0 to y
' --> Checking to see if the Sort is different. If it is, change it.
varElement = &quot;sort&quot; & aRecSet(0,j)
RecUpdate &quot;Photos&quot;
if NOT CInt(Request.Form(varElement)) = aRecSet(1,j) then
batchSet.Find &quot;ID = '&quot; & aRecSet(0,j) & &quot;'&quot;
batchSet(&quot;Sort&quot = CInt(Request.Form(varElement))
end if
' --> Checking to see if the Hide is different. If it is, change it.
if Request.Form(varElement) = &quot;on&quot; then '-->This is a checkbox, so I need to change the value from on to TRUE.
tempVar = True
else
tempVar = False
end if
if NOT tempVar = aRecSet(2,j) then
batchSet.Find &quot;ID = '&quot; & aRecSet(0,j) & &quot;'&quot;
batchSet(&quot;Hide&quot = tempVar
end if
BatchClose
Next
ProcessErrors '-->A function I have created to mail me any errors.
Response.Redirect &quot;imageAdmin.asp?Job=&quot; & job & &quot;&jobID=&quot; & jobID 'this is where the code sends the person to next.

 

[Bastien]

You keep hitting the DB to do the sort, instead just sort the recordset array

try here for some articles on how to do this:

http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=89

Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[qwert231]

I don't see what you mean. When the images are fist entered into the database, they are given a sort number. However, those numbers are changed by the studio. If we change the sort numbers while we are making changes... you can imagine how that could be. That is why I use the array as my fixed lookup. It looks for the Image ID not the sort....

Wait a second... I am getting a glimmer of lite. Here's the big question I have now.
When I open the table to update (RecUpdate &quot;Photos&quot can I sort then?
Sub RecUpdate(upTable)
set batchSet = Server.CreateObject(&quot;ADODB.Recordset&quot
batchSet.Open upTable, strC, adOpenStatic, adLockBatchOptimistic, adCmdTable
End Sub