Speed issue with a query on a large Dataset (Help needed)

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" wrote in message

> 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.
>