Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Maintenance Plan Question


Maintenance Plan Question

Author
Message
kabaari
kabaari
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 365
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.
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8411 Visits: 19511
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
kabaari
kabaari
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 365
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


e4d4
e4d4
SSC Veteran
SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)

Group: General Forum Members
Points: 275 Visits: 2398
Rewrite the query or maybe creating a computed column on substring(dsn, 7, 5) and creating index on that column will help this query.
Michael L John
Michael L John
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2056 Visits: 7407
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
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search