Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email email@example.com
From: Robert Heinig (rheinig-beikukGMX.NET)
Date: Wed Jan 03 2001 - 03:00:53 CST
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
seems OK. This would generate a set of [unidade] values for which at least
values of [endereco] exist, then select all rows of the original table where
one of those values. Lacking indices, this would mean at most 2 sequential
Adding AND endereco = Tabela2.endereco to *the HAVING clause of the nested
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?
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.