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 andset up the database connection. This will inevitably be slower thanASP 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. Ifyour fields are small, this is acceptable. Otherwise, consider creating a KEYWORDS field and do all of your searching on that fieldinstead. 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, youneed an index on int_order. However, you should profile yourquery to make sure that the index is being used. Often, if a fieldis indexed, but not referred to in the WHERE clause, the index willnot be used. -- John PeacockDirector of Information Research and TechnologyRowman & Littlefield Publishing Group4720 Boston WayLanham, MD 20706301-459-3366 x.5010fax 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:
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 andset up the database connection. This will inevitably be slower thanASP 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. Ifyour fields are small, this is acceptable. Otherwise, consider creating a KEYWORDS field and do all of your searching on that fieldinstead. 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, youneed an index on int_order. However, you should profile yourquery to make sure that the index is being used. Often, if a fieldis indexed, but not referred to in the WHERE clause, the index willnot be used. -- John PeacockDirector of Information Research and TechnologyRowman & Littlefield Publishing Group4720 Boston WayLanham, MD 20706301-459-3366 x.5010fax 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:
all records returned. (1997)
no global [username] or [password] displayed ... (1997)
Browser Problem?!? POST forms and NN 4.0+ browsers (1997)
[searchString] (1997)
[WebDNA] [OT] Twitter/Facebook (was: talklist & twitter) (2009)
# fields limited? (1997)
I got caught! (2003)
rn doesn't work on date fields (1998)
WebCat cannot handle compatible search parameters? (1997)
vs (1997)
Summing fields in a search/founditems context?? (2000)
Question about [encrypt] (1998)
WebCat2b13MacPlugIn - syntax to convert date (1997)
Error Lob.db records error message not name (1997)
Unable to view next 101-200 (1997)
[isfile] ? (1997)
Banners and sort of random display (1997)
Updating Prices in DB (working) (1999)
Change Subtotal (2000)
Problem displaying search result (1997)