Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2003
    Posts
    2

    Unanswered: Theoretical Question

    Hello All,
    I have queries that I am running against several ASE12.5 DBs. The basic structure follows this approach:
    1) Create a few temp tables to store key parameters (usually 1 to 4)
    2) Load the parameters into the temp table(s)
    3) Create the Main temp table
    4) Load the main temp table (this table may end up with 100,000+ records, the main database usually contains 7 million records or more)
    5) Query the Main Temp table with different levels of grouping returning multiple recordsets.

    My question has to do primarily with Step 4.

    Which of the following is a better (overall) approach to retrieving the data:

    a)
    INSERT INTO #utilization
    SELECT
    hospital.hospital_name
    ,
    '',
    '',
    '',
    '',
    count(inpatient.case_id),
    sum( inpatient.los ) ,
    round(sum(inpatient.charge * 1.0),4),
    round(sum(hospital_rdrg_all.st_total_days * 1.0 / hospital_rdrg_all.st_total_cases ),4),
    round(sum(hospital_rdrg_all.st_total_charges * 1.0 / hospital_rdrg_all.st_total_cases ),4),
    round(sum( hospital_rdrg_all.relative_weight ),4) ,0
    FROM wial..inpatient inpatient,
    wial..hospital_rdrg_all hospital_rdrg_all
    , #period, #facility, wial..hospital hospital

    WHERE hospital_rdrg_all.period_code = inpatient.period_code
    AND hospital_rdrg_all.rdrg_code = inpatient.rdrg_code
    AND hospital_rdrg_all.hospital_code = inpatient.hospital_code
    and inpatient.period_code = #period.id
    and inpatient.hospital_code = #facility.id
    and inpatient.hospital_code = hospital.hospital_code

    or b)
    INSERT INTO #utilization
    SELECT
    hospital.hospital_name
    ,
    '',
    '',
    '',
    '',
    count(inpatient.case_id),
    sum( inpatient.los ) ,
    round(sum(inpatient.charge * 1.0),4),
    round(sum(hospital_rdrg_all.st_total_days * 1.0 / hospital_rdrg_all.st_total_cases ),4),
    round(sum(hospital_rdrg_all.st_total_charges * 1.0 / hospital_rdrg_all.st_total_cases ),4),
    round(sum( hospital_rdrg_all.relative_weight ),4) ,0
    FROM wial..inpatient inpatient,
    wial..hospital_rdrg_all hospital_rdrg_all
    , wial..hospital hospital

    WHERE hospital_rdrg_all.period_code = inpatient.period_code
    AND hospital_rdrg_all.rdrg_code = inpatient.rdrg_code
    AND hospital_rdrg_all.hospital_code = inpatient.hospital_code
    and inpatient.period_code in (select id from #period)
    and inpatient.hospital_code in (select id from #facility)
    and inpatient.hospital_code in (select id from hospital)

    Am I better off performance wise physically joining my temp tables in the where clause (a) or not joining to them and using a in (Select...) structure.

    My initial test(s) showed improved performance with b, when running in an ISQL window, but from my WebApp using jconnect it was no change and possibly a little slower....

  2. #2
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    One option is to do showplans and see how the Optimizer is costing the queries.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •