defiantclass1
Posts: 24
|
| Posted: 03/10/2006, 5:06 AM |
|
Hello,
In CCS 2.3.2.28, is it possible that I could have too many lookup fields for the application to function properly? In my form in question, I currently have 5 lookup fields that are working great. I have 3 more to add. When I attempt to add any or all of the remaining 3 fields, I can not view the page in Live mode. The page just sits at a white screen and eventually times out.
Each time I check the running process in Task Manager and DLLHOST.EXE is running at very high percentages and the CPU is maxed out at 100%.
I have to forcibly close CCS.
Thanks
|
 |
 |
defiantclass1
Posts: 24
|
| Posted: 03/10/2006, 5:47 AM |
|
I just answered my own question....well partly. The problem appears to be that there too many records in the table that my form is looking up to. I deleted all but 30 records (this is a test enviornment) and the form works fine now.
So, the question becomes, is there another way for me to accomplish this. The records I'm looking up are insurance companies. We generate hunderds of new records every day and each and every one needs an insurance company from this list, so I do infact need to reference them all, in some way or another. Perhaps another type of lookup???
Thank you
|
|
|
|
Christian
|
| Posted: 03/10/2006, 11:19 PM |
|
Hi,
are you really sure that you need these lookups??? They are normally quite slow. If you have 1000 records in your database and you are doing 5 lookups on each records, your database has to handle 5000 requests. Most lookups can be replaced by optimizing the original database query with table joins or sub selects. Try thinking in this way. It is possible that you can reduce your database workload to 1 single database request. Even if this one is more complicated, it has a much better performance. which type of databse are you using. if you are running sql server try the 'profiler' in which you can see you what your database is doing.
greetings from germany - Christian.
|
|
|
|
defiantclass1
Posts: 24
|
| Posted: 03/11/2006, 6:32 AM |
|
Thanks for the response bud.
However, I am afraid that this is the way it has to be. I am mimicking another program. We have an old Btrieve medical billing program which need to be replaced badly for many reasons. However, my boss is terrified of making the switch to another program. So I decided to mimick the old program exactly to make him more comfortable with it, and so there was no user training involved. It will be so similar everyone should be able to just jump right in and start working.
|
|
|
|
wkempees
|
| Posted: 03/11/2006, 6:52 AM |
|
As the solution to your problems might be limiting the number of rows
retreived each refresh of the page:
Q1, Is there any relationship between the five listboxes that might be used
to limit the number of records to be retreived in any of the others?
Q2, try and edit the Datasource for the Listbox, to use Limit/Top?
Q3,Is database running on same machine that is also hosting your
application, or are you using a DB server?
Search the forum for "type ahead listbox", with some tinckering you might be
able to "select"smaller parts of data at a time.
Interesting problem though.
Walter (down with the flue)
|
|
|
|
wkempees
|
| Posted: 03/11/2006, 6:56 AM |
|
Primary Question, rereading the post
What do you actualy mean by lookupfields, I interpreted Listbox, but if you
mean data displayed pulled from another table triggered by data already in
your record, than JOIN would be the way to go and bring, when written well,
the number of rows back to 1 for the main and 5 for the lookups.
Walter
|
|
|
|
defiantclass1
Posts: 24
|
| Posted: 03/13/2006, 10:30 AM |
|
Walter,
Thanks!
Q1 - There is no relationship between the five
The deal is, in a new page, a record is created containing mostly info about the new patient, i.e. Name, Address, etc. There are also a number of listboxes where you select an insurance company, a clinic, a sales rep, an account rep, an account type, a company name. There are 3 fields for insurance as some people have a primary, secondary, and tertiary insurance company. Each of these fields reference another table in the database to get the values. All 3 Insurance fields reference the same table for their values.
Q2- If I did a Top "20" (for example), and my insurance company was Bluecross, which is the 50th record in the "Insurance" table, I would not get Bluecross in my list, right? Only the Top 20???? Am I understanding the process correctly??
Q3- My database is SQL Server 2000 and it is not on the same machine I am creating the application in. I do not intend on publishing the final live pages to that machine either. Although the SQL database is on a server machine.
And you are correct, I meant "listbox" not "lookup".
Thanks for all your thoughts and comments. I was wondering if a "Smart Lookup / Pop-up List" would be a better approach?
Dave
|
|
|
|
wkempees
|
| Posted: 03/13/2006, 4:05 PM |
|
Dave,
Ok that clearifies a lot, and will probably help you to define or find a
solution.
Typeahaed listboxes would be a big improvement, although with a twist.
If you search the forum for type ahead listboxes you'll find a fine set of
solutions, even googling will give you the JS needed.
Type ahead works in JS on an already filled listbox, this you don't want
(30K records)!
What you might need is a sort of priming routine, catching the users input
and then do SELECT from WHERE like "%value%", to restrict the size of the
generated dataset.
The user types (f.i.) "Mutu" and then do a SELECT containing "Mutu" to get
to smaller datasets. (Mutual and Benefit, as an example).
Using Ajax-techniques or even the Examples way of doing background lookups
would maybe improve performance and user experience.
Catch the entered userdata and (re)select the dataset limiting by doing the
WHERE containing construction.
Let us all know what you decided and how it resolved, or if any further
assistance is needed.
Walter
|
|
|
|
wkempees
|
| Posted: 03/13/2006, 4:14 PM |
|
Quote :
> Q2- If I did a Top "20" (for example), and my insurance company was
> Bluecross,
> which is the 50th record in the "Insurance" table, I would not get
> Bluecross in
> my list, right? Only the Top 20???? Am I understanding the process
> correctly??
Nope.
Limit/Top or Limit is merely an instruction for the database server, how
many rows to GET at a time.
your table of 30k records could be handled like this:
Suppose a listbox that shows 20 records at a time, and your user using the
downarrow to scroll through it.
If " SELECT * from table limit 100 " is used the listbox would have to fire
another SELECT after 5 pages of info.
It would in fact get smaller datasets but do more selects, if the table was
completely queried for the 30K records.
slower but many times faster than what your are attempting now.
I would have to experiment to see if this (for a Grid normal behaviour) is
applicable to Listboxes, but I can't see why not.
|
|
|
|
wkempees
|
| Posted: 03/13/2006, 4:20 PM |
|
Quote :
Q3- My database is SQL Server 2000 and it is not on the same machine I am
creating the application in. I do not intend on publishing the final live
pages
to that machine either. Although the SQL database is on a server machine.
And you are correct, I meant "listbox" not "lookup".
Thanks for all your thoughts and comments. I was wondering if a "Smart
Lookup /
Pop-up List" would be a better approach?
Q3 is a goood answer, keep it that way, and profile the queries as Christian
suggested, add as many indexes as needed.
You need all the performance you can get, and take it from me it's there for
the taking.
"Smart Lookup / Pop-up List" is a good approach, still taking at heart above
suggestions (Q2 and Q3).
Pro: user types, rows are selected (smaller sets) and presented.
Con: you will probably loose the look and feel you were after in the first
place, creating the situ at hand.
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 03/13/2006, 4:42 PM |
|
PS 10 percent downbubble all ahead full, down scope.
I was wandering about your tagname, then it got to me, ours would be dolphin.
Forget the LIMIT/TOP suggestion, won't work.
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
|
