January 10, 2006 at 1:52 am
Hi,
I have designed an SSIS package, which basically loops through a recordset, record by record and executes a Script task at each loop.
it is taking ages to run even though there are only 80000 records, so if you have any tips for speeding things up, that would be great.
Structure is
ForEachLoop
Recordset
ForEachLoop (each record, ADO)
Script Task
January 10, 2006 at 5:22 am
What task is it performing? I.e. why does it have to be done row by row and not via an entire SET?
Good Hunting!
AJ Ahrens
webmaster@kritter.net
January 11, 2006 at 1:45 am
How long is ages?
I'm not surprised - firing off 80000 tasks in ANY package, whether you're in a loop or not, is going to take a long time. "Ages" in fact!
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
January 11, 2006 at 2:49 am
hi and thanks for you answers.
i have the following columns in a flat file, which is not normalised, more an olap structure :
TIME PERIOD PRODUCT SALESPERSONID AMOUNT
200501 A 1 15$
200501 B 2 30$
200501 A 1 15$ 1
200502 A 2 10$
200502 C 3 45$
... ... ... ...
i have to add a category to each sales person depending on the amount sold.
this is done by ranking the sp's by time period by product ordered by descending amount.
then i calculate a running total per time period/product, then i divide the running total per record by the total amount for this sp for a period. that gives me a precentage per sp, and then i know which category they are in . for example cat A = 0% - 15%, cat B = 16% - 40%, cat C = 41% - 100%
which gives me this result
TIME PERIOD PRODUCT SALESPERSONID AMOUNT RANK RUNNING TOTAL % CATEGORY
200501 A 1 15$ 1 15$ 5 A
200501 A 2 20$ 2 35$ 45 C
200501 B 2 30$ 1
...
200502 A 2 10$ 1
200502 C 3 45$ 1
... ... ... ...
all this could be done with a query, i guess, but then, take the case where the percentage of B puts him in a higher category than A, but they have the same AMOUNT, I need to force B into the same category as A.
Also, another rule is that the first sp to be in the next category must be forced in the previous category.
these are business rules, and i don't see a way to do this otherwise than looping record to record.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply