Too Slow - SQL Server Forums

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)

Too Slow

Author

Topic

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2008-11-21 : 11:54:27

Dear All,

Kindly help me look into this scenario..

I am working a BI report consumed by both analysis services and reporting service project. I sourced the data from different databases tables, views.

I use 'union all' to join each collection to make a whole view for the report.

for example

Table 1
key | item | value | date
001 Food 100 200810
002 Food 2000 200802

Table 2
key | item | value | date
001 Cloth 300 200803
002 Cloth 500 200811

To do this for my view..
create view xx
as
Select
key,date,item,value
from table1
union all
Select
key,date,item,value
from table1

For 2 to 3 tables with few records it worked fine. But I need to add like 60 items(different tables), it began to slow.

Then I wrote a store procedure to the loading into another table..

create procedure yy
as
truncate table pp;

insert into pp
values
(
Select
key,date,item,value
from table1
union all
Select
key,date,item,value
from table1
)

worked fine at first, but lately now, if found out that the store procedure kept running even for 2 - 3 hours without finishing. It worked like a 'goto without end' script.

Please help.

Thanks

abacus

I sign for fame not for shame but all the same, I sign my name.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-21 : 11:57:20

you mean you need take data from 60 tables? are these tables really big? also what does the execution plan of stored procedure sugests?

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2008-11-21 : 12:17:06

quote:

you mean you need take data from 60 tables?

Yes

quote:

are these tables really big?

Yes

quote:

also what does the execution plan of stored procedure sugests?

It says ok. when I run each table individually, under 8 sec, it has queried 1000 plus record. But when I do all slows.

Thanks

I sign for fame not for shame but all the same, I sign my name.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-21 : 12:23:13

Nope. i was asking what steps were bottleneck?

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2008-11-21 : 12:47:33

They all work fine individually.

I think is at the level of sourcing from different tables.

Do you think of another method of doing this same job?

Thanks

I sign for fame not for shame but all the same, I sign my name.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-11-21 : 13:00:41

quote:
Originally posted by abacusdotcom
[br
It says ok. when I run each table individually, under 8 sec, it has queried 1000 plus record. But when I do all slows.

I think yoiu found your answer. If you doing UNION ALL on 60 tables to populate a single table then just run 60 insert statements and be done with it.

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2008-11-21 : 13:52:58

Thanks... will try that...

I sign for fame not for shame but all the same, I sign my name.

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2008-11-26 : 06:13:15

Thanks.... Lampery .. this work fine

I sign for fame not for shame but all the same, I sign my name.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources