Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

view help 2

Status
Not open for further replies.
wbodger

wbodger

Programmer
Apr 23, 2007
769
US
OK, I have been working on this view for a while. The view pulls all of the correct information, but it is painfully slow (48 seconds to build a view just under 100k rows). I know at least part of the problem is that the underlying table structure was not well thought out ahead of time and unfortunately we cannot change it... Here is the full query
Code: 
SELECT     PrimaryTbl.SpecimenID, COALESCE (SecondaryTbl.PptID, PrimaryTbl.PptID) AS pptid, 
	CASE 
		WHEN LEFT(COALESCE (SecondaryTbl.pptid, PrimaryTbl.pptid), 1) = '0' THEN substring((COALESCE (SecondaryTbl.pptid, PrimaryTbl.pptid)), 2, 2) 
		ELSE LEFT(COALESCE (SecondaryTbl.pptid, PrimaryTbl.pptid), 3) 
    END AS CollectionSiteID, 
    s.Abbreviation AS CollectionSiteName, 
    CASE 
		WHEN PrimaryTbl.parentspecimenid = '' THEN 'Parent' 
		ELSE 'Child' 
	END AS parentorchild, 
    PrimaryTbl.SpecimenSourceID,
  (SELECT     ValueName
    FROM          dbo.tblMetaElementValues
    WHERE      (ElementID = 8) AND (Active = 1) AND (Value = PrimaryTbl.SpecimenSourceID)) AS SpecimenSourceName, 
    PrimaryTbl.SpecimenStoredID,
  (SELECT     ValueName
    FROM          dbo.tblMetaElementValues AS tblMetaElementValues_18
    WHERE      (ElementID = 9) AND (Active = 1) AND (Value = PrimaryTbl.SpecimenStoredID)) AS SpecimenStoredName, 
    PrimaryTbl.Quantity, 
    CASE 
		WHEN PrimaryTbl.UnitID = 0 THEN 'unknown' 
		WHEN PrimaryTbl.UnitID = 1 THEN 'µl' 
		WHEN PrimaryTbl.UnitId = 2 THEN 'ml' 
	END AS Unit, 
    PrimaryTbl.CollectionDate, 
    PrimaryTbl.Comments, 
    PrimaryTbl.ParentSpecimenID, 
    CASE 
		WHEN PrimaryTbl.PptVisitType = 0 THEN SecondaryTbl.PptVisitType 
		ELSE PrimaryTbl.PptVisitType 
	END AS PptVisitType,
  (SELECT     ValueName
    FROM          dbo.tblMetaElementValues AS tblMetaElementValues_17
    WHERE      (ElementID = 82) AND (Active = 1) AND 
    (Value = CASE 
				WHEN PrimaryTbl.PptVisitType = 0 THEN SecondaryTbl.PptVisitType 
				ELSE PrimaryTbl.PptVisitType 
			 END)) AS PptVisitTypeName, 
   PrimaryTbl.PptVisitDate, 
   PrimaryTbl.CurrentQuantity, 
   PrimaryTbl.CurrentQuantityUnitID,
  (SELECT     ValueName
    FROM          dbo.tblMetaElementValues AS tblMetaElementValues_16
    WHERE      (ElementID = 47) AND (Active = 1) AND (Value = PrimaryTbl.CurrentQuantityUnitID)) AS CurrentQuantityUnitName, 
    PrimaryTbl.Concentration, 
    PrimaryTbl.ConcentrationUnitID,
  (SELECT     ValueName
    FROM          dbo.tblMetaElementValues AS tblMetaElementValues_15
    WHERE      (ElementID = 42) AND (Active = 1) AND (Value = PrimaryTbl.ConcentrationUnitID)) AS ConcentrationUnitName, 
   PrimaryTbl.NumOfThawed, 
   L.SiteID AS StorageLabID,
   L.LabNameShort AS StorageLabName,
   PrimaryTbl.RefrigeratorID, 
   PrimaryTbl.BoxID, 
   PrimaryTbl.Row, 
   PrimaryTbl.Col, 
   --if specimenstoredid in 2,35,9, then fasting needs to be inherited from the parent
   Case
	WHEN PrimaryTbl.specimenstoredid in (2,9,35) THEN SecondaryTbl.Fasting
	ELSE PrimaryTbl.Fasting
	END AS Fasting,
  (SELECT     ValueName
    FROM          dbo.tblMetaElementValues AS tblMetaElementValues_14
    WHERE      (ElementID = 434) AND (Active = 1) AND (Value = Case
		WHEN PrimaryTbl.specimenstoredid in (2,9,35) THEN SecondaryTbl.Fasting
		ELSE PrimaryTbl.Fasting
		END)) AS FastingName, 
    PrimaryTbl.Status,
  (SELECT     ValueName
    FROM          dbo.tblMetaElementValues AS tblMetaElementValues_13
    WHERE      (ElementID = 29) AND (Active = 1) AND (Value = PrimaryTbl.Status)) AS StatusName, 
    PrimaryTbl.ReasonOfChange, 
    PrimaryTbl.ReasonOfDefective,
  (SELECT     ValueName
    FROM          dbo.tblMetaElementValues AS tblMetaElementValues_12
    WHERE      (ElementID = 61) AND (Active = 1) AND (Value = PrimaryTbl.ReasonOfDefective)) AS ReasonOfDefectiveName, 
    PrimaryTbl.OtherReasonOfDefective, 
    PrimaryTbl.TubeSize, 
    PrimaryTbl.MainSelected,
  (SELECT     Answer
    FROM          dbo.tblSpecimenPQ195
    WHERE      (ProcessQuestionID = 422) AND (SpecimenID = PrimaryTbl.SpecimenID)) AS SpecimensFrozenAtMinus70OrColder,
  (SELECT     ValueName
    FROM          dbo.tblMetaElementValues AS tblMetaElementValues_11
    WHERE      (ElementID = 422) AND (Value =
       (SELECT     Answer
         FROM          dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_26
         WHERE      (ProcessQuestionID = 422) AND (SpecimenID = PrimaryTbl.SpecimenID)))) AS SpecimensFrozenAtMinus70OrColderName,
  (SELECT     Answer
    FROM          dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_25
    WHERE      (ProcessQuestionID = 431) AND (SpecimenID = PrimaryTbl.SpecimenID)) AS PQComments,
  (SELECT     Answer
    FROM          dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_24
    WHERE      (ProcessQuestionID = 435) AND (SpecimenID = PrimaryTbl.SpecimenID)) AS SpecimenPlacedImmedOnIceOrAt4Celsius,
  (SELECT     ValueName
    FROM          dbo.tblMetaElementValues AS tblMetaElementValues_10
    WHERE      (ElementID = 435) AND (Value =
       (SELECT     Answer
         FROM          dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_23
         WHERE      (ProcessQuestionID = 435) AND (SpecimenID = PrimaryTbl.SpecimenID)))) AS SpecimenPlacedImmedOnIceOrAt4CelsiusName,
  (SELECT     Answer
    FROM          dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_22
    WHERE      (ProcessQuestionID = 438) AND (SpecimenID = PrimaryTbl.SpecimenID)) AS FreezerNumberSpecimenStoredAt,
  (SELECT     Answer
    FROM          dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_21
    WHERE      (ProcessQuestionID = 439) AND (SpecimenID = PrimaryTbl.SpecimenID)) AS SpecimenHemolyedLipemicIcteric,
  (SELECT     ValueName
    FROM          dbo.tblMetaElementValues AS tblMetaElementValues_9
    WHERE      (ElementID = 439) AND (Value =
       (SELECT     Answer
         FROM          dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_20
         WHERE      (ProcessQuestionID = 439) AND (SpecimenID = PrimaryTbl.SpecimenID)))) AS SpecimenHemolyedLipemicIctericName,
  (SELECT     Answer
    FROM          dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_19
    WHERE      (ProcessQuestionID = 442) AND (SpecimenID = PrimaryTbl.SpecimenID)) AS TimeFromCollectionToFreezingHours,
  (SELECT     Answer
    FROM          dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_18
    WHERE      (ProcessQuestionID = 443) AND (SpecimenID = PrimaryTbl.SpecimenID)) AS EquipmentFailureDeviationFromProtocol,
  (SELECT     ValueName
    FROM          dbo.tblMetaElementValues AS tblMetaElementValues_8
    WHERE      (ElementID = 443) AND (Value =
       (SELECT     Answer
         FROM          dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_17
         WHERE      (ProcessQuestionID = 443) AND (SpecimenID = PrimaryTbl.SpecimenID)))) AS EquipmentFailureDeviationFromProtocolName,
  (SELECT     Answer
    FROM          dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_16
    WHERE      (ProcessQuestionID = 450) AND (SpecimenID = PrimaryTbl.SpecimenID)) AS VolumeRawUrineProcessed,
  (SELECT     Answer
    FROM          dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_15
    WHERE      (ProcessQuestionID = 451) AND (SpecimenID = PrimaryTbl.SpecimenID)) AS SpecimenInverted,
  (SELECT     ValueName
    FROM          dbo.tblMetaElementValues AS tblMetaElementValues_7
    WHERE      (ElementID = 451) AND (Value =
       (SELECT     Answer
         FROM          dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_14
         WHERE      (ProcessQuestionID = 451) AND (SpecimenID = PrimaryTbl.SpecimenID)))) AS SpecimenInvertedName,
  (SELECT     Answer
    FROM          dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_13
    WHERE      (ProcessQuestionID = 454) AND (SpecimenID = PrimaryTbl.SpecimenID)) AS SpecimenProcessedWithin,
  (SELECT     ValueName
    FROM          dbo.tblMetaElementValues AS tblMetaElementValues_6
    WHERE      (ElementID = 454) AND (Value =
       (SELECT     Answer
         FROM          dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_12
         WHERE      (ProcessQuestionID = 454) AND (SpecimenID = PrimaryTbl.SpecimenID)))) AS SpecimenProcessedWithinName,
  (SELECT     Answer
    FROM          dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_11
    WHERE      (ProcessQuestionID = 455) AND (SpecimenID = PrimaryTbl.SpecimenID)) AS TissuePosition,
  (SELECT     ValueName
    FROM          dbo.tblMetaElementValues AS tblMetaElementValues_5
    WHERE      (ElementID = 455) AND (Value =
       (SELECT     Answer
         FROM          dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_10
         WHERE      (ProcessQuestionID = 455) AND (SpecimenID = PrimaryTbl.SpecimenID)))) AS TissuePositionName,
  (SELECT     Answer
    FROM          dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_9
    WHERE      (ProcessQuestionID = 456) AND (SpecimenID = PrimaryTbl.SpecimenID)) AS StorageTemperature,
  (SELECT     ValueName
    FROM          dbo.tblMetaElementValues AS tblMetaElementValues_4
    WHERE      (ElementID = 456) AND (Value =
       (SELECT     Answer
         FROM          dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_8
         WHERE      (ProcessQuestionID = 456) AND (SpecimenID = PrimaryTbl.SpecimenID)))) AS StorageTemperatureName,
  (SELECT     Answer
    FROM          dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_7
    WHERE      (ProcessQuestionID = 457) AND (SpecimenID = PrimaryTbl.SpecimenID)) AS ProcessingMethod,
  (SELECT     ValueName
    FROM          dbo.tblMetaElementValues AS tblMetaElementValues_3
    WHERE      (ElementID = 457) AND (Value =
       (SELECT     Answer
         FROM          dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_6
         WHERE      (ProcessQuestionID = 457) AND (SpecimenID = PrimaryTbl.SpecimenID)))) AS ProcessingMethodName,
  (SELECT     Answer
    FROM          dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_5
    WHERE      (ProcessQuestionID = 458) AND (SpecimenID = PrimaryTbl.SpecimenID)) AS PathologyDeptBlockNumber,
  (SELECT     Answer
    FROM          dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_4
    WHERE      (ProcessQuestionID = 459) AND (SpecimenID = PrimaryTbl.SpecimenID)) AS PrimaryTissueType,
  (SELECT     ValueName
    FROM          dbo.tblMetaElementValues AS tblMetaElementValues_2
    WHERE      (ElementID = 459) AND (Value =
       (SELECT     Answer
         FROM          dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_3
         WHERE      (ProcessQuestionID = 459) AND (SpecimenID = PrimaryTbl.SpecimenID)))) AS PrimaryTissueTypeName,
  (SELECT     Answer
    FROM          dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_2
    WHERE      (ProcessQuestionID = 460) AND (SpecimenID = PrimaryTbl.SpecimenID)) AS SpecimenProcessedProtocol,
  (SELECT     ValueName
    FROM          dbo.tblMetaElementValues AS tblMetaElementValues_1
    WHERE      (ElementID = 460) AND (Value =
       (SELECT     Answer
         FROM          dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_1
         WHERE      (ProcessQuestionID = 460) AND (SpecimenID = PrimaryTbl.SpecimenID)))) AS SpecimenProcessedProtocolName
