Program Speed

I was rushed into putting live a new program for our datawarehouse load last
night without adequate testing.
The program took over 3 hours to run in approx 44000 records last night as
opposed to about 1 hour normally. The program uses a loop.
This is as a direct result of a change we put in for Vehicle Make Model code
s.
The program previously only ever ran into an Add vehicle Id program for non
existing vehicle_id's but due to the restructuring of the make model setup i
t
needed to run into this program for each record in the loop.
Due to no testing on our parrallel box, I could not evaluate the time impact
on the program.
I wonder could we talk about any methods I could use to speed up the process
.
It goes a little something like this...
-- IF @.vehicle_id IS NULL or @.vehicle_id = 0
EXECUTE usp_dim_vh_add_id
@.parms in @.vh_make_model, @.vh_group etc...
@.vehicle_id out
-- usp_dim_vh_add_id
SELECT @.make_id = a.Vh_Make_id, @.model_id = Vh_Model_id
FROM DB1..vht_lu_Make a, DB1..vht_lu_Model b
WHERE a.Vh_Make_Id = b.Vh_Make_Id
AND b.Vh_Make_Model_id = @.vh_make_model
UPDATE VHt_lu_Vehicle
SET Vh_make_model_code = @.vh_make_model,
Vh_make_id = @.make_id, Vh_model_id = @.model_id, WHERE vehicle_id =
@.vehicle_id
....
Any input here greatly appreciated.If any of the _id or _code are varchar, and the original value was NULL,
then updating the value to something not NULL may increase the length of the
row in such a way that it results in chaning the rows position in the page
or perhaps even page splitting. This results in more database I/O and
transaction logging.
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:0DF8E237-32BD-4D2C-9D49-74092F1613CD@.microsoft.com...
>I was rushed into putting live a new program for our datawarehouse load
>last
> night without adequate testing.
> The program took over 3 hours to run in approx 44000 records last night as
> opposed to about 1 hour normally. The program uses a loop.
> This is as a direct result of a change we put in for Vehicle Make Model
> codes.
> The program previously only ever ran into an Add vehicle Id program for
> non
> existing vehicle_id's but due to the restructuring of the make model setup
> it
> needed to run into this program for each record in the loop.
> Due to no testing on our parrallel box, I could not evaluate the time
> impact
> on the program.
> I wonder could we talk about any methods I could use to speed up the
> process.
> It goes a little something like this...
> -- IF @.vehicle_id IS NULL or @.vehicle_id = 0
> EXECUTE usp_dim_vh_add_id
> @.parms in @.vh_make_model, @.vh_group etc...
> @.vehicle_id out
> -- usp_dim_vh_add_id
> SELECT @.make_id = a.Vh_Make_id, @.model_id = Vh_Model_id
> FROM DB1..vht_lu_Make a, DB1..vht_lu_Model b
> WHERE a.Vh_Make_Id = b.Vh_Make_Id
> AND b.Vh_Make_Model_id = @.vh_make_model
> UPDATE VHt_lu_Vehicle
> SET Vh_make_model_code = @.vh_make_model,
> Vh_make_id = @.make_id, Vh_model_id = @.model_id, WHERE vehicle_id =
> @.vehicle_id
> ....
> Any input here greatly appreciated.
>
>|||examnotes <marcmc@.discussions.microsoft.com> wrote in
news:0DF8E237-32BD-4D2C-9D49-74092F1613CD@.microsoft.com:

> I was rushed into putting live a new program for our datawarehouse
> load last night without adequate testing.
> The program took over 3 hours to run in approx 44000 records last
> night as opposed to about 1 hour normally. The program uses a loop.
> This is as a direct result of a change we put in for Vehicle Make
> Model codes.
> The program previously only ever ran into an Add vehicle Id program
> for non existing vehicle_id's but due to the restructuring of the make
> model setup it needed to run into this program for each record in the
> loop.
> Due to no testing on our parrallel box, I could not evaluate the time
> impact on the program.
You could try tuning the sql statements. As datasets get better the
performance of a SQL and even the execution plan can change. So you'll need
to modify the SQL to increase the speed. If you want a tool that can do
this for you automatically, look at SQL Optimizer for Visual Studio. You
can download it from
http://www.extensibles.com/modules...=Products&op=NS

> I wonder could we talk about any methods I could use to speed up the
> process.
> It goes a little something like this...
> Any input here greatly appreciated.
>
>
The Relentless One
Debugging is a state of mind
http://www.extensibles.com/|||Not trying to pick this apart, but 44,000 records in an hour sounds
like an incredibly long time; 3 hours sounds like an eternity. Our
data warehouse processes 40,000 rows in about 2 minutes, and it's not a
massive server; I'm curious as to why it takes so long for you as well.
This is the first data warehouse I've designed; maybe I'm missing
something <G>
Just based off the sample code you sent, I'm curious as to why you are
doing row-at-a-time updates; can you not batch this?
DDL would help us give better hints.
Stu