ORM with streaming

[pebrian27]

Hi! Is there an ORM that provides streaming support? What I mean by streaming is that the users can see the rows/objects being loaded one-by-one/partially (Think of SQL Management Studio when the query result is thousands of record). I can't implement paging since our data changes very fast. Background: - DB contains >250 million of data (but I will limit the result set to 10K max) - Query can take minutes even hours - Can't change DB architecture Thanks

[Mycroft Holmes]

pebrian27 wrote:

but I will limit the result set to 10K max

Why, how useful is 10k records in a UI, why not limit to 100 or 50 or something the user can work with.

pebrian27 wrote:

Query can take minutes even hours

This does not make sense when servicing a UI. A select statement that takes hours, ridiculous, your design is screwed. If the query takes hours the user has got bored and gone home. Change you design to data dump, output the data via a job/SSIS package and make it available in a shared folder. We extended SSRS to do just this sort of work, anything more than 1k rows is a lousy design for a UI and a report. [edit] Checked your profile, web developer, so use an FTP/download scenario[/edit]

Never underestimate the power of human stupidity RAH

[pebrian27]

Our users are also technical people and they need ~10k records. Also, the users get to create the queries (ad-hoc queries). My current ORM is Nhibernate so they use HQL which is very similar to SQL.

Mycroft Holmes wrote:

pebrian27 wrote: Query can take minutes even hours This does not make sense when servicing a UI. A select statement that takes hours, ridiculous, your design is screwed.

Oops... Queries will only take hours if I don't do the 10k limit. Currently, querying 500 records takes 2 mins. Thats why I want streaming support so that if they get bored, they can press cancel but still get partial result. Our systems will be upgraded after 8 months.

[pebrian27]

Nhibernate isn't the cause of the slow query and there are no joins in the queries

[i j russell]

500 records in 2 mins with no joins is very very slow. It would appear that either your server is useless or the query optimizer is unable to use indexes and is doing a table scan of all 250million records. Have you looked at the query execution plan to see if there are indexes that you can add?