Improve performance on a process which populate a dropdownlist

Posted on 2006-06-29
Medium Priority
Last Modified: 2008-02-01
I've got a problem with speed on a dropdownlist when it is populated

I have 2000 records in one table (Clientes), this will eventually be upto!

I need to display those 2000 records in a dropdownlist on a web page.  The format will be IdCliente, Nombre

Here's my code:

        Me.cmbCliente.DataSource = oControlMensajes.GetDataCliente("sp_GetDataCliente")
        Me.cmbCliente.DataValueField = "IdCliente"
        Me.cmbCliente.DataTextField = "Nombre"
        Me.cmbCliente.Items.Insert(0, nulo)

My function GetDataCliente is in other solution which has classes for the management of data access, encryption

and so on. The code is this:

 Public Overloads Function GetDataCliente(ByVal strsql As String) As DataSet
        Dim ConnectionString As String = ConfigurationSettings.AppSettings("ConnString")
        Dim con As New SqlConnection(ConnectionString)
        Dim Command As New SqlCommand(strsql, con)
        Dim Adapter As New SqlDataAdapter(Command)
        Dim oDataSet As New DataSet

        Command = con.CreateCommand
        Command.CommandText = strsql
        Command.CommandType = CommandType.StoredProcedure

        Adapter.SelectCommand = Command
        Adapter.Fill(oDataSet, "Datos")
        Return oDataSet
    End Function

As you can see im using a stored procedure to return the results, I'm using recordset.
The combobox eventually populates in about 10 - 15 seconds! All processing is done on the server.

Can anyone give me any hints or tips on how to get this down to about 5 seconds???

Urgent response would be much appreciated

Question by:lcc_kaury
  • 4
  • 3
  • 3
LVL 27

Expert Comment

ID: 17015222
15 seconds for 2000 records in a dropdown menu is excellent time.
May I ask why this did you choose to have this type of design? It will take the user a lot of time to go through this list
LVL 12

Expert Comment

ID: 17015290
I agree,

what size is the aspx page if you save it to disk?

dont ask me for the code for this but this would help:
1. javascript to capture the "dataset" object
2. auto complete dropdownlist generated from the data object so that you are not loading loads of text.
LVL 27

Expert Comment

ID: 17015322
7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

LVL 12

Expert Comment

ID: 17015384
I think regardless of what you do, with 2000 records the text that is rendered must still be transported to the client browser through the network connection eg broadband or dialup. caching will hardly help performance in this case - the cached data is server side so it still has to "download" to the client browser, the server getting these records from a database would only be like a second or so anyway(unless there are many users connected simultaneously ...

maybe a rethink of design here is better, like a search box that populates the droplist rather, or maybe a procedure that use a limit sql statement almost like paging in a datagrid - so what happens is the user gets the first 100 records and the last list item is "more..." if the user selects this it gets the next set of 100 records...
or maybe hyperlinks with eg A B C D E F .... when the user clicks a link it populates the grid...

just remeber that not only is the data in the droplist stored in the page, but so is the view state for that control...

good luck

LVL 27

Expert Comment

ID: 17015429
I agree

Accepted Solution

Drakecoldwinter earned 1000 total points
ID: 17015438
I have a solution performance wise for you, but it is a complex solution to implement. I will give you bits of my own code and you must adapt this to your proyect. The solution goes like this, you make a web page called dropdown.aspx, this page grabs a SQL request from a Querystring and produces a list with names, so you ask for exemple the names Like Pe% then you will get only the names beggining with pe% this list should be small one (around 100 maybe) then on the main.aspx page you put a textbox, then with javascript you make a on key down and grab the letters inputed on the textbox and then use javascript to grab data from dropdownlist.aspx and fill a floating div under the textbox, in that floating div, you put the list with the names beggining with Pe... I used this solution with a database of 1300 records and the list loads in under 2 seconds ;)

If you are hard coder and like chalenges then read this, otherwise scroll down for the easy (but not free) solution

