Table scan, Table scan, Table scan

Table scan, Table scan, Table scan

Post by Bj?rn Lind » Fri, 02 Feb 2001 17:16:09



Again I got a table scan. It was again between two primarykeys.
It's a insert with a select between 5 tables where the condition
is on the first table. The third table contains 10 000 000 records
and when the "query optimizer" thinks that a table scan is
faster than using the primary key the query takes 30 seconds
instead off 0.5.

I have sometimes used SET FORCEPLAN ON but it does not work
well when doing insert or update. The table that is used for
insert/update is taken as first table and that is not optimal
if the condition is on another table in the select statement.

Does anyone know if there is a way to give the complete execution
order for a query?

/Bj?rn Linde

 
 
 

Table scan, Table scan, Table scan

Post by Dejan Sark » Fri, 02 Feb 2001 19:18:17


Bj?rn,

check few things:
1. Do you have appropriate index on the large table?
2. Try to add a redundant condition for the huge table in the Where clause.
3. If the index used for filtering the large table is nonclustered, can you
change it to clustered?
4. Can you use optimizer hint for the large table?

At least some of these method should work.

HTH,
Dejan Sarka

 
 
 

Table scan, Table scan, Table scan

Post by Tibor Karasz » Fri, 02 Feb 2001 19:21:09


In addition to Dejan's post:

What datatype?

--
Tibor Karaszi, SQL Server MVP
FAQ from Neil at: http://www.sqlserverfaq.com
Please reply to the newsgroup only, not by email.


Again I got a table scan. It was again between two primarykeys.
It's a insert with a select between 5 tables where the condition
is on the first table. The third table contains 10 000 000 records
and when the "query optimizer" thinks that a table scan is
faster than using the primary key the query takes 30 seconds
instead off 0.5.

I have sometimes used SET FORCEPLAN ON but it does not work
well when doing insert or update. The table that is used for
insert/update is taken as first table and that is not optimal
if the condition is on another table in the select statement.

Does anyone know if there is a way to give the complete execution
order for a query?

/Bj?rn Linde

 
 
 

Table scan, Table scan, Table scan

Post by Bj?rn Lind » Fri, 02 Feb 2001 21:22:33


Thank you for the tips. This time most of the overhead by
using optimizer hints where i defined the primarykey for
the table that was scaned. I have used the primary and foreign
-key constraints on all tables. Adding more indexes makes the
number of deadlocks on index updates to increase and I dont think
clustred indexes works i a large table where you delete records.
I have not used dummy "where" but I be happy if there was a safe
way to define the execution order when you do the programming.
Since the evaluation seems to depend on the amount of data, you
have to debug this in the production systems!?!

/Bj?rn Linde

Quote:-----Original Message-----

Bj?rn,

check few things:
1. Do you have appropriate index on the large table?
2. Try to add a redundant condition for the huge table in the Where clause.
3. If the index used for filtering the large table is nonclustered, can you
change it to clustered?
4. Can you use optimizer hint for the large table?

At least some of these method should work.

HTH,
Dejan Sarka

.

 
 
 

Table scan, Table scan, Table scan

Post by Bj?rn Lind » Fri, 02 Feb 2001 21:46:26


All key columns are integer. The statement looks someting like:

Insert Into #temp
  ...
Select
  ...
From invoice, orderhead, orderline, product, stock

  And orderhead.invoiceno = invoice.invoiceno
  And orderline.orderno = orderhead.orderno
  And product.prodno = orderline.prodno
  And stock.stockno = orderhead.stockno
  And stock.prodno = orderline.prodno

I think that the optimizer uses index for the stock table
and then scans the orderline for the matching prodno?

/Bj?rn Linde

-----Original Message-----

In addition to Dejan's post:

What datatype?

--
Tibor Karaszi, SQL Server MVP
FAQ from Neil at: http://www.sqlserverfaq.com
Please reply to the newsgroup only, not by email.



Again I got a table scan. It was again between two primarykeys.
It's a insert with a select between 5 tables where the condition
is on the first table. The third table contains 10 000 000 records
and when the "query optimizer" thinks that a table scan is
faster than using the primary key the query takes 30 seconds
instead off 0.5.

I have sometimes used SET FORCEPLAN ON but it does not work
well when doing insert or update. The table that is used for
insert/update is taken as first table and that is not optimal
if the condition is on another table in the select statement.

Does anyone know if there is a way to give the complete execution
order for a query?

/Bj?rn Linde

.

 
 
 

Table scan, Table scan, Table scan

Post by Dejan Sark » Fri, 02 Feb 2001 22:23:27


Bj?rn,

query is optimized based on actual index schema, data and statistics pages,
not based on the syntax, so you figured out right: you have to do the final
optimization in the production systems. However, most of the time the Query
Optimizer does the job very well. It's just in some specific cases where you
can help it with a hint. One of these cases is exactly the one you found: it
decides way too early to switch from index seek to table scan if you don't
have a clustered index.

HTH,
Dejan Sarka


Thank you for the tips. This time most of the overhead by
using optimizer hints where i defined the primarykey for
the table that was scaned. I have used the primary and foreign
-key constraints on all tables. Adding more indexes makes the
number of deadlocks on index updates to increase and I dont think
clustred indexes works i a large table where you delete records.
I have not used dummy "where" but I be happy if there was a safe
way to define the execution order when you do the programming.
Since the evaluation seems to depend on the amount of data, you
have to debug this in the production systems!?!

/Bj?rn Linde

Quote:-----Original Message-----

Bj?rn,

