Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
LVL 27

Expert Comment

by:Sammy Ageil
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

by:Sammy Ageil
ID: 17015322
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

by:Sammy Ageil
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{'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." + + ",parent." + + ");'><table width='100%' cellpadding='0' cellspacing='0'>");
      if (myarray.length==1){
            theindex.value = "";
            maxitems = "";
            wi.document.write("<tr onclick=\"javascript:parent.document.forms[0]." + + ".value=" + ii +";parent.makedivappear(parent." + + ",'hide',parent." + + ");\"><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]." + + ".value=" + items[0] + ";parent.document.forms[0]." + + ".value='" + items[1] + " " + items[2] + "';parent.makedivappear(parent." + + ",'hide',parent." + + ");parent.document.forms[0]." + + ".focus();\"  onmouseover=\"javascript:parent.document.forms[0]." + + ".value=" + ii + ";parent.selectthegooditem(parent." + + ",parent.document.forms[0]." + + ",parent.document.forms[0]." + + ");\"><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]." + + ".value=" + items[0] + ";parent.document.forms[0]." + + ".value='" + items[1] + " " + items[2] + "';parent.makedivappear(parent." + + ",'hide',parent." + + ");parent.document.forms[0]." + + ".focus();\"  onmouseover=\"javascript:parent.document.forms[0]." + + ".value=" + ii + ";parent.selectthegooditem(parent." + + ",parent.document.forms[0]." + + ",parent.document.forms[0]." + + ");\"><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 (!='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 ((('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(' + + ',"hide",' + + ');', 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 ( ) I sugest you to check 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 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

by:Sammy Ageil
ID: 17033705
looks like it :-)

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Lots of people ask this question on how to extend the “MembershipProvider” to make use of custom authentication like using existing database or make use of some other way of authentication. Many blogs show you how to extend the membership provider c…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

721 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