Solved

Improve performance on a process which populate a dropdownlist

Posted on 2006-06-29
10
666 Views
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.DataBind()
        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")
        con.Close()
        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

0
Comment
Question by:lcc_kaury
  • 4
  • 3
  • 3
10 Comments
 
LVL 27

Expert Comment

by:Sammy
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
0
 
LVL 12

Expert Comment

by:deanvanrooyen
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.
0
 
LVL 27

Expert Comment

by:Sammy
ID: 17015322
0
 
LVL 12

Expert Comment

by:deanvanrooyen
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

0
 
LVL 27

Expert Comment

by:Sammy
ID: 17015429
I agree
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 2

Accepted Solution

by:
Drakecoldwinter earned 250 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.open();
      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){
            selecteditem="";
            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];                        
                  selecteditem=0;                        
                  theindex.value = items[0];                        
                  maxitems = myarray.length-2;            
            }else{                        
                  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>");
            wi.document.write("</table></form></body></html>");
            wi.document.close();
      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')){
                  thediv.style.filter="blendTrans(duration=0.5)";
                  if (thediv.filters.blendTrans.status != 2) {
                        thediv.filters.blendTrans.apply();
                        thediv.style.visibility='hidden';
                        thediv.filters.blendTrans.play();}
                  }else{thediv.style.filter="blendTrans(duration=0.5)";
                        if (thediv.filters.blendTrans.status != 2) {
                              thediv.filters.blendTrans.apply();
                              thediv.style.visibility='visible';
                              thediv.filters.blendTrans.play();}}}}

//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 ;)
0
 
LVL 2

Expert Comment

by:Drakecoldwinter
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")
      myConnection5.Open()
      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") & "@@@@")
         Loop
         myreader.close()
         myreader = nothing
         myConnection5.Close()
      myConnection5 = Nothing
End Sub
</SCRIPT>


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... ;)
0
 
LVL 2

Expert Comment

by:Drakecoldwinter
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
0
 
LVL 12

Expert Comment

by:deanvanrooyen
ID: 17015916
so...

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

Expert Comment

by:Sammy
ID: 17033705
deanvanrooyen,
looks like it :-)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

AJAX ModalPopupExtender has a required property "TargetControlID" which may seem to be very confusing to new users. It means the server control that will be extended by the ModalPopup, for instance, if when you click a button, a ModalPopup displays,…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

758 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now