LinqToLLBL + XtraGrid + 30000 records : Very Bad performance:
Thanks for the info. However, fetching 30000 rows is still going to take a lot of time, also with linq to sql. Are you sure it fetches all 30000 rows in linq to sql as well?
If you want a limited amount, just specify .Take(_number_). I know with a grid this is a bit awkward, but can't you specify paging information? As you probably don't want to see 30000 rows to show up in the grid (which will take forever to render I'm sure), I think specifying paging information is essential.
The 'cancel' trick is indeed something we don't use, but that can be very helpful, as we don't need output parameters, rows affected and other info from the datareader: when close is called, the reader is simply done. I'll see if 'cancel' is safe to be called on other datareaders besides sqlserver's.. .
(edit). Btw, 60 seconds for closing a datareader on a large set is IMHO way too much and is caused by other things than that Close() is called alone. tests here on 50,000 rows fetch with a datareader are very fast, Close() takes just a couple of ms. 
.
Also a SqlDataAdapter fill uses Close() (it simply calls dispose). If you fetch a datatable with the first 100 rows using Fill on a SqldataAdapter where yuo specify 0, 100 for startrow and maxrows on that 30K table, is it still taking a lot of time?
(edit). Hmm, it indeed makes a difference if you break off a datareader loop:
string sql = "SELECT * from RandomData";
Stopwatch sw = new Stopwatch();
sw.Start();
using(SqlConnection conn = new SqlConnection(CONNSTR))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader dr = cmd.ExecuteReader();
int i = 0;
ArrayList list = new ArrayList();
while(dr.Read())
{
list.Add(dr[0]);
if(list.Count == 64)
break;
}
cmd.Cancel();
dr.Close();
conn.Close();
}
sw.Stop();
Console.WriteLine("Took: {0}ms", sw.ElapsedMilliseconds);
takes 800ms (table contains 50000 12 field rows with random data) over the network. Not calling cmd.Cancel takes 5000ms!
(edit). Indeed! 
The whole query takes 200ms at first, close takes 4800ms! .Then calling cancel right before close: query: the same, close: 10ms.
The only thing now is: is Cancel harmless?
Btw, this only occurs when the datareader is broken off before the end is reached, which is the case when paging is done on the client.
(edit). Hmm, several providers don't support this (throw exceptions), oracle's ODP.NET throws an exception when cancellation succeeds (err.... ) so it's not something which can be added globally. Casting the reader to SqlDataReader might help, as it seems that the problem mainly occurs in sqlserver.
Otis wrote:
(edit). Btw, 60 seconds for closing a datareader on a large set is IMHO way too much and is caused by other things than that Close() is called alone. tests here on 50,000 rows fetch with a datareader are very fast, Close() takes just a couple of ms.
60 seconds on a remote database server, that I access on the internet. In our LAN no problem!
I found 1 potential issue, where randomly transactions were falling apart due to this. (a blog post about this, not here).
I'll add two extra checks to the cleanup routine: 1) if the command is in a transaction, cancel isn't called 2) if the datareader.Read() method returns false, cancel isn't called either.
That should make things safe, and OK, although the MSDN doesn't describe a single issue about this, but one never knows... 
Could you try this final build as well? (attached). If it works, I'll run the rest of the tests and push out a new build.
Otis wrote:
Could you try this final build as well?
tested: it's ok...
Cool thanks!
Otis wrote:
Cool thanks!
Hi, I found that in some cases the datareader closing operation still goes in timeout!
I think this happens with more complex queries than a simple select. For example:
PrefetchPath2 pp2=new PrefetchPath2(EntityType.ArticoliEntity);
pp2.Add(ArticoliEntity.PrefetchPathProduttori);
pp2.Add(ArticoliEntity.PrefetchPathCategorieArticoli);
pp2.Add(ArticoliEntity.PrefetchPathClientiFornitori);
pp2.Add(ArticoliEntity.PrefetchPathLineeProdotto);
var query = from art in linq.Articoli.WithPath(pp2)
select new
{
Idarticolo = art.Idarticolo,
CodiceArticolo= art.CodiceArticolo,
DescrizioneBreve=art.DescrizioneBreve,
CodiceAbarre=art.CodiceAbarre,
NomeProduttore = art.Produttori.NomeProduttore,
Fornitore=art.ClientiFornitori.RagioneSociale,
DescrizioneCategoria=art.CategorieArticoli.Descrizione,
DescrizioneLineaProdotto = art.LineeProdotto.Descrizione
};
Causes execution of:
SELECT [LPA_L1].[Idarticolo], [LPA_L1].[CodiceArticolo], [LPA_L1].[DescrizioneBreve],
[LPA_L1].[CodiceAbarre], [LPA_L1].[NomeProduttore], [LPA_L1].[Fornitore], [LPA_L1].[DescrizioneCategoria],
[LPA_L1].[DescrizioneLineaProdotto] FROM (SELECT [LPA_L3].[IDArticolo] AS [Idarticolo], [LPA_L3].[CodiceArticolo],
[LPA_L3].[DescrizioneBreve], [LPA_L3].[CodiceABarre] AS [CodiceAbarre], [LPA_L2].[NomeProduttore],
[LPA_L4].[RagioneSociale] AS [Fornitore], [LPA_L5].[Descrizione] AS [DescrizioneCategoria],
[LPA_L6].[Descrizione] AS [DescrizioneLineaProdotto]
FROM (((( [dbo].[Produttori] [LPA_L2]
RIGHT JOIN [dbo].[Articoli] [LPA_L3]
ON [LPA_L2].[IDProduttore]=[LPA_L3].[IDProduttore])
LEFT JOIN [dbo].[ClientiFornitori] [LPA_L4]
ON [LPA_L4].[IDClienteFornitore]=[LPA_L3].[IDClienteFornitorePrincipale])
INNER JOIN [dbo].[CategorieArticoli] [LPA_L5]
ON [LPA_L5].[IDCategoriaArticolo]=[LPA_L3].[IDCategoriaArticolo])
LEFT JOIN [dbo].[LineeProdotto] [LPA_L6]
ON [LPA_L6].[IDLineaProdotto]=[LPA_L3].[IDLineaProdotto])) [LPA_L1] ORDER BY [LPA_L1].[Idarticolo] ASC
in this case I see that calling queryExecuted.Command.Cancel() isn't sufficient, and reader.Close() goes in timeout....
Maybe I wrote a wrong linq query? Can I optimize it?