Query brutually slow SQL Server 2005

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)

Query brutually slow SQL Server 2005

Author

Topic

ScottishDBA
Starting Member

1 Post

Posted - 2007-10-31 : 11:08:40

Hi, Can anyone help speed this up.

I've got a table that takes site statistics and in order for our webman to display properly I need to populate it with records with a 0 value where necessary - long story as to why, i just do.

The statistics are for 7 types, so I check for companies that have a value for a certain type/time period against all companies for a specific time period and type and i'm left with those that i want to create 0 values for.

The problem is when i execute the plain Select section of the query below, it executes and returns me 13000 rows in about 4 seconds, when i run the whole thing with the insert, it takes around 2 hours and canes the DB. if it were just the one type i could live with this but it goes round 7 types and is untenable.

The server has autogrowth at 1MB.. unlimited size and is on a powerful box so i can't see why it's so damn bad.

This is the following query that gets called

insert into stats_companies (ID_COM,[TYPE],Number,MonthBreak)
select distinct ID_COM,@TYPE,0,@MONTH
from stats_companies where MonthBreak=@MONTH AND
ID_COM NOT in
(
select
distinct ID_COM
from stats_companies where [type]=@TYPE AND MonthBreak=@MONTH

cheers in advance

nr
SQLTeam MVY

12543 Posts

Posted - 2007-10-31 : 11:24:58

try these two - if neither helps then you'll have to look at the query plan.
Maybe try extracting the IDs for the month into a temp table as well then do the join.

select distinct ID_COM
into #a
from stats_companies where [type]=@TYPE AND MonthBreak=@MONTH

insert into stats_companies (ID_COM,[TYPE],Number,MonthBreak)
select distinct ID_COM,@TYPE,0,@MONTH
from stats_companies
where MonthBreak = @MONTH
AND ID_COM NOT in (select ID_COM from #a)

create index ix on #a (ID_COM)
insert into stats_companies (ID_COM,[TYPE],Number,MonthBreak)
select distinct stats_companies.ID_COM,@TYPE,0,@MONTH
from stats_companies
left join #a
on #a.ID_COM = stats_companies.ID_COM
where stats_companies.MonthBreak = @MONTH
AND #a.ID_COM is null

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources