Quick question - Criteria and enumeration

[ajaishankar]

Hi

Is it possible to do an Enumerate on a criteria than a List?

I see IQuery having an Enumerate method...

I'm trying to loop through the results similar to a data reader...

Thanks

Ajai

[Diego Mijelshon]

No, it's not possible. What do you need it for?

   Diego

--

You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.

[ajaishankar]

I am in a situation where I need to retrieve a list of customers (>
200K) and various metrics (over 15) for each customer.

Each one of the metrics collection code (such as #site_logins,
#csrcalls etc) are already neatly written in criteria queries - but on
a individual customer basis.

I was able to project each of these existing criteria into a group by
Customer.Id.

So now I have potentially a stream of data each detached criteria
generates:

Customers, CustomerLogin { CustomerId, NumLogin } , CSRCall
{ CustomerId, NumCalls } and so on...

I want to enumerate each result for some calculation and do not want
to load everything to memory since it is a large data set

And doing the final (left) join in detached criteria I think is quite
complex...

I am at a point where I'm pondering the following:

1. Is it possible to stick everything in a multi-criteria and do the
final join there itself and get just the last record set?

2. A criteria transformer into a query would be helpful so that I can
iterate enumerate the result

3. Also does NH support MARS (multiple active record set) in SQL
server where I potentially can stream ALL the data...

Ajai

On Dec 9, 5:33 am, Diego Mijelshon <di...@mijelshon.com.ar> wrote:
> No, it's not possible. What do you need it for?
>
>    Diego
>

> On Wed, Dec 9, 2009 at 00:26, ajaishankar <ajai.shan...@gmail.com> wrote:
> > Hi
>
> > Is it possible to do an Enumerate on a criteria than a List?
>
> > I see IQuery having an Enumerate method...
>
> > I'm trying to loop through the results similar to a data reader...
>
> > Thanks
>
> > Ajai
>
> > --
>
> > You received this message because you are subscribed to the Google Groups
> > "nhusers" group.
> > To post to this group, send email to nhu...@googlegroups.com.
> > To unsubscribe from this group, send email to

> > nhusers+u...@googlegroups.com<nhusers%2Bunsu...@googlegroups.com>
> > .

[Jason Meckley]

" I am in a situation where I need to retrieve a list of customers (>
200K) and various metrics (over 15) for each customer."

are you sure NH is the right tool for this? this is a ton a data which
may be better suited for an ETL (extract, transform, load) paradigm
rather than ORM.
You may be able to do some form of ETL with NH and IStatelessSession.
But again, is NH the right tool?

"Also does NH support MARS (multiple active record set) in SQL server
where I potentially can stream ALL the data"

I thought this is what NH is using under the hood to support
MultiQuery/Criteria and Future/FutureValue

another approach is to process customer individually or in small
batches each with a unique session.

[ajaishankar]

On Dec 9, 8:25 am, Jason Meckley <jasonmeck...@gmail.com> wrote:

> You may be able to do some form of ETL with NH and IStatelessSession.
> But again, is NH the right tool?
>

