#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    2
    Rep Power
    0

    MY sql help needed to improve the query performance


    </select SELECT DISTINCT INPATIENT_NUMBER,MEDICAL_RECORD_NUMBER,FROM_DATE,THRU_DATE,FILE_NAME,FILE_PATH,CONTENT_TYPE,FIRST_NA ME,LAST_NAME,GENDER,DOCTOR,DOB
    FROM admission_details,admitting_department,patient
    WHERE admission_details.PATIENT_ID = patient.id
    <if test="admissionFromDate !=null">
    AND
    FROM_DATE &gt;= #{admissionFromDate}
    </if>
    <if test="admissionThruDate !=null">
    AND
    FROM_DATE &lt;= #{admissionThruDate}
    </if>
    <if test="gender !=null">
    AND
    patient.gender = #{gender}
    </if>
    <if test="doctorId !=null">
    AND
    admission_details.DOCTOR = #{doctorId}
    </if>

    <if test="calculatedDate != null &amp;&amp; comparision != null &amp;&amp; comparision == 'Less Than'">
    AND
    patient.DOB &gt;= #{calculatedDate} AND patient.DOB &lt;= #{nowDate}
    </if>
    <if test="calculatedDate != null &amp;&amp; comparision != null &amp;&amp; comparision == 'Greater Than'">
    AND
    patient.DOB &lt; #{calculatedDate}
    </if>
    <if test="calculatedDate != null &amp;&amp; comparision != null &amp;&amp; comparision == 'Equal To'">
    AND
    patient.DOB = #{calculatedDate}
    </if>
    <if test="calculatedDate != null &amp;&amp; comparision != null &amp;&amp; comparision == 'In Between'">
    AND
    patient.DOB &gt;= #{nowDate} AND patient.DOB &lt;= #{calculatedDate}
    </if>

    </select>
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,163
    Rep Power
    4274
    step 1 in debugging your mysql query performance is to test the query directly in mysql, and not via that pseudo-html language, whatever it is

    step 2 is to show us the results of SHOW CREATE TABLE statements for each of your tables

    step 3 is to do an EXPLAIN on the query

    after those three steps, we'll have a look
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    2
    Rep Power
    0
    thanks for the update
    actually the code is in .zul format.

    what i am trying to do is . i want to use coalesce or NVL function to check the data base if the value inside is null or not, if not null then get the value of the data
    for example i have patients record where i am checking SELECT COUNT(DISTINCT(admission_details.PATIENT_ID)) AS CASECOUNT
    FROM admission_details,patient
    WHERE
    admission_details.PATIENT_ID = patient.id
    AND COALESCE(FROM_DATE,1) &gt;= COALESCE(#{admissionDate},FROM_DATE,1)
    AND COALESCE(THRU_DATE,1) &gt;= COALESCE(#

    {admissionDateThru},THRU_DATE,1)
    AND COALESCE(FROM_DATE,1) &lt;= COALESCE(#{admissionDate},FROM_DATE,1)
    AND COALESCE(THRU_DATE,1) &lt;= COALESCE(#

    {admissionDateThru},THRU_DATE,1)
    AND COALESCE(ADMITTINGWARD,-1) = COALESCE(#{wardId},ADMITTINGWARD,-1)
    AND COALESCE(patient.gender, -1) = COALESCE(#

    {gender},patient.gender,-1)

    ------------------------

    so i need more information to check whether it contains null or not, if yes i need to ignore the function or line of code.

    step one: check if the value is not null
    step two : not null capture the value
    step three: if null ignore and move to the next condition.

    i hope this explains what i need or looking for...

    thanks for the help..
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,163
    Rep Power
    4274
    Originally Posted by shash_waj
    step three: if null ignore
    you aren't ignoring, though, you're using COALESCE to choose a date value of 1 (if that's even valid, which i doubt)



    by the way, don't do this --

    COUNT(DISTINCT(admission_details.PATIENT_ID))

    do this instead --

    COUNT(DISTINCT admission_details.PATIENT_ID )

    reason: DISTINCT is ~not~ a function
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo