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=20 year. 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=20 reconstruct the fields in those 3 tables into 5 tables. I carefully=20 looked at all the information in the tables and moved all of the least=20 used fields to the extra tables. Then I reprogrammed all the searches=20 so the extra 2 tables are rarely opened. I then created another table with the most important few searchable=20 fields. This acts as my index. I rebuild it nightly. Users hitting the=20 site are always hitting this main index when doing broad product=20 searches. I'm only using a single index at the moment, but I know the=20 next step is to have a different index for different product categories. Even though it took a while to accomplish this the results were very=20 rewarding. -Matt On 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 > > --=20 Matthew A Perosi Corporate Consultant Mobile Marketing Expert Senior Web Developer SEO Analyst & Educator matt@psiprime.com Psi Prime, Inc. 323 Union Blvd. Totowa, NJ 07512 Direct: 888.872.0274 Fax: 888.488.5924 http://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=20 year. 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=20 reconstruct the fields in those 3 tables into 5 tables. I carefully=20 looked at all the information in the tables and moved all of the least=20 used fields to the extra tables. Then I reprogrammed all the searches=20 so the extra 2 tables are rarely opened. I then created another table with the most important few searchable=20 fields. This acts as my index. I rebuild it nightly. Users hitting the=20 site are always hitting this main index when doing broad product=20 searches. I'm only using a single index at the moment, but I know the=20 next step is to have a different index for different product categories. Even though it took a while to accomplish this the results were very=20 rewarding. -Matt On 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 > > --=20 Matthew A Perosi Corporate Consultant Mobile Marketing Expert Senior Web Developer SEO Analyst & Educator matt@psiprime.com Psi Prime, Inc. 323 Union Blvd. Totowa, NJ 07512 Direct: 888.872.0274 Fax: 888.488.5924 http://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:
WebCat2 - Getting to the browser's username/password data (1997)
what characters are replaced for tab and CR? (1998)
Printing a final order (1997)
Where is f2? (1997)
Max Record length restated as maybe bug (1997)
Mac System Experience (1998)
View Source from cache (1997)
sorting month by month (2004)
Displaying Location (1997)
[LISTFILES] (1998)
PCS Frames (1997)
Extended [ConvertChars] (1997)
absolute paths for databases? (1997)
WebCat2 several catalogs? (1997)
[index] (1997)
Generating Report Totals (1997)
So Simple (2005)
[WriteFile] problems (1997)
Quit revisited (1997)
Nesting format tags (1997)