[ic] Customers.HTML Crashes With Large UserDB

[Mike Heins]

Quoting Ed LaFrance (edl@newmediaems.com):
> At 08:09 PM 5/23/2002 -0500, you wrote:
> >Hi -
> >
> >I'm currently running IC 4.8.4 on FreeBSD with MySQL.
> >
> >When I access /admin/customer.html, I get an Internal Server Error.
> >Once upon a time, this wasn't a problem; however, we have almost 15k 
> >customers in our userdb now.
> >
> >After some creative MySQL logging, I figured out that IC is passing the 
> >following query to MySQL when customer.html loads:
> >"SELECT * FROM userdb"
> >
> >Each time I load the customer.html page, IC is requesting all 15,000 records.
> >I think that IC waits so long for the query to finish that it sees the 
> >instance as a "spurious process" and kills it before it displays the page 
> >-- hence the 505 Internal Server Error.
> >
> >Here's the code snipped that causes the problem.
> >
> >I tried adding a couple result limiting options to the search, but IC 
> >doesn't seem to apply them when it translates the search into an SQL query.
> >
> >[search-region more=1 search="
> >                 fi=userdb
> >                 ml=25
> >                 mm=25
> >                 md=1
> >                 st=db
> >                 [if cgi mv_like_spec]
> >                     [cgi ui_text_qualification]
> >                 [elsif cgi ui_text_qualification]
> >                     se=[cgi ui_text_qualification]
> >                 [/elsif]
> >                 [else]
> >                     co=yes
> >                     sf=inactive
> >                     se=1
> >                     op=[scratch active_sense]
> >                 [/else]
> >                 [/if]
> >                 [if cgi ui_sort_field]
> >                     tf=[cgi ui_sort_field]
> >                     to=[cgi ui_sort_option]
> >                 [else]
> >                     tf=0
> >                 [/else]
> >                 [/if]
> >                 [scratch start_at][set start_at][/set]
> >                 rf=username,company,lname,fname,city,state,country
> >"
> >]
> >
> >Any advice would be very helpful...  :)
> 
> I've seen similar behavior with some clients who have large userdb tables. 
> You might want to consider modifying admin/customer.html to dispense with 
> the default listing of all active customers in favor of a purely 
> search-driven listing. This could mean that the admin must enter a keyword 
> or two in the "Limit with search" box to get a listing. You could also do a 
> series of click-based searches linked to an alphabetized index across the 
> top of the page (A B C D etc).
> 
> In other words, the page needs to be redesigned to optimize it for a large 
> customer base.

Actually, if you set the LARGE parameter for the database table in
question, and change the "Customers" menu item to point to admin/flex_select
with a table of userdb, I believe it does that automatically. It may not
be in 4.8, but it is in 4.9 for sure.

	Database userdb LARGE 1

then in icmenu:

	code: 0002
	mgroup: Top
	name: Customers
	page: admin/flex_select
	form: mv_data_table=userdb

To make this work in all situations, you can instead override
admin/customer.html with a flex_select version hard-coded to have userdb
as the table.

-- 
Mike Heins
Perusion -- Expert Interchange Consulting
phone +1.513.523.7621      <mike@perusion.com>

Prove you aren't stupid.  Say NO to Passport.