Increase performance of View in SQL server.

Question

I have 2 views. One view is union all of all tables (18 tables) and second view is using 1st view's information to get the result. 1st view is working good and taking few mins only but 2nd view is talking too much time to execute. Is there a way that I can reduce the process time? if yes then please show me how, I ma new to SQL Thanks for the help in advance!!

1st view is like below:

CREATE View [dbo].[View1]
as

select a.*, b.Curr from T1 a, Lookup_Table b

where a.L = b.L
and a.S = b.S
and a.C = b.C
union all
select a.*, b.Curr  from T2 a, Lookup_Table b
where a.L = b.L
and a.S = b.S
and a.C = b.C

union all

.... same thing for up to 18 tables

2nd view is like below:

CREATE View [dbo].[View2]
AS
SELECT a.C,a.S,a.R,a.L
a.V,....,a.Amount, ((SUM(a.Amount)*c.Sca)/b.FX) as 'ProAmount'  
FROM View1 a, LookupT3 b, LookupT4 c
where a.P = b.P
and a.PQ = b.PQ
and a.C = b.C
and a.c = c.C
and a.L = c.L
group by a.C,a.S, a.R,a.L,a.C,a.V,a.V,
a.P,... ,b.FX,c.Sca

Answer 1

Hi there,

 

There is probably a missing join reference.  You should select Query – Include Actual Execution Plan and execute a select against the second view.  Find the statement that is using the most cost %.  Identify what fields it is using and ensure there is an index on them.

 

Also, using inner join rather than joining tables in where clause is best practice.  If you use New View and the visual editor it should help with building the statements for you.

 

You may need to add an index to help with the results.  You can also try to Analyze Query using Database Tuning Advisor.

 

cheers,

Andrew

 

"Vicky13" wrote in message news:345b798f-2648-4f75-99ac-e8b044093c01...

I have 2 views. One view is union all of all tables (18 tables) and second view is using 1st view's information to get the result. 1st view is working good and taking few mins only but 2nd view is talking too much time to execute. Is there a way that I can reduce the process time? if yes then please show me how, I ma new to SQL Thanks for the help in advance!!

1st view is like below:

CREATE View [dbo].[View1]
as

select a.*, b.Curr from T1 a, Lookup_Table b

where a.L = b.L
and a.S = b.S
and a.C = b.C
union all
select a.*, b.Curr  from T2 a, Lookup_Table b
where a.L = b.L
and a.S = b.S
and a.C = b.C

union all

.... same thing for up to 18 tables

2nd view is like below:

CREATE View [dbo].[View2]
AS
SELECT a.C,a.S,a.R,a.L
a.V,....,a.Amount, ((SUM(a.Amount)*c.Sca)/b.FX) as 'ProAmount' 
FROM View1 a, LookupT3 b, LookupT4 c
where a.P = b.P
and a.PQ = b.PQ
and a.C = b.C
and a.c = c.C
and a.L = c.L
group by a.C,a.S, a.R,a.L,a.C,a.V,a.V,
a.P,... ,b.FX,c.Sca

---

Andrew Sears, T4G Limited, http://www.performancepointing.com

Answer 2

Could you please provide execution plan for View2, as per my understanding it should perfrom Table Scan on big tables used in View1... if that is the case you may have to re-architecture your view2 to avoid table scan..

Thanks..

Answer 3

How many rows the first view returns? I think you will not be able to achieve good performance by trying to select from a view. 

What is the reason of having 18 tables with a similar information?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 4

Thanks Andrew for your reply. I already gave Index on all the fields I am using here. Also used inner join instead of where. But it is still taking same time. It is also giving me below error: An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

Is some SP can help me to reduce execution task and combine this 2 views?

Please help. Thanks

---

Vicky

Answer 5

You can convert the same in SP by creating Temp table out of first View and create index on temp table and join in second view..

But before you create Index on temp table you need to know how many records you are expecting from View1

Answer 6

All tables are separated based on Product and there are large number of records are coming into each tables in millions. So to create report on particular one table can take too much time so I created separate table of each product.

First view returns '27387914' now. And this is beginning as I just loaded data for one period. I have to load data for many periods.

---

Vicky

Answer 7

Hi,

How can I paste execution plan here? But I checked that and when is it scanning tables it is taking 57% to 100%.

---

Vicky

Answer 8

I think spreading data into multiple tables complicates your problem. It may be better to use partitioning instead of many separate tables.

Do you really want to return all 27387914 rows back to the client? That's too many...

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 9

The things is I don't know the number of records I am getting for View1 as it is keep increasing with job run every hour.

---

Vicky

Answer 10

Yes. Cognos reports are using these data (View2) to create their reports.

---

Vicky