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_num, tblpolcoverage.eff_dt, tblpolcoverage.premium
INNER JOIN tblpolmaster on tblpolcoverage.masteridx = tblpolmaster.idx
Where (tblPolCoverage.trans_dt Between '12/01/02' And '12/31/02
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?