July 28, 2011 at 7:55 am
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
July 28, 2011 at 8:01 am
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.
July 28, 2011 at 9:05 am
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
July 28, 2011 at 9:13 am
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.
July 28, 2011 at 9:15 am
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
July 28, 2011 at 9:28 am
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
July 28, 2011 at 9:41 am
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
July 28, 2011 at 9:46 am
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 ;
July 28, 2011 at 9:49 am
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).
July 28, 2011 at 10:59 am
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
July 28, 2011 at 11:06 am
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).
July 28, 2011 at 12:11 pm
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
July 28, 2011 at 3:30 pm
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
July 28, 2011 at 3:31 pm
PS, it's SSMS (management studio)
July 28, 2011 at 3:34 pm
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