使用.NET 2.0的Sql CE 4.0性能非常慢 [英] Sql CE 4.0 with .NET 2.0 very slow performence
问题描述
大家好，
使用内部项目。我根据要求使用以下技术。
1）VS 2012.
2）SQL Compact 4.0
3）.net 2.0框架。
当我试图查询哪些检索592行需要20秒。虽然需要1.7分钟才能检索2472行。这真的很慢。任何机构都可以帮我解决这个性能问题。
以下是DB结构。 [ ^ ]
1）1103行的LCD表。
2）LCDHcpcs有17807行。
3）HcpcsRef with 16207行。
4）LcdICD10有356441行
5）Icd10Ref有69825行。
我是尝试使用以下代码和查询。
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;
}
我的尝试：
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.Execute ResultSet（System.Data.SqlServerCe.ResultSetOptions.Scrollable）;
}
conn.Close（）;
返回视图;
}
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;
}
推荐答案
通过根据执行计划添加索引来调整查询。
取决于版本的您正在使用的SQL Compact可以使用SQL Management Studio（v4.0之前版本）或Visual Studio 2010 Sp1 Transact SQL编辑器。
以下MS Technet链接可以帮助您掌握性能调优; 查询性能调优（SQL Server Compact） [ ^ ]
亲切的问候
Look at tuning the Query through adding indexes based on the execution plan.
Depending on what version of SQL Compact you are using you can use SQL Management Studio (pre v4.0) or Visual Studio 2010 Sp1 Transact SQL Editor.
The following MS Technet link should help you with getting a handle on performance tuning; Query Performance Tuning (SQL Server Compact)[^]
Kind Regards
这篇关于使用.NET 2.0的Sql CE 4.0性能非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
