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

>
> 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. Received on Sat Apr 18 1998 - 20:36:25 CDT