locked
Query timeout for large table RRS feed

  • Question

  • Dear friend,

    My view always shows timeout because my table is now having 18,00,000 data row.

    Now what should I do with this table? can anyone help me?

    another question is, in my work purpose I need to create 5-7 report every day. So every time I need to create view for those report. I can not create procedure always because creating view is easier for me.  But the views become slower day by day. My server is I think quiet good. Xeon cuad core dual processor and Ram is 32.

    Is advice will be appreciated.

    Thanks in advance

    Thursday, May 22, 2014 8:33 AM

Answers

  • Hi,

    1. Before we start to understand your specific problem we need information on your database structure: tables structure (including indexes, constraint , etc), tables relationship and other element in the database that are relevant to your issue (to the question). The same query in 2 different database can beehive differently! Moreover is you have 2 queries then bring the same result there is a good chance then on one database query A is better while on the other database query B is better. Therefore without the information on your specific database structure we are just playing  guessing game. Can you post DDL?

    * DDL (Data Definition Language) mean in our case to the CREATE TABLE statements for your tables and other definitions that are needed to understand your tables structure. How to get DDL: Right click on the table in Object Explorer and select script table as CREATE. Post these create table scripts here.

    For the same reason we need you to post the query if you want us to check why it is slowly and how to improve it. how can we answer without seen the query? maybe your query is "select top 1 1 from table_name"?!? this query should be fast :-) I hope i succeed to make you understand that we need information to answer your question. 

    2. I am guessing that when you talk about reports, then you mean SSRS reports. I will answer according this assumptions:

    * SSRS is not the best approach for displaying a long and complex reports in my opinion. It is fit for local user but much less for multipule remote users. For this we better use a web application (I am not talking on using SSRS in a web application but using a simple clean code to produce HTML/JS table for example). If you have a developer in house I recommend to check this option.

    * A view element in the database is great for easy life but is not always the best approach.  A complex  view might bring the SQL Server's Engine to build  very bad executing plan. I have seen  companies where the user (I cant call them developers or DBAs) used one big view with lot of JOIN operations to get JOIN all the tables in the database, in order to use only this view for the application. a one hour executing time was reduce by me to 4 sec after "fixing" (more accurate develop several models from scratch) both the application and the queries. 

    To make sure what is going in your database you should look at the execution plan.

    Please post the information we need and we will try to help you more :-)


    [Personal Site] [Blog] [Facebook]signature

    • Proposed as answer by Sofiya Li Friday, May 23, 2014 6:37 AM
    • Marked as answer by Soulidentities Friday, May 23, 2014 8:05 AM
    Thursday, May 22, 2014 11:17 AM
  • Yep, That is so bad :-)

    At first glance that look exactly like the problem I mentioned above. It is look just like my story on improving from one hour to less then 4 second :-)

    I can not give you the specific architecture that fit you in the forum (not that I dont want, but just because I dont familiar your system, needs, etc), and as I mention I will not have time for this in the forum, but I will give you some "golden rules" that probably will help you a lot.  those recommendations are not the best solution, but according the poor information I have, It is the best that I assume will fit you at this time :-)

    1. Stop use this view!!! if you want to use view and you need 3 tables then use 3 tables in the view!

    2. Join is very very very... very expensive operation. Dont JOIN tables that you dont need!

    3. If there is more then 3-4 JOIN in a view (the number can change from one system to another, and again it is just a golden rule) then the SQL Server do not build a good executing plan and it start to join tables even if we dont need them. Since you have filter as well the server have to execute that filter as well in that case and the result might be 1 hour instead of 1 second. So the rule will be: Not more then 3 JOIN in a view! if you need 4 then JOIN the result of the three with the fourth table (at this time, just to keep it as a rule).

    4. At this time while your people do not know how to optimize queries or build a good architecture best option is to leave it simple. Dont use any view (this is my recommendation for you at this time). Just use direct query from the tables.

    5. It is highly recommend to take some outsourcing help, if you can not hire in-house DBA!

    I hope this will be useful and it was not too harsh :-)


    [Personal Site] [Blog] [Facebook]signature

    Friday, May 23, 2014 8:35 PM

