ASP.NET Database/speed issue 1

[GhostWolf]

The front page of the site I'm creating contains a drop down list; the code-behind uses a SQL query to populate the list with almost 10,000 choices. This initial population takes almost forever to complete.

I've seen mention of services and, (although I know nothing about them yet), wonder if it might not be beneficial to set that list up as a service.

I'm not certain, though, because the pages that front page redirects the visitor to also take what I consider to be an inordinately long time to load - leading me to wonder how much of the speed problem is due to the limited-capacity server we're using.

Any advice would be greatly appreciated.

You should be able to get a look at the problem at

http://staging.jeffersoncountyclerk.org/wdiv

 

[ca8msm]

It's not how you are getting those 10k items to the browser that is the concern, it is the fact that you are attempting it. This is far too many items to add and no-one could be expected to browse through that list to pick one, yet alone asking a browser to render them all.

You need to look at a better method of allowing the use to make a choice such as:

1. A search page so they can narrow down the number of items
2. A predictive "as-you-type" search that displays matching items as the user types in each character
3. Categories that allow the user to drill down to a particular item

etc...

Mark,

Darlington Web Design[tab]|[tab]Experts, Information, Ideas & Knowledge[tab]|[tab]ASP.NET Tips & Tricks

 

[GhostWolf]

The list is street names; as the user types, the list behaves as a combobox. (It's a list of street names in the county we serve.)

My argument when first asked to provide this functionality was that the list would be too large, but PETAYBE says "do it."

Any advice on how to provide this list without making the user wait so long? Or how to provide this functionality for this list?

 

[ca8msm]

Any advice on how to provide this list without making the user wait so long?

Yes, any of the 3 examples I gave above. You simply can't write out 10k items to a web page and expect it to be quick.

Personally, I'd go with #2 like you get when you start typing something into Google (and it predicts what you are looking for as each character changes).


Mark,

Darlington Web Design[tab]|[tab]Experts, Information, Ideas & Knowledge[tab]|[tab]ASP.NET Tips & Tricks

 

[GhostWolf]

Okay, then I get to show my ignorance - again. It seems to me that that functionality is akin to a combobox: it has to have data to work against in order to autocomplete.

I've modified it to use a SQLDataSource, but apparently that's not the solution either.

So, I guess I need to ask: how can I accomplish the same functionality without a dropdownlist?

 

[jmeckley]

here is one example of an auto complete (jquery autocomplete). you don't load any addresses until the user begins requesting them. and even then you need to limit the returning results (10, 20, 25. i wouldn't return any more than that).

you will also need to determine a decent search algorithm as well. Full Text Searching (whether sql server full text indexing or lucene.net) is probably the most user friendly search. It's also the most difficult to implement as a developer.


Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[jbenson001]

The SQLDataSource control is NOT the answer, stay AWAY from the datasource controls.

Mark and Jason made some very good suggetions.

If you are dead set on using a ddl, then I would load the street names into cache, since they aren't going to change.

 

[jmeckley]

cache or db, it's still a preformance hit based on size and rendering.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[jbenson001]

if the cache is already loaded, then loading the ddl will be signifigantly faster
However, I don't agree that loading the ddl with the full set of street names is the best idea.

 

[jmeckley]

you save on the remote call, but you still have to consider loading all objects into memory which may or may not be used. The hardware this is running on will play a role in that factor. I would favor querying the database each time returning a limited set of records before I move to cache.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[GhostWolf]

Oh no, I'm not dead set on using anything! It was just that drop down list was what I was familiar with from VB6, (this is my second ASP.NET page, and the first that's really involved).

Right now I'm checking the jQuery Autocomplete link from jmeckley above - and it looks awfully good to me.

 

[jmeckley]

this is my second ASP.NET page

nitpicker:
your second WebForms page. asp.net doesn't have pages, it just handles requests and responses (application, session, cookies, request, response, etc).
Webforms is an html engine and page controller. that is where page, user controls, web server controls come into play

it's an important detail that few .net web developers understand. knowing this will help you better understand how webforms and asp.net work along with the base knowledge for understanding other asp.net webpage frameworks like MVC.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[GhostWolf]

Picky, picky, picky!

Kidding aside, I'll learn some of this terminology yet - and I do appreciate the responses so far.

I'm still looking at the AutoComplete offering...

 

[ingresman]

You could also use a serial file with the street names in that will save a load of SQL Server overhead. You could refresh the file every day via an batch job. You could even make the page static (i.e. not even an asp.net page !) or something like

http://support.microsoft.com/kb/306575

and never actualy open the file, just let asp.net do it.
I'd also look into getting IIS to gzip your page as well (if only this one) as if you have 10,000 address with an average of say 30 bytes that's 300,000 which these days is not too bad but still quite big.
These links look interesting

http://www.codeproject.com/KB/aspnet/GoogleSuggestDictionary.aspx

http://userscripts.org/scripts/review/5948

http://www.codeproject.com/KB/scripting/jsactb.aspx

 

[jmeckley]

ingresman, while these are all good practices at the infrastructure, it still doesn't solve the business problem that too much data is presented to the user at once.

these practices would solve the symptoms, not the problem.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[ingresman]

Sure , totaly get that point , but just in case I tbought I'd just add my 2penneth !
It would be interesting to understand what the business problem is or is to OP just thinking this would be a good idea?

 

[GhostWolf]

Thanks Jason: jQuery's AutoComplete works just fine. I'd like to figure out how to make it do a Like search instead of a Contains search though.

 

[jmeckley]

how do you define "like".
Contains is a form of like, but if you mean "sounds like" or "similar to" then you are getting into a whole new form of querying, and not necessarily a database query.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[GhostWolf]

Like: "where [FieldName] like 'be%'"
Contains: "where [FieldName] contains 'be'"

That Like sample will find only words beginning with "be", but Contains returns anything with "be" in it. I use "like" queries every day against SQL Server and DB2 databases, but haven't learned enough java yet to figure out where to modify jQuery to accomplish them.

 

[jmeckley]

wait, how are you querying exactly? I'm assuming you are using jquery's autocomplete to access a web service. the webservice would query the database to get your results. it would look something like this

$('the text box').autocomplete({
   source: 'myservice/search.asmx',
   minLength: 2
});

class MyService : WebService
{
   public string[] Search(string term)
   {
       select top 20 field from table where field like %term% order by field
       return results;
   }
}

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259