homepage Welcome to WebmasterWorld Guest from 54.204.182.118
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Microsoft / Microsoft IIS Web Server and ASP.NET
Forum Library, Charter, Moderators: ocean10000

Microsoft IIS Web Server and ASP.NET Forum

    
Classic ASP - Lcase operation on array taking too long
Classic ASP - Lcase operation on array taking too long
buck1107

10+ Year Member



 
Msg#: 4412808 posted 7:40 pm on Jan 31, 2012 (gmt 0)

Hi,
I'm working with a script designed to compare values returned from a form against values from a database dumped to an array, via GetRows.
Currently, the code uses an inner and outer loop to run this comparison, with a temporary variable being assigned the current col/row from the

aforementioned array. An lcase and trim operation are performed on the value to obtain the temporary variable.
This is causing a considerable performance drain, and I was wondering if the lcase/trim functionality could perhaps be performed during the creation

of that array, rather than in a looping situation?
Here's my code:
**note: this utilizes the FastString Class for concatenation, thus the "FastString" and ".Append"

dim iRowLoop, iColLoop, zRowLoop, strChange, tempDbValsCase
Set strChange = New FastString
for iRowLoop = 0 to ubound(arrDbVals, 2)
'///response.write(""""&arrDbVals(1,iRowLoop)&""""&"<BR>")

for zRowLoop = 0 to ubound(arrFormComplete)
'///response.write(""""&arrFormComplete(zRowLoop)&""""&"<BR>")

'****below line is what is causing the bottleneck, according
'****to a timer test
tempDbValsCase = lcase(trim(arrDbVals(1, iRowLoop)))
'****

if (mid(trim(arrFormComplete(zRowLoop)),1,8) = trim(arrDbVals(0, iRowLoop))) AND (mid(trim(arrFormComplete(zRowLoop)),9) <> tempDbValsCase)

then


dim strFormAllVals
strFormAllVals = arrFormComplete(zRowLoop)
strChange.Append strFormAllVals & ","

end if

next

next






On the database side, the table from which the array is derived through GetRows contains the bit datatype column "Complete". The lcase and trim

operations are performed upon this column of the array. Does the bit datatype add any hidden characters in the output? Visually, I don't detect

any, but when I compare a value of "True" from the form input against a value from the array that looks like "True," it doesn't match, until I run

the lcase and trim on the "Complete" column.

Thanks for any help!
Kind Regards :)

 

Ocean10000

WebmasterWorld Administrator 10+ Year Member



 
Msg#: 4412808 posted 3:10 am on Feb 5, 2012 (gmt 0)

Why not use a sql statement to do the entire search or reduce the amount of rows you have to search though the database and let the DB do the heavy lifting?

buck1107

10+ Year Member



 
Msg#: 4412808 posted 2:47 pm on Feb 6, 2012 (gmt 0)

Would you be referring to using a Stored Procedure to do the comparisons?

Ocean10000

WebmasterWorld Administrator 10+ Year Member



 
Msg#: 4412808 posted 7:50 pm on Feb 6, 2012 (gmt 0)

Yes either a stored procedure or ADO command object using sql string with strongly typed parameters, to avoid injection attacks.

By having the Database do the comparison it should speed things up since it doesn't have to send as much data back and forth between the db and ASP.

buck1107

10+ Year Member



 
Msg#: 4412808 posted 3:19 pm on Feb 7, 2012 (gmt 0)

Thanks for your response.
The data from the form that is compared against the database...would that data (cycled through an array) be input to SQL server as an array, somehow?

Ocean10000

WebmasterWorld Administrator 10+ Year Member



 
Msg#: 4412808 posted 3:00 pm on Feb 8, 2012 (gmt 0)

I am not sure why you are putting it into an array in the first place. But normally a sql server can not process an array being passed into it, its not impossible but not usually the best method for databases.

I am going to make an assumption that the database table you are comparing against has unique ID assigned to each row also called a primary key. And that you can use this to determine which rows in the db you need to update via your form.

buck1107

10+ Year Member



 
Msg#: 4412808 posted 4:24 pm on Feb 8, 2012 (gmt 0)

The array is needed because the form posts many rows to the same table - the form has approx 300 rows, some of which change, and some that don't. Each of those goes into the array, which is then compared against the DB rows that already exist, to compare for changes.

Ocean10000

WebmasterWorld Administrator 10+ Year Member



 
Msg#: 4412808 posted 3:00 pm on Feb 9, 2012 (gmt 0)

When I am developing a site I normally try to avoid this situation altogether because of the bottle necks you are having.

If I was in this situation, I would end up coding the update stored procedure to work on a single row, and loop though updating row by row. In most databases the stored procedures are strongly typed. But it would only update the record if one of the fields was different then what was currently available.

The main point I am trying to make is I would push the checking of the data into the database level, and out of the Classic ASP code. This will avoid having to pull all that data back down again, then push the updates back.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Microsoft / Microsoft IIS Web Server and ASP.NET
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved