Performance Issue - 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)

Performance Issue

Author

Topic

Friznost
Starting Member

20 Posts

Posted - 2007-10-02 : 14:22:51

I've got three tables...

Initial_Procedure
ID
Person_ID
Completed_DTTM

Procedure_2
ID
Person_ID
Completed_DTTM

Procedure_3
ID
Person_ID
Completed_DTTM

I need the final result to be:

Tbl_Final_Result
Initial_Procedure.ID
Initial_Procedure. Person_ID
Initial_Procedure. Completed_DTTM
Procedure_2.ID
Procedure_2.Person_ID
Procedure_2.Completed_DTTM
Procedure_3. ID
Procedure_3. Person_ID
Procedure_3. Completed_DTTM

Rules (Hope these are clear enough):
- Each person has at least one Initial_Procedure.

Procedure_2 and Procedure_3 rules are the same

- There may be zero, one, or more Procedure_2 for each Initial_Procedure.
- If there is more than one Procedure_2 for an Initial_Procedure get the most recent.

- To link Procedure_2 to Initial_Procedure the Initial_Procedure.Completed_DTTM < Procedure_2.Completed_DTTM and
Initial_Procedure.Person_ID = Procedure_2.Person_ID

- If there is more than one Initial_Procedure: (Procedure_2.Completed_DTTM Between row 1: Initial_Procedure.Completed_DTTM and row 2: Initial_Procedure.Completed_DTTM) --(assuming Initial_Procedure is in order)
AND Initial_Procedure.Person_ID = Procedure_2.Person_ID

I've got this working right now by using nested loops but it's very inefficient because of the size of my tables. There must be another way?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-02 : 14:36:28

can you also post some sample records from each table?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Friznost
Starting Member

20 Posts

Posted - 2007-10-02 : 15:01:12

Initial_Procedure IP
ID Person_ID Completed_DTTM
1 1 01/10/2007
1 1 02/15/2007
1 1 02/20/2007
1 2 01/02/2007
1 3 06/26/2007

Procedure_2 P2
ID Person_ID Completed_DTTM
2 1 01/09/2007
2 1 01/15/2007
2 1 01/16/2007
2 1 01/17/2007
2 1 02/19/2007
2 1 07/25/2007
2 1 09/02/2007
2 2 01/01/2007
2 2 01/14/2007
2 2 01/20/2007
2 3 05/04/2007
2 3 06/27/2007

Tbl_Final_Result
IP.ID IP.Person_ID IP.Completed_DTTM P2.ID P2.Person_ID P2.Completed_DTTM
1 1 01/10/2007 2 1 01/17/2007
1 1 02/15/2007 2 1 02/19/2007
1 1 02/20/2007 2 1 09/02/2007
1 2 01/02/2007 2 2 01/20/2007
1 3 06/26/2007 2 3 06/27/2007

I've excluded the table Procedure_3 becuase it's essentially the same as Procedure_2.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-02 : 16:15:29

HEre's a sample: I just got the results for IP and P2. You can add the third table appropriately.


Declare @Initial_Procedure table (ID int, Person_ID int, Completed_DTTM datetime)
Insert into @Initial_Procedure 
Select 1, 1, '01/10/2007' union all
Select 1, 1, '02/15/2007' union all
Select 1, 1, '02/20/2007' union all
Select 1, 2, '01/02/2007' union all
Select 1, 3, '06/26/2007'

Declare @Procedure_2 table( ID int, Person_ID int, Completed_DTTM datetime)
Insert into @Procedure_2 
Select 2, 1, '01/09/2007' union all
Select 2, 1, '01/15/2007' union all
Select 2, 1, '01/16/2007' union all
Select 2, 1, '01/17/2007' union all
Select 2, 1, '02/19/2007' union all
Select 2, 1, '07/25/2007' union all
Select 2, 1, '09/02/2007' union all
Select 2, 2, '01/01/2007' union all
Select 2, 2, '01/14/2007' union all
Select 2, 2, '01/20/2007' union all
Select 2, 3, '05/04/2007' union all
Select 2, 3, '06/27/2007'

Select IP.*
FROM @Initial_Procedure IP
LEFT JOIN (
	Select  P.Person_ID, max(P.Completed_DTTM) MaxCompleted
	from @Procedure_2 P
	Group by P.Person_ID 
	) P2 on IP.Person_ID = P2.Person_ID And IP.Completed_DTTM < P2.MaxCompleted


(5 row(s) affected)