I already got everything going in a nice set based SQL batch (ETL) but
as new data points come in I'm having to replicate the logic at both
places :-(

Once in detached criteria & once in SQL which I wanted to avoid with
this exercise.

Am not convinces yet if it can all be done in NH since the metrics are
all aggregations.

> "Also does NH support MARS (multiple active record set) in SQL server
> where I potentially can stream ALL the data"
> I thought this is what NH is using under the hood to support
> MultiQuery/Criteria and Future/FutureValue

I didn't know that - I'd always assumed it was more like a batch SQL
with multiple record sets:

select x from a;
select y from b;

Which would then be lazily listed - though to the client it looks like
an enumeration...

> another approach is to process customer individually or in small
> batches each with a unique session.

Thanks Jason - I'll try this too but am liking a set based approach
most...

[Jason Meckley]

""Also does NH support MARS (multiple active record set) in SQL server
where I potentially can stream ALL the data"
I thought this is what NH is using under the hood to support
MultiQuery/Criteria and Future/FutureValue

I didn't know that - I'd always assumed it was more like a batch SQL
with multiple record sets:

select x from a;
select y from b;"

Yeah, I thought they are one in the same.

[ajaishankar]

On Dec 9, 1:03 pm, Jason Meckley <jasonmeck...@gmail.com> wrote:
> ""Also does NH support MARS (multiple active record set) in SQL server
> where I potentially can stream ALL the data"
> I thought this is what NH is using under the hood to support
> MultiQuery/Criteria and Future/FutureValue
>
>  I didn't know that - I'd always assumed it was more like a batch SQL
> with multiple record sets:
>
>  select x from a;
>  select y from b;"
>
> Yeah, I thought they are one in the same.

I think the difference is in MARS (when enabled) on same connection
you can do 2 concurrent commands:

command 1 - select x from a
command 2 - select y from b

Then iterate over the records from each in parallel.

http://www.sqlteam.com/article/multiple-active-result-sets-mars

This is slightly different from 1 command submitting 2 SQL in a batch.

[Jason Meckley]

ok, MARS is different than multicriteria.

[Eric W. Brown]

I'm pretty new to nhibernate... but we're running a lot of sql through it
Via our libraries. (I know that's not the best way, but we are converting step by step and we have a lot of custom sql in our apps)

But I have a piece of sql, which in nhibernate returns 0 rows, but in my sql
Tool returns rows galore.

I've tried running just the first half of the union and just the second half
Again nh = rows, sql tool 300 rows.

I've tried removing all the tab chars, no joy.
I've tried pulling out the extra spaces (yes I was that desperate) and that didn't do it.

I'm really stumped.

Can anyone eyeball it and see if I'm doing something obviously stupid?
Are there nhibernate gotchas that can cause this behavior?

Thanks

Cal-

SELECT top 300 svy_potential_call.svy_case_id,
case phone_type_cd
when 3882 then address.home_phone
when 3883 then address.work_phone
when 3884 then address.mobile_phone
end as phone_number,
case phone_type_cd
when 3882 then convert(varchar(10), 'home')
when 3883 then convert(varchar(10), 'work')
when 3884 then convert(varchar(10), 'mobile')
end as phone_type,
phone_type_cd,
'student' as who,
convert(varchar(150),
student.stud_lname) + ',
' + convert(varchar(150),
student.stud_fname) as person_name,
student.stud_id,
svy_potential_call_id,
svy_case.case_survey_end,
'(' + convert(varchar(5),svy_callback_priority.priority) + ') ' + svy_callback_priority.name as priority_name,
svy_potential_call.svy_callback_priority_id,
svy_potential_call.last_mod_dt,
svy_potential_call.last_mod_user,
null as relationship_name

FROM svy_potential_call,
svy_callback_priority,
svy_case,
student,
address

WHERE (svy_potential_call.svy_callback_priority_id = svy_callback_priority.svy_callback_priority_id)
AND (svy_case.svy_case_id = svy_potential_call.svy_case_id)
AND (svy_case.stud_id = student.stud_id)
AND (student.address_id = address.address_id)
AND contact_id = null
AND case phone_type_cd when 3882 then address.home_phone when 3883 then address.work_phone when 3884 then address.mobile_phone end != null
AND svy_potential_call.call_in_process_flg = 'Y'
AND svy_potential_call.last_mod_dt <= dateadd(mi,
-90,
getdate())

union

SELECT top 300 svy_potential_call.svy_case_id,
case phone_type_cd
when 3882 then address.home_phone
when 3883 then address.work_phone
when 3884 then address.mobile_phone
end as phone_number,
case phone_type_cd
when 3882 then convert(varchar(10), 'home')
when 3883 then convert(varchar(10), 'work')
when 3884 then convert(varchar(10), 'mobile')
end as phone_type,
phone_type_cd,
'contact' as who,
convert(varchar(150),
contact.contact_lname) + ',
' + convert(varchar(150),
contact.contact_fname) as person_name,
svy_potential_call.contact_id,
svy_potential_call_id,
svy_case.case_survey_end,
'(' + convert(varchar(5), svy_callback_priority.priority) + ') ' + svy_callback_priority.name as priority_name,
svy_potential_call.svy_callback_priority_id,
svy_potential_call.last_mod_dt,
svy_potential_call.last_mod_user,
relationship.code_desc as relationship_name

FROM svy_potential_call,
svy_callback_priority,
svy_case,
contact,
address,
code as relationship

WHERE (svy_potential_call.svy_callback_priority_id = svy_callback_priority.svy_callback_priority_id)
AND (svy_case.svy_case_id = svy_potential_call.svy_case_id)
AND (svy_potential_call.contact_id = contact.contact_id)
AND (contact.address_id = address.address_id)
AND (contact.contact_rel_cd = relationship.code_id)
AND svy_potential_call.contact_id != null
AND case phone_type_cd when 3882 then address.home_phone when 3883 then address.work_phone when 3884 then address.mobile_phone end != null
AND svy_potential_call.call_in_process_flg = 'Y'
AND svy_potential_call.last_mod_dt <= dateadd(mi, -90, getdate())

[Jason Meckley]

how you query via NH depends on how you may your domain.
I would start with altering the sql into more effecient sql statements
that sort of reflect the domain. once the new sql results are similar
(because they won't be exactly the same) enough to the exsiting sql
then you can migrate from the sql to NH queries.
I would start by
1. removing the formatting
example: instead of '(' + convert(varchar
(5),svy_callback_priority.priority) + ') ' just return the data and
save the formatting for the UI.
2. remove the case statement again, save this logic for the domain
3. use efficient joins in the "from" clause rather than the "where"
clause.
4. converting sql defaults into user parameters: example dateadd
(mi, -90, getdate()) becomes @date

Once this is done you can then take your edge cases and move then
either into the mapping files or I[Composite]UserType implementations
masking the ulgyness within NH.

it looks to me like you want to get everything in 1 huge query. this
always isn't the best way depending the structure of the data. this is
where NH fetching strageties and batching can improve preformance.

[Eric W. Brown]

Thanks!

The issue isn't performance though, it comes back quickly, I just returns 0 rows,
Where the exact same sql ran by hand (I copy it out of the nh logs) returns 300 rows.

Any idea why it's not returning any rows?

Thanks Again,

Cal-

--

You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.

To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.

[Eric W. Brown]

I figured it out, with your hints,
It was 2 things, it didn't like the 'col = null' syntax, I wanted 'col is null',
And it does not like the case statements in the where.

Your hint were very helpful.

Thanks!

Eric-