SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 need help: what am i doing wrong
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

murd
Starting Member

23 Posts

Posted - 09/27/2012 :  13:25:20  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4604 Posts

Posted - 09/27/2012 :  13:39:00  Show Profile  Reply with Quote
Wait longer?

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

murd
Starting Member

23 Posts

Posted - 09/27/2012 :  14:11:19  Show Profile  Reply with Quote
edit: nevermind i found the solution

Edited by - murd on 09/27/2012 15:18:54
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/27/2012 :  15:21:30  Show Profile  Reply with Quote
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.

Edited by - sunitabeck on 09/27/2012 15:22:42
Go to Top of Page

murd
Starting Member

23 Posts

Posted - 09/27/2012 :  15:40:45  Show Profile  Reply with Quote
no...I still read and thanks for the answer i was initially looking for. Will keep this in mind for future headaches
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000