Re: local temp tables

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Table Expressions have no statistics they are simply inline macros. <<

Unh? The base tables from which they are built do have indexes,
statistics, constraints, etc. to pass to the optimizer when the inline
macro is expanded. Either you contradict yourself or you think that
there optimizer has a mechanism to ignore the base tables in a a table
expression.

But if I materialize a temp table in the non-standard T-SQL model in a
special temp_db, does it get indexes, statistics, constraints, etc. or
is it pretty much a sequential file?

Have you ever self joined the CTE a number of times? <<

Yes. Often. That is why I used a CTE. Yes, I know that you are
trying to say that MS SQL Server stinks and cannot handle CTE choices
(materialize vs inline macro). This is one reason why we SQL people
call it a "lesser SQL" among ourselves. It will get to the point of
D2, Oracle, etc, in a few years. But it is not that bad right now.

Richard Romley's Sudoku query used a 81-way self-join and flies like a
bat out of hell. He uses PRINT to actually display each valid
grid. That eats more CPUs than the query.

When the table can fit into main storage, SQL Server can work with
it. His query is nothing but a straight translation of the rules into
a very long WHERE clause. We played with trying to use bit masking,
and other low-level crap and it failed. The SQL Server optimizer was
quite good and did its job.

His procedure prints out ALL the valid girds – yes, published puzzles
have multiple solutions. This is the blessing/curse of a good code in
a set-oriented language. You get the ENTIRE set of vaid answers.
Richard found one published grid with 250+ valid answers!

Oh, speaking of that particular problem, all those many months ago,
you said that you could write a better Sudoku query in five minutes
but apparently forgot to post anywhere I can find. Did I miss it?

You sent Richard a grid with 0 thru 9 across the top row; he pointed
out that this would lead to so many grids that no computer on Earth
could store them. The number of possible Sudoku is
6,670,903,752,021,072,936,960 grids, so your request would have
dropped that by (10!) = 3,628,800.

Even without doing the math, I think is more than BIGINT.

I am sure that you did not mean to sound like a troll and had
something else in mind, so what was your point?

.

Relevant Pages

  • Re: NOT IN vs LEFT JOIN
    ... I don't expect performance difference, as the optimizer is smart ... implemented in SQL Server, ... My preference is therefor to use a subquery with NOT EXISTS: ...
    (microsoft.public.sqlserver.programming)
  • Re: more than 8192 characters in text file output
    ... Thanks Erland. ... results that are "export as grid" you can't choose the delimiter. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.programming)
  • Re: Execution plan
    ... optimizer don't use this index? ... What happens if you drop the index on TransportStatus? ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.programming)
  • Re: Query Plan Question
    ... If you use a variable in a WHERE clause, then the optimizer doesn't know ... this means that SQL Server will read 1 million rows. ... > And AddedOn>= '2004-03-10' ... > The first query which uses @FromDate does a table scan. ...
    (microsoft.public.sqlserver.server)
  • Re: Setup index
    ... nonclustered index to retrieve data is often the best answer when there is a ... What you are seeing is the normally good behaviour of the optimizer. ... Wayne Snyder, MCDBA, SQL Server MVP ... if I change the query to: ...
    (microsoft.public.sqlserver.server)