April 19, 2011 at 6:52 am
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/
April 19, 2011 at 7:14 am
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.
April 19, 2011 at 8:54 am
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
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 19, 2011 at 4:51 pm
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 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply