CODEWITHSUNDEEP
sqlperformance
ARB
ARB

Reputation: 295

Creating view ,SQL Query performance

I am trying to create view, But select statement from this view is taking more than 15 secs.How can i make it faster. My query for the view is below.

create view Summary as

select distinct A.Process_date,A.SN,A.New,A.Processing,

COUNT(case when B.type='Sold' and A.status='Processing' then 1 end) as Sold,

COUNT(case when B.type='Repaired' and A.status='Processing' then 1 end) as Repaired,

COUNT(case when B.type='Returned' and A.status='Processing' then 1 end) as Returned

 from

 (select distinct M.Process_date,M.SN,max(P.enter_date) as enter_date,M.status,

 COUNT(case when M.status='New' then 1 end) as New,

COUNT(case when M.status='Processing' and P.cn is null then 1 end) as Processing

from DB1.dbo.Item_details M 

left outer join DB2.dbo.track_data P on M.SN=P.SN

group by M.Process_date,M.SN,M.status) A 

left outer join DB2.dbo.track_data B on A.SN=B.SN 

where A.enter_date=B.enter_date or A.enter_date is null 

group by A.Process_date,A.New,A.Processing,A.SN

After this view..my select query is

select distinct process_date,sum(New),sum(Processing),sum(sold),sum(repaired),sum(returned) from Summary where month(process_date)=03 and year(process_date)=2011

Please suggest me on what changes to be made for the query to perform faster.

Thank you ARB

Upvotes: 1

Views: 181

Answers (3)

Kangkan
Kangkan

Reputation: 15571

For tuning a database query, I shall add few items additional to what @Davyd has already listed:

  1. Look at the tables and indexing on those tables. Putting the right index and avoiding the wrong ones always speed up the query.
  2. Is there anything in the where condition that is not part of any index? At times we put index on a column and in the query we use a cast or convert on the column. So the underlying index is not effective. You may consider setting the index on the cast/convert of the column.
  3. Look at the normal form conformity or over normalisation. 3.

Good luck.

Upvotes: 1

plang
plang

Reputation: 5656

If your are using Postgresql, I suggest you use a tool like "http://explain.depesz.com/" in order to see more clearly what part of your query is slow. Depending on what you get, you could either optimize your indexes, or rewrite part of your query. If your are using another database, I'm sure a similar tool exists.

If none of these ideas help, the final solution would be to create a "materialized query". There are plenty of infos on the web regarding this.

Good luck.

Upvotes: 0

Davyd Geyl
Davyd Geyl

Reputation: 4623

It is hard to give advices without seeing the actual data and the structure of the tables. I would rewrite the query keeping in mind these principles:

  1. Use inner join instead of outer join if possible.
  2. Get rid of case operator inside COUNT function. Build a query so you use conditions in WHERE section not in COUNT.
  3. Try to not use aggregated values in GROUP BY. Currently you use aggregated values New and Processing for grouping. Use GROUP BY by existing table values if possible.
  4. If the query gets too complicated, break it into smaller queries and combine results in the final query. Writing a store procedure may help in this case.

I hope this helps.

Upvotes: 1

Related Questions