check few things:
1. Do you have appropriate index on the large table?
2. Try to add a redundant condition for the huge table in the Where clause.
3. If the index used for filtering the large table is nonclustered, can you
change it to clustered?
4. Can you use optimizer hint for the large table?

At least some of these method should work.

HTH,
Dejan Sarka

.

 
 
 

Table scan, Table scan, Table scan

Post by Bj?rn Lind » Fri, 02 Feb 2001 22:44:29


The least the optimizer should do is to give a error when you store
a procedure that does table scan on large tables. Scan 10 miljon rows
is nothing you want in a production system at any time.

/Bj?rn

-----Original Message-----

Bj?rn,

query is optimized based on actual index schema, data and statistics pages,
not based on the syntax, so you figured out right: you have to do the final
optimization in the production systems. However, most of the time the Query
Optimizer does the job very well. It's just in some specific cases where you
can help it with a hint. One of these cases is exactly the one you found: it
decides way too early to switch from index seek to table scan if you don't
have a clustered index.

HTH,
Dejan Sarka



Thank you for the tips. This time most of the overhead by
using optimizer hints where i defined the primarykey for
the table that was scaned. I have used the primary and foreign
-key constraints on all tables. Adding more indexes makes the
number of deadlocks on index updates to increase and I dont think
clustred indexes works i a large table where you delete records.
I have not used dummy "where" but I be happy if there was a safe
way to define the execution order when you do the programming.
Since the evaluation seems to depend on the amount of data, you
have to debug this in the production systems!?!

/Bj?rn Linde

-----Original Message-----
Bj?rn,

check few things:
1. Do you have appropriate index on the large table?
2. Try to add a redundant condition for the huge table in the Where clause.
3. If the index used for filtering the large table is nonclustered, can you
change it to clustered?
4. Can you use optimizer hint for the large table?

At least some of these method should work.

HTH,
Dejan Sarka

..

.

 
 
 

Table scan, Table scan, Table scan

Post by Erland Sommarsk » Mon, 05 Feb 2001 07:54:55



>The least the optimizer should do is to give a error when you store
>a procedure that does table scan on large tables. Scan 10 miljon rows
>is nothing you want in a production system at any time.

So how is the optimizer to know that it is a production system? And
how does it now that you are running a batch on off-hours where you
don't actually care about the table scan. After all, a table scan on
10 million rows might not be more than 250.000 logical reads, if
your average row is 200 bytes.

--

 
 
 

Table scan, Table scan, Table scan

Post by Erland Sommarsk » Mon, 05 Feb 2001 08:00:51



>Insert Into #temp
>  ...
>Select
>  ...
>From invoice, orderhead, orderline, product, stock

>  And orderhead.invoiceno = invoice.invoiceno
>  And orderline.orderno = orderhead.orderno
>  And product.prodno = orderline.prodno
>  And stock.stockno = orderhead.stockno
>  And stock.prodno = orderline.prodno

Certainly I would add


   AND product.prodno = stock.prodno

While logically redudant, they may be of benefit for the optimizer.

--

 
 
 

Table scan, Table scan, Table scan

Post by Bj?rn Lind » Wed, 07 Feb 2001 01:30:16


You are right! Test or production, I never want table scan on large tables.
Off-hours? For whom? US, Japan or England? (This is a www application.)
200.000 reads 50 times a second. I think this is a big problem.

/Bj?rn Linde

-----Original Message-----

>The least the optimizer should do is to give a error when you store
>a procedure that does table scan on large tables. Scan 10 miljon rows
>is nothing you want in a production system at any time.

So how is the optimizer to know that it is a production system? And
how does it now that you are running a batch on off-hours where you
don't actually care about the table scan. After all, a table scan on
10 million rows might not be more than 250.000 logical reads, if
your average row is 200 bytes.

--

.

 
 
 

Table scan, Table scan, Table scan

Post by Erland Sommarsk » Wed, 07 Feb 2001 08:26:53



>You are right! Test or production, I never want table scan on large tables.
>Off-hours? For whom? US, Japan or England? (This is a www application.)
>200.000 reads 50 times a second. I think this is a big problem.

200000 reads 50 times a second is of course unacceptable. However
there is no need to put your experimental query into the code of
the web server. Run it from Query Analzyer. And, you don't even have
to run it on the production data. If your business is critical you
need to have a test environment which is a copy of your production
anyway. Else there is no way you can track down performance problems
when they happen.

One single query causing 200.000 logical reads is not any major
concern in terms of load. You may risk to lock some users out
for the duration of the query.

By the way, if you want restrictions on how expensive queries that are
permitted, you may want to check out SET QUERY_GOVERNOR_COST_LIMIT
in Books Online.

--

 
 
 

1. Clustered Index Scan vs. Table Scan

This is a follow up post to the "Are Table Scans Always Bad?" post I posted
last week. I am in the process of re-indexing some tables, and I have been
cleaning things up so execution plans show no table scans. Performance seems
to be helped not hurt, so I was just curious, is it safe to say:

A Clustered Index Scan is always better then a Table Scan?

--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com

2. SYSLOGS corrupted?

3. Table Scan - Scans more records than are there

4. Cannot connect to repository

5. TABLE SCAN Vs INDEX SCAN/SEEK

6. Inserting MS Access data into Excel spreadsheet in VB form

7. Index scan vs Full table scan

8. Trigger on Successful database Restore

9. Table scans with large table joined to small table

10. Table Scan on very small table...

11. Forcing table scan on the desired table

12. Scanning tables and moving data across tables