FROM	dbo.tblSpecimenBank195 AS PrimaryTbl 
		LEFT OUTER JOIN dbo.tblSpecimenBank195 AS SecondaryTbl ON PrimaryTbl.ParentSpecimenID = SecondaryTbl.SpecimenID 
		INNER JOIN NewCompass.dbo.tblSite AS s ON s.SiteID = 
			(CASE 
				WHEN LEFT(COALESCE (SecondaryTbl.pptid, PrimaryTbl.pptid), 1) 
                      = '0' THEN substring((COALESCE (SecondaryTbl.pptid, PrimaryTbl.pptid)), 2, 2) 
                ELSE LEFT(COALESCE (SecondaryTbl.pptid, PrimaryTbl.pptid), 3) 
             END) LEFT JOIN tblLabs L on PrimaryTbl.LabID=L.LabID
The parts that I am particularly interested in are the two areas where I have to make subquery calls over and over to separate tables. For instance
Code: 
  (SELECT     Answer
    FROM          dbo.tblSpecimenPQ195
    WHERE      (ProcessQuestionID = 422) AND (SpecimenID = PrimaryTbl.SpecimenID)) AS SpecimensFrozenAtMinus70OrColder,
  (SELECT     ValueName
    FROM          dbo.tblMetaElementValues AS tblMetaElementValues_11
    WHERE      (ElementID = 422) AND (Value =
       (SELECT     Answer
         FROM          dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_26
         WHERE      (ProcessQuestionID = 422) AND (SpecimenID = PrimaryTbl.SpecimenID)))) AS SpecimensFrozenAtMinus70OrColderName,
