Re: SQL speed issues

This WebDNA talk-list message is from

2001


It keeps the original formatting.
numero = 38773
interpreted = N
texte = Pedro Rivera wrote: > > Hi, we're having some speed issues when using [SQL...][/SQL] tags. For some > reason the query takes too long to return the results, even when there is > only one record on the database!!! > This is my query: > > [SQL dsn=sql7&username=theuser&password=thepassword&statement=SELECT * FROM > vw_ItemFamily WHERE str_ItemNumber LIKE '%[keyword]%' OR str_Title LIKE > '%[keyword]%' ORDER BY int_Order] > > If I run the query with ASP it runs super fast, but when I use [SQL] tag it > takes over 10 seconds.When run as ASP, are you creating the ADO object in the globals.asa? The reason I ask is that every instance of [SQL] must allocate and set up the database connection. This will inevitably be slower than ASP with a preallocated ADO object.That said, here are a few suggestions to speed up SQL queries:1) Never use SELECT * - always specify the fields you are planning to display. If you use SELECT *, the database engine must scan the master table definition to determine which fields are present prior to executing the query. In addition, if you use only a few fields in a given query, and the database contains lots of fields (especially large MEMO fields), the network traffic will be vastly increased.2) LIKE queries are always slow, especially on large text fields. If your fields are small, this is acceptable. Otherwise, consider creating a KEYWORDS field and do all of your searching on that field instead. Imagine if the user typed the single character 'e' into the [keyword] field. If your database is in English, that may match 90% of your titles. Imagine if the user type a space character!3) Create appropriate indexes - in your case, at the very least, you need an index on int_order. However, you should profile your query to make sure that the index is being used. Often, if a field is indexed, but not referred to in the WHERE clause, the index will not be used. -- 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/ Associated Messages, from the most recent to the oldest:

    
  1. Re: SQL speed issues (John Peacock 2001)
  2. SQL speed issues (Pedro Rivera 2001)
Pedro Rivera wrote: > > Hi, we're having some speed issues when using [SQL...][/SQL] tags. For some > reason the query takes too long to return the results, even when there is > only one record on the database!!! > This is my query: > > [SQL dsn=sql7&username=theuser&password=thepassword&statement=SELECT * FROM > vw_ItemFamily WHERE str_ItemNumber LIKE '%[keyword]%' OR str_Title LIKE > '%[keyword]%' ORDER BY int_Order] > > If I run the query with ASP it runs super fast, but when I use [SQL] tag it > takes over 10 seconds.When run as ASP, are you creating the ADO object in the globals.asa? The reason I ask is that every instance of [SQL] must allocate and set up the database connection. This will inevitably be slower than ASP with a preallocated ADO object.That said, here are a few suggestions to speed up SQL queries:1) Never use SELECT * - always specify the fields you are planning to display. If you use SELECT *, the database engine must scan the master table definition to determine which fields are present prior to executing the query. In addition, if you use only a few fields in a given query, and the database contains lots of fields (especially large MEMO fields), the network traffic will be vastly increased.2) LIKE queries are always slow, especially on large text fields. If your fields are small, this is acceptable. Otherwise, consider creating a KEYWORDS field and do all of your searching on that field instead. Imagine if the user typed the single character 'e' into the [keyword] field. If your database is in English, that may match 90% of your titles. Imagine if the user type a space character!3) Create appropriate indexes - in your case, at the very least, you need an index on int_order. However, you should profile your query to make sure that the index is being used. Often, if a field is indexed, but not referred to in the WHERE clause, the index will not be used. -- 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/ John Peacock

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:

Moment of Thanks (1997) Grant, please help me ... (1997) TRAINING videos - Prove IT. (1998) Paths, relative paths, webstar server setup and security (1997) Database Security (1998) Navigator 4.01 (1997) Are they really global tags? (2002) Emailer problems. (1998) math on date? (1997) Showing unopened cart (1997) Am I going senile? (Price recalc based on quantity) (1997) Problems with upload / WebCat (1998) WebCat2b12 CGI Mac - [shownext] problem (1997) Tab Charactor (1997) How to Display text in empty fields (1997) WebCat2 Append problem (B14Macacgi) (1997) PDF Writefile problem (2005) Triggers (1999) [TEXT SECURE=T] (2000) Help! WebCat2 bug (1997)