![alt text] : /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 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
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.