Uses the same code twice, once as the main subquery and then again as a nested subquery, and I have to do this like 16 times. I have tried this with a scalar valued function and it slowed it down massively. I have considered breaking this down into multiple steps, creating a few views and then creating an aggregate view, but I haven't been able to see where that would cut any time. Looking at the Execution Plan, there are no big time draws, but there are a bunch at about 3% on tblSpecimenPQ195. That table has a clustered index on ProcessQuestionID and SpecimenID. Any thoughts on what I might try to speed this up a bit?

Thanks,
wb
 
Sort by date Sort by votes
just an initial pointer..
the sub selects with "select answer from tblSpecimenPQ195" can all be replaced with either a inner or left outer join to the main table and then using case statements on the selected fields.
Something like

Code: 
select
       case ProcessQuestionID
       when 459 then answer
       else null
       end as PrimaryTissueType
     , case ProcessQuestionID
       when 439 then answer
       else null
       end as SpecimenHemolyedLipemicIcteric

       
FROM    dbo.tblSpecimenBank195 AS PrimaryTbl
left outer join dbo.tblSpecimenPQ195 as pq195_1
on ProcessQuestionID in (459 ,439 ,422 ,431 ,435 ,438 ,442 ,443 
                        ,450 ,451 ,454 ,455 ,456 ,457 ,458 ,460)
and SpecimenID = PrimaryTbl.SpecimenID

The other subselects can be changed in similar way although the one with a further subselect will give you a bit more work

Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
the one with 2 subselects can probably be done as

Code: 
select case 
       when meValues.ElementID = 422 
        and PQ195_26.ProcessQuestionID = 422 
        and meValues.Value = PQ195_26.Answer
        and PQ195_26.SpecimenID = PrimaryTbl.SpecimenID
            then meValues.ValueName
       else null
       end as SpecimensFrozenAtMinus70OrColderName

