[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 383
  • Last Modified:

SQL Query Fails when put into a View or Reporting Services

The attached query will run fine (usually under 2 seconds) when run in a query window, however if I try to run the exact same query in a view or as a dataset in Reporting Services it will fail with timeout error message ("Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.").  The Execution time-out is set to 0.  Needless to say, this is a huge query that took a long time to develop.  I just don't understand why it works as a query, but not in SRS or a view.  Any ideas or help would be greatly appreciated.
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @Site nvarchar(50)
DECLARE @Acuity nvarchar(50)
DECLARE @Disposition nvarchar(50)
DECLARE @EMCode nvarchar(50)
SET @StartDate = '11/04/2008'
SET @EndDate = '11/04/2008'
SET @Site = 'BF'
SET @Acuity = 'NPV - Level 0'
SET @Disposition = 'Discharge'
SET @EMCode = '-'
SELECT TOP(100) PERCENT
       RTRIM(dbo.CDX_BE_VisitInfo.VisitID) AS VisitID, dbo.CDX_Visit.SubSiteID
       AS SiteID, dbo.CDX_Visit.RegTime,
       CASE dbo.CDX_Visit.SubSiteID
              WHEN 'BF' THEN 'Binford'
              WHEN 'CH' THEN 'Chapel Hill'
              WHEN 'GW' THEN 'Greenwood'
              WHEN 'NR' THEN 'Nora'
              WHEN 'SV' THEN 'Schererville'
              WHEN 'WS' THEN 'Washington Square'
              ELSE RTRIM(dbo.CDX_Visit.SubSiteID)
       END AS Site, RTRIM(dbo.CDX_Visit.AcctNo) AS Account, RTRIM(
       dbo.CDX_Patient.LastName) + ', ' + LEFT(dbo.CDX_Patient.FirstName, 1) +
       '.' AS PatientName, FLOOR(dbo.CDX_BE_VisitInfo.Age) AS Age,
       dbo.CDX_Patient.Sex AS Gender, ISNULL(Acuity.Acuity, '-') AS Acuity,
       dbo.CDX_BE_VisitInfo.LOSTotal AS LOS, REPLACE(
       CheifComplaint.ChiefComplaint, '^', CHAR(13) + CHAR(10)) AS
       ChiefComplaint, REPLACE(Diagnosis.Dx, '^', CHAR(13) + CHAR(10)) AS
       FinalDiagnosis,
       CASE ISNULL(Disposition.JCAHOCode, '-')
              WHEN 4    THEN 'Admit'
              WHEN 5    THEN 'LWOT'
              WHEN 6    THEN 'AMA'
              WHEN 7    THEN 'DID'
              WHEN 10   THEN 'Transfer'
              WHEN 15   THEN 'Discharge'
              WHEN 20   THEN 'DOA'
              WHEN 1355 THEN 'LBTC'
              ELSE 'Unknown'
       END AS Disposition, ISNULL(CPT.EMCode, '-') AS EMCode, RTRIM(
       Provider.LastName) + ', ' + LEFT(Provider.FirstName, 1) + '.' AS
       Provider, RTRIM(TriageMA.LastName) + ', ' + LEFT(TriageMA.FirstName, 1)
       + '.' AS TriageMA, RTRIM(DispoMA.LastName) + ', ' + LEFT(
       DispoMA.FirstName, 1) + '.' AS DispoMA, dbo.CDX_BE_VisitInfo.SignRNID,
       dbo.CDX_BE_VisitInfo.FirstRNID,
       CASE
              WHEN LOSTotal <= 30 THEN '1 0 - 30 Minutes'
              WHEN LOSTotal BETWEEN 31 AND 45  THEN '2 31 - 45 Minutes'
              WHEN lostotal BETWEEN 46 AND 60  THEN '3 46 - 60 Minutes'
              WHEN lostotal BETWEEN 61 AND 120 THEN '4 61 - 120 Minutes'
              WHEN lostotal >= 121             THEN '5 120 + Minutes'
       END AS LOSCategory
FROM   dbo.CDX_BE_VisitInfo
       LEFT OUTER JOIN
              (SELECT DISTINCT CDX_Visit_1.VisitID, LEFT(CDX_BE_CPT_1.CPTCode,
                              5) AS EMCode
              FROM            dbo.CDX_BE_CPT AS CDX_BE_CPT_1
                              INNER JOIN dbo.CDX_Visit AS CDX_Visit_1
                              ON              CDX_BE_CPT_1.VisitID =
                                              CDX_Visit_1.VisitID
                              INNER JOIN
                                              (SELECT  CDX_Visit_1.VisitID, MIN
                                                       (LEN(
                                                       CDX_BE_CPT_1.CPTCode))
                                                       AS CPTLength
                                              FROM     dbo.CDX_BE_CPT AS
                                                       CDX_BE_CPT_1
                                                       INNER JOIN dbo.CDX_Visit
                                                                AS CDX_Visit_1
                                                       ON
                                                                CDX_BE_CPT_1.VisitID
                                                                =
                                                                CDX_Visit_1.VisitID
                                              WHERE(CDX_Visit_1.RegTime >=
                                                       @StartDate)
                                                   AND(CDX_Visit_1.RegTime <
                                                       DATEADD(d, 1, @EndDate))
                                                   AND(LEFT(
                                                       CDX_BE_CPT_1.CPTCode, 5)
                                                       BETWEEN '99201' AND
                                                       '99215'
                                                    OR LEFT(
                                                       CDX_BE_CPT_1.CPTCode, 5)
                                                       = '99024')
                                              GROUP BY CDX_Visit_1.VisitID
                                              ) AS CPTLength
                              ON              CDX_BE_CPT_1.VisitID =
                                              CPTLength.VisitID
                                          AND LEN(CDX_BE_CPT_1.CPTCode) =
                                              CPTLength.CPTLength
              WHERE(CDX_Visit_1.RegTime >= @StartDate)
                          AND(CDX_Visit_1.RegTime < DATEADD(d, 1, @EndDate))
                          AND(LEFT(CDX_BE_CPT_1.CPTCode, 5) BETWEEN '99201' AND
                              '99215'
                           OR LEFT(CDX_BE_CPT_1.CPTCode, 5) = '99024')
              ) AS CPT
       ON     dbo.CDX_BE_VisitInfo.VisitID = CPT.VisitID
       LEFT OUTER JOIN
              (SELECT t1_1.VisitID, t1_1.JCAHOCode
              FROM
                     (SELECT j.VisitID, j.JCAHOCode, o.Created
                     FROM   dbo.CDX_BE_JCAHO AS j
                            LEFT OUTER JOIN dbo.CDX_BE_Output AS o
                            ON     j.OutputID = o.OutputID
                     WHERE(j.JCAHOCode IN(4, 5, 6, 7, 10, 15, 20, 1355))
                        AND(o.Created >= @StartDate)
                        AND(o.Created < DATEADD(d, 2, @EndDate))
                     ) AS t1_1
                     INNER JOIN
                            (SELECT  VisitID, MAX(Created) AS Created
                            FROM
                                     (SELECT j.VisitID, j.JCAHOCode, o.Created
                                     FROM   dbo.CDX_BE_JCAHO AS j
                                            LEFT OUTER JOIN dbo.CDX_BE_Output
                                                   AS o
                                            ON     j.OutputID = o.OutputID
                                     WHERE(j.JCAHOCode IN(4, 5, 6, 7, 10, 15,
                                            20, 1355))
                                        AND(o.Created >= @StartDate)
                                        AND(o.Created < DATEADD(d, 2, @EndDate)
                                            )
                                     ) AS t2_1
                            GROUP BY VisitID
                            ) AS t3
                     ON     t1_1.VisitID = t3.VisitID
                        AND t1_1.Created = t3.Created
              ) AS Disposition
       ON     dbo.CDX_BE_VisitInfo.VisitID = Disposition.VisitID
       LEFT OUTER JOIN dbo.CDX_User AS DispoMA
       ON     dbo.CDX_BE_VisitInfo.SignRNID = DispoMA.UserID
       LEFT OUTER JOIN dbo.CDX_User AS Provider
       ON     dbo.CDX_BE_VisitInfo.SignMDID = Provider.UserID
       LEFT OUTER JOIN
              (SELECT  VisitID, SUBSTRING(MAX(
                       CASE seq
                                WHEN 1 THEN '^' + Dx
                                ELSE ''
                       END) + MAX(
                       CASE seq
                                WHEN 2 THEN '^' + Dx
                                ELSE ''
                       END) + MAX(
                       CASE seq
                                WHEN 3 THEN '^' + Dx
                                ELSE ''
                       END) + MAX(
                       CASE seq
                                WHEN 4 THEN '^' + Dx
                                ELSE ''
                       END) + MAX(
                       CASE seq
                                WHEN 5 THEN '^' + Dx
                                ELSE ''
                       END) + MAX(
                       CASE seq
                                WHEN 6 THEN '^' + Dx
                                ELSE ''
                       END), 2, 8000) AS Dx
              FROM
                       (SELECT VisitID, Dx,
                              (SELECT COUNT( *) AS Expr1
                              FROM
                                     (SELECT DISTINCT ICD9.VisitID, RTRIM(
                                                     ICD9.DiagnosisCode) +
                                                     ' - ' + RTRIM(
                                                     ICD9.DiagnosisText) AS Dx
                                     FROM            dbo.CDX_BE_ICD9 AS ICD9
                                                     INNER JOIN dbo.CDX_Visit
                                                                     AS Visit
                                                     ON
                                                                     ICD9.VisitID
                                                                     =
                                                                     Visit.VisitID
                                     WHERE(ICD9.PrimaryDX = 'Y')
                                                 AND(Visit.RegTime >=
                                                     @StartDate)
                                                 AND(Visit.RegTime < DATEADD(d,
                                                     1, @EndDate))
                                     ) AS t2_2
                              WHERE(VisitID = t1_2.VisitID)
                                 AND(Dx <= t1_2.Dx)
                              ) AS Expr1
                       FROM
                              (SELECT DISTINCT ICD9.VisitID, RTRIM(
                                              ICD9.DiagnosisCode) + ' - ' +
                                              RTRIM(ICD9.DiagnosisText) AS Dx
                              FROM            dbo.CDX_BE_ICD9 AS ICD9
                                              INNER JOIN dbo.CDX_Visit AS Visit
                                              ON              ICD9.VisitID =
                                                              Visit.VisitID
                              WHERE(ICD9.PrimaryDX = 'Y')
                                          AND(Visit.RegTime >= @StartDate)
                                          AND(Visit.RegTime < DATEADD(d, 1,
                                              @EndDate))
                              ) AS t1_2
                       ) AS D(VisitID, Dx, seq)
              GROUP BY VisitID
              ) AS Diagnosis
       ON     dbo.CDX_BE_VisitInfo.VisitID = Diagnosis.VisitID
       LEFT OUTER JOIN
              (SELECT a.VisitID,
                     CASE
                            WHEN TextReturned LIKE 'NPV%'     THEN 'NPV - Level 0'
                            WHEN TextReturned LIKE 'Recheck%' THEN
                                   'Recheck - Level 1'
                            WHEN TextReturned LIKE 'Routine%' THEN
                                   'Routine - Level 2'
                            WHEN TextReturned LIKE 'Emergent%' THEN
                                   'Emergent - Level 3'
                            WHEN TextReturned LIKE 'Triage acuity not%' THEN
                                   'Not Available'
                            WHEN TextReturned LIKE 'Unable to determine%' THEN
                                   'Unable to determine'
                            ELSE 'Other'
                     END AS Acuity
              FROM   dbo.CDX_BE_Output AS a
                     INNER JOIN
                            (SELECT  VisitID, MAX(Created) AS Created
                            FROM     dbo.CDX_BE_Output
                            WHERE(FieldDestination = 'Acuity')
                                 AND(Created >= @StartDate)
                                 AND(Created < DATEADD(d, 2, @EndDate))
                                 AND(NOT(TextReturned IS NULL))
                            GROUP BY VisitID
                            ) AS b
                     ON     a.VisitID = b.VisitID
                        AND a.Created = b.Created
              WHERE(a.FieldDestination = 'Acuity')
              ) AS Acuity
       ON     dbo.CDX_BE_VisitInfo.VisitID = Acuity.VisitID
       LEFT OUTER JOIN
              (SELECT  VisitID, SUBSTRING(MAX(
                       CASE seq
                                WHEN 1 THEN '^* ' + CAST(TextReturned AS
                                         VARCHAR(255))
                                ELSE ''
                       END) + MAX(
                       CASE seq
                                WHEN 2 THEN '^* ' + CAST(TextReturned AS
                                         VARCHAR(255))
                                ELSE ''
                       END) + MAX(
                       CASE seq
                                WHEN 3 THEN '^* ' + CAST(TextReturned AS
                                         VARCHAR(255))
                                ELSE ''
                       END) + MAX(
                       CASE seq
                                WHEN 4 THEN '^* ' + CAST(TextReturned AS
                                         VARCHAR(255))
                                ELSE ''
                       END) + MAX(
                       CASE seq
                                WHEN 5 THEN '^* ' + CAST(TextReturned AS
                                         VARCHAR(255))
                                ELSE ''
                       END) + MAX(
                       CASE seq
                                WHEN 6 THEN '^* ' + CAST(TextReturned AS
                                         VARCHAR(255))
                                ELSE ''
                       END), 2, 8000) AS ChiefComplaint
              FROM
                       (SELECT VisitID, CAST(TextReturned AS VARCHAR(255)) AS
                              TextReturned,
                              (SELECT COUNT( *) AS Expr1
                              FROM   dbo.CDX_BE_Output AS t2
                              WHERE(VisitID = t1.VisitID)
                                 AND(CAST(TextReturned AS VARCHAR(255)) <= CAST
                                     (t1.TextReturned AS VARCHAR(255)))
                                 AND(Created >= @StartDate)
                                 AND(Created < DATEADD(d, 2, @EndDate))
                                 AND(FieldDestination = 'CC')
                                 AND(NOT(CAST(TextReturned AS VARCHAR(255))
                                     IS NULL))
                              ) AS Expr1
                       FROM   dbo.CDX_BE_Output AS t1
                       WHERE(Created >= @StartDate)
                          AND(Created < DATEADD(d, 2, @EndDate))
                          AND(FieldDestination = 'CC')
                          AND(NOT(CAST(TextReturned AS VARCHAR(255)) IS NULL))
                       ) AS D_1(VisitID, TextReturned, seq)
              GROUP BY VisitID
              ) AS CheifComplaint
       ON     dbo.CDX_BE_VisitInfo.VisitID = CheifComplaint.VisitID
       LEFT OUTER JOIN dbo.CDX_User AS TriageMA
       ON     dbo.CDX_BE_VisitInfo.FirstRNID = TriageMA.UserID
       LEFT OUTER JOIN dbo.CDX_Visit
       ON     dbo.CDX_BE_VisitInfo.VisitID = dbo.CDX_Visit.VisitID
       LEFT OUTER JOIN dbo.CDX_Patient
       ON     dbo.CDX_Visit.PatientID = dbo.CDX_Patient.PatientID
WHERE(
       CASE ISNULL(Disposition.JCAHOCode, '-')
              WHEN 4    THEN 'Admit'
              WHEN 5    THEN 'LWOT'
              WHEN 6    THEN 'AMA'
              WHEN 7    THEN 'DID'
              WHEN 10   THEN 'Transfer'
              WHEN 15   THEN 'Discharge'
              WHEN 20   THEN 'DOA'
              WHEN 1355 THEN 'LBTC'
              ELSE 'Unknown'
       END IN(@Disposition))
   AND(dbo.CDX_BE_VisitInfo.SiteID = 'UPG')
   AND(dbo.CDX_Visit.RegTime >= @StartDate)
   AND(dbo.CDX_Visit.RegTime < DATEADD(d, 1, @EndDate))
   AND(dbo.CDX_Visit.SubSiteID IN(@Site))
   AND(ISNULL(Acuity.Acuity, '-') IN(@Acuity))
   AND(ISNULL(CPT.EMCode, '-') IN(@EMCode))

Open in new window

0
UnityPG
Asked:
UnityPG
1 Solution
 
BrandonGalderisiCommented:
You can't use variables in views.  It would have to be a stored procedure.

Here i've made a procedure with the variables you had as input parameters.  I have also made the parameters have default values of the values you were setting so calling the procedure (currently names up_YourData) with not parameters will return exactly what your select returned.
create procedure up_YourData
     @StartDate DATETIME = '11/04/2008'
     ,@EndDate DATETIME = '11/04/2008'
     ,@Site nvarchar(50)= 'BF'
     ,@Acuity nvarchar(50)= 'NPV - Level 0'
     ,@Disposition nvarchar(50) = 'Discharge'
     ,@EMCode nvarchar(50) = '-'
AS
set nocount on
 
SELECT TOP(100) PERCENT
       RTRIM(dbo.CDX_BE_VisitInfo.VisitID) AS VisitID, dbo.CDX_Visit.SubSiteID
       AS SiteID, dbo.CDX_Visit.RegTime,
       CASE dbo.CDX_Visit.SubSiteID
              WHEN 'BF' THEN 'Binford'
              WHEN 'CH' THEN 'Chapel Hill'
              WHEN 'GW' THEN 'Greenwood'
              WHEN 'NR' THEN 'Nora'
              WHEN 'SV' THEN 'Schererville'
              WHEN 'WS' THEN 'Washington Square'
              ELSE RTRIM(dbo.CDX_Visit.SubSiteID)
       END AS Site, RTRIM(dbo.CDX_Visit.AcctNo) AS Account, RTRIM(
       dbo.CDX_Patient.LastName) + ', ' + LEFT(dbo.CDX_Patient.FirstName, 1) +
       '.' AS PatientName, FLOOR(dbo.CDX_BE_VisitInfo.Age) AS Age,
       dbo.CDX_Patient.Sex AS Gender, ISNULL(Acuity.Acuity, '-') AS Acuity,
       dbo.CDX_BE_VisitInfo.LOSTotal AS LOS, REPLACE(
       CheifComplaint.ChiefComplaint, '^', CHAR(13) + CHAR(10)) AS
       ChiefComplaint, REPLACE(Diagnosis.Dx, '^', CHAR(13) + CHAR(10)) AS
       FinalDiagnosis,
       CASE ISNULL(Disposition.JCAHOCode, '-')
              WHEN 4    THEN 'Admit'
              WHEN 5    THEN 'LWOT'
              WHEN 6    THEN 'AMA'
              WHEN 7    THEN 'DID'
              WHEN 10   THEN 'Transfer'
              WHEN 15   THEN 'Discharge'
              WHEN 20   THEN 'DOA'
              WHEN 1355 THEN 'LBTC'
              ELSE 'Unknown'
       END AS Disposition, ISNULL(CPT.EMCode, '-') AS EMCode, RTRIM(
       Provider.LastName) + ', ' + LEFT(Provider.FirstName, 1) + '.' AS
       Provider, RTRIM(TriageMA.LastName) + ', ' + LEFT(TriageMA.FirstName, 1)
       + '.' AS TriageMA, RTRIM(DispoMA.LastName) + ', ' + LEFT(
       DispoMA.FirstName, 1) + '.' AS DispoMA, dbo.CDX_BE_VisitInfo.SignRNID,
       dbo.CDX_BE_VisitInfo.FirstRNID,
       CASE
              WHEN LOSTotal <= 30 THEN '1 0 - 30 Minutes'
              WHEN LOSTotal BETWEEN 31 AND 45  THEN '2 31 - 45 Minutes'
              WHEN lostotal BETWEEN 46 AND 60  THEN '3 46 - 60 Minutes'
              WHEN lostotal BETWEEN 61 AND 120 THEN '4 61 - 120 Minutes'
              WHEN lostotal >= 121             THEN '5 120 + Minutes'
       END AS LOSCategory
FROM   dbo.CDX_BE_VisitInfo
       LEFT OUTER JOIN
              (SELECT DISTINCT CDX_Visit_1.VisitID, LEFT(CDX_BE_CPT_1.CPTCode,
                              5) AS EMCode
              FROM            dbo.CDX_BE_CPT AS CDX_BE_CPT_1
                              INNER JOIN dbo.CDX_Visit AS CDX_Visit_1
                              ON              CDX_BE_CPT_1.VisitID =
                                              CDX_Visit_1.VisitID
                              INNER JOIN
                                              (SELECT  CDX_Visit_1.VisitID, MIN
                                                       (LEN(
                                                       CDX_BE_CPT_1.CPTCode))
                                                       AS CPTLength
                                              FROM     dbo.CDX_BE_CPT AS
                                                       CDX_BE_CPT_1
                                                       INNER JOIN dbo.CDX_Visit
                                                                AS CDX_Visit_1
                                                       ON
                                                                CDX_BE_CPT_1.VisitID
                                                                =
                                                                CDX_Visit_1.VisitID
                                              WHERE(CDX_Visit_1.RegTime >=
                                                       @StartDate)
                                                   AND(CDX_Visit_1.RegTime <
                                                       DATEADD(d, 1, @EndDate))
                                                   AND(LEFT(
                                                       CDX_BE_CPT_1.CPTCode, 5)
                                                       BETWEEN '99201' AND
                                                       '99215'
                                                    OR LEFT(
                                                       CDX_BE_CPT_1.CPTCode, 5)
                                                       = '99024')
                                              GROUP BY CDX_Visit_1.VisitID
                                              ) AS CPTLength
                              ON              CDX_BE_CPT_1.VisitID =
                                              CPTLength.VisitID
                                          AND LEN(CDX_BE_CPT_1.CPTCode) =
                                              CPTLength.CPTLength
              WHERE(CDX_Visit_1.RegTime >= @StartDate)
                          AND(CDX_Visit_1.RegTime < DATEADD(d, 1, @EndDate))
                          AND(LEFT(CDX_BE_CPT_1.CPTCode, 5) BETWEEN '99201' AND
                              '99215'
                           OR LEFT(CDX_BE_CPT_1.CPTCode, 5) = '99024')
              ) AS CPT
       ON     dbo.CDX_BE_VisitInfo.VisitID = CPT.VisitID
       LEFT OUTER JOIN
              (SELECT t1_1.VisitID, t1_1.JCAHOCode
              FROM
                     (SELECT j.VisitID, j.JCAHOCode, o.Created
                     FROM   dbo.CDX_BE_JCAHO AS j
                            LEFT OUTER JOIN dbo.CDX_BE_Output AS o
                            ON     j.OutputID = o.OutputID
                     WHERE(j.JCAHOCode IN(4, 5, 6, 7, 10, 15, 20, 1355))
                        AND(o.Created >= @StartDate)
                        AND(o.Created < DATEADD(d, 2, @EndDate))
                     ) AS t1_1
                     INNER JOIN
                            (SELECT  VisitID, MAX(Created) AS Created
                            FROM
                                     (SELECT j.VisitID, j.JCAHOCode, o.Created
                                     FROM   dbo.CDX_BE_JCAHO AS j
                                            LEFT OUTER JOIN dbo.CDX_BE_Output
                                                   AS o
                                            ON     j.OutputID = o.OutputID
                                     WHERE(j.JCAHOCode IN(4, 5, 6, 7, 10, 15,
                                            20, 1355))
                                        AND(o.Created >= @StartDate)
                                        AND(o.Created < DATEADD(d, 2, @EndDate)
                                            )
                                     ) AS t2_1
                            GROUP BY VisitID
                            ) AS t3
                     ON     t1_1.VisitID = t3.VisitID
                        AND t1_1.Created = t3.Created
              ) AS Disposition
       ON     dbo.CDX_BE_VisitInfo.VisitID = Disposition.VisitID
       LEFT OUTER JOIN dbo.CDX_User AS DispoMA
       ON     dbo.CDX_BE_VisitInfo.SignRNID = DispoMA.UserID
       LEFT OUTER JOIN dbo.CDX_User AS Provider
       ON     dbo.CDX_BE_VisitInfo.SignMDID = Provider.UserID
       LEFT OUTER JOIN
              (SELECT  VisitID, SUBSTRING(MAX(
                       CASE seq
                                WHEN 1 THEN '^' + Dx
                                ELSE ''
                       END) + MAX(
                       CASE seq
                                WHEN 2 THEN '^' + Dx
                                ELSE ''
                       END) + MAX(
                       CASE seq
                                WHEN 3 THEN '^' + Dx
                                ELSE ''
                       END) + MAX(
                       CASE seq
                                WHEN 4 THEN '^' + Dx
                                ELSE ''
                       END) + MAX(
                       CASE seq
                                WHEN 5 THEN '^' + Dx
                                ELSE ''
                       END) + MAX(
                       CASE seq
                                WHEN 6 THEN '^' + Dx
                                ELSE ''
                       END), 2, 8000) AS Dx
              FROM
                       (SELECT VisitID, Dx,
                              (SELECT COUNT( *) AS Expr1
                              FROM
                                     (SELECT DISTINCT ICD9.VisitID, RTRIM(
                                                     ICD9.DiagnosisCode) +
                                                     ' - ' + RTRIM(
                                                     ICD9.DiagnosisText) AS Dx
                                     FROM            dbo.CDX_BE_ICD9 AS ICD9
                                                     INNER JOIN dbo.CDX_Visit
                                                                     AS Visit
                                                     ON
                                                                     ICD9.VisitID
                                                                     =
                                                                     Visit.VisitID
                                     WHERE(ICD9.PrimaryDX = 'Y')
                                                 AND(Visit.RegTime >=
                                                     @StartDate)
                                                 AND(Visit.RegTime < DATEADD(d,
                                                     1, @EndDate))
                                     ) AS t2_2
                              WHERE(VisitID = t1_2.VisitID)
                                 AND(Dx <= t1_2.Dx)
                              ) AS Expr1
                       FROM
                              (SELECT DISTINCT ICD9.VisitID, RTRIM(
                                              ICD9.DiagnosisCode) + ' - ' +
                                              RTRIM(ICD9.DiagnosisText) AS Dx
                              FROM            dbo.CDX_BE_ICD9 AS ICD9
                                              INNER JOIN dbo.CDX_Visit AS Visit
                                              ON              ICD9.VisitID =
                                                              Visit.VisitID
                              WHERE(ICD9.PrimaryDX = 'Y')
                                          AND(Visit.RegTime >= @StartDate)
                                          AND(Visit.RegTime < DATEADD(d, 1,
                                              @EndDate))
                              ) AS t1_2
                       ) AS D(VisitID, Dx, seq)
              GROUP BY VisitID
              ) AS Diagnosis
       ON     dbo.CDX_BE_VisitInfo.VisitID = Diagnosis.VisitID
       LEFT OUTER JOIN
              (SELECT a.VisitID,
                     CASE
                            WHEN TextReturned LIKE 'NPV%'     THEN 'NPV - Level 0'
                            WHEN TextReturned LIKE 'Recheck%' THEN
                                   'Recheck - Level 1'
                            WHEN TextReturned LIKE 'Routine%' THEN
                                   'Routine - Level 2'
                            WHEN TextReturned LIKE 'Emergent%' THEN
                                   'Emergent - Level 3'
                            WHEN TextReturned LIKE 'Triage acuity not%' THEN
                                   'Not Available'
                            WHEN TextReturned LIKE 'Unable to determine%' THEN
                                   'Unable to determine'
                            ELSE 'Other'
                     END AS Acuity
              FROM   dbo.CDX_BE_Output AS a
                     INNER JOIN
                            (SELECT  VisitID, MAX(Created) AS Created
                            FROM     dbo.CDX_BE_Output
                            WHERE(FieldDestination = 'Acuity')
                                 AND(Created >= @StartDate)
                                 AND(Created < DATEADD(d, 2, @EndDate))
                                 AND(NOT(TextReturned IS NULL))
                            GROUP BY VisitID
                            ) AS b
                     ON     a.VisitID = b.VisitID
                        AND a.Created = b.Created
              WHERE(a.FieldDestination = 'Acuity')
              ) AS Acuity
       ON     dbo.CDX_BE_VisitInfo.VisitID = Acuity.VisitID
       LEFT OUTER JOIN
              (SELECT  VisitID, SUBSTRING(MAX(
                       CASE seq
                                WHEN 1 THEN '^* ' + CAST(TextReturned AS
                                         VARCHAR(255))
                                ELSE ''
                       END) + MAX(
                       CASE seq
                                WHEN 2 THEN '^* ' + CAST(TextReturned AS
                                         VARCHAR(255))
                                ELSE ''
                       END) + MAX(
                       CASE seq
                                WHEN 3 THEN '^* ' + CAST(TextReturned AS
                                         VARCHAR(255))
                                ELSE ''
                       END) + MAX(
                       CASE seq
                                WHEN 4 THEN '^* ' + CAST(TextReturned AS
                                         VARCHAR(255))
                                ELSE ''
                       END) + MAX(
                       CASE seq
                                WHEN 5 THEN '^* ' + CAST(TextReturned AS
                                         VARCHAR(255))
                                ELSE ''
                       END) + MAX(
                       CASE seq
                                WHEN 6 THEN '^* ' + CAST(TextReturned AS
                                         VARCHAR(255))
                                ELSE ''
                       END), 2, 8000) AS ChiefComplaint
              FROM
                       (SELECT VisitID, CAST(TextReturned AS VARCHAR(255)) AS
                              TextReturned,
                              (SELECT COUNT( *) AS Expr1
                              FROM   dbo.CDX_BE_Output AS t2
                              WHERE(VisitID = t1.VisitID)
                                 AND(CAST(TextReturned AS VARCHAR(255)) <= CAST
                                     (t1.TextReturned AS VARCHAR(255)))
                                 AND(Created >= @StartDate)
                                 AND(Created < DATEADD(d, 2, @EndDate))
                                 AND(FieldDestination = 'CC')
                                 AND(NOT(CAST(TextReturned AS VARCHAR(255))
                                     IS NULL))
                              ) AS Expr1
                       FROM   dbo.CDX_BE_Output AS t1
                       WHERE(Created >= @StartDate)
                          AND(Created < DATEADD(d, 2, @EndDate))
                          AND(FieldDestination = 'CC')
                          AND(NOT(CAST(TextReturned AS VARCHAR(255)) IS NULL))
                       ) AS D_1(VisitID, TextReturned, seq)
              GROUP BY VisitID
              ) AS CheifComplaint
       ON     dbo.CDX_BE_VisitInfo.VisitID = CheifComplaint.VisitID
       LEFT OUTER JOIN dbo.CDX_User AS TriageMA
       ON     dbo.CDX_BE_VisitInfo.FirstRNID = TriageMA.UserID
       LEFT OUTER JOIN dbo.CDX_Visit
       ON     dbo.CDX_BE_VisitInfo.VisitID = dbo.CDX_Visit.VisitID
       LEFT OUTER JOIN dbo.CDX_Patient
       ON     dbo.CDX_Visit.PatientID = dbo.CDX_Patient.PatientID
WHERE(
       CASE ISNULL(Disposition.JCAHOCode, '-')
              WHEN 4    THEN 'Admit'
              WHEN 5    THEN 'LWOT'
              WHEN 6    THEN 'AMA'
              WHEN 7    THEN 'DID'
              WHEN 10   THEN 'Transfer'
              WHEN 15   THEN 'Discharge'
              WHEN 20   THEN 'DOA'
              WHEN 1355 THEN 'LBTC'
              ELSE 'Unknown'
       END IN(@Disposition))
   AND(dbo.CDX_BE_VisitInfo.SiteID = 'UPG')
   AND(dbo.CDX_Visit.RegTime >= @StartDate)
   AND(dbo.CDX_Visit.RegTime < DATEADD(d, 1, @EndDate))
   AND(dbo.CDX_Visit.SubSiteID IN(@Site))
   AND(ISNULL(Acuity.Acuity, '-') IN(@Acuity))
   AND(ISNULL(CPT.EMCode, '-') IN(@EMCode))

Open in new window

0
 
Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
Just a thought,
Be sure that all variables are being set and passed properly to the query. If one is being missed, it could be the cause.
0
 
HoggZillaCommented:
You cannot use variables in views. You could build this into a Table Function.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
UnityPGAuthor Commented:
My objective is not to have it in a view, but rather Reporting Services.  I was trying to run it as a (non-saved) view so I could see it represented graphically.  However, when I placed it in the view, I realized I was having the same problem there as I was in SRS.

After reading your comments, I thought it would be a good idea to hard code the values into the SQL Query, SRS dataset, and view just to see what would happen.  But the results creating a more confusing situation.  The SQL query took nearly a minute to run, instead of running in 2 seconds as it did when I declared the variables.  
0
 
BrandonGalderisiCommented:
If you create the procedure as I did, you can use the procedure as your report dataset.  Alternatively, you can use a table valued function as hoggzilla stated.  The difference is that UDF's don't allow you do default parameters by not supplying them.
0
 
UnityPGAuthor Commented:
I just created a procedure but the run time is still significantly different than it is if I run the query with the variable declared.  This doesn't make any sense.  Does anyone know what the difference is when the query runs between the query with the declared values versus hard coded?
0
 
UnityPGAuthor Commented:
Even though I haven't received any more comments to my original post, I've tried to work on the problem myself.  So here is a synapsis of where I'm at with this problem:

In an attempt to diagnose the problem I copied the dataset query to SQL, declared and set my variables and attempted to run it.  I was surprised, it ran fine and completed in about 2 seconds.  I also copied the query into a view (mainly because I wanted a graphical representation of my joins) but this time I got a time-out error.  I then hard coded my values into the SQL Query (not the view) but this time the runtime whet up to 50 seconds.  I repeated the tests several times with different variables and received the same results....declared variable = 2 to 5 second runtime, hard coded values = 50 to 70 second runtime.

What didn't make sense was that it worked when I first deployed it.  The only thing that came to mind was that I ran the Database Engine Tuning Adviser off the query when I created it (I only had it check the physical design structures of the indexes).  I decided to run the Tuning Adviser with my query as the workload again today.  Miraculously, the speed of the hard coded query has improved to less than 1 second (from the original 50 seconds), the view and SRS report are now just as fast.

Obviously, this fixes the problem for now, but it raises some important questions.  First, please understand that my SQL Server knowledge is only at an intermediate level.  I can write the complex queries and I research the topics I need more help on, but when it comes to indexes I get lost.  It appears that the index changes the Tuning Adviser makes work for a while, but as the database continues to grow (which it will since this is an electronic medical record that has new data added every minute) the tuning becomes out of date/irrelevant.  I looked at the maintenance plan for the database in question and it's set to reorganize the index and rebuild the index every Saturday.  Does this affect the changes implemented by the Tuning Adviser?  If so, is there a way to schedule SQL to analyze my query every month and update the indexes?  

I guess what it comes down to is how do I keep my query performing at the speed it is now?  I'm sure if I had a better understanding of what an index is, how an index affects/interacts with my query, and how the index affects the database the query is running on, this would be a little easier.  But more importantly, how do I fix this problem?

Thank you for your help.  Your assistance is greatly appreciated
0
 
BrandonGalderisiCommented:
Think of an index just like the index at the back of a book.  It's a speedy, organized way, to lookup information (records) at different locations in the book (table).  But imagine the book could change.  And every time the book changed, you penciled in the new information to the index.  that may work for a while, but eventually, the index would become difficult to read.  that's reindexing.
0
 
UnityPGAuthor Commented:
So the maintenance plan that is set to reorganize and rebuild the index is basically putting the "penciled in" items in the index in "ink".  So what is happening when I do the database tuning adviser against my query and making the recommended index changes?  Why is that causing my query to dramatically improve?  Is the maintenance plan index reorganization and rebuild wiping away the changes I make with the database tuning adviser?  Or do the changes made by the tuning adviser become obsolete after a certain time because the database is constantly changing?

I will probably have some followup questions based on the answers I get from these questions.
0
 
BrandonGalderisiCommented:
The rebuild rebuilds it.  It doesn't just convert the pencil to ink.  

The maintenance plan will rebuild the newly added indexes as well.

You may have had columns that were not covered by an index that were added.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now