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

From: <akind_at_yahoo.com>
Date: Fri, 17 Apr 1998 13:35:49 -0600
Message-ID: <6h87e5$k95$1@nnrp1.dejanews.com>

I'm running into a strange problem that has me stumped. A query that used to work has mysteriously stopped working. Here's the basic setup of the application:

Microsoft IIS 3.0 running Active Server Pages (ASP) accessing Oracle 7.3 databases
using Microsoft ODBC Driver for Oracle version 2.00.006325 (the one included with MS Interdev version 1.0)
and ADO

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) The first symptom of trouble was when the page timed out before the query could finish. However, I've tried increasing the time allowed for a page and also increased the CommandTimeout parameter of the connection object, without success.

Now here's the strangest part. When I execute this query from within Visual Interdev 1.0 data view it also fails, after about a minute, with the message "ODBC Error: Execution Cancelled". (What an informative message.) However, the exact same query succeeds if I use MS Query with the same ODBC driver.

This would seem to indicate that the problem lies in how Interdev and ASP are accessing ODBC. Perhaps in ADO?

By the way, this query also succeeds if I run it directly against Oracle using SQL Plus 3.3.

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

Here are some of the other things I've tried:

• Used the new Microsoft ODBC for Oracle version 2.73.728303, which accompanies MDAC 1.5. I didn't install this on the server, because I doubt it's real compatible with IIS 3.0, but I tried it on my development machine with Interdev 1.0. DevStudio hangs when I use this driver to run any query, i.e., when I double-click on a table name in Data View. (We're talking about upgrading our server to IIS 4.0, in which case we will use MDAC 1.5 with this new driver. Gee, why does this make me nervous?)
• Used the Oracle73 driver from Oracle, version 2.00.0301. The query worked once when I ran it from Interdev, but when I try to use it from IIS, it just hangs the entire website. Actually it worked the first time I tried it, but after that it just hung. This is probably due to Oracle driver being unable to handle multiple users.

*Simplified the query to use MINUS instead of NOT IN. This actually works, which is kind of interesting. Though the results of these two queries are exactly the same, they clearly are processed quite differently, because the MINUS query takes a fraction of the time to execute. Unfortunately, this doesn't solve my problem because the query I need for production requires a join on a bunch of other tables to get other information and to sort the results. I've worked around the problem by creating a page that runs the simpler MINUS query and then runs a second query on each EMPLID to get additional details, but this is cumbersome, and the results are not sorted. Also, it takes so long that the ASP times out before it's done.

Does anyone have any ideas on what else I might try?

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Fri Apr 17 1998 - 14:35:49 CDT