Re: ODBC Performance?
This WebDNA talk-list message is from 2001
It keeps the original formatting.
numero = 37356
interpreted = N
texte = Rob Blair wrote: > > Hey, > > I am having a serious performance problem with WebCatalog 4 connecting to an > Oracle 8 database through the Windows ODBC control panel. > Not seeing your SQL query, I can only wave my arms and give very vague recommendations.- If I read your e-mail correctly, you are performing a WebCat search and using the results to search into Oracle. If this is the case, you must have an index on the joining column in Oracle, or you will be doing a full table search for each record. You can use the GUI tools to add an index. If it is a multi-column index, you should be sure to use the 'ORDER BY' clause, even if you expect the result to be a single row. This will clue Oracle into using the proper index (you also use Oracle's query hints to do this).- If you are using Oracle in that fashion, you should reconsider your schema design. Oracle's ODBC driver is very good about prefetching rows and returning them from the cache (I cannot speak for M$oft's, since I always use Oracle's). The major time for running a simple remote query is setting up the communication channel. If you are asking Oracle lots of small queries, you are wasting all of that setup time on each query. You should try to always ask 1 query that returns lots of records, rather than lots of queries that return 1 record.- If you are performing anything more complicated in Oracle (like a left outer join of three tables), think about creating a view in Oracle. This allow you to tune the join (creating indexes, etc) and then refer to the whole unit as a virtual table from WebCat. The key is to get it working well/fast in Oracle natively, then call it from some other program.HTHJohn-- 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:
Rob Blair wrote: > > Hey, > > I am having a serious performance problem with WebCatalog 4 connecting to an > Oracle 8 database through the Windows ODBC control panel. > Not seeing your SQL query, I can only wave my arms and give very vague recommendations.- If I read your e-mail correctly, you are performing a WebCat search and using the results to search into Oracle. If this is the case, you must have an index on the joining column in Oracle, or you will be doing a full table search for each record. You can use the GUI tools to add an index. If it is a multi-column index, you should be sure to use the 'ORDER BY' clause, even if you expect the result to be a single row. This will clue Oracle into using the proper index (you also use Oracle's query hints to do this).- If you are using Oracle in that fashion, you should reconsider your schema design. Oracle's ODBC driver is very good about prefetching rows and returning them from the cache (I cannot speak for M$oft's, since I always use Oracle's). The major time for running a simple remote query is setting up the communication channel. If you are asking Oracle lots of small queries, you are wasting all of that setup time on each query. You should try to always ask 1 query that returns lots of records, rather than lots of queries that return 1 record.- If you are performing anything more complicated in Oracle (like a left outer join of three tables), think about creating a view in Oracle. This allow you to tune the join (creating indexes, etc) and then refer to the whole unit as a virtual table from WebCat. The key is to get it working well/fast in Oracle natively, then call it from some other program.HTHJohn-- 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:
Multiple Merchant Accounts? (1997)
Not reading code (1997)
WebCat2final1 crashes (1997)
WC on Mac OS X or Mac OS X Server 2.x (2001)
Is [thisurl] http or https? (1998)
OT: auto restart MySQL - System Preferences (2006)
New Shipping Options? (2001)
Enhancement Request for WebCatalog-NT (1996)
Emailer Question (1998)
[WebDNA] Does not understand 'search' command? (2014)
exclamation mark... (1999)
WebCat2b13MacPlugIn - [include] doesn't allow creator (1997)
Displaying backgrounds (1999)
SSL (1998)
[LOOKUP] (1997)
WebCat2: multiple currency support (1997)
Virtual hosting and webcatNT (1997)
Cancel Subscription (1996)
Keep away (1997)
Friday night shopping site joke (1997)