Somebody once told me (or I read it, I'm not sure) that no matter how much
data is returned or how complex the query is, if the query is written
correctly, data should be displayed almost instantly.
Is this true?

I vote false.  The complexity or "correctness" of the query isn't the only
factor that affects speed.  You can write the most "correct" query in the
world, but if it's pulling 50,000,000 rows across a 56K connection, there's
not going to be much "instantly" to it.  Ditto if your tables aren't indexed
properly, server doesn't have enough memory, etc.  Writing good queries is
just one step toward optimizing data retrieval.

On Thu, 8 Jun 2006 13:47:02 -0700, VMI <V**@discussions.microsoft.com>
wrote:

No.

For example, write a complex query that returns an extremely large
result set.  Include an ORDER BY that can not be satisfied by walking
an index.  The first row can not be returned until the entire set has
been generated and sorted.

Likewise, getting back the first row quickly is not always a sign that
the query is efficient.  Generally that should be measured by
comparing the time until the last row is returned, not the first.  It
is quite possible to have an ORDER BY that can be satisfied by
following an index, where using the index is far slower than a table
scan with sort would be.  (An index might lead you back to the same
page dozens of times, where the scan deals with each page just once.)

Roy Harvey
Beacon Falls, CT

Whoever said that probably never had to write a complex query (i.e.
accessing a dozen or more tables) that accessed millions of rows of data.
It is possible to query millions of rows in multiple tables quickly, but
sometimes the logic is simply too complex.  Add CPU, memory, and disk
constraints into the mix and this becomes difficult or impossible in many
cases.  Logic involving complex combinations of AND/OR, and logic involving
trees, can make tuning particularly difficult.


Show quoteHide quote

Help! Users can't connect to database
Help with stored procedure using "IN"
Default constraints in SQL Server 2000
Bring back Query Analyser
SQL2005: "Registered Owner" and "Registered Organization" available?