Maintenance Plan Question

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Maintenance Plan Question

Maintenance Plan Question

Rate Topic

Display Mode

Topic Options

Author

Message

kabaari

Posted Wednesday, February 20, 2013 10:28 PM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 5:54 AM
Points: 87, Visits: 287

I 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.

Post #1422400

Phil Parkin

Posted Thursday, February 21, 2013 2:07 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 9:39 AM
Points: 4,247, Visits: 9,500

I'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 ...

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1422441

kabaari

Posted Thursday, February 21, 2013 1:01 PM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 5:54 AM
Points: 87, Visits: 287

GO

Drop view AssemblyDefects

GO

create View AssemblyDefects AS

(select p.model, i.defect_title, p.item, d.sn, d.dateinspected, t.type, i.defect_id
from productiondefect as d, assignworkorder as p, tlkp_defects as i, tlkp_item as t
where substring(d.sn, 7, 5) = p.workorder
and d.defect_id = i.defect_id
and  p.item = t.item) 

GO


DROP Table ADR

GO

select *
into ADR
from AssemblyDefects

Post #1422760

e4d4

Posted Thursday, February 21, 2013 1:17 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 1:54 PM
Points: 151, Visits: 1,039

Rewrite the query or maybe creating a computed column on substring(dsn, 7, 5) and creating index on that column will help this query.

Post #1422769

Michael L John

Posted Thursday, February 21, 2013 1:21 PM

Right there with Babe

Group: General Forum Members
Last Login: Yesterday @ 10:38 AM
Points: 742, Visits: 2,088

Do 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_id
from
(SELECT sn,
substring(sn, 7, 5) as [sn_join]
dateinspected,
defect_id
FROM productiondefect) D
INNER JOIN assignworkorder p ON d.[sn_join] = p.workorder
INNER JOIN tlkp_defects i ON d.defect_id = i.defect_id
INNER JOIN tlkp_item as t ON p.item = t.item

Michael L John
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Post #1422772

« Prev Topic | Next Topic »

Permissions