(12 row(s) affected)
ID          Person_ID   Completed_DTTM
----------- ----------- -----------------------
1           1           2007-01-10 00:00:00.000
1           1           2007-02-15 00:00:00.000
1           1           2007-02-20 00:00:00.000
1           2           2007-01-02 00:00:00.000
1           3           2007-06-26 00:00:00.000

(5 row(s) affected)

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Friznost
Starting Member

20 Posts

Posted - 2007-10-02 : 18:27:22

This works great except for one part. Here is an example which doesn't work with your code.

Initial_Procedure IP
ID Person_ID Completed_DTTM
1 4 03/14/2006
1 4 10/10/2006
1 4 08/27/2007

Procedure_2 P2
ID Person_ID Completed_DTTM
2 4 06/11/2006

Tbl_Final_Result
IP.ID IP.Person_ID IP.Completed_DTTM P2.ID P2.Person_ID P2.Completed_DTTM
1 4 03/14/2006 NULL NULL NULL
1 4 10/10/2006 2 4 06/11/2006
1 4 08/27/2007 NULL NULL NULL

Your code gives me...

1 4 03/14/2006 2 4 06/11/2006
1 4 10/10/2006 2 4 06/11/2006
1 4 08/27/2007 NULL NULL NULL

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-02 : 19:42:03

Not sure why you would expect the values from Procedure_2 P2 for second row with IP value of 10/10/2006 when the value of 03/14/2006 < P2 value of 06/11/2006. my query would return:


ID          Person_ID   Completed_DTTM          Person_ID   MaxCompleted
----------- ----------- ----------------------- ----------- -----------------------
1           4           2006-03-14 00:00:00.000 4           2006-06-11 00:00:00.000
1           4           2006-10-10 00:00:00.000 NULL        NULL
1           4           2007-08-27 00:00:00.000 NULL        NULL

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Friznost
Starting Member

20 Posts

Posted - 2007-10-03 : 11:20:17

I'm sorry you are totally correct with that. I meant to add the date 11/6/06 not 6/11/06. With this date it dosen't work.

Initial_Procedure IP
ID Person_ID Completed_DTTM
-- --------- --------------
1  4         03/14/2006
1  4         10/10/2006
1  4         08/27/2007


Procedure_2 P2
ID Person_ID Completed_DTTM
-- --------- --------------
2  4         11/06/2006


Tbl_Final_Result
IP.ID IP.Person_ID IP.Completed_DTTM P2.ID P2.Person_ID P2.Completed_DTTM
----- ------------ ----------------- ----- ------------ -----------------
1     4            03/14/2006        NULL  NULL         NULL
1     4            10/10/2006        2     4            11/06/2006
1     4            08/27/2007        NULL  NULL         NULL

Your code gives me...
IP.ID IP.Person_ID IP.Completed_DTTM P2.ID P2.Person_ID P2.Completed_DTTM
----- ------------ ----------------- ----- ------------ -----------------
1     4            03/14/2006        2     4            11/06/2006
1     4            10/10/2006        2     4            11/06/2006
1     4            08/27/2007        NULL  NULL         NULL

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-03 : 12:14:27

Why dond you check the results again. Isn't 03/14/2006 < 11/06/2006?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Friznost
Starting Member

20 Posts

Posted - 2007-10-03 : 12:47:41

I know that 03/14/2006 < 11/06/2006 but each Procedure_2 can only link to one initial_screen and it has to be the more recent of the initial_screens(in this case 10/10/2006). This is why I was using inefficient nested loops.

quote:
- If there is more than one Initial_Procedure: (Procedure_2.Completed_DTTM Between row 1: Initial_Procedure.Completed_DTTM and row 2: Initial_Procedure.Completed_DTTM) --(assuming Initial_Procedure is in order)
AND Initial_Procedure.Person_ID = Procedure_2.Person_ID

Friznost
Starting Member

20 Posts

Posted - 2007-10-04 : 13:09:15

If anyone is interested below is the code for the answer.

select ip.*, p2.*

from @Initial_Procedure ip

left outer join @INitial_procedure ip2

on ip.person_id = ip2.person_id

and ip2.completed_dttm =

(select min(ip3.completed_dttm) from @Initial_Procedure ip3

where ip.person_id = ip3.person_id

and ip3.completed_dttm > ip.completed_dttm

)

left outer join @Procedure_2 p2

on ip.person_id = p2.person_id

and p2.completed_dttm =

(select max(completed_dttm) from @Procedure_2 p22 where p2.person_id = p22.person_id

and p22.completed_dttm between ip.completed_dttm and coalesce (ip2.completed_dttm, p22.completed_dttm)

)

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-04 : 13:25:54

Sorry I forgot to get back on this.. got caught with other stuff..glad you were able to resolve it.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources