Darren is absolutely correct, my first idea was to create a Stored
Procedure and pass all the search parameters. And thus use the power of the
database instead of the ORM.
But one of these parameters is an array and to pass that to a sproc I would
need to use the Table Value Parameters of SQL Server 2008.
Two problems with that, first I need to support SQL Server 2005 also and an
even bigger problem I needed to support Oracle as well.
In the end, here's the solution I came up with:
- Use the ORM to select only the Id's of the entities
- Do a foreach on all those Guids and insert each one in a temporary
table
- Then call a Stored Procedure that will use the temp table to do all
the full inserts
Step 2 has been optimized to use a for loop with a step of 1000 in
combination with .SetParameterList(). This will generate one insert query
every 1000 ids instead of 1000 inserts.
It's a compromise, but one I can live with for the moment.
On Monday, July 30, 2012 10:08:47 PM UTC+2, Jason Meckley wrote:
>
> +1 for Darren. this is an ETL operation, ORM is not the right tool for
> this context.
>
> On Monday, July 30, 2012 12:37:32 PM UTC-4, Darren Kopp wrote:
>>
>> If you are dealing w/ 100k records, you should really be thinking about
>> doing this outside of nhibernate. There are a lot of problems associated
>> with large result sets like that (mainly memory but also easily cpu /
>> network). Depending on how many parameters there are, it would probably not
>> be that complicated of a stored procedure.
>>
>> On Monday, July 30, 2012 8:31:15 AM UTC-6, Jochen Jonckheere wrote:
>>>
>>> Unfortunately the number of records can be very high (+100k), a single
>>> query is a must or maybe multiple queries like batching per 1000.
>>>
>>>
>>> On Monday, July 30, 2012 3:55:34 PM UTC+2, Jason Meckley wrote:
>>>>
>>>> what you are asking for, no I don't think that's possible. you are
>>>> mixing object graphs with sql statements and they get's real messy real
>>>> fast. Instead I would manage this through objects and let NH deal with the
>>>> sql statements.
>>>> how about:
>>>>
>>>> var books = session.Query<Book>().Where(...).Futures();
>>>> foreach(var book in books)
>>>> {
>>>> var mybook = new MyBook(book...);
>>>> session.Save(mybook);
>>>> }
>>>>
>>>> //tx.Commit();
>>>>
>>>> Depending on your POID this will batch the write statements together.
>>>> If you are using HiLo or guid as ids the saves are batched together as a
>>>> single db call. If you are using Identity or another DB-centric POID than
>>>> the statements are executed individually. Depending on the number of
>>>> records expected to return from the books query individual insert
>>>> statements may or may not be acceptable.
>>>>
>>>> On Monday, July 30, 2012 9:02:19 AM UTC-4, Jochen Jonckheere wrote:
>>>>>
>>>>> Hi,
>>>>>
>>>>> For a project I'm working on I want to do some DLM stuff with
>>>>> NHibernate, I know not the best option. But anyway I'm stuck on complex
>>>>> where statement.
>>>>>
>>>>> On the screen a user can select multiple parameters to do a search and
>>>>> in code this generates some linq-code that is than executed via
>>>>> NHibernate.
>>>>> Example: _session.Query<Book>().Where(b => b.ISBN.StartsWith("10025")
>>>>> && b.Pages > 100 && b.Author.Name == "Jochen" && ...)
>>>>>
>>>>> But now I want to use that Where part in combination with INSERT INTO.
>>>>> An insert into that only works via hql or sql.
>>>>> Example: _session.CreateQuery("insert into MyBooks (Id, NumberOfPages)
>>>>> select b.Id, b.Pages where ... (and here should the linq part be inserted)
>>>>>
>>>>> Is it possible to mix Linq and Hql? Or is there a better solution to
>>>>> this problem.
>>>>>
>>>>> Regards,
>>>>>
>>>>> Jochen
>>>>>
>>>>>
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/nhusers/-/hvlU_1SYVPMJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
