• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 411
  • Last Modified:

Query Performance: TSql - Insert..Into Select From taking lot of time.

Sql Server 2005:

Just Select statement is taking about 45 seconds.  
But Insert Into...Select From iis taking lot of time, more than 10 hours some times.

Your suggestion is appreciated



INSERT INTO dbtemp.dbo.tbl_PLIB_CrossReferenced_Staging_Test
([PrimaryKey],[BIViewID],[CountryKey],[RegionKey],[SrvOrdKey],[SrvOrd],[SrvOrdCreatedDateKey],[SrvOrdCreatedTimeKey],[SrvOrdCreatedDate],[SrvOrdLocOriginKey]
,[SrvOrdLocDestinationKey],[SrvOrdCACWarrantyKey],[SrvOrdCSAWarrantyKey],[SrvOrdDecisionKey],[SrvOrdStatusKey],[SrvOrdCategoryKey],[CarrierKey],[ModelKey]
,[SerialNbrKey],[SerialNbr],[UnitProcessStepKey],[ReincidenceKey],[LegKey],[LegProcStepInitSODateKey],[LegProcStepInitSOTimeKey],[LegProcStepInitSODate]
,[LegProcStepInitDateKey],[LegProcStepInitTimeKey],[LegProcStepInitDate],[LegProcStepStopSODateKey],[LegProcStepStopSOTimeKey],[LegProcStepStopSODate]
,[LegProcStepStopDateKey],[LegProcStepStopTimeKey],[LegProcStepStopDate],[LegResponsibleInitKey],[LegResponsibleStopKey],[LegRangeKey],[LegSLAKey]
,[JustificationKey],[SLAStatusKey],[AdjSLAStatusKey],[JustificationComments],[JustificationDateKey],[JustificationTimeKey],[JustificationDate]
,[UnitFailures],[UnitSolutions],[CustomerVIP],[LegCost],[LegSLATarget],[LegDifference],[CrtDate],[BIViewTimestamp])
SELECT C.[PrimaryKey],C.[BI_VIEW_ID],DC.[CountryKey],DR.[RegionKey],DS.[SrvOrdKey],C.[SERVICE_ORDER_ID],DD1.[DateKey],DT1.[TimeKey],C.SERVICE_ORDER_CREATION,DL1.[SrvOrdLocationKey]
,DL2.[SrvOrdLocationKey],DW1.[SrvOrdLocWarrantyKey],DW2.[SrvOrdLocWarrantyKey],DSD.[SrvOrdDecisionKey],DSS.[SrvOrdStatusKey],DSC.[SrvOrdCategoryKey],DCR.[CarrierKey],DM.[ModelKey]
,DSN.[SerialNbrKey],C.[UNIT_SERIAL_NUMBER],DUP.[UnitProcessStepKey],DRD.[ReincidenceKey],DL.[LegKey],DD2.[DateKey],DT2.[TimeKey],C.[LEG_PROCESS_STEP_INIT_SO_DATE]
,DD3.[DateKey],DT3.[TimeKey],C.[LEG_PROCESS_STEP_INIT_DATE],DD4.[DateKey],DT4.[TimeKey],C.[LEG_PROCESS_STEP_STOP_SO_DATE]
,DD5.[DateKey],DT5.[TimeKey],C.[LEG_PROCESS_STEP_STOP_DATE],DRS1.[LegResponsibleKey],DRS2.[LegResponsibleKey],DLR.[LegRangeKey],DLSLA.[LegSLAKey]
,DJF.[JustificationKey],DSLAS1.[SLAStatusKey],DSLAS2.[SLAStatusKey],C.[JUSTIFICATION_COMMENTS],DD6.[DateKey],DT6.[TimeKey],C.[JUSTIFICATION_DATE]
,C.[UNIT_FAILURES],C.[UNIT_SOLUTIONS],C.[CUSTOMER_VIP],C.[LEG_COST],C.[LEG_SLA_TARGET],C.[LEG_DIFFERENCE],C.[CrtDate],C.[BI_VIEW_TIMESTAMP]
FROM dbtemp.dbo.tbl_PLIB_CrossReferenced C (nolock)
INNER JOIN dbo.tbl_PLIB_dimCountry DC (NOLOCK) ON C.COUNTRY_ID = DC.CountryID
INNER JOIN dbo.tbl_PLIB_dimRegion DR (NOLOCK) ON C.REGION_ID = DR.RegionID
INNER JOIN dbo.tbl_PLIB_dimSrvOrd DS (NOLOCK) ON C.SERVICE_ORDER_ID = DS.SrvOrd
INNER JOIN dbo.tbl_PLIB_dimDate DD1 (NOLOCK) ON  CAST(Convert(varchar(10),C.SERVICE_ORDER_CREATION,101) as datetime)= DD1.Date
INNER JOIN dbo.tbl_PLIB_dimTime DT1 (NOLOCK) ON  Convert(varchar(8),C.SERVICE_ORDER_CREATION,108) = DT1.Time        
INNER JOIN dbo.tbl_PLIB_dimSrvOrdLocation DL1 (NOLOCK) ON C.SERVICE_ORDER_OriLocID = DL1.SrvOrdLocationID
INNER JOIN dbo.tbl_PLIB_dimSrvOrdLocation DL2 (NOLOCK) ON C.SERVICE_ORDER_DestLocID = DL2.SrvOrdLocationID      
INNER JOIN dbo.tbl_PLIB_dimSrvOrdLocWarranty DW1 (NOLOCK) ON c.SERVICE_ORDER_CAC_WRT_ID = DW1.WarrantyID
INNER JOIN dbo.tbl_PLIB_dimSrvOrdLocWarranty DW2 (NOLOCK) ON c.SERVICE_ORDER_CSA_WRT_ID = DW2.WarrantyID      
INNER JOIN dbo.tbl_PLIB_dimSrvOrdDecision DSD (NOLOCK) ON C.SERVICE_ORDER_DECISION_ID = DSD.DecisionID      
INNER JOIN dbo.tbl_PLIB_dimSrvOrdStatus DSS (NOLOCK) ON c.SERVICE_ORDER_STATUS = LTRIM(RTRIM(DSS.SrvOrdStatus))      
INNER JOIN dbo.tbl_PLIB_dimSrvOrdCategory DSC (NOLOCK) ON c.SERVICE_ORDER_CATEGORY = LTRIM(RTRIM(DSC.SrvOrdCategory))      
INNER JOIN dbo.tbl_PLIB_dimCarrier DCR (NOLOCK) ON  C.UNIT_CARRIER_ID = DCR.CarrierID          
INNER JOIN dbo.tbl_PLIB_dimModel DM (NOLOCK) ON C.UNIT_MODEL_ID= DM.ModelID      
INNER JOIN dbo.tbl_PLIB_dimSerialNbr DSN (NOLOCK) ON  c.UNIT_SERIAL_NUMBER = DSN.SerialNbr  
INNER JOIN dbo.tbl_PLIB_dimUnitProcessStep DUP (NOLOCK) ON c.UNIT_PROCESS_STEP = LTRIM(RTRIM(DUP.UnitProcessStep))      
INNER JOIN dbo.tbl_PLIB_dimReincidence DRD (NOLOCK) ON c.UNIT_REINCIDENCE = LTRIM(RTRIM(DRD.Reincidence))      
INNER JOIN dbo.tbl_PLIB_dimLeg  DL (NOLOCK) ON c.LEG_ID = DL.LegID      
INNER JOIN dbo.tbl_PLIB_dimDate DD2 (NOLOCK) ON  CAST(Convert(varchar(10),C.LEG_PROCESS_STEP_INIT_SO_DATE,101) as datetime)= DD2.Date
INNER JOIN dbo.tbl_PLIB_dimTime DT2 (NOLOCK) ON  Convert(varchar(8),C.LEG_PROCESS_STEP_INIT_SO_DATE,108) = DT2.Time      
INNER JOIN dbo.tbl_PLIB_dimDate DD3 (NOLOCK) ON  CAST(Convert(varchar(10),C.LEG_PROCESS_STEP_INIT_DATE,101) as datetime)= DD3.Date
INNER JOIN dbo.tbl_PLIB_dimTime DT3 (NOLOCK) ON  Convert(varchar(8),C.LEG_PROCESS_STEP_INIT_DATE,108) = DT3.Time          
INNER JOIN dbo.tbl_PLIB_dimDate DD4 (NOLOCK) ON  CAST(Convert(varchar(10),C.LEG_PROCESS_STEP_STOP_SO_DATE,101) as datetime)= DD4.Date
INNER JOIN dbo.tbl_PLIB_dimTime DT4 (NOLOCK) ON  Convert(varchar(8),C.LEG_PROCESS_STEP_STOP_SO_DATE,108) = DT4.Time                  
INNER JOIN dbo.tbl_PLIB_dimDate DD5 (NOLOCK) ON  CAST(Convert(varchar(10),C.LEG_PROCESS_STEP_STOP_DATE,101) as datetime)= DD5.Date
INNER JOIN dbo.tbl_PLIB_dimTime DT5 (NOLOCK) ON  Convert(varchar(8),C.LEG_PROCESS_STEP_STOP_DATE,108) = DT5.Time        
INNER JOIN dbo.tbl_PLIB_dimLegResponsible DRS1 (NOLOCK) ON  C.LEG_RESPONSABLE_INIT_ID = DRS1.ResponsibleID      
INNER JOIN dbo.tbl_PLIB_dimLegResponsible DRS2 (NOLOCK) ON  C.LEG_RESPONSABLE_STOP_ID = DRS2.ResponsibleID      
INNER JOIN dbo.tbl_PLIB_dimLegRange  DLR (NOLOCK) ON C.LEG_RANGE_ID = DLR.RangeID      
INNER JOIN dbo.tbl_PLIB_dimLegSLA DLSLA (NOLOCK) ON C.LEG_SLA_ID = DLSLA.SLAID      
INNER JOIN dbo.tbl_PLIB_dimJustification DJF (NOLOCK) ON C.JUSTIFICATION_ID = DJF.JustificationID      
INNER JOIN dbo.tbl_PLIB_dimSLAStatus DSLAS1 (NOLOCK) ON LTRIM(RTRIM(C.JUSTIFICATION_CLASSIF_INIT)) = LTRIM(RTRIM(DSLAS1.SLAStatusName))
INNER JOIN dbo.tbl_PLIB_dimSLAStatus DSLAS2 (NOLOCK) ON LTRIM(RTRIM(C.JUSTIFICATION_CLASSIF_FINAL)) = LTRIM(RTRIM(DSLAS2.SLAStatusName))      
INNER JOIN dbo.tbl_PLIB_dimDate DD6 (NOLOCK) ON  CAST(Convert(varchar(10),C.JUSTIFICATION_DATE,101) as datetime)= DD6.Date
INNER JOIN dbo.tbl_PLIB_dimTime DT6 (NOLOCK) ON  Convert(varchar(8),C.JUSTIFICATION_DATE,108) = DT6.Time              
0
kris norman
Asked:
kris norman
1 Solution
 
