Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

how to improve the query performance (speed) in sql server 2005

I have a view with 270677 rows.and I have the one of the column as_of_date.
I want to get the distinct as_of_date from that view.But the query is running so long(it'staking 30sec's) But I have to display this less than that time.

Please give me some idea
and I am ruuning the query
select convert(varchar(10),as_of_date,120) as asofdate from xxxx group by as_of_date order by as_of_date desc
0
petersti
Asked:
petersti
  • 2
  • 2
  • 2
  • +2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
do you really need the "view" ?
apart from that, seeing the explain plan of the query might help
0
 
peterstiAuthor Commented:
I need that view.And Iapplied same query from the table also it is taking 30sec's
0
 
Éric MoreauSenior .Net ConsultantCommented:
can you try this

select convert(varchar(10),as_of_date,120) as asofdate
from (
   select  distinct as_of_date from xxxx
) AS A
order by as_of_date desc
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
peterstiAuthor Commented:
There is no change .It is taking the same time
0
 
Éric MoreauSenior .Net ConsultantCommented:
what about : select  distinct as_of_date from xxxx
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so you need to "improve" the view, and/or add possibly indexes to the underlying tables.
without more details, impossible to suggest something concrete.
0
 
ZberteocCommented:
make sure you have an index on the as_of_date column on the table where the column actually comes from.
0
 
cfEngineersCommented:
if your as_of_date column is a timestamp, then you will always get all records and they are all distinct
you should use datepart function to pull year, month and day, maybe you want hours too?
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now