left outer join dbo.tblSpecimenPQ195 AS PQ195_26 
on PQ195_26.SpecimenID = PrimaryTbl.SpecimenID
and PQ195_26.ProcessQuestionID in ( 422 ,435 ,439 ,443 ,451 ,454
                                   ,455 ,456 ,457 ,459 ,460)
left outer join dbo.tblMetaElementValues AS meValues
on (meValues.ElementID = 422 and PQ195_26.ProcessQuestionID = 422 AND meValues.Value = PQ195_26.Answer)
or (meValues.ElementID = 435 and PQ195_26.ProcessQuestionID = 435 AND meValues.Value = PQ195_26.Answer)
or (meValues.ElementID = 439 and PQ195_26.ProcessQuestionID = 439 AND meValues.Value = PQ195_26.Answer)
or (meValues.ElementID = 443 and PQ195_26.ProcessQuestionID = 443 AND meValues.Value = PQ195_26.Answer)
or (meValues.ElementID = 451 and PQ195_26.ProcessQuestionID = 451 AND meValues.Value = PQ195_26.Answer)
or (meValues.ElementID = 454 and PQ195_26.ProcessQuestionID = 454 AND meValues.Value = PQ195_26.Answer)
or (meValues.ElementID = 455 and PQ195_26.ProcessQuestionID = 455 AND meValues.Value = PQ195_26.Answer)
or (meValues.ElementID = 456 and PQ195_26.ProcessQuestionID = 456 AND meValues.Value = PQ195_26.Answer)
or (meValues.ElementID = 457 and PQ195_26.ProcessQuestionID = 457 AND meValues.Value = PQ195_26.Answer)
or (meValues.ElementID = 459 and PQ195_26.ProcessQuestionID = 459 AND meValues.Value = PQ195_26.Answer)
or (meValues.ElementID = 460 and PQ195_26.ProcessQuestionID = 460 AND meValues.Value = PQ195_26.Answer)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
upon looking further at this the previous answers were not clear and if done just as they were they would not work.

changing your sql so that the subselect fields are done separately will most likely yield better results and the code will be more readable

Something like this should work.
Code: 
SELECT PrimaryTbl.SpecimenID
--.... all your other fields

---- your subselect fields - only 3 put here as example
      ,PrimaryTissueType
      ,SpecimenHemolyedLipemicIcteric
      ,SpecimensFrozenAtMinus70OrColderName
from dbo.tblSpecimenBank195 PrimaryTbl
left OUTER JOIN 
    (select PrimaryTbl.SpecimenID
          ,max(case pq195_1.ProcessQuestionID
           when 459 then pq195_1.answer
           else null
           end )as PrimaryTissueType
          ,max(case pq195_1.ProcessQuestionID
           when 439 then pq195_1.answer
           else null
           end) as SpecimenHemolyedLipemicIcteric
          ,max(case
           when meValues.ElementID = 422
            and PQ195_26.ProcessQuestionID = 422
            and meValues.Value = PQ195_26.Answer
            and PQ195_26.SpecimenID = PrimaryTbl.SpecimenID
                then meValues.ValueName
           else null
           end) as SpecimensFrozenAtMinus70OrColderName
    FROM    dbo.tblSpecimenBank195 AS PrimaryTbl
    left outer join dbo.tblSpecimenPQ195 as pq195_1
    on pq195_1.ProcessQuestionID in (459 ,439 ,422 ,431 ,435 ,438 ,442 ,443 
                            ,450 ,451 ,454 ,455 ,456 ,457 ,458 ,460)
    and pq195_1.SpecimenID = PrimaryTbl.SpecimenID
    left outer join dbo.tblSpecimenPQ195 AS PQ195_26 
    on PQ195_26.SpecimenID = PrimaryTbl.SpecimenID
    and PQ195_26.ProcessQuestionID in ( 422 ,435 ,439 ,443 ,451 ,454
                                       ,455 ,456 ,457 ,459 ,460)
    left outer join dbo.tblMetaElementValues AS meValues
    on (meValues.ElementID = 422 and PQ195_26.ProcessQuestionID = 422 AND meValues.Value = PQ195_26.Answer)
    or (meValues.ElementID = 435 and PQ195_26.ProcessQuestionID = 435 AND meValues.Value = PQ195_26.Answer)
    or (meValues.ElementID = 439 and PQ195_26.ProcessQuestionID = 439 AND meValues.Value = PQ195_26.Answer)
    or (meValues.ElementID = 443 and PQ195_26.ProcessQuestionID = 443 AND meValues.Value = PQ195_26.Answer)
    or (meValues.ElementID = 451 and PQ195_26.ProcessQuestionID = 451 AND meValues.Value = PQ195_26.Answer)
    or (meValues.ElementID = 454 and PQ195_26.ProcessQuestionID = 454 AND meValues.Value = PQ195_26.Answer)
    or (meValues.ElementID = 455 and PQ195_26.ProcessQuestionID = 455 AND meValues.Value = PQ195_26.Answer)
    or (meValues.ElementID = 456 and PQ195_26.ProcessQuestionID = 456 AND meValues.Value = PQ195_26.Answer)
    or (meValues.ElementID = 457 and PQ195_26.ProcessQuestionID = 457 AND meValues.Value = PQ195_26.Answer)
    or (meValues.ElementID = 459 and PQ195_26.ProcessQuestionID = 459 AND meValues.Value = PQ195_26.Answer)
    or (meValues.ElementID = 460 and PQ195_26.ProcessQuestionID = 460 AND meValues.Value = PQ195_26.Answer)
    where pq195_1.ProcessQuestionID in (459 ,439 ,422 ,431 ,435 ,438 ,442 ,443 
                                       ,450 ,451 ,454 ,455 ,456 ,457 ,458 ,460)
    or PQ195_26.ProcessQuestionID in ( 422 ,435 ,439 ,443 ,451 ,454
                                      ,455 ,456 ,457 ,459 ,460)

    GROUP BY PrimaryTbl.SpecimenID
    ) t
