InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 15:19:40 GMT20http://www.sqlservercentral.com/Forums/Topic1422400-338-1.aspxDo you know why dropping and recreating makes things go faster, and they gradually slow down?Do you have maintenance that updates statistics, and reindexes, if needed?Plus, if the code within the view is indicative of your coding, there is likely a lot of optimization that can occur.You are using the old style joins, which functionally are the same, but are not very readable. Also, doing the join on the substring(sn, 7, 5) is referred to as NON-sargable. Please research this further. I took the liberty of re-writing thie code. There are many other ways to do this, but since I am at the office, you are only getting the "quick" version.select p.model, i.defect_title, p.item, d.sn, d.dateinspected, t.type, i.defect_idfrom (SELECT sn, substring(sn, 7, 5) as [sn_join] dateinspected, defect_id FROM productiondefect) DINNER JOIN assignworkorder p ON d.[sn_join] = p.workorderINNER JOIN tlkp_defects i ON d.defect_id = i.defect_idINNER JOIN tlkp_item as t ON p.item = t.item Thu, 21 Feb 2013 13:21:22 GMTMichael L Johnhttp://www.sqlservercentral.com/Forums/Topic1422400-338-1.aspxRewrite the query or maybe creating a computed column on [i][b]substring(dsn, 7, 5)[/b][/i] and creating index on that column will help this query.Thu, 21 Feb 2013 13:17:36 GMTe4d4http://www.sqlservercentral.com/Forums/Topic1422400-338-1.aspx[code="sql"]GODrop view AssemblyDefectsGOcreate View AssemblyDefects AS(select p.model, i.defect_title, p.item, d.sn, d.dateinspected, t.type, i.defect_idfrom productiondefect as d, assignworkorder as p, tlkp_defects as i, tlkp_item as twhere substring(d.sn, 7, 5) = p.workorderand d.defect_id = i.defect_idand p.item = t.item) GODROP Table ADRGOselect *into ADRfrom AssemblyDefects[/code]Thu, 21 Feb 2013 13:01:25 GMTkabaarihttp://www.sqlservercentral.com/Forums/Topic1422400-338-1.aspxI'd say that the first thing you should try is some query optimisation - maybe you can get the query running fast enough so that you don't need to do this.Otherwise, I'd consider creating a physical table which has the query's structure and then building a stored proc which truncates/rebuilds this table using your query. Then you can call this stored proc as part of your Agent job & then use the table in what comes next ...Thu, 21 Feb 2013 02:07:12 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic1422400-338-1.aspxI have a extremely time consuming query that times out whenever it runs. To cure the issue, I created a view of the lengthy query then a table of the view. Next, I plan to add a maintenance plan that would Drop the view then create the view, then drop the table the create the table. Is this the most efficient method to achieve my goal of improve performance? The resulting query return large chunks of data very quickly versus timing out. I plan to post the plan some time in the morning.Thanks.Wed, 20 Feb 2013 22:28:40 GMTkabaari