//goblal vars declared
var keys;var timeStamp;timeStamp = new Date();var globalTimeout;

//this function grabs a file and parses data from inside.
function getFile(filename){oxmlhttp = null;try{oxmlhttp = new XMLHttpRequest();oxmlhttp.overrideMimeType('text/xml');}catch(e){try{oxmlhttp = new ActiveXObject('Msxml2.XMLHTTP');}catch(e){ return null;}}if(!oxmlhttp) return null;try{oxmlhttp.open('GET',filename,false);oxmlhttp.send(null);}catch(e){ return null;}return oxmlhttp.responseText;}

//this function is the most important one, it actually does the keypress, seek and parse of the file, also generates the div, very complex...
function smartkeypress(thetextbox,thediv,theframe,theindex,theselecteditem,themaxitems){
var key = event.keyCode;var tempstr;
var selecteditem; selecteditem = parseInt(theselecteditem.value);
var maxitems; maxitems = parseInt(themaxitems.value);
if (key!=9){event.returnValue=false;}
if (key==9){makedivappear(thediv,'hide',theframe);}
if (key==8){thetextbox.value='';keys='';}
if ((key==13)||(key==40)||(key==38)){
if (key==40){if (selecteditem<maxitems){selecteditem = selecteditem + 1;}}
if (key==38){if (selecteditem>0){selecteditem=selecteditem-1;}}
theframe.document.location='#section' + selecteditem;
tempstr = theframe.document.getElementById('section' + selecteditem).innerHTML;
theindex.value = theframe.document.getElementById('item' + selecteditem).alt;
if (tempstr!='undefined'){thetextbox.value = tempstr;}
if (key==13){makedivappear(thediv,'either',theframe);}}
if ((key>=97 && key<=122) || (key>=65 && key<=90) || (key>=48 && key<=57)){
key = String.fromCharCode(key);
var now = new Date();
var diff = (now.getTime() - timeStamp.getTime());
timeStamp = new Date();
if (diff > 1500){keys = key;}else{keys = keys + key;}
keys = keys.replace('undefined','');
thetextbox.value = keys.toLowerCase();
      var myString = getFile("Dropdown.aspx?SQL=SELECT * FROM tbl_Boutons WHERE fld_Nom LIKE '" + keys.toLowerCase() + "%' ORDER BY fld_Nom");
      var myarray = myString.split('@@@@');
      var wi = theframe;
      wi.document.write("<html><body style='border-style:solid;border-width:1px;' topmargin='0' leftmargin='0'><table width='100%' height='100%' cellpadding='0' cellspacing='0'>");
      wi.document.write("<tr><td colspan='2' height='20' style='border-bottom-style:solid;border-bottom-width:1px;border-color:000000;'>En tete</td></tr><tr><td colspan='2'><div style='overflow-y:auto;height:180px;' onfocus='parent.window.clearTimeout(parent.globalTimeout);' onblur='parent.hideDiv(parent." + thediv.name + ",parent." + theframe.name + ");'><table width='100%' cellpadding='0' cellspacing='0'>");
      if (myarray.length==1){
            theindex.value = "";
            maxitems = "";
            wi.document.write("<tr onclick=\"javascript:parent.document.forms[0]." + theselecteditem.name + ".value=" + ii +";parent.makedivappear(parent." + thediv.id + ",'hide',parent." + theframe.name + ");\"><td colspan='2'>Aucun nom de retrouvé<br>here comes demostring</td></tr>");}      
      for(var ii=0;ii<myarray.length-1;ii++){            
            var items = myarray[ii].split('##');            
            if (ii==0){                        
                  wi.document.write("<tr alt='" + items[0] + "' id='item" + ii + "' style='background-color:#eeddee' style='cursor:default' onclick=\"javascript:parent.document.forms[0]." + theindex.name + ".value=" + items[0] + ";parent.document.forms[0]." + thetextbox.id + ".value='" + items[1] + " " + items[2] + "';parent.makedivappear(parent." + thediv.id + ",'hide',parent." + theframe.name + ");parent.document.forms[0]." + thetextbox.id + ".focus();\"  onmouseover=\"javascript:parent.document.forms[0]." + theselecteditem.name + ".value=" + ii + ";parent.selectthegooditem(parent." + theframe.name + ",parent.document.forms[0]." + theselecteditem.name + ",parent.document.forms[0]." + themaxitems.name + ");\"><td colspan='2' height='20'><A NAME='section" + ii + "' />" + items[1] + " " + items[2] + "</td></tr>");                              
                  thetextbox.value = items[1] + " " + items[2];                        
                  theindex.value = items[0];                        
                  maxitems = myarray.length-2;            
                  wi.document.write("<tr alt='" + items[0] + "' id='item" + ii + "' style='cursor:default' onclick=\"javascript:parent.document.forms[0]." + theindex.name + ".value=" + items[0] + ";parent.document.forms[0]." + thetextbox.id + ".value='" + items[1] + " " + items[2] + "';parent.makedivappear(parent." + thediv.id + ",'hide',parent." + theframe.name + ");parent.document.forms[0]." + thetextbox.id + ".focus();\"  onmouseover=\"javascript:parent.document.forms[0]." + theselecteditem.name + ".value=" + ii + ";parent.selectthegooditem(parent." + theframe.name + ",parent.document.forms[0]." + theselecteditem.name + ",parent.document.forms[0]." + themaxitems.name + ");\"><td colspan='2' height='20'><A NAME='section" + ii + "' />" + items[1] + " " + items[2] + "</td></tr>");            
            wi.document.write("<tr><td colspan='2'></td></tr></table></div></td></tr><tr><td style='border-top-style:solid;border-top-width:1px;border-color:000000;'>Buy me !</td><td height='50' align='right' style='border-top-style:solid;border-top-width:1px;border-color:000000;'><img src='Cold images/Colddrop images/botmenudcw1.gif'></td></tr>");
      if (thediv.style.visibility!='visible'){makedivappear(thediv,'show',theframe);}
}theselecteditem.value = selecteditem;themaxitems.value=maxitems;selectthegooditem(theframe, theselecteditem, themaxitems);}

//this function makes the div appear with a nice fade in fade out effect
function makedivappear(thediv,action,theframe){
      if (theframe.document.location!='about:blank'){
            if (((thediv.style.visibility=='visible')||(action=='hide')) && (action!='show')){
                  if (thediv.filters.blendTrans.status != 2) {
                        if (thediv.filters.blendTrans.status != 2) {

//this makes the div disapear, no effect
function hideDiv(thediv,theframe){globalTimeout = window.setTimeout('makedivappear(' + thediv.name + ',"hide",' + theframe.name + ');', 300);}

//this function selects different items on the div and puts them on the dropdownbox
function selectthegooditem(theframe, theselecteditem, themaxitems){
      var selecteditem; selecteditem = parseInt(theselecteditem.value);
      if (selecteditem>=0){for(var ii=0;ii<=parseInt(themaxitems.value);ii++){theframe.document.getElementById('item'+ ii).style.backgroundColor='#ffffff';}      
      theframe.document.getElementById('item'+ selecteditem).style.backgroundColor='#eeddee';}}

now, after all this code, you just need to do :
<table cellpadding='0' cellspacing='1' bgcolor="#999999" width='200'>
<tr><td bgcolor="#CCCCCC" valign='middle' align='center' alt="test">
<table cellpadding='0' cellspacing='0' width='200'><tr>
<td valign='middle'>
<input type='text' id='p2F32tmpmaxitems' name='p2F32tmpmaxitems' style='display:none'>
<input type='text' id='p2F32tmpindex' name='p2F32tmpindex' style='display:none'>
<input type='text' id='p2F32tmpselect' name='p2F32tmpselect' style='display:none'>

<input type='text' style='color:green;font-size:8pt;background-color:CCCCCC;' onBlur='javascript:hideDiv(p2F32tmpdiv,p2F32tmpmenu);'
id='p2F32tmpbox' name='p2F32tmpbox' style='width:184px;border:none' onclick='javascript:makedivappear(p2F32tmpdiv,"either",p2F32tmpmenu);'
onkeydown='javascript:smartkeypress(p2F32tmpbox, p2F32tmpdiv,p2F32tmpmenu,p2F32tmpindex,p2F32tmpselect,p2F32tmpmaxitems);' >
</td><td valign='middle'><img src='/Cold images/Colddrop images/arrowup.gif' onmouseup='this.src="/Cold images/Colddrop images/arrowup.gif"'
onmousedown='this.src="/Cold images/Colddrop images/arrowdown.gif"' onclick='javascript:window.clearTimeout(parent.globalTimeout);makedivappear(p2F32tmpdiv,"either",p2F32tmpmenu);p2F32tmpbox.focus();'></td>
<td width='1'><td></tr></table></td></tr></table>
<div id='p2F32tmpdiv' name='p2F32tmpdiv' style='visibility:hidden;position:absolute;'>
<iframe width='300' height='250' frameborder='0' id='p2F32tmpmenu' scrolling="no" name='p2F32tmpmenu'></iframe></div>

Now, did I say that this solution is not for the faint of heart ? I'm trying to put this together and do a custom control, but with my lack of time I don't expect to release this custom control soon... If you are not very fluent with javascript and happy to try this chalenge I sugest you the easy way, find and buy an auto completing dropdownbox control (http://www.asp.net/ControlGallery/default.aspx?Category=23&tabindex=2 ) I sugest you to check http://www.dbcombo.net/ very closely as they offer a very good dropdownbox that can manage easily 2000 records and will load in under 2 seconds. Also is not very expensive. Of course the code I wrote is free but it doesn't work as well.

Te deseo suerte con tu proyecto y con el dropdown ;)

Expert Comment

ID: 17015457
I'm bad, I forgot the code from the dropdown.aspx page, the code up here goes into a javascript section on Main.aspx, the page where you put the dropdownlist, here it is the dropdown.aspx code :

<%@ Page Language="VB" ContentType="text/html" %>
<%@ Import Namespace="System.Data.OleDb" %>
<SCRIPT language="vb" runat="server">
Sub Page_Load()
         Dim myConnection5 as OleDbConnection = New OleDbConnection(ConfigurationSettings.AppSettings("access_data"))
      Dim mySQL5 = Request.Querystring("SQL")
      Dim myCmd5 as New OleDbCommand(mySQL5,myConnection5)
         Dim myreader = myCmd5.ExecuteReader()
         Do While myreader.Read()
               Response.write(myreader("pk_Numero") & "##" & myreader("fld_Nom") & "##" & myreader("fld_Iconne") & "@@@@")
         myreader = nothing
      myConnection5 = Nothing
End Sub

I'm really sorry for the lack of comments in my code and for the complex javascript, I'm a very messy programmer and often my code gets very complex :( but as I sayed, if you are up to chalenges and know your javascript you can clean and analyze my code to get a cleaner and easier solution.

Que te sea leve... ;)

Expert Comment

ID: 17015475
just another comment http://www.dbcombo.net is a cheap solution, very dinamic and performant (the demo on their web uses a 60 000 records database) but under firefox it looks ugly as hell...  :( is very pitifull that they don't support better a browser that is used by more than 10% of internet public... before investing on dbcombo check your user group if they use firefox or Ie
LVL 12

Expert Comment

ID: 17015916

limit the number or records the query pulls in the first place.
LVL 27

Expert Comment

ID: 17033705
looks like it :-)

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
When you have multiple client accounts to manage, it often feels like there aren’t enough hours in the day. With too many applications to juggle, you can’t focus on your clients, much less your growing to-do list. But that doesn’t have to be the cas…
From store locators to asset tracking and route optimization, learn how leading companies are using Google Maps APIs throughout the customer journey to increase checkout conversions, boost user engagement, and optimize order fulfillment. Powered …
Suggested Courses

607 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