All replies

  • I hope you do have a WHERE condition to generate the report? In order to speed up the query you need properly created indexes.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, May 22, 2014 10:21 AM
    Answerer
  • Yes I know the where condition, in fact I there is many filters and many views created for generate the expected result. But it shows timeout. If I reduce the number of records from the table then the view works. But This is not the solution I am asking for. Okay I have done the indexes in that table but still shows timeout. 

    I am expecting some maintenance advice from you. I am not a DBA so I do not have enough knowledge about maintenance. Could you advice some?  Or from where I can get that?

    Thanks

    Thursday, May 22, 2014 11:02 AM
  • Hi,

    1. Before we start to understand your specific problem we need information on your database structure: tables structure (including indexes, constraint , etc), tables relationship and other element in the database that are relevant to your issue (to the question). The same query in 2 different database can beehive differently! Moreover is you have 2 queries then bring the same result there is a good chance then on one database query A is better while on the other database query B is better. Therefore without the information on your specific database structure we are just playing  guessing game. Can you post DDL?

    * DDL (Data Definition Language) mean in our case to the CREATE TABLE statements for your tables and other definitions that are needed to understand your tables structure. How to get DDL: Right click on the table in Object Explorer and select script table as CREATE. Post these create table scripts here.

    For the same reason we need you to post the query if you want us to check why it is slowly and how to improve it. how can we answer without seen the query? maybe your query is "select top 1 1 from table_name"?!? this query should be fast :-) I hope i succeed to make you understand that we need information to answer your question. 

    2. I am guessing that when you talk about reports, then you mean SSRS reports. I will answer according this assumptions:

    * SSRS is not the best approach for displaying a long and complex reports in my opinion. It is fit for local user but much less for multipule remote users. For this we better use a web application (I am not talking on using SSRS in a web application but using a simple clean code to produce HTML/JS table for example). If you have a developer in house I recommend to check this option.

    * A view element in the database is great for easy life but is not always the best approach.  A complex  view might bring the SQL Server's Engine to build  very bad executing plan. I have seen  companies where the user (I cant call them developers or DBAs) used one big view with lot of JOIN operations to get JOIN all the tables in the database, in order to use only this view for the application. a one hour executing time was reduce by me to 4 sec after "fixing" (more accurate develop several models from scratch) both the application and the queries. 

    To make sure what is going in your database you should look at the execution plan.

    Please post the information we need and we will try to help you more :-)


    [Personal Site] [Blog] [Facebook]signature

    • Proposed as answer by Sofiya Li Friday, May 23, 2014 6:37 AM
    • Marked as answer by Soulidentities Friday, May 23, 2014 8:05 AM
    Thursday, May 22, 2014 11:17 AM
  • How many rows it is generated?  How you launch the report? From .NET? SSRS? SQL Server? 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, May 22, 2014 11:22 AM
    Answerer
  • Yes I agree and I am doing the same that you have mentioned in your last para.

    But how can post my table structure here and query as well?

    I have extracted the script file from my 2 database and a screenshot of my query so that you can have an idea of the way I build query. But I do not have any option to attachment here.

    what should I do now?

    I am trying to give some link. Hope it works.

    Monitoring

    ROSC

    Image

    Friday, May 23, 2014 8:58 AM
  • But how can post my table structure here and query as well?

    I have extracted the script file from my 2 database and a screenshot of my query so that you can have an idea of the way I build query. But I do not have any option to attachment here.

    what should I do now?

    Hi,

    Don't post an image of the script, but pure text, so we could copy it directly to our SSMS and recreate the same tables and structure as you have (only the relevant table! dont post all the DDL if it is not relevant to the question of course).

    There is a button "Insert Code Block" above the text box in the forum to post code. please use it :-)

    * I wrote above how to get the table DDL if you dont know to write the script (look for the section with "How to get DDL" above).

    ** When you say that you do the same, are you meaning that you are using Dot.Net application or are that you use one big view that collect all the data from all tables?

    If you are using Dot.Net please inform us if you use Webform or MVC and what language please. In this case some code can help us to make sure that the problem is not with the logic in the application.

    *** I am not sure I will have time during this weekend (we are in the weekend now here in Israel) but I will try to check the links and your response if I can (else it will wait for a while).


    [Personal Site] [Blog] [Facebook]signature

    • Edited by pituachMVP Friday, May 23, 2014 3:39 PM
    Friday, May 23, 2014 3:27 PM
  • OK, I see the links but...

    is your report use all those tables in one reports?!?

    I don't have time to go over all that you have post. This is free supporting on our free time :-) please try to focus on the problem. Only post the relevant DDL.


    [Personal Site] [Blog] [Facebook]signature

    Friday, May 23, 2014 3:35 PM
  • Ya thanks for your time. I am appreciating all the way.

    Actually I attach those for present an idea about my database. Most of the time I need to work with just 3 or 4 table which is LC_Profile and student_profile or ROSC database.

    I am adding the query but you do not need to go all the query. Just understand how difficult my query use to be. My question is there good way to get result faster than the view?I need to make several report every day. So I use view and join many tables and need to use many where clause, case, convert time etc. That is why I am asking for suggestion.

    SELECT     TOP (100) PERCENT dbo.ACF_LCs.YearTrim, dbo.ACF_LCs.EduYr, dbo.vw_Geocode.DivisionID, dbo.vw_Geocode.DivisionB, dbo.vw_Geocode.Division, 
                          dbo.vw_Geocode.DistrictID, dbo.vw_Geocode.District, dbo.vw_Geocode.DistrictB, dbo.vw_Geocode.UpazilaID, dbo.vw_Geocode.Upazila, dbo.vw_Geocode.UpazilaB, 
                          dbo.LCProfile.LCID, dbo.LCProfile.LCYr, dbo.LCProfile.LCNm, dbo.LCProfile.LCNmB, dbo.Vw_Teacher_Active.TeachYr, dbo.Vw_Teacher_Active.TeachEdu, 
                          CASE WHEN TeachEdu = 1 THEN 3000 ELSE 3000 END AS TeacherSalaryOld, dbo.LCProfile.LCAccountNo, dbo.Vw_Teacher_Active.TeachNm, 
                          dbo.Vw_Teacher_Active.TeachSex, dbo.vw_Bank_Branch.LCBankBr, dbo.Vw_Teacher_Active.TeachMob, dbo.LCProfile.UnionID, dbo.UnionCode.UnionB, 
                          dbo.LCProfile.LCVill, MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.DistrictID AS MDistrictID, 
                          MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.UpazilaID AS MUpazilaID, MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.LCID AS MLCID, 
                          MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.VisitType, MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.LCVisitYr, 
                          MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.Trimister, MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.MOID, 
                          MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.LCStatus, MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.LC1stVstDt, 
                          MonitoringROSCII.dbo.Venu_Info.VenuType, MonitoringROSCII.dbo.Venu_Info.VenuTypeOthr, MonitoringROSCII.dbo.Venu_Info.NoWindow, 
                          MonitoringROSCII.dbo.Venu_Info.SuffWinAir, MonitoringROSCII.dbo.Venu_Info.FreeArsWater, MonitoringROSCII.dbo.Venu_Info.HigLatrin, 
                          MonitoringROSCII.dbo.Venu_Info.SeatArg, MonitoringROSCII.dbo.Venu_Info.Blackboard, MonitoringROSCII.dbo.Venu_Info.DistrictID AS VDistrictID, 
                          MonitoringROSCII.dbo.Venu_Info.UpazilaID AS VUpazilaID, MonitoringROSCII.dbo.Venu_Info.LCID AS VLCID, 
                          MonitoringROSCII.dbo.Vw_UniformYes.DistrictID AS UDistrictID, MonitoringROSCII.dbo.Vw_UniformYes.UpazilaID AS UUpazilaID, 
                          MonitoringROSCII.dbo.Vw_UniformYes.LCID AS ULCID, MonitoringROSCII.dbo.Vw_UniformYes.RecUniformY, 
                          MonitoringROSCII.dbo.Teacher_Training.DistrictID AS TDistrictID, MonitoringROSCII.dbo.Teacher_Training.UpazilaID AS TUpazilaID, 
                          MonitoringROSCII.dbo.Teacher_Training.LCID AS TLCID, MonitoringROSCII.dbo.Teacher_Training.TcrRecFndTrn, MonitoringROSCII.dbo.LC_Info.PrsnMale, 
                          MonitoringROSCII.dbo.LC_Info.PrsnFemale, MonitoringROSCII.dbo.LC_Info.PrsnStdTot, RIGHT('00' + CONVERT(varchar, dbo.vw_Geocode.DivisionID), 2) 
                          + RIGHT('00' + CONVERT(varchar, dbo.vw_Geocode.DistrictID), 2) + RIGHT(CONVERT(varchar, dbo.vw_Geocode.UpazilaID), 2) + RIGHT('000' + CONVERT(varchar, 
                          dbo.Vw_Teacher_Active.LCID), 3) AS InstituteID, MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.LCStartHr, 
                          MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.LCEndHr, dbo.Vw_LCProfile_QStudent_LCwise2013_3.NoStudent AS NoQStudent, 
                          dbo.Vw_LCProfile_QStudent_LCwise2013_3.Stu13, dbo.Vw_LCProfile_QStudent_LCwise2013_3.Stu45, dbo.PO.PO_NM_E, dbo.PO.PO_NM_B, 
                          dbo.vw_Geocode.Status AS UpStatus, dbo.vw_Geocode.Phase, MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.SpecialStatus, 
                          dbo.ACF_LCs.SpecialStatus AS SpecialStatusACF, MonitoringROSCII.dbo.Teacher_Profile.TcrPres, MonitoringROSCII.dbo.Teacher_Profile.TcrMtchLCProf, 
                          CASE WHEN NOT (MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.LCID IS NULL) AND LCStatus = 1 AND ((TcrPres = 1 AND TcrMtchLCProf = 2) OR
                          TcrPres = 2) THEN 0 ELSE 3000 END AS TeacherSalary
    FROM         dbo.Vw_Teacher_Active RIGHT OUTER JOIN
                          dbo.PO RIGHT OUTER JOIN
                          dbo.vw_LC_Functioning RIGHT OUTER JOIN
                          dbo.Vw_LCProfile_QStudent_LCwise2013_3 INNER JOIN
                          dbo.ACF_LCs INNER JOIN
                          dbo.vw_Geocode INNER JOIN
                          dbo.LCProfile ON dbo.vw_Geocode.DistrictID = dbo.LCProfile.DistrictID AND dbo.vw_Geocode.UpazilaID = dbo.LCProfile.UpazilaID ON 
                          dbo.ACF_LCs.DistrictID = dbo.LCProfile.DistrictID AND dbo.ACF_LCs.UpazilaID = dbo.LCProfile.UpazilaID AND dbo.ACF_LCs.LcID = dbo.LCProfile.LCID ON 
                          dbo.Vw_LCProfile_QStudent_LCwise2013_3.DistrictID = dbo.ACF_LCs.DistrictID AND 
                          dbo.Vw_LCProfile_QStudent_LCwise2013_3.UpazilaID = dbo.ACF_LCs.UpazilaID AND dbo.Vw_LCProfile_QStudent_LCwise2013_3.LCID = dbo.ACF_LCs.LcID ON 
                          dbo.vw_LC_Functioning.DistrictID = dbo.ACF_LCs.DistrictID AND dbo.vw_LC_Functioning.UpazilaID = dbo.ACF_LCs.UpazilaID AND 
                          dbo.vw_LC_Functioning.LCID = dbo.ACF_LCs.LcID LEFT OUTER JOIN
                          MonitoringROSCII.dbo.Teacher_Training RIGHT OUTER JOIN
                          MonitoringROSCII.dbo.Venu_Info RIGHT OUTER JOIN
                          MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo LEFT OUTER JOIN
                          MonitoringROSCII.dbo.Teacher_Profile ON MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.DistrictID = MonitoringROSCII.dbo.Teacher_Profile.DistrictID AND 
                          MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.UpazilaID = MonitoringROSCII.dbo.Teacher_Profile.UpazilaID AND 
                          MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.LCID = MonitoringROSCII.dbo.Teacher_Profile.LCID AND 
                          MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.VisitType = MonitoringROSCII.dbo.Teacher_Profile.VisitType AND 
                          MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.LCVisitYr = MonitoringROSCII.dbo.Teacher_Profile.LCVisitYr AND 
                          MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.Trimister = MonitoringROSCII.dbo.Teacher_Profile.Trimister ON 
                          MonitoringROSCII.dbo.Venu_Info.DistrictID = MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.DistrictID AND 
                          MonitoringROSCII.dbo.Venu_Info.UpazilaID = MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.UpazilaID AND 
                          MonitoringROSCII.dbo.Venu_Info.LCID = MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.LCID AND 
                          MonitoringROSCII.dbo.Venu_Info.VisitType = MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.VisitType AND 
                          MonitoringROSCII.dbo.Venu_Info.LCVisitYr = MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.LCVisitYr AND 
                          MonitoringROSCII.dbo.Venu_Info.Trimister = MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.Trimister LEFT OUTER JOIN
                          MonitoringROSCII.dbo.Vw_UniformYes ON MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.DistrictID = MonitoringROSCII.dbo.Vw_UniformYes.DistrictID AND 
                          MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.UpazilaID = MonitoringROSCII.dbo.Vw_UniformYes.UpazilaID AND 
                          MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.LCID = MonitoringROSCII.dbo.Vw_UniformYes.LCID AND 
                          MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.VisitType = MonitoringROSCII.dbo.Vw_UniformYes.VisitType AND 
                          MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.LCVisitYr = MonitoringROSCII.dbo.Vw_UniformYes.LCVisitYr LEFT OUTER JOIN
                          MonitoringROSCII.dbo.LC_Info ON MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.DistrictID = MonitoringROSCII.dbo.LC_Info.DistrictID AND 
                          MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.UpazilaID = MonitoringROSCII.dbo.LC_Info.UpazilaID AND 
                          MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.LCID = MonitoringROSCII.dbo.LC_Info.LCID AND 
                          MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.VisitType = MonitoringROSCII.dbo.LC_Info.VisitType AND 
                          MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.LCVisitYr = MonitoringROSCII.dbo.LC_Info.LCVisitYr AND 
                          MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.Trimister = MonitoringROSCII.dbo.LC_Info.Trimister ON 
                          MonitoringROSCII.dbo.Teacher_Training.DistrictID = MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.DistrictID AND 
                          MonitoringROSCII.dbo.Teacher_Training.UpazilaID = MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.UpazilaID AND 
                          MonitoringROSCII.dbo.Teacher_Training.LCID = MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.LCID AND 
                          MonitoringROSCII.dbo.Teacher_Training.VisitType = MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.VisitType AND 
                          MonitoringROSCII.dbo.Teacher_Training.LCVisitYr = MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.LCVisitYr AND 
                          MonitoringROSCII.dbo.Teacher_Training.Trimister = MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.Trimister ON 
                          dbo.ACF_LCs.DistrictID = MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.DistrictID AND 
                          dbo.ACF_LCs.UpazilaID = MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.UpazilaID AND 
                          dbo.ACF_LCs.LcID = MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.LCID ON dbo.PO.DistrictID = dbo.LCProfile.DistrictID AND 
                          dbo.PO.UpazilaID = dbo.LCProfile.UpazilaID ON dbo.Vw_Teacher_Active.DistrictID = dbo.LCProfile.DistrictID AND 
                          dbo.Vw_Teacher_Active.UpazilaID = dbo.LCProfile.UpazilaID AND dbo.Vw_Teacher_Active.LCID = dbo.LCProfile.LCID LEFT OUTER JOIN
                          dbo.UnionCode ON dbo.LCProfile.UnionID = dbo.UnionCode.UnionID AND dbo.LCProfile.UpazilaID = dbo.UnionCode.UpazilaID AND 
                          dbo.LCProfile.DistrictID = dbo.UnionCode.DistrictID LEFT OUTER JOIN
                          dbo.vw_Bank_Branch ON dbo.LCProfile.LCBankBr = dbo.vw_Bank_Branch.BranchID
    GROUP BY dbo.vw_Geocode.DivisionID, dbo.vw_Geocode.DivisionB, dbo.vw_Geocode.DistrictID, dbo.vw_Geocode.DistrictB, dbo.vw_Geocode.UpazilaID, 
                          dbo.vw_Geocode.UpazilaB, dbo.LCProfile.LCID, dbo.LCProfile.LCYr, dbo.LCProfile.LCNmB, dbo.Vw_Teacher_Active.TeachEdu, dbo.LCProfile.LCAccountNo, 
                          dbo.Vw_Teacher_Active.TeachNm, dbo.Vw_Teacher_Active.TeachSex, dbo.vw_Bank_Branch.LCBankBr, dbo.UnionCode.UnionB, dbo.vw_Geocode.Division, 
                          dbo.vw_Geocode.District, dbo.vw_Geocode.Upazila, MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.DistrictID, 
                          MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.UpazilaID, MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.LCID, 
                          MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.VisitType, MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.LCVisitYr, 
                          MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.Trimister, MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.MOID, 
                          MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.LCStatus, MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.LC1stVstDt, 
                          MonitoringROSCII.dbo.Venu_Info.NoWindow, MonitoringROSCII.dbo.Venu_Info.SuffWinAir, MonitoringROSCII.dbo.Venu_Info.FreeArsWater, 
                          MonitoringROSCII.dbo.Venu_Info.HigLatrin, MonitoringROSCII.dbo.Venu_Info.SeatArg, MonitoringROSCII.dbo.Venu_Info.Blackboard, 
                          MonitoringROSCII.dbo.Venu_Info.DistrictID, MonitoringROSCII.dbo.Venu_Info.UpazilaID, MonitoringROSCII.dbo.Venu_Info.LCID, 
                          MonitoringROSCII.dbo.Venu_Info.VenuType, MonitoringROSCII.dbo.Venu_Info.VenuTypeOthr, MonitoringROSCII.dbo.Vw_UniformYes.RecUniformY, 
                          MonitoringROSCII.dbo.Vw_UniformYes.DistrictID, MonitoringROSCII.dbo.Vw_UniformYes.UpazilaID, MonitoringROSCII.dbo.Vw_UniformYes.LCID, 
                          MonitoringROSCII.dbo.Teacher_Training.DistrictID, MonitoringROSCII.dbo.Teacher_Training.UpazilaID, MonitoringROSCII.dbo.Teacher_Training.LCID, 
                          MonitoringROSCII.dbo.Teacher_Training.TcrRecFndTrn, dbo.LCProfile.UnionID, MonitoringROSCII.dbo.LC_Info.PrsnMale, MonitoringROSCII.dbo.LC_Info.PrsnFemale, 
                          MonitoringROSCII.dbo.LC_Info.PrsnStdTot, dbo.LCProfile.LCVill, dbo.Vw_Teacher_Active.TeachMob, RIGHT('00' + CONVERT(varchar, dbo.vw_Geocode.DivisionID), 2) 
                          + RIGHT('00' + CONVERT(varchar, dbo.vw_Geocode.DistrictID), 2) + RIGHT(CONVERT(varchar, dbo.vw_Geocode.UpazilaID), 2) + RIGHT('000' + CONVERT(varchar, 
                          dbo.Vw_Teacher_Active.LCID), 3), MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.LCStartHr, 
                          MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.LCEndHr, dbo.Vw_LCProfile_QStudent_LCwise2013_3.NoStudent, dbo.PO.PO_NM_E, dbo.PO.PO_NM_B, 
                          dbo.vw_Geocode.Status, dbo.vw_Geocode.Phase, dbo.Vw_Teacher_Active.TeachYr, dbo.LCProfile.LCNm, 
                          MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.SpecialStatus, dbo.ACF_LCs.YearTrim, dbo.ACF_LCs.EduYr, 
                          dbo.Vw_LCProfile_QStudent_LCwise2013_3.Stu13, dbo.Vw_LCProfile_QStudent_LCwise2013_3.Stu45, dbo.ACF_LCs.SpecialStatus, 
                          MonitoringROSCII.dbo.Teacher_Profile.TcrPres, MonitoringROSCII.dbo.Teacher_Profile.TcrMtchLCProf, 
                          CASE WHEN NOT (MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.LCID IS NULL) AND LCStatus = 1 AND ((TcrPres = 1 AND TcrMtchLCProf = 2) OR
                          TcrPres = 2) THEN 0 ELSE 3000 END
    HAVING      (dbo.ACF_LCs.YearTrim = 1) AND (dbo.ACF_LCs.EduYr = 2014) AND (dbo.LCProfile.LCYr < 2013) AND 
                          (MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.LCVisitYr = 2014) AND (MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.Trimister = 1) AND 
                          (MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.VisitType = 3) AND (NOT (MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.LCID IS NULL)) OR
                          (dbo.ACF_LCs.YearTrim = 1) AND (dbo.ACF_LCs.EduYr = 2014) AND (dbo.LCProfile.LCYr < 2013) AND 
                          (MonitoringROSCII.dbo.VwComplianceMonitoringBasicInfo.LCID IS NULL)
    ORDER BY dbo.vw_Geocode.DivisionID

    Another problem is ,

    Lets say I have a table with id, name, place,address, timeof_attendance and an id which is big int type. This table have 18,00,000 record. and each day increasing with 5000 record. From there I am finding the attandance in every day. So I have to create 4 nested view to come to a result. Now My query shows timeout. If I delete old data then it works. This kind of problem I am facing.

    Please advice me.

    Thanks

    Friday, May 23, 2014 6:05 PM
  • Yep, That is so bad :-)

    At first glance that look exactly like the problem I mentioned above. It is look just like my story on improving from one hour to less then 4 second :-)

    I can not give you the specific architecture that fit you in the forum (not that I dont want, but just because I dont familiar your system, needs, etc), and as I mention I will not have time for this in the forum, but I will give you some "golden rules" that probably will help you a lot.  those recommendations are not the best solution, but according the poor information I have, It is the best that I assume will fit you at this time :-)

    1. Stop use this view!!! if you want to use view and you need 3 tables then use 3 tables in the view!

    2. Join is very very very... very expensive operation. Dont JOIN tables that you dont need!

    3. If there is more then 3-4 JOIN in a view (the number can change from one system to another, and again it is just a golden rule) then the SQL Server do not build a good executing plan and it start to join tables even if we dont need them. Since you have filter as well the server have to execute that filter as well in that case and the result might be 1 hour instead of 1 second. So the rule will be: Not more then 3 JOIN in a view! if you need 4 then JOIN the result of the three with the fourth table (at this time, just to keep it as a rule).

    4. At this time while your people do not know how to optimize queries or build a good architecture best option is to leave it simple. Dont use any view (this is my recommendation for you at this time). Just use direct query from the tables.

    5. It is highly recommend to take some outsourcing help, if you can not hire in-house DBA!

    I hope this will be useful and it was not too harsh :-)


    [Personal Site] [Blog] [Facebook]signature

    Friday, May 23, 2014 8:35 PM
  • Okay thanks

    I got this. I will try to study more. You have made some clearance about those views.

    Thanks a lot.

    Saturday, May 24, 2014 3:10 AM
  • You are most welcome :-)

    [Personal Site] [Blog] [Facebook]signature

    Saturday, May 24, 2014 9:24 AM