improving query time without adding index

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

improving query time without adding index

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-08-19 : 11:06:25

I am working on a rather large stored procedure that takes about 30 minutes to run. The execution plan shows that a significant amount of time is spent on a table scan. Those in charge do not want to add indexes to the table. Is there any way to get away from the table scan and improve performance?

table history has a few million records, which is why the scan is taking so long.

There is an existing index(non-unique, non-clustered) on the column rate_table. Would making it first in the where clause help at all?

Here is the query:
insert into @tbl_tempwritten
select a.policy_base,
a.policy_suffix,
b.vehicle_number,
b.driver_number,
a.coverage,
a.driver_class,
a.rate_zip ,
written_premium = sum(written_premium)
from history a WITH (NOLOCK)
LEFT OUTER JOIN vehicle b WITH (NOLOCK)
ON (a.policy_base = b.policy_base and
a.policy_suffix = b.policy_suffix and
a.vehicle_number = b.vehicle_number and
(b.current_flag = 'Y' or b.endorse_status = 'D'))
where a.policy_state = 'TX' and
a.coverage in ('BI','CL') and
a.rate_zip is not null and
a.accounting_period >= @begin_period and
a.accounting_period <= @end_period and
a.rate_table >= @start_rate_table and
a.rate_table <= @end_rate_table
group by a.policy_base,
a.policy_suffix,
b.vehicle_number,
b.driver_number,
a.coverage,
a.driver_class,
a.rate_zip

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-08-19 : 11:25:13

Here is the issue:

1) Table variables are worst when dealing with Million Records. Instead use Temp Table with appropriate indexes.

2)Check Execution plan and see what table is doing Table scan.

3)Take Advantage of include columns to avoid Bookmark or Key Lookups. But DML operation will be slow with include column as they reside in Leaf level of N-Clustered indexes.

4) Make Clustered index on appropriate columns

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources