---
title : SQL效能調校
tags: SQL效能調校
creat-date: 2022-12-21
update_date : 2022-12-21
---

---
## SQL 效能調校
---

### 一、連線字串使用 using：

**原始寫法：**

public static DataTable QueryData(int nPyear)
{
    ado db = new ado(CS.GConst.CONN_Default);
    string sSql = @" SELECT *
                     FROM   wra_plan
                     WHERE  pyear= @pyear ";

    var sqlParams = new SqlParameterBuilder();
    sqlParams.Add("@pyear", SqlDbType.Int, nPyear);
    return db.getDT(sSql, sqlParams.getParamList()).toDataTable();
}

**使用 using 改寫：**

public static DataTable QueryData(int nPyear)
{
    DataTable RtVal = null;
    using (DbHelper db = new DbHelper(CS.GConst.CONN_Default)) {
        string sSql = @"SELECT *
                        FROM   wra_plan
                        WHERE  pyear= @pyear ";

        db.CommandText = sSql;
        db.Parameters.Clear();
        db.Parameters.Add("@pyear", SqlDbType.Int, nPyear);
        RtVal = db.GetTable();
    }
    return RtVal;
}

### 二、Where 條件用程式改寫：

public static DataTable QueryPlanPriceUnitLv1(int nPyear)
{
    DataTable RtVal = null;
    using (DbHelper db = new DbHelper(CS.GConst.CONN_Default)) {
        string sqlcmd = @"SELECT pyear,short_unit_name AS UnitName,Total,TotalPrice2,ISNULL(TotalPrice,0) AS TotalPrice
                          FROM   V_H_WraPlanPrice_UnitLv1
                          --WHERE  pyear=@pyear
                          ORDER BY pyear,unit_order;";
        db.CommandText = sqlcmd;
        //db.Parameters.Clear();
        //db.Parameters.Add("@pyear", nPyear);
        RtVal = db.GetTable();
    }
    
    // 「Where pyear=@pyear」條件改用程式篩選
    for (int i = 0; i <= RtVal.Rows.Count - 1; i++) {
        DataRow dr = RtVal.Rows[i];
        if (Convert.ToInt32(dr["pyear"]) != nPyear)
            dr.Delete();
    }
    RtVal.AcceptChanges();

    return RtVal;
}

### 三、少用序列函數 ROW_NUMBER()：

**SQL語法：**

public static GisTable ListvProjectBasicQuery(int nYearStart, int nYearEnd)
{
    DbHelper db = new DbHelper(AppGlobal.ConnectionString);
    db.Parameters.Clear();
    db.CommandText = @"SELECT --ROW_NUMBER() OVER (ORDER BY ProjectYear,vPB.ProjectId) AS FlowNo,
                              *,
                              CASE WHEN ProjectType in ('01','02') AND (TelExtension='' OR PBS.ProjectId IS NOT NULL) THEN 'N' 
                                   WHEN ProjectType='03' AND (TelExtension='' OR Village = '' OR PBS.ProjectId IS NOT NULL) THEN 'N' 
                                   WHEN ProjectType='04' AND (ProjectNature='' OR ProjectEnginType= '' OR TelExtension = '' OR  Village = '' 
                                        OR PBS.ProjectId IS NOT NULL OR (IsNonOpeningCeremony='Y' AND OpeningCeremonyDate IS null)) THEN 'N' 
                                   ELSE 'Y' END AS ProjectBasicFinish	  
                       FROM   vProjectBasicQuery vPB INNER JOIN
                              Sync_Department AS SD ON vPB.AgencyUnit2Id=SD.OU AND SD.Dn like '%,OU=387100000G,%' LEFT JOIN 
                             (SELECT distinct ProjectId
                              FROM   ProjectBudgetSource 
                              WHERE  SourceItem='中央部會' and (PlanName='' or SubsidySituation='' or ApplicationDate= '' or ApplicationNo='')) PBS 
                              ON vPB.ProjectId = PBS.ProjectId      
                       WHERE  ProjectYear BETWEEN @YearStart AND @YearEnd 
                       ORDER BY ProjectYear,vPB.ProjectId ";       
    db.Parameters.Add("@YearStart", nYearStart);
    db.Parameters.Add("@YearEnd", nYearEnd);

    return db.GetTable();
}

**ListView中的流水號「項次」：**

<asp:ListView ID="lvProject" runat="server" DataKeyNames="ProjectId" EnableViewState="true"> 
    <GroupTemplate>
        <asp:PlaceHolder runat="server" ID="itemPlaceholder" />
    </GroupTemplate>
    <LayoutTemplate>
        <table class="table_list table-rwd">
            <thead>
                <tr>
                    <th class="center" style="width:3%">項次</th>
                    <th class="center" style="width:3%">年度</th>
                    <th class="center" style="width:19%">案件名稱</th>
                </tr>
            </thead>
            <tbody>
                <asp:PlaceHolder ID="groupPlaceholder" runat="server" />   
            </tbody>
        </table>                    
    </LayoutTemplate>
    <ItemTemplate>
        <tr>
            <%--<td data-th="項次" class="center"><%# HttpUtility.HtmlEncode(Eval("FlowNo")) %></td>--%>
            <td data-th="項次" class="center"><%# Container.DataItemIndex + 1 %></td>
            <td data-th="年度" class="center"><%# HttpUtility.HtmlEncode(Eval("ProjectYear")) %></td>
            <td data-th="案件名稱" class="left"><%# HttpUtility.HtmlEncode(Eval("ProjectName")) %></td>
        </tr>                                
    </ItemTemplate>
    <EmptyDataTemplate>
    </EmptyDataTemplate>
</asp:ListView>