Complex Query Fails w/ ASP->ADO->ODBC->Oracle

Complex Query Fails w/ ASP->ADO->ODBC->Oracle

Post by José Lima Suáre » Mon, 20 Apr 1998 04:00:00



Try outer-joining the query:

SELECT EMPLID
FROM     (SELECT EMPLID EMPL
                  FROM     EFFORT
                  WHERE  MONTH = 2 AND YEAR = 1998), EMPDETAIL
WHERE  EMPLID = EMPL (+) AND EMPL IS NULL

Avoid the use of DISTINCT

 
 
 

Complex Query Fails w/ ASP->ADO->ODBC->Oracle

Post by Iosif Tanasesc » Mon, 20 Apr 1998 04:00:00


Quote:

> This is a web-based intranet application that employees use to enter the
> amount of time they spend on projects. It's been up and running for a
couple
> of months now without any problems. Until a week ago.

> The problem is that one page has a query with a subquery that takes a
long
> time to execute, namely:

> SELECT      EMPDETAIL.EMPLID
> FROM          EMPDETAIL
> WHERE       EMPDETAIL.EMPLID NOT IN
>  (SELECT DISTINCT EFFORT.EMPLID
>  FROM   EFFORT
>  WHERE  EFFORT.MONTH = 2 AND  EFFORT.YEAR = 1998)

> I suspect that this problem has just now appeared because the table in
the
> subquery has grown rather large (over 8000 rows).

Yes, while the number of rows grow, the execution time will grow.
Always try to tune your SQL statement, based on the execution plan that
ORACLE choose for it.

In this case, you must know :
- what type of optimizer do you use in your database ?
-if you left CHOOSE, did you (at least) estimate the statistics on the
schema ?
- if so, have your tables the godd index defined ( in your case, empdetail
should have index on emplid, and effort on (month,year))
- and not in the last way, try to not use NOT IN ( which will involve an
full scan, even you have index) and distinct (which will do an sort unique)
The aproach with ..In (... MINUS..) is the best in your case.
Good luck !
I.K.

 
 
 

1. Applet =>Asp =>DB =>Asp =>Applet

Hi Friends.
This is the first time i use newsgroups.I have a problem!

I want some(a lot of) help from U reguarding source in how I
from a applet(j++), when pressing a btn("btnGet/btnSave") in the applet
I
want to "get/read/save"  "values/strings"  "to/from"
a "editbox/listbox" in the applet
"from/to" a DB in SQL7,0 "via/throw" a ASP-page.
Can somebody please write me an example how the applet source,
the asp source etc would be like to get a connection.

Thank U from a beginner //  Toni/Sweden

Sent via Deja.com http://www.deja.com/
Before you buy.

2. Please help me with this stored procedure. Thank you.

3. Delphi>>>>>>>

4. How do you put a list of values into a variable for a WHERE FIELD IN () statement

5. >>>SYBASE/UNIX ENIGNEERS WANTED>>>>Bay Area, CA.

6. @@Rowcount

7. ADO->MTS->ODBC->ORACLE

8. SQL Server 2000 Desktop Engine throttling

9. Check out the Web below for great Computer Prices >>>>>>>>>>>>

10. >>>>>>UNIX/SYBASE ENGINEERS WANTED>>>>>> Bay Area, CA

11. US-NY: ORACLE DBA >>>>>URGENT