Table Scan - Scans more records than are there

Table Scan - Scans more records than are there

Post by David Roberts » Sat, 09 Mar 2002 00:11:59



My problem is there is a complex stored procedure which somedays takes
0 seconds and other days takes 15. Looking at the sp via query
analyser shows that the estimated execution plan for the table scan
part has an estimated row count of 42 but when run, the row count
value is 72,000. The table is only 2000 rows long!!

What I have tried.
Forcing a recompile.
Dropping the procedure and recreating it.
Updating the statistics on all the relevant table.
Running the procedure manually, with all its parameters infilled (this
returned only 42 rows) and worked in 1 second.

Any suggestions would be appreciated.

Thanks

 
 
 

Table Scan - Scans more records than are there

Post by yang zho » Sat, 09 Mar 2002 05:55:20


Have your tried to use "index hints" in your queries for any indexes?
See BOL for more information on index hints.

Yang Zhong


> My problem is there is a complex stored procedure which somedays takes
> 0 seconds and other days takes 15. Looking at the sp via query
> analyser shows that the estimated execution plan for the table scan
> part has an estimated row count of 42 but when run, the row count
> value is 72,000. The table is only 2000 rows long!!

> What I have tried.
> Forcing a recompile.
> Dropping the procedure and recreating it.
> Updating the statistics on all the relevant table.
> Running the procedure manually, with all its parameters infilled (this
> returned only 42 rows) and worked in 1 second.

> Any suggestions would be appreciated.

> Thanks


 
 
 

1. Table scan, Table scan, Table scan

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

2. DAO, data bound Listbox and MSFlexGrid/DBGrids

3. TABLE SCAN Vs INDEX SCAN/SEEK

4. convert *.btr to sql server

5. Index scan vs Full table scan

6. Info Needed: Stonefiled VFP database toolkit

7. Clustered Index Scan vs. Table Scan

8. Combine two SQL SELECT statements

9. Why am I doing a Table Scan?

10. Index Scans become Seq Scans after VACUUM ANALYSE

11. Light Scans and Light Appends (Light Scans/Appends)

12. Index Fast Full Scan vs Index Full Scan?

13. Index Full Scan Vs. Index Range Scan