Recreate table -- While in use

  • This table (a denormalized "shadow" table) is used in a form that allows it to be "refreshed" on demand. Unfortunately, the way I do that now is to truncate the table, then insert the new records from a query that could take 10 minutes to run. That's not so good for multiple users....

    I want to write a stored procedure that recreates the table from a rather complex query while the table is in use. I assume this means creating a temp table, then truncating the production table, then inserting the new rows from the temp table.

    I'm new at SS, but I am a programmer. I don't know the strategy for this, so the table can be "unavailable" or whatever to other users for as short a time as possible....

    Jim

  • SNAPSHOT ISOLATION is 1 option but this will increase the load in tempdb.

    One other option is to build a local temp table in your stored procedure. Take all the time you need to fill it. Then once that part is done you can do the truncate / reload (could do merge with 2K8).

    The real option here would also be to tune this procedure to run subsecond which I can gladly help with if you can send me the actual execution plan and the code of the proc.

  • I'm really impressed you'd offer. Thank you so much...but....

    The query summarizes budgets and expenses for projects, equipment, and category. There may be projects with expenses and no budgets, budgets with no expenses, and so on with equipment and category. Thus, I end up summing up 6 expense tables (a couple have half a million rows...) to be sure I have identified all the possible combinations, then using the query that identifies the combinations to drive the summing up again of the expense and budget detail (sums to maybe 5,000 rows.) It's a horrible mess, that probably belongs in some sort of analysis cube, but I haven't learned how to use SSAS yet, and truthfully, the app is still in Access for now.

    I have put the final table into a SS2k5 table (the subject of this post), and I'm about 80% done converting the whole app (105 tables, more than a hundred queries, more than 100 forms in the FE, etc....)

    I need to, I think, execute the Access query using a (stored procedure?/function?) to create the rows, then the truncate/insert sequence should be subsecond, as you suggest. But I'm open to your advice.

    Thanks

    Jim

  • Wow this is not a small query (sent via PM). Ideally you'd repost it here but I understand if you can't or don't want to... but plz understand we can't fix what we don't see ;-).

    What I wanted is the actual query plan.

    You can copy the query to SSMS. Then in the query menu, hit "include actual plan".

    Run the query and then save that as .sqlplan file and upload it here (big graphical thing that appears in the results). We can decipher than and help point a few things out.

  • PS I run reports live from the prod DB (sub-optimal setup) with larger tables than yours and I usually get the results sub-second. My longest reports run under 1 minute with 3000 lines of code and basically spit out the whole DB ;-).

    So there's room for improvement in your query AFAICS

  • OK, but it's a massive thing. Since it's an Access query, it uses sum queries instead of subqueries. All of the subqueries simply (right....) add up budgets or expenses by Project, MICAP, Discipline. Some budgets/expenses have no MICAP and/or No Discipline, and those default to 0 and 40 respectively....

    INSERT INTO tblCompositeProjectSummary2 ( CERNbr, Project, ProjectDescription, CapitalExpense, MICAP, ToolDescription, PricingProgram, Budget, ODD, CCD, StaffAugStartDate, StaffAugEndDate, Duration, [Staff Aug Std Hrs], [Staff Aug OT Hrs], [Staff Aug Prem Hrs], TotalStaffAugSpend, [Virtual Hours], [PO Committed], POTotalAmtPaid, [Unregulated Inventory], [Internal Std Hrs], [Internal OT Hrs], InternalLaborCost, [MATREQ Issues], [PCard Spending], ScopeDescription, StatusDescription, MICAPID, ProjectID, Hex, TotHrsBud, SCRTot, RFI, ProjectStatus, CPMICAPBudget, ProjNbr )

    SELECT tblCER.CERNbr, tblProjects.ProjectNbr AS Project, tblProjects.ProjectDescription, tblProjects.CapitalExpense, tblMICAP.MICAP, tblMICAP.ToolDescription, xrefProjectMICAP.PricingProgram, tblProjects.Projectbudget AS Budget, xrefProjectMICAP.ODD, xrefProjectMICAP.CCD, qryStaffAugSpending.StaffAugStartDate, qryStaffAugSpending.StaffAugEndDate, DateDiff("d",[staffaugstartdate],[staffaugenddate]) AS Duration, qryStaffAugSpending.SumOfStdHours AS [Staff Aug Std Hrs], qryStaffAugSpending.SumOfOTHours AS [Staff Aug OT Hrs], qryStaffAugSpending.SumOfDTHours AS [Staff Aug Prem Hrs], qryStaffAugSpending.TotalStaffAugSpend, [sumofstdhours]+(1.5*[sumofothours])+(2*[sumofdthours]) AS [Virtual Hours], qryPOSpend.SumOfPOLineDollarValue AS [PO Committed], qryPOSpend.SumOfPOLineAmtPaid AS POTotalAmtPaid, qryInventorySpend.[Inventory Used] AS [Unregulated Inventory], qryIntLaborSpend.StdHrs AS [Internal Std Hrs], qryIntLaborSpend.OvtHrs AS [Internal OT Hrs], qryIntLaborSpend.InternalLaborCost, qryMatReqIssuesPCard.[MATREQ Issues], qryMatReqIssuesPCard.PCardAmt AS [PCard Spending], tblScope.ScopeDescription, tblProjectStatus.StatusDescription, qryxrefProjectIDMICAPID.MICAPID, qryxrefProjectIDMICAPID.ProjectID, xrefProjectMICAP.Hex, DSum("[hoursbudget]","tblWorkOrders","ProjectID=" & [qryxrefprojectIDMICAPID].[ProjectID]) AS TotHrsBud, DSum("nz([SCRROMDesignest],0)+nz([SCRROMConstest],0)","[tblSCR]","SCRProjectID=" & [qryxrefprojectIDMICAPID].[ProjectID]) AS SCRTot, DSum("RFICostEstimate","tblRFI","ProjectID=" & [qryxrefprojectIDMICAPID].[ProjectID]) AS RFI, tblProjects.ProjectStatus, qryCPBudgetybyProjectMicap.Budget, Val(Right([ProjectNbr],Len([ProjectNbr])-1)) AS Expr1

    FROM tblScope INNER JOIN (tblProjectStatus RIGHT JOIN (tblCER INNER JOIN ((((tblMICAP RIGHT JOIN (((((qryxrefProjectIDMICAPID LEFT JOIN qryPOSpend ON (qryxrefProjectIDMICAPID.MICAPID = qryPOSpend.MICAPID) AND (qryxrefProjectIDMICAPID.ProjectID = qryPOSpend.ProjectID)) LEFT JOIN qryStaffAugSpending ON (qryxrefProjectIDMICAPID.MICAPID = qryStaffAugSpending.MICAPID) AND (qryxrefProjectIDMICAPID.ProjectID = qryStaffAugSpending.ProjectID)) INNER JOIN tblProjects ON qryxrefProjectIDMICAPID.ProjectID = tblProjects.ID) LEFT JOIN qryInventorySpend ON (qryxrefProjectIDMICAPID.MICAPID = qryInventorySpend.MICAPID) AND (qryxrefProjectIDMICAPID.ProjectID = qryInventorySpend.ProjectID)) LEFT JOIN qryIntLaborSpend ON (qryxrefProjectIDMICAPID.MICAPID = qryIntLaborSpend.MICAPID) AND (qryxrefProjectIDMICAPID.ProjectID = qryIntLaborSpend.ProjectID)) ON tblMICAP.ID = qryxrefProjectIDMICAPID.MICAPID) LEFT JOIN xrefProjectMICAP ON (qryxrefProjectIDMICAPID.ProjectID = xrefProjectMICAP.ProjectID) AND (qryxrefProjectIDMICAPID.MICAPID = xrefProjectMICAP.MICAPID)) LEFT JOIN qryCPBudgetybyProjectMicap ON (qryxrefProjectIDMICAPID.MICAPID = qryCPBudgetybyProjectMicap.MICAPID) AND (qryxrefProjectIDMICAPID.ProjectID = qryCPBudgetybyProjectMicap.CPProjectID)) LEFT JOIN qryMatReqIssuesPCard ON (qryxrefProjectIDMICAPID.MICAPID = qryMatReqIssuesPCard.MICAPID) AND (qryxrefProjectIDMICAPID.ProjectID = qryMatReqIssuesPCard.ProjectID)) ON tblCER.CERID = tblProjects.CERID) ON tblProjectStatus.StatusID = tblProjects.ProjectStatus) ON tblScope.ScopeID = tblProjects.ProjectScope

    ORDER BY Val(Right([ProjectNbr],Len([ProjectNbr])-1)) DESC , tblMICAP.MICAP DESC;

    Jim

  • On another note, see the "Val" function? My project numbers are of the form "C99999", though a few are totally non-numeric. Val() works nicely by returning a zero for mal-formed project numbers while returning the numeric part of well-formed project numbers.

    Any ideas about how to replicate this feature in SS2k5?

    Jim

  • P.S. I really need to see the execution plan to pinpoint the real problems.

    Easier to read this way.

    SELECT

    tblCER.CERNbr

    , tblProjects.ProjectNbr AS Project

    , tblProjects.ProjectDescription

    , tblProjects.CapitalExpense

    , tblMICAP.MICAP

    , tblMICAP.ToolDescription

    , xrefProjectMICAP.PricingProgram

    , tblProjects.Projectbudget AS Budget

    , xrefProjectMICAP.ODD

    , xrefProjectMICAP.CCD

    , qryStaffAugSpending.StaffAugStartDate

    , qryStaffAugSpending.StaffAugEndDate

    , DateDiff("d" , [staffaugstartdate] , [staffaugenddate]) AS Duration

    , qryStaffAugSpending.SumOfStdHours AS [Staff Aug Std Hrs]

    , qryStaffAugSpending.SumOfOTHours AS [Staff Aug OT Hrs]

    , qryStaffAugSpending.SumOfDTHours AS [Staff Aug Prem Hrs]

    , qryStaffAugSpending.TotalStaffAugSpend

    , [sumofstdhours] + ( 1.5 * [sumofothours] ) + ( 2 * [sumofdthours] ) AS [Virtual Hours]

    , qryPOSpend.SumOfPOLineDollarValue AS [PO Committed]

    , qryPOSpend.SumOfPOLineAmtPaid AS POTotalAmtPaid

    , qryInventorySpend.[Inventory Used] AS [Unregulated Inventory]

    , qryIntLaborSpend.StdHrs AS [Internal Std Hrs]

    , qryIntLaborSpend.OvtHrs AS [Internal OT Hrs]

    , qryIntLaborSpend.InternalLaborCost

    , qryMatReqIssuesPCard.[MATREQ Issues]

    , qryMatReqIssuesPCard.PCardAmt AS [PCard Spending]

    , tblScope.ScopeDescription

    , tblProjectStatus.StatusDescription

    , qryxrefProjectIDMICAPID.MICAPID

    , qryxrefProjectIDMICAPID.ProjectID

    , xrefProjectMICAP.Hex

    , DSum("[hoursbudget]" , "tblWorkOrders" ,

    "ProjectID=" & [qryxrefprojectIDMICAPID].[ProjectID]) AS TotHrsBud

    , DSum("nz([SCRROMDesignest],0)+nz([SCRROMConstest],0)" , "[tblSCR]" ,

    "SCRProjectID=" & [qryxrefprojectIDMICAPID].[ProjectID]) AS SCRTot

    , DSum("RFICostEstimate" , "tblRFI" ,

    "ProjectID=" & [qryxrefprojectIDMICAPID].[ProjectID]) AS RFI

    , tblProjects.ProjectStatus

    , qryCPBudgetybyProjectMicap.Budget

    , Val(Right([ProjectNbr] , Len([ProjectNbr]) - 1)) AS Expr1

    FROM

    tblScope

    INNER JOIN (

    tblProjectStatus

    RIGHT JOIN (

    tblCER

    INNER JOIN (

    (

    (

    (

    tblMICAP

    RIGHT JOIN (

    (

    (

    (

    (

    qryxrefProjectIDMICAPID

    LEFT JOIN qryPOSpend

    ON ( qryxrefProjectIDMICAPID.MICAPID = qryPOSpend.MICAPID )

    AND ( qryxrefProjectIDMICAPID.ProjectID = qryPOSpend.ProjectID )

    )

    LEFT JOIN qryStaffAugSpending

    ON ( qryxrefProjectIDMICAPID.MICAPID = qryStaffAugSpending.MICAPID )

    AND ( qryxrefProjectIDMICAPID.ProjectID = qryStaffAugSpending.ProjectID )

    )

    INNER JOIN tblProjects

    ON qryxrefProjectIDMICAPID.ProjectID = tblProjects.ID

    )

    LEFT JOIN qryInventorySpend

    ON ( qryxrefProjectIDMICAPID.MICAPID = qryInventorySpend.MICAPID )

    AND ( qryxrefProjectIDMICAPID.ProjectID = qryInventorySpend.ProjectID )

    )

    LEFT JOIN qryIntLaborSpend

    ON ( qryxrefProjectIDMICAPID.MICAPID = qryIntLaborSpend.MICAPID )

    AND ( qryxrefProjectIDMICAPID.ProjectID = qryIntLaborSpend.ProjectID )

    )

    ON tblMICAP.ID = qryxrefProjectIDMICAPID.MICAPID

    )

    LEFT JOIN xrefProjectMICAP

    ON ( qryxrefProjectIDMICAPID.ProjectID = xrefProjectMICAP.ProjectID )

    AND ( qryxrefProjectIDMICAPID.MICAPID = xrefProjectMICAP.MICAPID )

    )

    LEFT JOIN qryCPBudgetybyProjectMicap

    ON ( qryxrefProjectIDMICAPID.MICAPID = qryCPBudgetybyProjectMicap.MICAPID )

    AND ( qryxrefProjectIDMICAPID.ProjectID = qryCPBudgetybyProjectMicap.CPProjectID )

    )

    LEFT JOIN qryMatReqIssuesPCard

    ON ( qryxrefProjectIDMICAPID.MICAPID = qryMatReqIssuesPCard.MICAPID )

    AND ( qryxrefProjectIDMICAPID.ProjectID = qryMatReqIssuesPCard.ProjectID )

    )

    ON tblCER.CERID = tblProjects.CERID

    )

    ON tblProjectStatus.StatusID = tblProjects.ProjectStatus

    )

    ON tblScope.ScopeID = tblProjects.ProjectScope

    ORDER BY

    Val(Right([ProjectNbr] , Len([ProjectNbr]) - 1)) DESC

    , tblMICAP.MICAP DESC ;

  • JimS-Indy (7/28/2011)


    On another note, see the "Val" function? My project numbers are of the form "C99999", though a few are totally non-numeric. Val() works nicely by returning a zero for mal-formed project numbers while returning the numeric part of well-formed project numbers.

    Any ideas about how to replicate this feature in SS2k5?

    Do you want something like IsAllDigits?

    Also that explains part of your issue. If you use only native sql functions, the query will run exclusively on sql server. But if you use access function or code then ALL the data has to be send to access which then processes it (WAY slower).

  • I'll read up on IsAllDigits....

    You misunderstand. I've not yet converted the query to SQL Server. You've shamed me into getting to it, but its final implementation will have to wait until I can finish other conversions and have a working SQL Server back end. I test one form at a time and find issues, either efficiency issues or show stoppers. Like, for example, SS won't allow you to update a query that references multiple tables, while Access will. I have a parent/child structure where the parent can have zero or one child (since there are only children in 10% of the parents, it didn't make sense to make them "full-time" fields.) So, now, I have to alter the forms that update those two tables (formerly updated as a single query/view) so the two tables are maintained separately. Taking me a long time....

    I am converting the query now as a SS2k5 view.

    Jim

  • IsAllDigits is not a standard function, you have to build it yourself. There is isnumeric() but it allows a lot more characters than I think you may want.

    SQL server can update a view, not sure how to set that up in Access (I know I've done it but that was 7 years ago).

  • With a little thought, I was able to use the IsNumeric function:

    ProjNbr: IIf(IsNumeric(Right([ProjectNbr],Len([Projectnbr])-1)),CInt(Right([ProjectNbr],Len([Projectnbr])-1)),0)

    Which SSMA will translate nicely into a case statement.

    Jim

  • I think this works better...

    DECLARE @Col VARCHAR(100)

    SET @Col = 'A1234567890'

    --if the string needs to start with any character (only allowing 1)

    SELECT CASE WHEN @Col LIKE '_[0-9]%' THEN 'Ok' ELSE 'Problem' END AS test

    --if the string can only contain numbers

    --Double negation. Find me any characters outside the 0-9 range. If you don't find any then they're all numbers

    SELECT CASE WHEN @Col NOT LIKE '%[^0-9]%' THEN 'OK' ELSE 'Problem' END AS test

    --in your code, replae @Col with the column in a table:

    SELECT CASE WHEN name NOT LIKE '%[^0-9]%' THEN 'OK' ELSE 'Problem' END AS test, name As ColName FROM sys.columns ORDER BY test, ColName

  • PS, it's SSMS (management studio)

  • Sql Server Migration Assistant

    Jim

Viewing 15 posts - 1 through 15 (of 31 total)

You must be logged in to reply to this topic. Login to reply