|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/5/2008 1:41:46 AM
Posts: 9,
Visits: 52
|
|
When I run #1 query it take several hrs or more than 1 day.
Records are new to 2.7 TB.
Query #1
SELECT DISTINCT
listing_ID,new_id
FROM tbllisting inner join rawdb.dbo.mapphysician on
new_id = rawdb.dbo.mapphysician.persistentrecordid
and ypheading1 in ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18')
or ypheading2 in ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18')
--or ypheading3 in ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18')
or ypheading4 in ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18')
When I run 2nd Query it takes less Hrs than Query #1.
Query #2
SELECT DISTINCT dbo.tbllisting.listing_id, dbo.tbllisting.new_id
FROM dbo.tbllisting INNER JOIN
rawdb.dbo.MapPhysician ON dbo.tbllisting.new_id = rawdb.dbo.MapPhysician.PERSISTENTRECORDID AND
rawdb.dbo.MapPhysician.YPHEADING1 IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18')
UNION
SELECT DISTINCT tbllisting_1.listing_id, tbllisting_1.new_id
FROM dbo.tbllisting AS tbllisting_1 INNER JOIN
rawdb.dbo.MapPhysician AS MapPhysician_1 ON tbllisting_1.new_id = MapPhysician_1.PERSISTENTRECORDID AND
MapPhysician_1.YPHEADING2 IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18')
union
SELECT DISTINCT dbo.tbllisting.listing_id, dbo.tbllisting.new_id
FROM dbo.tbllisting INNER JOIN
rawdb.dbo.MapPhysician ON dbo.tbllisting.new_id = rawdb.dbo.MapPhysician.PERSISTENTRECORDID AND
rawdb.dbo.MapPhysician.YPHEADING3 IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18')
UNION
SELECT DISTINCT tbllisting_1.listing_id, tbllisting_1.new_id
FROM dbo.tbllisting AS tbllisting_1 INNER JOIN
rawdb.dbo.MapPhysician AS MapPhysician_1 ON tbllisting_1.new_id = MapPhysician_1.PERSISTENTRECORDID AND
MapPhysician_1.YPHEADING4 IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18')
i am little bit confuse at this time thats why asking with friends.
arshad.
(TD master LOST..!! every thing.(
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/23/2008 11:25:35 PM
Posts: 76,
Visits: 148
|
|
hi,
here's my suggestion...
-> Use 'GROUP BY' instead of DISTINCT
-> Use a volatile table to populate with records qualifying the conditions on ypheading1,ypheading2,ypheading3 and ypheading4.
Now, join the other table with the volatile table on the join column(s).
thx,
-SN
|
|
|
|