Concatenation and recordset loops - speed it up

I have a recordset with code below that loops through each record and concatenates a string for a "<select>" option list. The problem is that the performance suffers greatly after loop through about 1000 records. How can I speed this query up and build the string as it is without suffering performance?

clisttxt = ''
while (!myQuery.eof && xrcount < 10000) {
    clisttxt += '<option value="'+myQuery.comp_companyid+'" '+iscselected+'>'+ myQuery.comp_Name+ ' </option>'
   myQuery.NextRecord();
}

clisttxt += '</optgroup></select>'
saturationAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Big MontyWeb Ninja at largeCommented:
normally, I would recommend using the GetRows method, however since you're using jscript for your server side language, I don't know how much relief you'll get, but I still recommend trying it.

Using this page as an example, your code may look something like this:

var tempArray = myQuery.GetRows();
var recArray = tempArray.toArray();

var col = 1;
var maxCols = myQuery.Fields.Count;

var selectValue, selectText;

clisttxt = ''
for (var thisField=0; thisField<recArray.length; thisField++) {
  if( col == 1 ) 
     selectValue = recArray[thisField];

  if( col == 2 )
    selectText = recArray[thisField];

  if (col > maxCols) {
    clisttxt += '<option value="'+selectValue +'" '+iscselected+'>'+ selectText + ' </option>';
    col = 1;
  }
}

Open in new window

the above code assumes only 2 fields are being returned in your sql query
saturationAuthor Commented:
I can't call GetRows() ("object doesn't support...") ...Is there another way to grab the recordset without a loop?/
leakim971PluritechnicianCommented:
send the options after page load with an ajax request. send only the data to the web browser and build the options with the browser
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
>The problem is that the performance suffers greatly after loop through about 1000 records.

Although jscript runs considerably slower than vbscript, 1000 records is still 1000 records and there should not be a reason to get that many rows of data to the screen and especially for a select/dropdown.   Too much data is sent to the browser and too many choices for the select statement.

If you must, I would write the data to a text file.  You can either place the data inside the option tag via a server side include, serverside ajax (xmlhttppost) or client side ajax.

The serverside include is going to be easy and straight forward.  First you have to write the data to the server.  You can write it as the exact html you you need.

You will have a separate page of pure asp that writes the file.  I have done this for blog tags and worked very nicely where there were many hundreds of tags.  In my case, I run this page every time the blog is posted.  If you have data that is created dynamically from multiple sources, then consider a scheduled task to run this page.

Here it is in vbscript using filesystem object. You will need to give the IIS_IUSRS or IIS_WPG account write permissions for the folder you are saving the text file in.  

I guarantee this will be a huge savings in time although I still think it is way too much data for a select.    

dim fs,f
set fs=Server.CreateObject("Scripting.FileSystemObject")
set f=fs.CreateTextFile("c:\intetpub\mydomain\includes\mySelect.asp",true)

do until myQuery.eof
    f.WriteLine("<option value='"&myQuery.comp_companyid& iscselected&">"& myQuery.comp_Name& " </option>")

myQuery.movenext
loop

f.close
set f=nothing
set fs=nothing

Open in new window


Another option instead of a select is to use ajax and typeahead  https://twitter.github.io/typeahead.js/ or http://jqueryui.com/autocomplete/.  These libraries will let you start typing and autocomplete the rest just like google. Probably a much better experience than picking from 1000 dropdown options.
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Almost forgot, you need to include the file http://www.w3schools.com/asp/asp_incfiles.asp

<option>
<!--#include virtual="\mySelect\mySelect.asp"-->
</option>

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
saturationAuthor Commented:
Thanks guys--give me a few days to try these out and I'll get back! I really appreciate it.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.