0

This is my code which works fine untill my database have no more than about 20000 records in main table. When DB is to big program freezes at statement da.Fill(data). There are no exceptions throw, program just stops working. What can I do?

DataTable data = new DataTable();
SqlCeCommand cmd = new SqlCeCommand();
SqlCeDataAdapter da = new SqlCeDataAdapter();

cmd = new SqlCeCommand("SELECT DISTINCT * FROM ((Osoba AS a LEFT OUTER JOIN Doswiadczenie AS b ON a.HashId = b.Hashid) LEFT OUTER JOIN Wyksztalcenie AS c on a.HashId = c.HashId) INNER JOIN Uczelnia as d ON c.UczelniaId = d.UczelniaId WHERE a.DataOstOdczytu >= @minDate AND a.DataOstOdczytu <= @maxDate AND b.DataOdczytu >= @minDate AND b.DataOdczytu <= @maxDate AND c.DataOdczytu >= @minDate AND c.DataOdczytu <= @maxDate", this.Connection);

cmd.Parameters.Add(new SqlCeParameter("@minDate", minDate));
cmd.Parameters.Add(new SqlCeParameter("@maxDate", maxDate));

da.SelectCommand = cmd;
try
{
    da.Fill(data);
}
catch (System.Data.SqlServerCe.SqlCeException)
{
    throw;
}

return data;
Noctis
  • 11,507
  • 3
  • 43
  • 82
hamstermaner
  • 11
  • 1
  • try getting row by row instead of da.Fill(data) – lordkain Nov 12 '13 at 13:34
  • use datareader for better performance. general compact sql is slow woth many records. – kostas ch. Nov 12 '13 at 13:38
  • 1
    possible duplicate of [DataAdapter.Fill too slow](http://stackoverflow.com/questions/8731241/dataadapter-fill-too-slow) – CodeCaster Nov 12 '13 at 13:38
  • Does the query work by itself (outside the program) for a large number of rows in reasonable time? "Distinct *" sure needs a lot of sorting to do if you have large row. – NoChance Nov 12 '13 at 13:39
  • Check CodeCaster's link. – kostas ch. Nov 12 '13 at 13:40
  • "What can I do?" - well, you could stop loading all the data into your app - that would be a great start. That *is not the ideal way to work*. The data is perfectly happy in the database. Leave it there. Query it from there. Resist the urge to load everything into your app's memory. – Marc Gravell Nov 12 '13 at 13:54
  • Emmad Kareem is right, my SQL query just takes forever. I need to fix it probably on the database level. Thank you all for help. – hamstermaner Nov 13 '13 at 12:12

1 Answers1

-1

In catch block make an object of System.Data.SqlServerCe.SqlCeException exname and instead of throw write Console.WriteLine(exname.Message) to confirm that there is no exception.

Afaq
  • 1,146
  • 1
  • 13
  • 25