Sql CE 4.0 with .NET 2.0 very slow performence

See more:

.NET2.0

SQL

C#3.5

Hi All,

Working with an internal project. I am using following technologies based on requirements.

1) VS 2012.
2) SQL Compact 4.0
3) .net 2.0 frame.

When i am trying to query which retrieves 592 rows is taking 20 seconds. While it is taking 1.7 minutes to retrieve 2472 rows. It is really slow. Can any body help me to resolve this performance issue.

Following is DB Structure. [^]


1) LCD table with 1103 rows.
2) LCDHcpcs with 17807 rows.
3) HcpcsRef with 16207 rows.
4) LcdICD10 with 356441 rows
5) Icd10Ref with 69825 rows.

I am trying to use following code and query.

public System.Data.SqlServerCe.SqlCeResultSet ExecuteAndGetSqlCEResultsetViewOption()
        {
		System.Data.SqlServerCe.SqlCeConnection conn = DAL.GetSqlCEConnection();
		stringStrQuery = "SELECT DISTINCT ICD10Ref.ICD10ID as [Icd 10], ICD10Ref.Description as Description FROM Lcd INNER JOIN LCDIcd10 ON " +
                "Lcd.LcdID = LCDIcd10.LCD AND Lcd.Version = LCDIcd10.LcdVersion INNER JOIN ICD10Ref ON LCDIcd10.ICD10ID = ICD10Ref.ICD10ID AND " +
                "LCDIcd10.ICD10Version = ICD10Ref.ICD10Version WHERE (Lcd.LcdID = 33621)"
            	
		System.Data.SqlServerCe.SqlCeResultSet view = null;

            using (System.Data.SqlServerCe.SqlCeCommand comm = new System.Data.SqlServerCe.SqlCeCommand(strQuery, conn))
            {
                view = comm.ExecuteResultSet(System.Data.SqlServerCe.ResultSetOptions.Scrollable);
            }
            conn.Close();
            return view;
        }

What I have tried:

public System.Data.SqlServerCe.SqlCeResultSet ExecuteAndGetSqlCEResultsetViewOption()
{
System.Data.SqlServerCe.SqlCeConnection conn = DAL.GetSqlCEConnection();
stringStrQuery = "SELECT DISTINCT ICD10Ref.ICD10ID as [Icd 10], ICD10Ref.Description as Description FROM Lcd INNER JOIN LCDIcd10 ON " +
"Lcd.LcdID = LCDIcd10.LCD AND Lcd.Version = LCDIcd10.LcdVersion INNER JOIN ICD10Ref ON LCDIcd10.ICD10ID = ICD10Ref.ICD10ID AND " +
"LCDIcd10.ICD10Version = ICD10Ref.ICD10Version WHERE (Lcd.LcdID = 33621)"

System.Data.SqlServerCe.SqlCeResultSet view = null;

using (System.Data.SqlServerCe.SqlCeCommand comm = new System.Data.SqlServerCe.SqlCeCommand(strQuery, conn))
{
view = comm.ExecuteResultSet(System.Data.SqlServerCe.ResultSetOptions.Scrollable);
}
conn.Close();
return view;
}