Database Response is too slow

  • Dave Ballantyne

    SSC-Dedicated

    Points: 33556

    If you run the code contained in the the functions

    CR_OverDueLoaneeInstWise_Rpt

    and

    CR_RecoveryReceiptWise_Rpt

    what does the execution plan look like then ?

    As i said above the cost of these as functions is hidden, youve got a hell of a lot going on there.

    the 'catch all query' does not optimize well ,

    http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/



    Clear Sky SQL
    My Blog[/url]

  • cliffb

    SSCarpal Tunnel

    Points: 4547

    Looking through your Schema.txt file, the first thing I noticed was a lack of indexes. You have alot of joins goin on in the functions and the more data you get in your tables, the more indexes are likely to help. I would start by adding an index on your FK fields, run the query a few times (I usually run at least 3 times) to check timings.

    EDIT: By lack of indexes, I did not mean no indexes just that based on the where clauses and joins, It looks like could be missing indexes. Wanted to clarify. Again, you'll have to play around and measure performance to see if indexes make any difference.

  • Syed Jahanzaib Bin hassan

    SSChampion

    Points: 11410

    Total CPU physical and logical ?

    Total Ram ?

    Total SQL Server RAM ?

    OS 32bit or 64bit ?

    check this query

    Select st.text,sp.* from sys.sysprocesses sp

    cross apply sys.dm_exec_sql_text(sp.sql_handle) st

    where cpu >1000

    order by cpu desc

    Regards,

    Syed Jahanzaib Bin Hassan

    MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog

    http://www.aureus-salah.com

    Regards,
    Syed Jahanzaib Bin Hassan
    BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    www.aureus-salah.com

  • ModestyBlaise84

    SSCommitted

    Points: 1931

    Hi

    Unfortunately, I wasn’t able to open the document containing exec plan, but based on the code, it seems to me that there are quite a few problems with your procedure (and table/index structure).

    I will probably not cover all of them, but what is, in my opinion, obvious is the following:

    1. Indexes, these that exist, are not set up properly.

    Table CR_CO_LOANEE_LEDGER has unique clustered index on fields:

    -MEMBER_CODE, TRANS_ID,LOAD_APPLY_DATE

    Non-cluster index has clustered index included (as a pointer). This means that creating index:

    IX_CR_CO_LOANEE_LEDGER_MEM_RB consisting of:

    MEMBER_CODE, LOAN_APPLY_DATE,RECEIPT_BOOK,RECEIPT_NO makes not much sense.

    MEMBER_CODE, TRANS_ID,LOAD_APPLY_DATE will be part of that non-clustered index anyway so you should consider designing different index.

    Same with IX_CR_CO_LOANEE_LEDGER_Member.

    Sorry that I am not going into detailed explanation here.

    (Gail Show has great articles about indexes, check:

    http://www.sqlservercentral.com/Authors/Articles/Gail_Shaw/148127/)

    2. Procedure is using SET DATEFORMAT which is executed at run time, not at parse time and it affects re-usage of cached execution plan. If possible, try to move it out of procedure.

    3. Add SET NOCOUNT ON to procedure

    4. Almost all parameters are sent as VARCHAR (even dates). Try to change parameters to really match the type of the passed values

    5. Consider possibility of sorting outside DB layer

    etc

    When all of the above is covered, see how it goes and get back with new perf results.

Viewing 4 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply