Concatenation and recordset loops - speed it up

Posted on 2014-10-09
Last Modified: 2014-10-16
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>'

clisttxt += '</optgroup></select>'
Question by:saturation
LVL 33

Expert Comment

by:Big Monty
ID: 40372088
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

Author Comment

ID: 40372104
I can't call GetRows() ("object doesn't support...") ...Is there another way to grab the recordset without a loop?/
LVL 82

Expert Comment

ID: 40372107
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

LVL 52

Assisted Solution

by:Scott Fell, EE MVE
Scott Fell,  EE MVE earned 500 total points
ID: 40372128
>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>")


set f=nothing
set fs=nothing

Open in new window

Another option instead of a select is to use ajax and typeahead or  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.
LVL 52

Accepted Solution

Scott Fell,  EE MVE earned 500 total points
ID: 40372131
Almost forgot, you need to include the file

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

Open in new window


Author Comment

ID: 40373484
Thanks guys--give me a few days to try these out and I'll get back! I really appreciate it.

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How do specify keydowns in JavaScript 3 44
Why doesn't Google directions work? 6 44
PHP Form Calculate Total Price 10 42
Hide cell in a table 2 11
I've been trying to accomplish this for a while and it just struck me yesterday how to accomplish this task. I have done searches all over the internet looking for ways to email pages from my applications and finally I have done it!!! Every single s…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question