Hello,

I'm a bit baffled that nobody noticed all these days since Jair Pedro
posted that the SQL code in question is itself flawed. I myself am
not surprised that such code might execute with acceptable performance
through one engine and take (e.g.) weeks through another.

From Access, this SQL goes directly to the Jet engine, while from the
posted ASP it also passes through ADO, ADO's provider for ODBC and
ODBC itself. Changing the code to have ADO utilize its OLE DB Jet
Provider might already "resolve" the issue.

As to why the SQL is BS:
SELECT * FROM Tabela2
WHERE unidade In
 ( SELECT unidade FROM Tabela2 As Tmp GROUP BY unidade,endereco HAVING
Count(*)>1 )
seems OK. This would generate a set of [unidade] values for which at least
two distinct
values of [endereco] exist, then select all rows of the original table where
[unidade] has
one of those values. Lacking indices, this would mean at most 2 sequential
passes over
the data.
Adding AND endereco = Tabela2.endereco to *the HAVING clause of the nested
SELECT*
is meaningless, and if the SQL optimizer does not recognize this, it forces
the engine to
reevaluate the nested SELECT once for each row of [Tabela2]. Am I right?

Have fun,
        Robert Heinig

P.S.: Shouldn't any important web server run on multi-CPU machines?
Very useful when you work with single-threaded, unstable software. In this
case, the flawed code would bind 100% of *one* CPU to the Jet engine,
leaving the other CPU's free to continue serving - unless ASP is able to
employ several Jet threads for distinct client requests.

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]