Re: [WebDNA] Acceleratin Search / Index Performance in a Formum Service
This WebDNA talk-list message is from 2013
It keeps the original formatting.
numero = 110761
interpreted = N
texte = Palle,I ran into a similar problem with a product database during the last=20year. My server already had a quad processor with 16GB of RAM.My biggest performance issues happen when I'm doing an import.My product database was already split into 3 tables. My solution was to=20reconstruct the fields in those 3 tables into 5 tables. I carefully=20looked at all the information in the tables and moved all of the least=20used fields to the extra tables. Then I reprogrammed all the searches=20so the extra 2 tables are rarely opened.I then created another table with the most important few searchable=20fields. This acts as my index. I rebuild it nightly. Users hitting the=20site are always hitting this main index when doing broad product=20searches. I'm only using a single index at the moment, but I know the=20next step is to have a different index for different product categories.Even though it took a while to accomplish this the results were very=20rewarding.-MattOn 10/2/2013 3:41 AM, Palle Bo Nielsen wrote:> Hi all,>> Looking for advise to optimise a webpage using WebDNA and large DB's.>> I have built a forum entirely in WebDNA and the DB's are getting big. T=he main DB has the following characteristics=85>> - Columns: 11> - Rows: 847.121> - Size: 264 MB>> If I display the different generic topics within the Forum and the most= recent Post within each category etc. then the code is executing quite f=ast (acceptable performance).>> But if I want to add the value of posts within each category or within =each thread then the performance is slowing the too much.>> I have a category DB which eventually show 25 topics on the main page o=f the forum, then I do a search into the main DB and search for all posts= with this categories ID and show the numFound. This is repeated for ever=y unique Topic.>>>> << Searching for the Topics >>> [search db=3D/forum/db/db1.db&eqdb1_publishdata=3D1&asdb1_prioritysort=3D=1&db1_prioritytype=3Dnum&AllReqd=3DT][founditems]>> << Searching for the numFound Value - This One is Pulling a Lot of Perf=ormance even though it's simple, but there is a lot of data to search thr=ough >>> [search db=3D/forum/db/db4.db&eqdb4_publishdata=3D1&eqdb4_db1data=3D[db=1_sku]&AllReqd=3DT][numFound][founditems][/founditems][/search]>>>> I would appreciate any good advise or ideas on how to accelerate the pe=rformance. Some kind of indexing to avoid live searching would be great, =but how?>> /Palle>> ---------------------------------------------------------> This message is sent to you because you are subscribed to> the mailing list
.> To unsubscribe, E-mail to: > archives: http://mail.webdna.us/list/talk@webdna.us> Bug Reporting: support@webdna.us>>--=20Matthew A PerosiCorporate ConsultantMobile Marketing ExpertSenior Web DeveloperSEO Analyst & Educatormatt@psiprime.comPsi Prime, Inc.323 Union Blvd.Totowa, NJ 07512Direct: 888.872.0274Fax: 888.488.5924http://www.perosi.com
Associated Messages, from the most recent to the oldest:
Palle,I ran into a similar problem with a product database during the last=20year. My server already had a quad processor with 16GB of RAM.My biggest performance issues happen when I'm doing an import.My product database was already split into 3 tables. My solution was to=20reconstruct the fields in those 3 tables into 5 tables. I carefully=20looked at all the information in the tables and moved all of the least=20used fields to the extra tables. Then I reprogrammed all the searches=20so the extra 2 tables are rarely opened.I then created another table with the most important few searchable=20fields. This acts as my index. I rebuild it nightly. Users hitting the=20site are always hitting this main index when doing broad product=20searches. I'm only using a single index at the moment, but I know the=20next step is to have a different index for different product categories.Even though it took a while to accomplish this the results were very=20rewarding.-MattOn 10/2/2013 3:41 AM, Palle Bo Nielsen wrote:> Hi all,>> Looking for advise to optimise a webpage using WebDNA and large DB's.>> I have built a forum entirely in WebDNA and the DB's are getting big. T=he main DB has the following characteristics=85>> - Columns: 11> - Rows: 847.121> - Size: 264 MB>> If I display the different generic topics within the Forum and the most= recent Post within each category etc. then the code is executing quite f=ast (acceptable performance).>> But if I want to add the value of posts within each category or within =each thread then the performance is slowing the too much.>> I have a category DB which eventually show 25 topics on the main page o=f the forum, then I do a search into the main DB and search for all posts= with this categories ID and show the numFound. This is repeated for ever=y unique Topic.>>>> << Searching for the Topics >>> [search db=3D/forum/db/db1.db&eqdb1_publishdata=3D1&asdb1_prioritysort=3D=1&db1_prioritytype=3Dnum&AllReqd=3DT][founditems]>> << Searching for the numFound Value - This One is Pulling a Lot of Perf=ormance even though it's simple, but there is a lot of data to search thr=ough >>> [search db=3D/forum/db/db4.db&eqdb4_publishdata=3D1&eqdb4_db1data=3D[db=1_sku]&AllReqd=3DT][numFound][founditems][/founditems][/search]>>>> I would appreciate any good advise or ideas on how to accelerate the pe=rformance. Some kind of indexing to avoid live searching would be great, =but how?>> /Palle>> ---------------------------------------------------------> This message is sent to you because you are subscribed to> the mailing list .> To unsubscribe, E-mail to: > archives: http://mail.webdna.us/list/talk@webdna.us> Bug Reporting: support@webdna.us>>--=20Matthew A PerosiCorporate ConsultantMobile Marketing ExpertSenior Web DeveloperSEO Analyst & Educatormatt@psiprime.comPsi Prime, Inc.323 Union Blvd.Totowa, NJ 07512Direct: 888.872.0274Fax: 888.488.5924http://www.perosi.com
matt@psiprime.com
DOWNLOAD WEBDNA NOW!
Top Articles:
Talk List
The WebDNA community talk-list is the best place to get some help: several hundred extremely proficient programmers with an excellent knowledge of WebDNA and an excellent spirit will deliver all the tips and tricks you can imagine...
Related Readings:
[WebDNA] Hosting Solution (2013)
OT: Variables in printed URL (2007)
2.1b3 --> way slow (1997)
[WebDNA] grep question (2009)
Queertrons? (1997)
The IBC root beer has arrived! (1997)
Click-through/Referral tracking solution needed (2000)
RE: [WebDNA] macOS 10.13.3 and WebDNA Mac Server 8.5.1 - hick-up (2019)
Some Questions (1997)
a new bug? (1999)
graphing (2004)
suffix mapping for NT? (1997)
Re:[off] Promotions Co? (1997)
&FieldNametype=time unnecessary? (2000)
Username for Admin Group (1997)
Order file numbers different than the cart number. why? (2000)
Templates on Unix & CGI on Mac? (1997)
[OT] HTML, HTML docs (2001)
Emailer Set Up (1997)
[WebDNA] Strange MATH result - BUG (2010)