1.5 mil recs and a web based UI
-
Here's a challenge I am facing right now. I need to develop a user interface allowing the user to be aware of 1.5 million distinct records that are Company names through a web app. Take a God's eye view perspective on this with me. Passing 1.5 mil recs to a drop down list for fast perusing is not preferable because of the 6 minute download time (I tried hehe). Anyone have any big ideas? Oh, by the way, multiply this question by 5. I have 5 fields with many records that I need to make the user aware of based on his selections.
Thanks in advance
-
This seems like a multiple lists form to me. EX :
Load the list of countries, once an item is selected,
Load the regions (states, qhatever)
Load the towns
Then load the clients.
This way it's still easy to find them and you don't load combos for 30 minutes
.Note that you might need a few versions to make this work.
-
Either that, or a single page form, "Enter the number of the record you want."
🙂
-
With a good progress meter
. -
I like the idea of asking how many records the user wants with a progress indicator. I think I'll use an I-frame and some flash and java for that.
I like the idea of Country, State, City, drill down idea, however in this scenario there is more than 1 logical starting point. I just got off the phone with a friend who suggested a structure like dmoz.org. I think that's the approach I will take. This way I can show off tons of data quickly without long load times.
Thanks guys!
-
Firstly, if country, State, City, ... hierarchy is not avaible, you can has a list of buttons of letters "A", "B", "C", "D",...."Z".
Second, it's better to implement paging in the server side. And in the UI implement the page control logic (next pg, previous pa, etc)
Just my 2cents
-
Or even a search form... You just can't download 1.5 M records and show them... that's just not programming.
-
An alternative to the above suggestions is to take a look at some of the UI widgets that exist out there. I don't really know how you want to present your data, but there are tables that will page data and drop downs that will do the same. See the http://www.ebusiness-apps.com/ combo box for an example of how massive amounts of data can be presented to the user.
-
The data categories are Company Name(1.5 mil distinct), Title(10k distinct), Division(100), Department(10k distinct), and Country(100).
The user may search for any of the above and filter by any.
This is why Country, State, City, Address is not a good model to follow because it has 1 logical entry point and 1 logical exit point.
The problem I'm dealing with has multiple entry and exit points with each being able to filter the other.
-
You'll have to do a search engine... there's really no point in loading 1.5M record and expecting the person to read it all to find the match.
-
a search form with the columns for filtering would do.
In the SQL, you can do some logic such as
SELECT TOP 10 * FROM table
WHERE
@col1 IS NULL OR @col1 = col1
AND
@col2 IS NULL OR @col2 = col2
AND
@col3 IS NULL OR @col3 = col3
You can pass in NULL if that's not one of your search criterias the user specified.
Indexes on each of those columns individually since you won't know which ones the user will be running.
BTW, seriously consider doing pagination in the db and using the first_rows hint. It's tedious but worth it
And no COUNT(*).
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply