need help: what am i doing wrong

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 2008 Forums

Transact-SQL (2008)

need help: what am i doing wrong

Author

Topic

murd
Starting Member

24 Posts

Posted - 2012-09-27 : 13:25:20

quote:
select top 1 searchname, visit, L_Name, apptstart, ORD_DATE, OB_DATE, apptstop
from
(
select row_number() over (partition by pp.searchname order by l.name DESC) AS Seq,
pp.searchname, max(pv.visit)as visit,l.name as L_Name, ap.apptstart, ord.orderdate as ORD_DATE, ob.obsdate as OB_DATE, ap.apptstop
from patientprofile pp
inner join patientvisit pv on pp.patientprofileid = pv.patientprofileid
inner join LOCREG l on pv.FacilityId = l.FacilityId
inner join appointments ap on pv.patientvisitid = ap.patientvisitid
inner join document d on pp.pid = d.pid and pv.patientvisitid = d.patientvisitid
inner join orders ord on d.pid = ord.pid
inner join obs ob on d.pid = ob.pid
inner join obshead oh on ob.hdid = oh.hdid
group by pp.searchname,l.NAME, ap.apptstart, ord.orderdate, ob.obsdate, ap.apptstop
)t
where Seq=1
order by searchname

Executing query.....nothing shows up even after 6 + mins

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-27 : 13:39:00

Wait longer?

Seriously thought, we can't possible help with the information provided. DDL? Indexes? Row Counts? Give us something..

murd
Starting Member

24 Posts

Posted - 2012-09-27 : 14:11:19

edit: nevermind i found the solution

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-27 : 15:21:30

If the query is taking longer than expected, I would do couple of diagnostic tests first.

1. Change the inner query to do a TOP (N) rows and see if that completes quickly.

select top 1 searchname, visit, L_Name, apptstart, ORD_DATE, OB_DATE, apptstop
from
(
select TOP (10) row_number() over (partition by pp.searchname order by l.name DESC) AS Seq,
pp.searchname, max(pv.visit)as visit,l.name as L_Name, ap.apptstart, ord.orderdate as ORD_DATE, ob.obsdate as OB_DATE, ap.apptstop 
from patientprofile pp

2. See how many rows there are in each of your tables. That should give you a sense of what you are up against. Rather than use the usual count(*) to find the number of rows, use:

SELECT row_count
FROM   sys.dm_db_partition_stats
WHERE  OBJECT_NAME(OBJECT_ID) = 'YourTableNameHere'

3. Focus on the tables with large number of rows. See if they have proper indexes on the join columns and/or columns in the where clauses.

[Edit]:

quote:
edit: nevermind i found the solution

In that case, don't read this.

murd
Starting Member

24 Posts

Posted - 2012-09-27 : 15:40:45

no...I still read and thanks for the answer i was initially looking for. Will keep this in mind for future headaches

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources