Table Scan Costing a lot of time

Table Scan Costing a lot of time

Post by Anthony Altie » Wed, 19 Mar 2003 19:48:18



table: tblpolcoverage has > 4million records
table: tblpolmaster has > 4million records
table Index: tblpolcoverage.trans_dt not clustered.
table Index: tblpolcoverage.masteridx not clustered.
table Index: tblpolmaster.idx clustered primary index.
I hope this is enought information to understand my problem.
Simple query at least i think so, the query below takes 1min 23 secs
and returns 281211 records this i believe is way to long for me to
wait for these records.  When i look at the execution plan it does a
table scann that cost me about 27 percent of the time and there is
also a clustered index scan for 63 percent of the time.
Am I asking sql server to do to much or return to much information.
Please if you need addition information let me know.

SELECT tblpolmaster.PR_NO, tblpolmaster.pol_lname,
tblpolmaster.pol_fname,
tblpolmaster.pol_num, tblpolcoverage.eff_dt, tblpolcoverage.premium
From tblpolcoverage
INNER JOIN tblpolmaster on tblpolcoverage.masteridx = tblpolmaster.idx
Where (tblPolCoverage.trans_dt Between '12/01/02' And '12/31/02
23:59:59')

if I change the dates in the query to give me one week worth of info
much faster and no table scan this is odd?

 
 
 

Table Scan Costing a lot of time

Post by chri » Wed, 19 Mar 2003 20:33:50


A couple of ideas here. First of all Im not positive that using "between"
will make use of your index. Im sure I will be corrected here if Im wrong.
If possible, rewrite the query to use >= and <= and see if that helps. If
not, see "index hints" in BOL. You arent asking to much of SQL here.


Quote:> table: tblpolcoverage has > 4million records
> table: tblpolmaster has > 4million records
> table Index: tblpolcoverage.trans_dt not clustered.
> table Index: tblpolcoverage.masteridx not clustered.
> table Index: tblpolmaster.idx clustered primary index.
> I hope this is enought information to understand my problem.
> Simple query at least i think so, the query below takes 1min 23 secs
> and returns 281211 records this i believe is way to long for me to
> wait for these records.  When i look at the execution plan it does a
> table scann that cost me about 27 percent of the time and there is
> also a clustered index scan for 63 percent of the time.
> Am I asking sql server to do to much or return to much information.
> Please if you need addition information let me know.

> SELECT tblpolmaster.PR_NO, tblpolmaster.pol_lname,
> tblpolmaster.pol_fname,
> tblpolmaster.pol_num, tblpolcoverage.eff_dt, tblpolcoverage.premium
> From tblpolcoverage
> INNER JOIN tblpolmaster on tblpolcoverage.masteridx = tblpolmaster.idx
> Where (tblPolCoverage.trans_dt Between '12/01/02' And '12/31/02
> 23:59:59')

> if I change the dates in the query to give me one week worth of info
> much faster and no table scan this is odd?


 
 
 

Table Scan Costing a lot of time

Post by Brian Mora » Wed, 19 Mar 2003 20:29:15


There are two issues:

* how to optimize the existing query.
* why in the world are you writing a single query that returns over 200K
rows

I'm not going to address the firts query for now... probally... it can be
made to run much faster. However.... it's almost always a bad idea to return
result sets that large. What are you doing with it? Result sets that large
are almost useless to a human being. People simply can't comprehend that
much data.

--

Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com


Quote:> table: tblpolcoverage has > 4million records
> table: tblpolmaster has > 4million records
> table Index: tblpolcoverage.trans_dt not clustered.
> table Index: tblpolcoverage.masteridx not clustered.
> table Index: tblpolmaster.idx clustered primary index.
> I hope this is enought information to understand my problem.
> Simple query at least i think so, the query below takes 1min 23 secs
> and returns 281211 records this i believe is way to long for me to
> wait for these records.  When i look at the execution plan it does a
> table scann that cost me about 27 percent of the time and there is
> also a clustered index scan for 63 percent of the time.
> Am I asking sql server to do to much or return to much information.
> Please if you need addition information let me know.

> SELECT tblpolmaster.PR_NO, tblpolmaster.pol_lname,
> tblpolmaster.pol_fname,
> tblpolmaster.pol_num, tblpolcoverage.eff_dt, tblpolcoverage.premium
> From tblpolcoverage
> INNER JOIN tblpolmaster on tblpolcoverage.masteridx = tblpolmaster.idx
> Where (tblPolCoverage.trans_dt Between '12/01/02' And '12/31/02
> 23:59:59')

> if I change the dates in the query to give me one week worth of info
> much faster and no table scan this is odd?

 
 
 

Table Scan Costing a lot of time

Post by Anthony Altier » Wed, 19 Mar 2003 21:12:54


A hard copy of the file i create must have all the records in the file
as well, i need a report to show management.  With that said i have
taken out the between clause and have picked up a couple of seconds but
nothing dramatic any other suggestions. Please

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Table Scan Costing a lot of time

Post by chri » Wed, 19 Mar 2003 21:42:29


Did you try an index hint?


Quote:> A hard copy of the file i create must have all the records in the file
> as well, i need a report to show management.  With that said i have
> taken out the between clause and have picked up a couple of seconds but
> nothing dramatic any other suggestions. Please

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

 
 
 

Table Scan Costing a lot of time

Post by Kalen Delane » Wed, 19 Mar 2003 22:40:54


Ok, I'll guess I'll correct you. :-)

You can actually see it if you look at the popup in the query plan output,
and look at the argument. A between will show up as two SEEK expressions,
column >= some expression AND column <= some expression. You'll usually see
this conversion even if the index is not used, but this is how the query is
evaluated. BETWEEN is always translated into >= and <= expressions.

HTH

--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com


> A couple of ideas here. First of all Im not positive that using "between"
> will make use of your index. Im sure I will be corrected here if Im wrong.
> If possible, rewrite the query to use >= and <= and see if that helps. If
> not, see "index hints" in BOL. You arent asking to much of SQL here.



> > table: tblpolcoverage has > 4million records
> > table: tblpolmaster has > 4million records
> > table Index: tblpolcoverage.trans_dt not clustered.
> > table Index: tblpolcoverage.masteridx not clustered.
> > table Index: tblpolmaster.idx clustered primary index.
> > I hope this is enought information to understand my problem.
> > Simple query at least i think so, the query below takes 1min 23 secs
> > and returns 281211 records this i believe is way to long for me to
> > wait for these records.  When i look at the execution plan it does a
> > table scann that cost me about 27 percent of the time and there is
> > also a clustered index scan for 63 percent of the time.
> > Am I asking sql server to do to much or return to much information.
> > Please if you need addition information let me know.

> > SELECT tblpolmaster.PR_NO, tblpolmaster.pol_lname,
> > tblpolmaster.pol_fname,
> > tblpolmaster.pol_num, tblpolcoverage.eff_dt, tblpolcoverage.premium
> > From tblpolcoverage
> > INNER JOIN tblpolmaster on tblpolcoverage.masteridx = tblpolmaster.idx
> > Where (tblPolCoverage.trans_dt Between '12/01/02' And '12/31/02
> > 23:59:59')

> > if I change the dates in the query to give me one week worth of info
> > much faster and no table scan this is odd?

 
 
 

Table Scan Costing a lot of time

Post by Anthony Altie » Thu, 20 Mar 2003 15:32:14



> Did you try an index hint?



> > A hard copy of the file i create must have all the records in the file
> > as well, i need a report to show management.  With that said i have
> > taken out the between clause and have picked up a couple of seconds but
> > nothing dramatic any other suggestions. Please

> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!

I have tried index hints and picked up some time but have only picked
up a couple of seconds.  Any other suggestions.
 
 
 

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. SQL 7 / Access 97 - Performance Problems

3. Reattaching tables from A97 takes a lot of CPU Time on the server

4. SQL Birthday

5. Undocumented feature costs a lot of performance in COPY

6. ADO Hangs: "4100 800A1004 Unable to create business object"

7. Database Comparison papers?

8. How do I set the SQL server to log all SQL( execution time, table scans)

9. clustered index scan cost is high

10. Clustered Index Scan vs. Table Scan

11. Query Analyzer - I/O Cost, CPU Cost, and Cost

12. Table Scan - Scans more records than are there