Hello

I need to fetch a lot of GUIDs from a view, and wondering which way is the better ?

I currently use Linq:

 return (from a in metadata.VwEmailAccessWithTask
                                where a.UsrId == Current.CompanyIdentity.UserId && a.EmId.HasValue
                                select a.EmId.Value.ToString()).Distinct().ToArray();

It takes ~1,4s for DB, then ~9,4s for .NET (according to ORM Profiler)

Do you know how I could increase these performance ? At least the .NET part ...

Thank you Fab

Hi Fab,

It's a lot of data to handle by .Net. In my opinion the slow time could be affected by:

A. Large data traveling though the wire. B. The String conversion on .NET. C. The array conversion.

In other tests I made in the past, I found that eliminating the .ToString() conversion could lead to reduce the time in 50%. Give it a try.

What about paging? It's an option in your scenario?

daelmo wrote:

Hi Fab,

It's a lot of data to handle by .Net. In my opinion the slow time could be affected by:

A. Large data traveling though the wire. B. The String conversion on .NET. C. The array conversion.

In other tests I made in the past, I found that eliminating the .ToString() conversion could lead to reduce the time in 50%. Give it a try.

What about paging? It's an option in your scenario?

I can't page as this is a filter layer for the security, it's not shown to the user. It's used by Lucene to filter the result of the search, so all ID must be available all the time. The the lucene filter require an array of string.

This is already cached, so that there isn't the sec wait time when searching, but the cache is often cleared so it impact the users sometime.

I think maybe using directly datareader may improve the query ? I don't know if you already had some experience on this. I'll try to make some change and do some benchmark

BTW these time was for fetching ~300 000 guid from a view

At least do this:

return (from a in metadata.VwEmailAccessWithTask
                                where a.UsrId == Current.CompanyIdentity.UserId && a.EmId.HasValue
                                select a.EmId.Value).Distinct().ToArray();

This is v3.5? v5 is much faster btw (as in... a lot).

The 'ToString()' call is run in-memory over the returned guids. I think that's a big part of the time taken. If you run it like the one above, you'll get an array of guids. Not sure why you'd use a string variant, but in any case, you can then convert them to strings after this call, which will tell you a bit more about performance of the various parts.