I have a Staging table with 29401560 records in it. No Indexes on this table
as it is the table loaded from my SSIS. I am performing an update on it using
the following query.

Update t1
Set
t1.PolicyID = t2.PolicyID
,t1.ValidStartDate = @StartDateTime
,t1.PolicyExt = Substring(t1.Policy,10,1)
From
[Staging].[dbo].[FCIAPLCYLogKey] t1
Left Join
[Policy].[dbo].[sprPolicy] t2
On
t2.PolicyNum = Left(t1.Policy,9)

The [sprPolicy] table is indexed. My problem is this query runs for over 30
minutes everytime and I need it to be under 10 minutes. Any suggestions would
be greatly appreciated.

well... doing 1 large update is not recommended.
you can do a loop of small batch instead-of 1 big (you can find a lot of
articles about this)

also, from what I see, you "t2.PolicyNum = Left(t1.Policy,9) " can break the
performance.
SQL Server can't use any index on this.
If you add a column "Policy9" in the staging table which contains the 9
characters to compare, then SQL Server can index this and improve the
performance. (add this column in the staging and change your SSIS package to
do the transformation)

but if you can do this during your loading instead-of an update, the
performance will be far better.
Sometimes, if you execute the query and load it into another table the
performance is better than doing an update.

the best way is to do this join in the current loading process in the
dataflow which insert the rows into the table, do the join here, everything
will be applied in "1 step"

good luck.


"Wildlife" <Wildlife@discussions.microsoft.com> wrote in message
news:2C0612A1-466D-4A8D-B2B4-D3F8D991E7D9@microsoft.com...
> I have a Staging table with 29401560 records in it. No Indexes on this
> table
> as it is the table loaded from my SSIS. I am performing an update on it
> using
> the following query.
>
> Update t1
> Set
> t1.PolicyID = t2.PolicyID
> ,t1.ValidStartDate = @StartDateTime
> ,t1.PolicyExt = Substring(t1.Policy,10,1)
> From
> [Staging].[dbo].[FCIAPLCYLogKey] t1
> Left Join
> [Policy].[dbo].[sprPolicy] t2
> On
> t2.PolicyNum = Left(t1.Policy,9)
>
> The [sprPolicy] table is indexed. My problem is this query runs for over
> 30
> minutes everytime and I need it to be under 10 minutes. Any suggestions
> would
> be greatly appreciated.
>

If you're doing this in SSIS, in your update task choose the option to
commit every 1000 or whatever number of records depending on the length of
your table.

Also, research sql server update hotspots or update hotspots for more info

--
Sincerely,
John K
Knowledgy Consulting
www.knowledgy.org

Atlanta's Business Intelligence and Data Warehouse Experts


"Wildlife" <Wildlife@discussions.microsoft.com> wrote in message
news:2C0612A1-466D-4A8D-B2B4-D3F8D991E7D9@microsoft.com...
>I have a Staging table with 29401560 records in it. No Indexes on this
>table
> as it is the table loaded from my SSIS. I am performing an update on it
> using
> the following query.
>
> Update t1
> Set
> t1.PolicyID = t2.PolicyID
> ,t1.ValidStartDate = @StartDateTime
> ,t1.PolicyExt = Substring(t1.Policy,10,1)
> From
> [Staging].[dbo].[FCIAPLCYLogKey] t1
> Left Join
> [Policy].[dbo].[sprPolicy] t2
> On
> t2.PolicyNum = Left(t1.Policy,9)
>
> The [sprPolicy] table is indexed. My problem is this query runs for over
> 30
> minutes everytime and I need it to be under 10 minutes. Any suggestions
> would
> be greatly appreciated.
>

If you look at the Execution Plan is it an Index Scan or seek on the Left
Join?

--

Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com




"Wildlife" <Wildlife@discussions.microsoft.com> wrote in message
news:2C0612A1-466D-4A8D-B2B4-D3F8D991E7D9@microsoft.com...
>I have a Staging table with 29401560 records in it. No Indexes on this
>table
> as it is the table loaded from my SSIS. I am performing an update on it
> using
> the following query.
>
> Update t1
> Set
> t1.PolicyID = t2.PolicyID
> ,t1.ValidStartDate = @StartDateTime
> ,t1.PolicyExt = Substring(t1.Policy,10,1)
> From
> [Staging].[dbo].[FCIAPLCYLogKey] t1
> Left Join
> [Policy].[dbo].[sprPolicy] t2
> On
> t2.PolicyNum = Left(t1.Policy,9)
>
> The [sprPolicy] table is indexed. My problem is this query runs for over
> 30
> minutes everytime and I need it to be under 10 minutes. Any suggestions
> would
> be greatly appreciated.
>