Jesus RodriguezIT ManagerCommented:
Get the select statetement into a dataset and then the value from the dataset pass it to the SQL
Be sure that the Dataset will have the same structure(fields) that will return the query

Will be something like this more or less

Dim tble as new dataset
DIm Qry as string="Your query"
Dim QryInsert as string=""
Dim AdTabl as new SqldataAdapter(Qry,YourConnectionString)
Tble.Clear()
AdTabl.Fill(tble)
For I as Integer=0 to Tble.rows.count() - 1
  QryInst="INSERT INTO YOURTABLE(field1,field2,...,FieldN) VALUES('"& tbl.rows(i).item(0) &"','"& tbl.rows(i).item(0) &"',...,'"& tbl.rows(i).item(N) &"')"
 DIm Tbl2 as new datatable
 Dim AdT2 as new sqldataadapter(qryInst,YourSqlConnection)
 tbl2.clear()
 Adt2.fill(Tbl2)
  Next
0
 
Scott PletcherSenior DBACommented:
You need to check the autogrowth amounts for dbtemp db.  If the SELECT runs that fast (which is remarkable, given all the manipulation it's doing on join values), It looks like the db is taking huge amounts of time to add the space to the db data and/or log files to store the rows.

You should pre-allocate enough db space to handle the INSERT before it starts.  Also make sure you specify a fixed amount for growth and not a percentage.

Also, make sure SQL is able to use IFI (Instant File Initialization) by giving the necessary Windows permission to the service account running SQL Server.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now