Re: Fw: [SQL] and [index] mystery solved

This WebDNA talk-list message is from

2001


It keeps the original formatting.
numero = 38127
interpreted = N
texte = Thanks John!----- Original Message ----- From: John Peacock To: WebCatalog Talk Sent: Thursday, August 23, 2001 11:42 AM Subject: Re: Fw: [SQL] and [index] mystery solved > Bob McPeak wrote: > > > > For a variety of reasons (CORRUPTION!!) we're migrating many of our dbs to > > SQL. Our trusty sys admin, Nick, has figured out a way to do a summary > > report in the sql context founditems. > > > > Any general feedback on migrating to sql would be greatly appreciated. > > > > Get yourself a book on SQL (for whatever flavor you are using). It is > trivial to do summaries in SQL: > > select min(field) as min_field, other_field > FROM table > WHERE criteria > GROUP BY other_field > > etc. It is also bad form to use select * ever, since that requires > the database to do an additional lookup to the schema tables to figure > out what fields are contained in that table. Always request only the > fields you are going to immediately use, to limit the amount of network > traffic dedicated to wasted data. > > When you move to a pure relational database, make sure that you > do all of your joins in SQL, rather than building them up in WebCat. > You can even create VIEW's in the backend database that encapsulate > your most common joins. Depending on the server, a VIEW will be > somewhat or a lot faster (since the query is already compiled and > optimized). > > Learn how to use indexes effiently for your database. Oracle, for > example, will not use an index unless you have all of the index > fields in the WHERE clause (you can also use query hints). There > are a lot of things that WebCatalog does for you that you will need > to do yourself with you use an SQL database backend. > > HTH > > John > -- > John Peacock > Director of Information Research and Technology > Rowman & Littlefield Publishing Group > 4720 Boston Way > Lanham, MD 20706 > 301-459-3366 x.5010 > fax 301-429-5747 > > ------------------------------------------------------------- > This message is sent to you because you are subscribed to > the mailing list . > To unsubscribe, E-mail to: > To switch to the DIGEST mode, E-mail to > Web Archive of this list is at: http://search.smithmicro.com/ ------------------------------------------------------------- This message is sent to you because you are subscribed to the mailing list . To unsubscribe, E-mail to: To switch to the DIGEST mode, E-mail to Web Archive of this list is at: http://search.smithmicro.com/ Associated Messages, from the most recent to the oldest:

    
  1. Re: Fw: [SQL] and [index] mystery solved (Bob McPeak 2001)
  2. Re: Fw: [SQL] and [index] mystery solved (John Peacock 2001)
  3. Fw: [SQL] and [index] mystery solved (Bob McPeak 2001)
Thanks John!----- Original Message ----- From: John Peacock To: WebCatalog Talk Sent: Thursday, August 23, 2001 11:42 AM Subject: Re: Fw: [SQL] and [index] mystery solved > Bob McPeak wrote: > > > > For a variety of reasons (CORRUPTION!!) we're migrating many of our dbs to > > SQL. Our trusty sys admin, Nick, has figured out a way to do a summary > > report in the sql context founditems. > > > > Any general feedback on migrating to sql would be greatly appreciated. > > > > Get yourself a book on SQL (for whatever flavor you are using). It is > trivial to do summaries in SQL: > > select min(field) as min_field, other_field > FROM table > WHERE criteria > GROUP BY other_field > > etc. It is also bad form to use select * ever, since that requires > the database to do an additional lookup to the schema tables to figure > out what fields are contained in that table. Always request only the > fields you are going to immediately use, to limit the amount of network > traffic dedicated to wasted data. > > When you move to a pure relational database, make sure that you > do all of your joins in SQL, rather than building them up in WebCat. > You can even create VIEW's in the backend database that encapsulate > your most common joins. Depending on the server, a VIEW will be > somewhat or a lot faster (since the query is already compiled and > optimized). > > Learn how to use indexes effiently for your database. Oracle, for > example, will not use an index unless you have all of the index > fields in the WHERE clause (you can also use query hints). There > are a lot of things that WebCatalog does for you that you will need > to do yourself with you use an SQL database backend. > > HTH > > John > -- > John Peacock > Director of Information Research and Technology > Rowman & Littlefield Publishing Group > 4720 Boston Way > Lanham, MD 20706 > 301-459-3366 x.5010 > fax 301-429-5747 > > ------------------------------------------------------------- > This message is sent to you because you are subscribed to > the mailing list . > To unsubscribe, E-mail to: > To switch to the DIGEST mode, E-mail to > Web Archive of this list is at: http://search.smithmicro.com/ ------------------------------------------------------------- This message is sent to you because you are subscribed to the mailing list . To unsubscribe, E-mail to: To switch to the DIGEST mode, E-mail to Web Archive of this list is at: http://search.smithmicro.com/ Bob McPeak

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:

Wanted: More Math Functions (or, Can You Solve This?) (1997) hidden databases (2000) processing .html pages (2001) linetiems in thankyou.tpl (1997) Dismissing dialogs (was 2.1b3 --> way slow) (1997) [subTotal] pricefield in another database It#180#s not working!? (1998) Re:Emailer problem....still (1997) OT - Dig Sigs (2004) creating writefile data from a nested search (1997) item sorting (1997) Webten + Webcat running smooth (1998) ANother SHOWIF problem (1997) Mail Service Dead (1998) [AppendFile] problem (WebCat2b13 Mac .acgi) (1997) Permissions with 3.06 Linux (2000) [table] and simultaneous users (2008) [WebDNA] SweepCartFrequency (2010) GrandTotal not right (1997) Search technique question (1998) grep matching (2003)