asked
in query in view is getting slow
![alt text][1] [1]: /storage/temp/717-temp.png I am using an in query to create view but it is slowing down the system, i have just shared the screen shot of the db structure. i need all records of a student from tblstudentledger where at least one credit entry exists ( means i want to get the ledger details of those students who have paid the fee for a specific period). first i am getting the list of those students from tblstudentledger who's credit entry exists, and then by using those student id's i am getting all ledger entries. can you please suggest me some alternative for this work?
The Requirement is now very clear sir, only one table is involved with sample data and out required is also there. how i can post the execution plan? i am not so expert for databases, so please guide me. i need all enteries from the detail table where one credit entry exists between some particular dates.
The WHERE clause there with the SELECT DISTINCT could simply be turned into another INNER JOIN. Structurally it's no different. The DISTINCT aggregation could absolutely be causing issues. But, beyond that, I couldn't say because I can't see the execution plan. You should try capturing the actual execution plan and examining it to understand what the query is doing, how it's accessing the data, which indexes it's using, if any, etc.
First of all for such performance problems, as @Grant Fritchey said, execution plans are most important. Secondly, a picture would not help us either. So next time please post the query in text, execution plan, the DDL of the tables if possible. Now, what I see is that twice as work is done in your query than it should have been (I hope I am not missing something as it is only a picture). There is no need to use the tblStudentLedger twice (Once in the FROM clause and once in the subquey with IN). So omit the IN and only keep the WHERE clause i.e. SELECT ..... FROM tblStudentLedger JOIN othertable.. JOIN othertable... --SEE NO IN CLAUSE WHERE Credit > 0 AND Description = 'Payment...' AND Transactiondate >= 'somedate' and TransactionDate
dear sir, i created the execution plan and tried to attached, but i don't have permission to attach this kind of file. what should i do know ? please help
this will only return the ledger entries where credit > 0. and all debit entries will not come in the result. actually when there is fee posting, debit values are entered in table and credit remains the zero. and when student pay the fee, credit amount is entered with the record and debit is zero. so i need all rows of a students who's at least one credit entry exists in some particular date. you can ignore the joins, because they are just to get the related fields for the result.
Aghhh..my bad. But see this is what happens if you do not provide the details as required. As our SQL GURU already said, you need to post the execution plan. There could be a missing index but it is all guesswork. With all these pictures, you are not helping us at all to help you. As a side note, I would try refactoring query using EXISTS. Something like SELECT ..... FROM tblStudentLedger tbl JOIN othertable.. JOIN othertable... --SEE NO IN CLAUSE WHERE EXISTS( SELECT 1 FROM tblStudentLedger tbl1 WHERE Credit > 0 AND Description = 'Payment...' AND Transactiondate >= 'somedate' AND Transactiondate
![alt text][1] [1]: /storage/temp/721-resultset.png I need all ledger entries for those students who have paid the fee ( at least have one credit entry). in this case student id 100 and 102 have paid the fee). Thanks for Advance.
Dear Sir, Thank you very much for your help, exists statement helps and query time is very low now. but this does not gives the desired results, because i need to specify the date for the outer query as well, otherwise it gives the ledger records for all the year because the exists query gives the comparison for the students who paid in this month, but when we take the records from outer query by specifying date, it takes much time **SELECT * FROM tblStudentLedger tbl WHERE EXISTS( SELECT 1 FROM tblStudentLedger tbl1 where transaction_date >='2013-03-01' and transaction_date <='2013-03-31' and description='Payment Deposited in Bank' and credit >0 AND tbl.Student_id = tbl1.Student_id)** and transaction_date>='2013-03-01' order by student_id The query in bold takes very low time, but it gives all the reocrds, i also need to specify the date criteria for the outer query and it again takes a long time to execute.
If you could please attach the execution plan then it would be easier for us to help. We do not know your data, indexes..nothing. To get the execution plan use combination of key Ctrl+M OR from menu option, Query->Include actual execution plan and then run the query. Save that execution plan and attach it here to the question. There seems to a missing index on Transaction date but that would be a guess work and it may not prove good.
14 People are following this question.
