Lookup Speed

[mcolen]

Is there any speed difference in using a stage variable of If not(lnkLkUpBranch.NOTFOUND) and lnkLkUpBranch.Source_System_Id = 15 Then @TRUE Else @FALSE or setting as a constraint on a write to a hashed file of -- not(lnkLkUpBranch.NOTFOUND) and lnkLkUpBranch.Source_System_Id = 15 ????

[DSguru2B]

I dont think it will make a considerable amount of difference. Its just that stage varaibles get executed first, so the @TRUE or @FALSE will be decided before the row comes in.

[mcolen]

seems to make a large speed diff 220m source rows coming from hashed file to 2 hashed files based on source_cd

[meena]

Hi
I prefer to write a constraint instead of other option..

[thumsup9]

I find improved times when I put the logic in stage variables..may be someone can explain better

[Krazykoolrohit]

seems to make a large speed diff 220m source rows coming from hashed file to 2 hashed files based on source_cd

It will make a differenece. true

but not something you would stand up and take notice of. It should be minor. thats what DSguru2B meant i suppose.

[mcolen]

Since I am speaking of a diff of over 600 rows a second with this amount of data we could be speaking of over 2 days diff to source this data

[kris007]

so what is the question now? You have the results with you. There will be a considerable amount of difference when you deal with very large number of rows as in your case. The speed difference increases as the data increases. How much? Thats a different question.

[mcolen]

I may have the answer but I was hoping for a confirm that I am correct from one of the DS experts ie Ray, Craig, Ken

[kcbland]

Each constraint is ALWAYS evaluated for every source row. Each stage variable is evaluated for every source row. If you have multiple output links, it makes sense to use a stage variable to hold a result if that result is needed in more than one constraint.

If you have one output link, there should be no difference between a constraint and a constraint using a stage variable except for the minor difference of one extra line to assign the stage variable each row.

You may have experienced a dirty cache in your testing. If you ran the job once, you may have had data sitting in the OS's reading/writing cache and felt the benefit when running the job a second time. This "warmed up" cache is handy sometimes, but probably not in your experiment.

[DSguru2B]

Technically speaking it shouldnt make much of a difference. But yes for large amount of records you might see that little difference add up and sum up.
I havent really played around with it so cant give you a definate answer. Maybe our gurus can provide some insight.
Actuallly if you think about it, the condition gets evaluated twice. Once in the stage variables and then in the constraint. It should be slower as compared to faster. Hmm, wierd