Paul Shearing
|
| Posted: 12/03/2005, 12:17 PM |
|
CCS 3.0 ASP with Templates. Dev System = WinXP Pro SP2. IIS AMD 4800+ 4GB RAM 500GB disk.
I have developed a search page that has 42 search fields on it. Sounds a lot but in fact many fields are doubled-up because I need to test a range (eg weight in tonnes between a lower and a higher value).
When the link to this search page is clicked, the system goes into a deep loop with InetInfo.exe at very high CPU until a timeout occurs. The search page does not contain any loops that I can see - its seems to be completely linear so I'm guessing that we must be hitting some internal limit in ASP. (I have tried splitting the Search / Grid page into two separate pages with the Search button redirecting to the grid page. Even with just the search form on its own, the system still hangs.)
HELP!!
I now have v. tight deadlines of a major project and this has thrown a spanner in the works at a late stage.
Paul
PS In IIS I have fiddled with the web page Application protection settings and this has made no difference. Neither have the ASP file cache settings.
|
|
|
 |
Edd
Posts: 547
|
| Posted: 12/03/2005, 3:19 PM |
|
Paul,
42 fields - that's a new record.
I think (only guessing) you might find that it has to do with string concatenation (the killer in traditional ASP programs) and how CCS is building your where clause.
I hope your database is something respectable like MSSQL, if so then I would be looking at 1 ratiionalising my parameters and 2 converting the search to a stored procedure where you could eliminate the double ups.
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
 |
