Query Performance - 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 2000 Forums

Transact-SQL (2000)

Query Performance

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2005-08-29 : 14:38:45

Below query can be run in 3 sec.
select 'Chol+' as Service , count(aaa.FLDSSN) as ct
from
(select distinct(g.FLDSSN)
from warehouse..f_ohm h
join warehouse..d_employee g on h.FLDEMPLOYEE = g.FLDREC_NUM

where h.FLDTYPE in ( 'CHOLSC', 'CHOLE')
and
h.dataset = 111
and h.flddate <= '20050631'
group by g.FLDSSN)aa

But, if I change h.flddate <= '20050631' to h.flddate between '20050401' and '20050630'. It takes more than 3 min.

select 'Chol+' as Service , count(aaa.FLDSSN) as ct
from
(select distinct(g.FLDSSN)
from warehouse..f_ohm h
join warehouse..d_employee g on h.FLDEMPLOYEE = g.FLDREC_NUM

where h.FLDTYPE in ( 'CHOLSC', 'CHOLE')
and
h.dataset = 111
and h.flddate between '20050401' and '20050631'
group by g.FLDSSN)aa

The flddate column has a cluster index on it...
I am not sure what possible I can do to improve the query spreed...

Kristen
Test

22859 Posts

Posted - 2005-08-29 : 14:51:59

Is this any faster? (Remove the outer select [I think I've got the syntax right for that] and explictly cast the dates)


select 'Chol+' as Service , COUNT(DISTINCT g.FLDSSN) as ct
from warehouse..f_ohm h
join warehouse..d_employee g on h.FLDEMPLOYEE = g.FLDREC_NUM
where h.FLDTYPE in ( 'CHOLSC', 'CHOLE')
and h.dataset = 111
and h.flddate >= CAST('20050401' AS DATETIME)
and h.flddate <= CAST('20050631' AS DATETIME)
group by g.FLDSSN

If that isn't any faster I would try pre-creating variables with the dates:


DECLARE @StartDate datetime,
        @StopDate datetime
SELECT @StartDate = CAST('20050401' AS DATETIME),
       @StopDate = CAST('20050631' AS DATETIME)
...
and h.flddate >= @StartDate 
and h.flddate <= @StopDate 
...

Kristen

jung1975
Aged Yak Warrior

503 Posts

Posted - 2005-08-29 : 15:37:54

No, neither of them made a query faster..

but, thanks for the input though..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-29 : 15:39:33

Why are you doing a DISTINCT and a GROUP BY?

Tara

jung1975
Aged Yak Warrior

503 Posts

Posted - 2005-08-29 : 17:02:53

Tara,
Actually there is another column(h.flddesc) in the select statement.
I would like to group by FLDSSN and FLDDESC and get the distinct count of FLDSSN...

select 'Chol+' as Service , count(aaa.FLDSSN) as ct
from
(select distinct(g.FLDSSN),h.flddesc
from warehouse..f_ohm h
join warehouse..d_employee g on h.FLDEMPLOYEE = g.FLDREC_NUM

where h.FLDTYPE in ( 'CHOLSC', 'CHOLE')
and
h.dataset = 111
and h.flddate <= '20050631'
group by g.FLDSSN,h.flddesc)aa

I was just wondering why the query got really slow after I added a between statement..
and trying to find a way to improve the query speed.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-29 : 17:05:32

Please post DDL for both tables, DML for sample data, and the expected result using that sample data. 5-10 rows per table of sample data should be enough. With that information, I'm sure we can come up with a more efficient query as it appears your derived table isn't right.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources