SQL Server Performance

Correlated Subquery performance

Discussion in 'General Developer Questions' started by griffiji, Oct 23, 2008.

  1. griffiji New Member

    Ok, I have a pretty nasty set of correlated subqueries, and I'm hoping for some help with making it more efficient.
    The Data Looks something like this:
    case_record is the primary table with cr_urn being the primary urn used to like tables
    booking contains certain scheduling information, both booking and case_record are fairly straightforward containing 1 record per row for the case in question.
    case_record_data is not so straight forward. custom_data_urn matches cr_urn for any given case, and there can be many rows for each case. Each row for a given case is differentiated by cd_field and field_name(eg the cd_field for anes_start is 352). The problem that this view is trying to solve, is that there are time fields in case_record_data that were created as numeric data. There are 6 times associated with each case which do not natively have a date associated with them, the case itself has a start date, but could cross over the midnight hour. So, the times follow each other, so I took the start time, and attached it to the start date, and then evaluated each time after that to see if it was greater than the start time, if not obviously we crossed over midnight and I need to add 1 day to the date associated with that time.
    I strongly suspect there's is a more efficient way of writing this, but I'm not seeing it right off, even a pointer in the right direction would probably do the trick.
    SELECT DISTINCT
    cr_urn = crd.custom_data_urn,
    crp_patient_urn = cr.crp_patient_urn,
    book_urn = b.book_urn,
    book_dur = b.book_dur,
    cr_prdate = cr.cr_prdate,
    room_urn = cr.room_urn,
    sch_start = CAST(Left(b.book_date, 12) + Left(Right('000'+CAST(b.book_start_time AS varchar), 4), 2)+':'+Right(Right('000'+CAST(b.book_start_time AS varchar), 4), 2)+':00.000' AS smalldatetime),
    pt_in_rm = (select CAST(Left(cr.cr_prdate, 12) + Left(Right('000'+CAST(a.cd_value AS varchar), 4), 2)+':'+Right(Right('000'+CAST(a.cd_value AS varchar), 4), 2)+':00.000' AS smalldatetime)
    from case_record_data as a (NOLOCK)
    where a.custom_data_urn = crd.custom_data_urn
    and a.cd_field = 350),
    anes_start = (case when (select a.cd_number_value
    from case_record_data as a (NOLOCK)
    where a.custom_data_urn = crd.custom_data_urn
    and a.cd_field = 350)
    <=
    (select a.cd_number_value
    from case_record_data as a (NOLOCK)
    where a.custom_data_urn = crd.custom_data_urn
    and a.cd_field = 352)
    then (select CAST(Left(cr.cr_prdate, 12) + Left(Right('000'+CAST(a.cd_value AS varchar), 4), 2)+':'+Right(Right('000'+CAST(a.cd_value AS varchar), 4), 2)+':00.000' AS smalldatetime)
    from case_record_data as a (NOLOCK)
    where a.custom_data_urn = crd.custom_data_urn
    and a.cd_field = 352)
    else (select CAST(Left(DATEADD(day, 1, cr.cr_prdate), 12) + Left(Right('000'+CAST(a.cd_value AS varchar), 4), 2)+':'+Right(Right('000'+CAST(a.cd_value AS varchar), 4), 2)+':00.000' AS smalldatetime)
    from case_record_data as a (NOLOCK)
    where a.custom_data_urn = crd.custom_data_urn
    and a.cd_field = 352)end),
    anes_ready = (case when (select a.cd_number_value
    from case_record_data as a (NOLOCK)
    where a.custom_data_urn = crd.custom_data_urn
    and a.cd_field = 350)
    <=
    (select a.cd_number_value
    from case_record_data as a (NOLOCK)
    where a.custom_data_urn = crd.custom_data_urn
    and a.cd_field = 3038)
    then (select CAST(Left(cr.cr_prdate, 12) + Left(Right('000'+CAST(a.cd_value AS varchar), 4), 2)+':'+Right(Right('000'+CAST(a.cd_value AS varchar), 4), 2)+':00.000' AS smalldatetime)
    from case_record_data as a (NOLOCK)
    where a.custom_data_urn = crd.custom_data_urn
    and a.cd_field = 3038)
    else (select CAST(Left(DATEADD(day, 1, cr.cr_prdate), 12) + Left(Right('000'+CAST(a.cd_value AS varchar), 4), 2)+':'+Right(Right('000'+CAST(a.cd_value AS varchar), 4), 2)+':00.000' AS smalldatetime)
    from case_record_data as a (NOLOCK)
    where a.custom_data_urn = crd.custom_data_urn
    and a.cd_field = 3038)end),
    surg_start = (case when (select a.cd_number_value
    from case_record_data as a (NOLOCK)
    where a.custom_data_urn = crd.custom_data_urn
    and a.cd_field = 350)
    <=
    (select a.cd_number_value
    from case_record_data as a (NOLOCK)
    where a.custom_data_urn = crd.custom_data_urn
    and a.cd_field = 418)
    then (select CAST(Left(cr.cr_prdate, 12) + Left(Right('000'+CAST(a.cd_value AS varchar), 4), 2)+':'+Right(Right('000'+CAST(a.cd_value AS varchar), 4), 2)+':00.000' AS smalldatetime)
    from case_record_data as a (NOLOCK)
    where a.custom_data_urn = crd.custom_data_urn
    and a.cd_field = 418)
    else (select CAST(Left(DATEADD(day, 1, cr.cr_prdate), 12) + Left(Right('000'+CAST(a.cd_value AS varchar), 4), 2)+':'+Right(Right('000'+CAST(a.cd_value AS varchar), 4), 2)+':00.000' AS smalldatetime)
    from case_record_data as a (NOLOCK)
    where a.custom_data_urn = crd.custom_data_urn
    and a.cd_field = 418)end),
    surg_end = (case when (select a.cd_number_value
    from case_record_data as a (NOLOCK)
    where a.custom_data_urn = crd.custom_data_urn
    and a.cd_field = 350)
    <=
    (select a.cd_number_value
    from case_record_data as a (NOLOCK)
    where a.custom_data_urn = crd.custom_data_urn
    and a.cd_field = 419)
    then (select CAST(Left(cr.cr_prdate, 12) + Left(Right('000'+CAST(a.cd_value AS varchar), 4), 2)+':'+Right(Right('000'+CAST(a.cd_value AS varchar), 4), 2)+':00.000' AS smalldatetime)
    from case_record_data as a (NOLOCK)
    where a.custom_data_urn = crd.custom_data_urn
    and a.cd_field = 419)
    else (select CAST(Left(DATEADD(day, 1, cr.cr_prdate), 12) + Left(Right('000'+CAST(a.cd_value AS varchar), 4), 2)+':'+Right(Right('000'+CAST(a.cd_value AS varchar), 4), 2)+':00.000' AS smalldatetime)
    from case_record_data as a (NOLOCK)
    where a.custom_data_urn = crd.custom_data_urn
    and a.cd_field = 419)end),
    pt_out_rm = (case when (select a.cd_number_value
    from case_record_data as a (NOLOCK)
    where a.custom_data_urn = crd.custom_data_urn
    and a.cd_field = 350)
    <=
    (select a.cd_number_value
    from case_record_data as a (NOLOCK)
    where a.custom_data_urn = crd.custom_data_urn
    and a.cd_field = 351)
    then (select CAST(Left(cr.cr_prdate, 12) + Left(Right('000'+CAST(a.cd_value AS varchar), 4), 2)+':'+Right(Right('000'+CAST(a.cd_value AS varchar), 4), 2)+':00.000' AS smalldatetime)
    from case_record_data as a (NOLOCK)
    where a.custom_data_urn = crd.custom_data_urn
    and a.cd_field = 351)
    else (select CAST(Left(DATEADD(day, 1, cr.cr_prdate), 12) + Left(Right('000'+CAST(a.cd_value AS varchar), 4), 2)+':'+Right(Right('000'+CAST(a.cd_value AS varchar), 4), 2)+':00.000' AS smalldatetime)
    from case_record_data as a (NOLOCK)
    where a.custom_data_urn = crd.custom_data_urn
    and a.cd_field = 351)end),
    anes_end = (case when (select a.cd_number_value
    from case_record_data as a (NOLOCK)
    where a.custom_data_urn = crd.custom_data_urn
    and a.cd_field = 350)
    <=
    (select a.cd_number_value
    from case_record_data as a (NOLOCK)
    where a.custom_data_urn = crd.custom_data_urn
    and a.cd_field = 353)
    then (select CAST(Left(cr.cr_prdate, 12) + Left(Right('000'+CAST(a.cd_value AS varchar), 4), 2)+':'+Right(Right('000'+CAST(a.cd_value AS varchar), 4), 2)+':00.000' AS smalldatetime)
    from case_record_data as a (NOLOCK)
    where a.custom_data_urn = crd.custom_data_urn
    and a.cd_field = 353)
    else (select CAST(Left(DATEADD(day, 1, cr.cr_prdate), 12) + Left(Right('000'+CAST(a.cd_value AS varchar), 4), 2)+':'+Right(Right('000'+CAST(a.cd_value AS varchar), 4), 2)+':00.000' AS smalldatetime)
    from case_record_data as a (NOLOCK)
    where a.custom_data_urn = crd.custom_data_urn
    and a.cd_field = 353)end)
    FROM case_record_data AS crd (NOLOCK) FULL OUTER JOIN
    case_record AS cr (NOLOCK) ON cr.cr_urn = crd.custom_data_urn FULL OUTER JOIN
    booking AS b (NOLOCK)ON b.book_urn = cr.book_urn
  2. Adriaan New Member

    Since those correlated subqueries are all the same, just each time with different values for the criteria, you should look at setting up derived tables.
    Each derived table needs to include the key columns, so you can use JOINs between the derived tables.

Share This Page