|
Paul Shearing
|
| Posted: 12/03/2005, 4:10 PM |
|
Thanks Edd
It might be a new record but the table against which I am searching has a total of 162 fields, so 42 is arguably, from the customers' point of view a bit restrictive (and yes the database is in at least 4th normal form).
What would be useful (are you listening CCS developers?) would be the ability to have, say, 6 dropdown lists each of which has the full set of available fields and against each you can nominate values. By this means you could select any field from the target table/view and nominate a search value.
Has anybody done this already - if so could you give me an idea of how to implement it?
Kind regards
Paul
(PS I'm basically a non-web software developer who has recently gotten into web development. Is it just me or is there a whole raft of arbitrary limits, restrictions and constraints in web development that dog you at every step?)
|
|
|
|
DonB
|
| Posted: 12/03/2005, 4:22 PM |
|
I think you need to delve into where the problem is. Processing the 42
parameters, or the query that uses them? It is likely to be due to the
complexity of the query that results.
Incorporating listboxes is only going to compound your problems because that
adds more queries and data transfers to setup and display those lists.
A 'hang' condition is most likely going to be due to the query taking a long
time to execute, so look there, not at the ASP code. Print out the query in
your Before Execute Select, and run it separately. Use the 'EXPLAIN'
facility in your database to evaluate the query. I hope you aren't using
Access, but a 'real' database engine.
If those 42 fields are all text or memo data, and you are using the "LIKE"
operator, you probably have an obscene quantity of table scans occurring,
thus it would take nigh onto forever to complete the query.
--
DonB
http://www.gotodon.com/ccbth
<PaulShearing@forum.codecharge (Paul Shearing)> wrote in message
news:6439233e8930f8@news.codecharge.com...
> Thanks Edd
>
> It might be a new record but the table against which I am searching has a
total
> of 162 fields, so 42 is arguably, from the customers' point of view a bit
> restrictive (and yes the database is in at least 4th normal form).
>
> What would be useful (are you listening CCS developers?) would be the
ability
> to have, say, 6 dropdown lists each of which has the full set of available
> fields and against each you can nominate values. By this means you could
select
> any field from the target table/view and nominate a search value.
>
> Has anybody done this already - if so could you give me an idea of how to
> implement it?
>
> Kind regards
>
> Paul
>
> (PS I'm basically a non-web software developer who has recently gotten
into web
> development. Is it just me or is there a whole raft of arbitrary limits,
> restrictions and constraints in web development that dog you at every
step?)
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
Vasiliy
Posts: 378
|
| Posted: 12/03/2005, 5:47 PM |
|
With ASP and MS web-server I guess you use MS SQL Server.
Try via Profiler.
Start Profiler.
After search is started, pause your Profiler and look at log. Find your search SQL request.
If no request - ASP search code needs optimization (or has problems), see task log on IIS server.
If request sent, copy and paste it into Query Analyzer.
Run your SQL request in QA and in most cases you'll be able to find the problem in SQL request by cutting working pieces from request.
_________________
Vasiliy |
|
|
|
Paul Shearing
|
| Posted: 12/03/2005, 5:55 PM |
|
Thanks Don
In every case the dropdown selection lists comprise an integer field in the main table and a single text field lookup (via a primary key). For example, CompanyID (Int primary key in the lookup table) and CompanyName (text). The back-end is SQL 2000 (I gave up on mickey-mouse solutions like MS Access 15 years ago). All the queries that are driving the search form are highly optimised and return values pretty-much instantaneously when run from MS Query Analyser. The query that I use to generate the search form returns values from a database of 140,000 records in less than one second.
I understand and appreciate your take on this - I too would be immediately suspicious of poor database design as the culprit, but I have been doing SQL databases for more than 16 years now.
Of the 42 lookup fields, just under half involve lookups - none involve LIKE or wildcard lookups. There are five companies - each of these has a company name, a country of domicile and a region of domicile. These 15 fields are lookups but the country lookup table is about 200 entries, the region table has less than a dozen and the remaining lookups have typically less than 20 values. So although the number of lookups is considerable, there is a) a lot of duplications, so records will presumably be cached and b) the number of values in the lookup tables is small. All in all, given that the lookup keys are indexed I would expect a modern database system to cope with this easily.
Thanks for the tip about [Before Execute Select] I will give this a go and post accordingly. I have a suspicion that, based on your suggestions, the culprit is the company lookup. The exception to the above is the company dropdown which selects from a table of more than 12,000 enties - and there are 5 of them. That's a lot of dropdown entries. Hmm.
If I perform a select statement that requests the company details it does so in less than a second (so I assumed that this would not be a problem) but I'm wondering now if the problem is not the time it takes to retrieve the records but the time it takes to build the dropdown lists. If, as so often is the case, the code for such operations is inefficient - on the grounds that most lists will be short so it doesn't matter - then this could be the problem. If so then the time taken is often a non-linear (typically quadratic) function of the number of entries; ouch.
The user expects to be able to select from a list of companies, by name, so I am not sure how to tackle this one. In the Access front-end to the database I have a series of buttons labelled "A..C", "D..F" etc and the user clicks one of these to populate the dropdown with company names that begin with the indicated letters. This is a doddle in Access (or my preferred language Delphi) but I have no idea how to implement this in CCS.
In any case, I have to say, this is an excellent forum with really useful feedback. Don, I think you have put me on the right track . Thanks to you and Edd for taking the time to help.
kind regards
Paul
|
|
|
|
Paul Shearing
|
| Posted: 12/03/2005, 5:59 PM |
|
Thanks also Vasiliy. Good idea. I will use MS SQL profiler as you suggest.
Paul
|
|
|
|
navneet
|
| Posted: 12/04/2005, 1:10 AM |
|
Paul i will suggest that instead of drop down lookups, please move on to popup selectable lookups, it will reduce the time required to generate the search form.
Also if you want you can in before execute sql, analayse the sql and trim out the variables which do not have criterias
|
|
|
|
DonB
|
| Posted: 12/04/2005, 4:43 AM |
|
The way I read your earlier post was that you were wanting to add listboxes.
Since you already have them, and 12K items per list, that's almost certainly
the problem. You should temporarily make them text boxes and compare the
performance. Putting that many items into a listbox is a LOT of script
execution. That's 12K loops through a chunk of script multiplied by 5
companies. If the company name is 30 characters - that alone is 1.8M of
company data to turn into lists and download. Yikes!
The CCS example for a popup picklist would tend to give better response,
since it's going to page through the full set of companies using a grid a
few at a time - but, at the expense of making the selection process slower
for the user (they have to flip through a few pages of grid data to find the
right value).
A perhaps better (although perhaps not practical - I don't know enough about
your data) alternative is to direct the user thru a set of 'pre' selection
pages so that they do a stepwise drill-down to the desired data rather than
use one massive up-front-selection to get the results.
--
DonB
http://www.gotodon.com/ccbth
|
|
|
|
Paul Shearing
|
| Posted: 12/04/2005, 9:34 AM |
|
Yup, it's the company drop-downs that are the problem. I have replaced them with text search boxes and the search form is displayed almost instantly.
What was throwing me a bit was the fact that the SQL Server was almost completely quiescent. The SQL server is not the bottleneck - it's the building of the dropdown lists by the ASP interpreter.
Version 1 of the application will have text boxes into which users can type the company name. In the mean time I will investigate the popup example.
Thanks everybody for your help and suggestions.
Kind regards
Paul
|
|
|
|
DonB
|
| Posted: 12/04/2005, 11:03 AM |
|
If that's a relatively static list, one way to deal with it is to create a
javascript file containing all the values, and call it from the window
onload event.. Something like this:
function loadlistbox(theListboxId) {
listbox = document.getElementById(theListboxId);
// Repeat the following for each possible option
listbox.options[listbox.options.length] = new Option("text1","value1");
listbox.options[listbox.options.length] = new Option("text2","value2");
listbox.options[listbox.options.length] = new Option("text3","value3");
}
In the appropriate event (such as onload) just call the function:
loadlistbox('someidvalue");
(this assumes you've added
id='someidvalue'
to the <SELECT> tag. And, of course, you would no longer specify a
Connection or Data Source for the Listbox properties in CCS. Repeat for
however many Listboxes you want it to appear in (each one having it's own
unique 'id' tag).
Put the function into a .js file and include it in your HTML.
That should load the listbox w-a-a-y faster than it currently does. The
trade-off is you have to maintain the script. That can be done various
ways, such as a simple 'cron' job to pull the data and reconstruct the list
values by overwriting the script file every few minutes or hours. The plus
side of this is, the js script full of options will be cached on the client
side, so one hit will be slow, the future hits will be really fast.
--
DonB
http://www.gotodon.com/ccbth
|
|
|
|
peterr
Posts: 5971
|
| Posted: 12/04/2005, 1:01 PM |
|
Quote Paul,:What was throwing me a bit was the fact that the SQL Server was almost completely quiescent. The SQL server is not the bottleneck - it's the building of the dropdown lists by the ASP interpreter.
Actually there are 2 bottlenecks. One is the ASP interpretter processing speed and the second one is the Web page size that needs to be downloaded by the Web browser like Don pointed.
The ASP speed can be improved by setting listbox populating type to ccsJoins. Please see http://forums.codecharge.com/search.php?s_keyword=ccsJo...[]=21&s_period=
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
|