on t.SpecimenID = PrimaryTbl.SpecimenID

Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Hmm... Why the max(case... ? I will give this a shot and let you know how it goes!

wb
 
Upvote 0 Downvote
max is because a group by is required so only one record per PrimaryTbl.SpecimenID is returned - like this you will get one value for each corresponding item (when existing) as max(valueX, null) will return you valueX

small example

SELECT id
,max(PrimaryTissueType) as PrimaryTissueType
,max(SpecimenHemolyedLipemicIcteric) as SpecimenHemolyedLipemicIcteric
from
(SELECT 'En' as id, 'A' as PrimaryTissueType, NULL as SpecimenHemolyedLipemicIcteric
UNION ALL
SELECT 'En' as id, NULL as PrimaryTissueType, 'B' as SpecimenHemolyedLipemicIcteric
) t
group BY id


Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Got it. Another question
Code: 
from dbo.tblSpecimenBank195 PrimaryTbl
left OUTER JOIN
    (select PrimaryTbl.SpecimenID
          ,max(case pq195_1.ProcessQuestionID
           when 459 then pq195_1.answer
           else null
           end )as PrimaryTissueType
          ,max(case pq195_1.ProcessQuestionID
           when 439 then pq195_1.answer
           else null
           end) as SpecimenHemolyedLipemicIcteric
          ,max(case
           when meValues.ElementID = 422
            and PQ195_26.ProcessQuestionID = 422
            and meValues.Value = PQ195_26.Answer
            and PQ195_26.SpecimenID = PrimaryTbl.SpecimenID
                then meValues.ValueName
           else null
           end) as SpecimensFrozenAtMinus70OrColderName
    FROM    dbo.tblSpecimenBank195 AS PrimaryTbl
    left outer join dbo.tblSpecimenPQ195 as pq195_1
When you use the FROM again near the end of this section, I assume that is a typo, but I am not sure what you would actually be joining to or on here.

wb
 
Upvote 0 Downvote
no..
on the sample I gave everything from
(select PrimaryTbl.SpecimenID
until
) t

is a sub query - as such it follows the rules of having a
select ...
from ...
inner join ...

The contents of this sub query is to allow the grouping and returning of a single row per PrimaryTbl.SpecimenID and with the fields you require on your outer main query

So the example I gave you would return a set of rows (table) with the following fields
SpecimenID
PrimaryTissueType
SpecimenHemolyedLipemicIcteric
SpecimensFrozenAtMinus70OrColderName

It is this table that you then join on your main outer sql


It should be easy enough for you to cut down your own sql to have only the fields mentioned above, and then compare the output to the one I gave.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Ahh... Now I get it. I think. Thanks!

wb
 
Upvote 0 Downvote
Hmm... This suggested approach is much slower than my original solution. I will keep working and let you guys know if I come up with a better solution.
 
Upvote 0 Downvote
can you post here both the old and new explain plan xml files. There might be something that can be changed.

This is always hard to know how it is going to behave without hands on and full knowledge of data and SQL Server instance setup

Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
I agree. I will get those plans and post them, thank you for your help, I appreciate it!
 
Upvote 0 Downvote
Can you run this and post the output:

sp_helpindex 'tblMetaElementValues'

I suspect this table has no indexes on it, and also suspect that adding an index will make a big difference in performance.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Oh yeah.... one more thing. What is that data type for:

tblMetaElementValues.Active

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
IX_ElementID_Value nonclustered located on PRIMARY ElementID, Value, ValueName

Active is a char(1) Somebody prior (or current perhaps) did not like bit data types.
 
Upvote 0 Downvote
tblSpecimenPQ195 has this
IX_PQ_Answer195 nonclustered located on PRIMARY SpecimenID, ProcessQuestionID, Answer
PK_tblSpecimenPQ195 clustered, unique, primary key located on PRIMARY SpecimenID, ProcessQuestionID
 
Upvote 0 Downvote
can you also post how many rows each of the tables have and the modified sql - the explain plain did cut most of it

Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
tblSpecimenBank195 - 100504
tblSpecimenPQ195 - 536513
tblMetaElementValues - 1868
tblLabs - 94
tblSite - 492


Code: 
SELECT     PrimaryTbl.SpecimenID, COALESCE (SecondaryTbl.PptID, PrimaryTbl.PptID) AS pptid, 
	CASE 
		WHEN LEFT(COALESCE (SecondaryTbl.pptid, PrimaryTbl.pptid), 1) = '0' THEN substring((COALESCE (SecondaryTbl.pptid, PrimaryTbl.pptid)), 2, 2) 
		ELSE LEFT(COALESCE (SecondaryTbl.pptid, PrimaryTbl.pptid), 3) 
    END AS CollectionSiteID, 
    s.Abbreviation AS CollectionSiteName, 
    CASE 
		WHEN PrimaryTbl.parentspecimenid = '' THEN 'Parent' 
		ELSE 'Child' 
	END AS parentorchild, 
    PrimaryTbl.SpecimenSourceID,
  (SELECT     ValueName
    FROM          dbo.tblMetaElementValues
    WHERE      (ElementID = 8) AND (Active = 1) AND (Value = PrimaryTbl.SpecimenSourceID)) AS SpecimenSourceName, 
    PrimaryTbl.SpecimenStoredID,
  (SELECT     ValueName
    FROM          dbo.tblMetaElementValues AS tblMetaElementValues_18
    WHERE      (ElementID = 9) AND (Active = 1) AND (Value = PrimaryTbl.SpecimenStoredID)) AS SpecimenStoredName, 
    PrimaryTbl.Quantity, 
    CASE 
		WHEN PrimaryTbl.UnitID = 0 THEN 'unknown' 
		WHEN PrimaryTbl.UnitID = 1 THEN 'µl' 
		WHEN PrimaryTbl.UnitId = 2 THEN 'ml' 
	END AS Unit, 
    PrimaryTbl.CollectionDate, 
    PrimaryTbl.Comments, 
    PrimaryTbl.ParentSpecimenID, 
    CASE 
		WHEN PrimaryTbl.PptVisitType = 0 THEN SecondaryTbl.PptVisitType 
		ELSE PrimaryTbl.PptVisitType 
	END AS PptVisitType,
  (SELECT     ValueName
    FROM          dbo.tblMetaElementValues AS tblMetaElementValues_17
    WHERE      (ElementID = 82) AND (Active = 1) AND 
    (Value = CASE 
				WHEN PrimaryTbl.PptVisitType = 0 THEN SecondaryTbl.PptVisitType 
				ELSE PrimaryTbl.PptVisitType 
			 END)) AS PptVisitTypeName, 
   PrimaryTbl.PptVisitDate, 
   PrimaryTbl.CurrentQuantity, 
   PrimaryTbl.CurrentQuantityUnitID,
  (SELECT     ValueName
    FROM          dbo.tblMetaElementValues AS tblMetaElementValues_16
    WHERE      (ElementID = 47) AND (Active = 1) AND (Value = PrimaryTbl.CurrentQuantityUnitID)) AS CurrentQuantityUnitName, 
    PrimaryTbl.Concentration, 
    PrimaryTbl.ConcentrationUnitID,
  (SELECT     ValueName
    FROM          dbo.tblMetaElementValues AS tblMetaElementValues_15
    WHERE      (ElementID = 42) AND (Active = 1) AND (Value = PrimaryTbl.ConcentrationUnitID)) AS ConcentrationUnitName, 
   PrimaryTbl.NumOfThawed, 
   L.SiteID AS StorageLabID,
   L.LabNameShort AS StorageLabName,
   PrimaryTbl.RefrigeratorID, 
   PrimaryTbl.BoxID, 
   PrimaryTbl.Row, 
   PrimaryTbl.Col, 
   --if specimenstoredid in 2,35,9, then fasting needs to be inherited from the parent
   Case
	WHEN PrimaryTbl.specimenstoredid in (2,9,35) THEN SecondaryTbl.Fasting
	ELSE PrimaryTbl.Fasting
	END AS Fasting,
  (SELECT     ValueName
    FROM          dbo.tblMetaElementValues AS tblMetaElementValues_14
    WHERE      (ElementID = 434) AND (Active = 1) AND (Value = Case
		WHEN PrimaryTbl.specimenstoredid in (2,9,35) THEN SecondaryTbl.Fasting
		ELSE PrimaryTbl.Fasting
		END)) AS FastingName, 
    PrimaryTbl.Status,
  (SELECT     ValueName
    FROM          dbo.tblMetaElementValues AS tblMetaElementValues_13
    WHERE      (ElementID = 29) AND (Active = 1) AND (Value = PrimaryTbl.Status)) AS StatusName, 
    PrimaryTbl.ReasonOfChange, 
    PrimaryTbl.ReasonOfDefective,
  (SELECT     ValueName
    FROM          dbo.tblMetaElementValues AS tblMetaElementValues_12
    WHERE      (ElementID = 61) AND (Active = 1) AND (Value = PrimaryTbl.ReasonOfDefective)) AS ReasonOfDefectiveName, 
    PrimaryTbl.OtherReasonOfDefective, 
    PrimaryTbl.TubeSize, 
    PrimaryTbl.MainSelected,
  SpecimensFrozenAtMinus70OrColder,
  SpecimensFrozenAtMinus70OrColderName,
  PQComments,
  SpecimenPlacedImmedOnIceOrAt4Celsius,
  SpecimenPlacedImmedOnIceOrAt4CelsiusName,
  FreezerNumberSpecimenStoredAt,
  SpecimenHemolyedLipemicIcteric,
  SpecimenHemolyedLipemicIctericName,
  TimeFromCollectionToFreezingHours,
  EquipmentFailureDeviationFromProtocol,
  EquipmentFailureDeviationFromProtocolName,
  VolumeRawUrineProcessed,
  SpecimenInverted,
  SpecimenInvertedName,
  SpecimenProcessedWithin,
  SpecimenProcessedWithinName,
  TissuePosition,
  TissuePositionName,
  StorageTemperature,
  StorageTemperatureName,
  ProcessingMethod,
  ProcessingMethodName,
  PathologyDeptBlockNumber,
  PrimaryTissueType,
  PrimaryTissueTypeName,
  SpecimenProcessedProtocol,
  SpecimenProcessedProtocolName
FROM	dbo.tblSpecimenBank195 AS PrimaryTbl 
		LEFT OUTER JOIN dbo.tblSpecimenBank195 AS SecondaryTbl ON PrimaryTbl.ParentSpecimenID = SecondaryTbl.SpecimenID 
		INNER JOIN NewCompass.dbo.tblSite AS s ON s.SiteID = 
			(CASE 
				WHEN LEFT(COALESCE (SecondaryTbl.pptid, PrimaryTbl.pptid), 1) 
                      = '0' THEN substring((COALESCE (SecondaryTbl.pptid, PrimaryTbl.pptid)), 2, 2) 
                ELSE LEFT(COALESCE (SecondaryTbl.pptid, PrimaryTbl.pptid), 3) 
             END) LEFT JOIN tblLabs L on PrimaryTbl.LabID=L.LabID
--This section added (started) 2/29 as a new way to join to PQ and MEV
left OUTER JOIN
    (select PrimaryTbl.SpecimenID,
    max(case pq195_1.ProcessQuestionID when 422 then pq195_1.answer else null end) as SpecimensFrozenAtMinus70OrColder,
    max(case pq195_1.ProcessQuestionID when 431 then pq195_1.answer else null end) as PQComments,
    max(case pq195_1.ProcessQuestionID when 435 then pq195_1.answer else null end) as SpecimenPlacedImmedOnIceOrAt4Celsius,
    max(case pq195_1.ProcessQuestionID when 438 then pq195_1.answer else null end) as FreezerNumberSpecimenStoredAt,
    max(case pq195_1.ProcessQuestionID when 439 then pq195_1.answer else null end) as SpecimenHemolyedLipemicIcteric,
    max(case pq195_1.ProcessQuestionID when 442 then pq195_1.answer else null end) as TimeFromCollectionToFreezingHours,
    max(case pq195_1.ProcessQuestionID when 443 then pq195_1.answer else null end) as EquipmentFailureDeviationFromProtocol,
    max(case pq195_1.ProcessQuestionID when 450 then pq195_1.answer else null end) as VolumeRawUrineProcessed,
    max(case pq195_1.ProcessQuestionID when 451 then pq195_1.answer else null end) as SpecimenInverted,
    max(case pq195_1.ProcessQuestionID when 454 then pq195_1.answer else null end) as SpecimenProcessedWithin,
    max(case pq195_1.ProcessQuestionID when 455 then pq195_1.answer else null end) as TissuePosition,
    max(case pq195_1.ProcessQuestionID when 456 then pq195_1.answer else null end) as StorageTemperature,
    max(case pq195_1.ProcessQuestionID when 457 then pq195_1.answer else null end) as ProcessingMethod,
    max(case pq195_1.ProcessQuestionID when 458 then pq195_1.answer else null end) as PathologyDeptBlockNumber,
    max(case pq195_1.ProcessQuestionID when 459 then pq195_1.answer else null end )as PrimaryTissueType,
    max(case pq195_1.ProcessQuestionID when 460 then pq195_1.answer else null end) as SpecimenProcessedProtocol,
    max(case when meValues.ElementID = 422 and PQ195_26.ProcessQuestionID = 422 and meValues.Value = PQ195_26.Answer and PQ195_26.SpecimenID = PrimaryTbl.SpecimenID
           then meValues.ValueName else null end) as SpecimensFrozenAtMinus70OrColderName,
    max(case when meValues.ElementID = 435 and PQ195_26.ProcessQuestionID = 435 and meValues.Value = PQ195_26.Answer and PQ195_26.SpecimenID = PrimaryTbl.SpecimenID
           then meValues.ValueName else null end) as SpecimenPlacedImmedOnIceOrAt4CelsiusName,
    max(case when meValues.ElementID = 439 and PQ195_26.ProcessQuestionID = 439 and meValues.Value = PQ195_26.Answer and PQ195_26.SpecimenID = PrimaryTbl.SpecimenID
           then meValues.ValueName else null end) as SpecimenHemolyedLipemicIctericName,
    max(case when meValues.ElementID = 443 and PQ195_26.ProcessQuestionID = 443 and meValues.Value = PQ195_26.Answer and PQ195_26.SpecimenID = PrimaryTbl.SpecimenID
           then meValues.ValueName else null end) as EquipmentFailureDeviationFromProtocolName,
    max(case when meValues.ElementID = 451 and PQ195_26.ProcessQuestionID = 451 and meValues.Value = PQ195_26.Answer and PQ195_26.SpecimenID = PrimaryTbl.SpecimenID
           then meValues.ValueName else null end) as SpecimenInvertedName,
    max(case when meValues.ElementID = 454 and PQ195_26.ProcessQuestionID = 454 and meValues.Value = PQ195_26.Answer and PQ195_26.SpecimenID = PrimaryTbl.SpecimenID
           then meValues.ValueName else null end) as SpecimenProcessedWithinName,
    max(case when meValues.ElementID = 455 and PQ195_26.ProcessQuestionID = 455 and meValues.Value = PQ195_26.Answer and PQ195_26.SpecimenID = PrimaryTbl.SpecimenID
           then meValues.ValueName else null end) as TissuePositionName,
    max(case when meValues.ElementID = 456 and PQ195_26.ProcessQuestionID = 456 and meValues.Value = PQ195_26.Answer and PQ195_26.SpecimenID = PrimaryTbl.SpecimenID
           then meValues.ValueName else null end) as StorageTemperatureName,
    max(case when meValues.ElementID = 457 and PQ195_26.ProcessQuestionID = 457 and meValues.Value = PQ195_26.Answer and PQ195_26.SpecimenID = PrimaryTbl.SpecimenID
           then meValues.ValueName else null end) as ProcessingMethodName,
    max(case when meValues.ElementID = 459 and PQ195_26.ProcessQuestionID = 459 and meValues.Value = PQ195_26.Answer and PQ195_26.SpecimenID = PrimaryTbl.SpecimenID
           then meValues.ValueName else null end) as PrimaryTissueTypeName,
    max(case when meValues.ElementID = 460 and PQ195_26.ProcessQuestionID = 460 and meValues.Value = PQ195_26.Answer and PQ195_26.SpecimenID = PrimaryTbl.SpecimenID
           then meValues.ValueName else null end) as SpecimenProcessedProtocolName
    FROM    dbo.tblSpecimenBank195 AS PrimaryTbl
    left outer join dbo.tblSpecimenPQ195 as pq195_1 on pq195_1.ProcessQuestionID in (459,439,422,431,435,438,442,443,450,451,454,455,456,457,458,460) and pq195_1.SpecimenID = PrimaryTbl.SpecimenID
    left outer join dbo.tblSpecimenPQ195 AS PQ195_26 on PQ195_26.SpecimenID = PrimaryTbl.SpecimenID and PQ195_26.ProcessQuestionID in (422,435,439,443,451,454,455,456,457,459,460)
    left outer join dbo.tblMetaElementValues AS meValues
    on (meValues.ElementID = 422 and PQ195_26.ProcessQuestionID = 422 AND meValues.Value = PQ195_26.Answer)
    or (meValues.ElementID = 435 and PQ195_26.ProcessQuestionID = 435 AND meValues.Value = PQ195_26.Answer)
    or (meValues.ElementID = 439 and PQ195_26.ProcessQuestionID = 439 AND meValues.Value = PQ195_26.Answer)
    or (meValues.ElementID = 443 and PQ195_26.ProcessQuestionID = 443 AND meValues.Value = PQ195_26.Answer)
    or (meValues.ElementID = 451 and PQ195_26.ProcessQuestionID = 451 AND meValues.Value = PQ195_26.Answer)
    or (meValues.ElementID = 454 and PQ195_26.ProcessQuestionID = 454 AND meValues.Value = PQ195_26.Answer)
    or (meValues.ElementID = 455 and PQ195_26.ProcessQuestionID = 455 AND meValues.Value = PQ195_26.Answer)
    or (meValues.ElementID = 456 and PQ195_26.ProcessQuestionID = 456 AND meValues.Value = PQ195_26.Answer)
    or (meValues.ElementID = 457 and PQ195_26.ProcessQuestionID = 457 AND meValues.Value = PQ195_26.Answer)
    or (meValues.ElementID = 459 and PQ195_26.ProcessQuestionID = 459 AND meValues.Value = PQ195_26.Answer)
    or (meValues.ElementID = 460 and PQ195_26.ProcessQuestionID = 460 AND meValues.Value = PQ195_26.Answer)
    where pq195_1.ProcessQuestionID in (459,439,422,431,435,438,442,443,450,451,454,455,456,457,458,460)
    or PQ195_26.ProcessQuestionID in (422,435,439,443,451,454,455,456,457,459,460)

    GROUP BY PrimaryTbl.SpecimenID
    ) t
on t.SpecimenID = PrimaryTbl.SpecimenID
 
Upvote 0 Downvote
Active is a char(1)
Click to expand...

In that case, you should change all of your code everywhere. [shocked]

Ok... maybe not everywhere, but certainly in a lot of places. Let me explain.

Code: 
SELECT     ValueName
FROM       dbo.tblMetaElementValues AS tblMetaElementValues_14    WHERE      (ElementID = 434) 
           AND ([!]Active = 1[/!])

Take a look at the simple query shown above. Do you see that hard-coded 1 in there? Do you know what SQL Server does with that (behind the scenes)?

When SQL Server parses this query, it sees that one and assumes that you want the data type to be an Int. Then it looks at the comparison operator where you are comparing it to the active column. The data type of the hard coded 1 does not match the data type of the Active column. When this happens, SQL Server will do an implicit conversion for you. In this case, every row in the table will be converted to an int because of data type precedence.

It is very simple to prevent this from happening.

Code: 
SELECT     ValueName
FROM       dbo.tblMetaElementValues AS tblMetaElementValues_14    WHERE      (ElementID = 434) 
           AND ([!]Active = '1'[/!])

By including single-quotes, the query parses will assume that what is inside the single quotes is a string. Since Active is a string and '1' is a string, the data will no longer need to be converted to integer for the compare.

As for the indexes... Can you change the index you have?

IX_ElementID_Value nonclustered located on PRIMARY ElementID, Value, ValueName

Can you add the ActiveColumn to the index as an included column? If you are running SQL2000, then add it as the 4th column in the index. Either way... by modifying the index, you should get Index seeks instead of table scans for those portions of the query.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

f0rg3tfu1
  • Locked
  • Question
PLSQL Package Help
Replies
0
Views
214
f0rg3tfu1
f0rg3tfu1
elly00
  • Locked
  • Question
create sql view using batch file
Replies
5
Views
239
Mike Lewis
Mike Lewis
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
539
Chris Miller
Chris Miller
bmacbmac
  • Locked
  • Question
Help searching within this Json file
Replies
0
Views
77
bmacbmac
bmacbmac
Niki_S
  • Locked
  • Question
Create view using two databases
Replies
4
Views
149
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top