開発のあいまに

SQLServerで、IISからのDB処理だけが遅い場合は、ストアド、ファンクションをリコンパイルする

テラバイトクラスのDBシステムを扱っていると、データ量が増えるたびに、特定のSQLが極端に遅くなることがあります。

その時に、遅くなったSQLの実行プランを確認すると、インデックススキャン（Index seek）のコストが跳ね上がり、SQL全体のコストの100%近くを、インデックススキャンが占めるように変化しています。

インデックススキャンのコストは、SQL全体で20%未満になっているのが、妥当な値です。

極端に遅くなっている、もしくは、コストが高くなっているSQLは、SQLServerのチューニングアドバイザーにかけると、インデックスと統計情報が抽出されるので、それを適用する事で解決できます。
※SQLが遅くなっているサーバーで、チューニングアドバイザーを実行せず、別のサーバーでチューングアドバイザーにそのSQLをかけると、必要なインデックスと統計情報が抽出されない事があるので、そこは注意が必要です。

テラバイトクラスのDBシステムを1年以上運用していて、今回初めて、必要なインデックスと統計情報を追加しても、IISからのDB処理だけ処理性能が改善しない、という現象に遭遇しました。
Management Studioからだと、遅くなっているSQLの実行プランは改善され、実行時間も1秒程度なのに対し、WEBサイトからそのSQLを実行すると、5分たっても結果が返って来ませんでした。

【環境】
WEBサーバー：Windows Server 2008 R2 SP1
DBサーバー：Windows Server 2008 R2 SP1、SQLServer 2008 R2 SP2 CU3
WEBサイトは.Net4.0で構築

色々試した結果、WEBサイトから実行した場合のみ遅い現象が発生したら、ストアドのリコンパイルが有効だという事が分かりました。ユーザー定義関数を利用している場合は、関数のリコンパイルも必要です。

ストアドに実行プランをキャッシュする、こちらのアーキテクチャにバグがあって、IIS側からアクセスされた場合に、古い実行プランを使い続けていたのが原因でした。
http://msdn.microsoft.com/ja-jp/library/ms181055(v=sql.105).aspx
http://msdn.microsoft.com/ja-jp/library/ms191007(v=sql.105).aspx

そういえば、SQLServerはIISのキャッシュを、別扱いするという技術資料を、以前どこかで読んだ気がします。

毎日のように、インデックスと統計情報を追加しているシステムなので、再発防止として、インデックスと統計情報を追加する頻度の高いテーブル、ストアド、ユーザー定義関数は全て、日次で夜間に、sp_recompile を実行するようにしました。
http://msdn.microsoft.com/ja-jp/library/ms181647.aspx

 

【関連記事】
チューニングアドバイザーによる、インデックスと統計情報の抽出と追加
テーブルの統計情報を、週次で更新
ストアドとファンクションを、日次のジョブで毎朝リコンパイル
IISと関連サービスを、日次で毎朝再起動。
IISと関連サービスからのDB処理が、極端に遅くなるストアド、ファンクションは、使用するインデックスをヒント文で固定する
SQLServerで、IISからのDB処理だけが遅い場合は、ストアドをリコンパイルする
SQLServer 2005 インデックス チューニング ライフサイクル
6ヶ月以上使われていないインデックスを抽出するSQL

 

bing mapを使い、住所から緯度経度を取得する、サンプルC#ソース

string str住所 = "東京都新宿区西新宿2-8-1";
double f緯度 = 0;
double f経度 = 0;
C01MAP.Get緯度経度(str住所, ref f緯度, ref f経度);

public static void Get緯度経度(string strAdress, ref double dblLatitude, ref double dblLongitude)
{
 string BingMapsKey = "INSERT_YOUR_BING_MAPS_KEY";
 string strURL = string.Format("http://dev.virtualearth.net/REST/v1/Locations?query={0}&key={1}&c=ja-jp&o=xml", strAdress, BingMapsKey);
 XmlDocument geocodeResponse = GetXmlResponse(strURL);

 XmlNamespaceManager nsmgr = new XmlNamespaceManager(geocodeResponse.NameTable);
 nsmgr.AddNamespace("rest", "http://schemas.microsoft.com/search/local/ws/rest/v1");
 XmlNodeList locationElements = geocodeResponse.SelectNodes("//rest:Location", nsmgr);
 string latitude = null;
 string longitude = null;
 if (locationElements.Count == 0)
 {
latitude = "0";
longitude = "0";
 }
 else
 {
XmlNodeList displayGeocodePoints = locationElements[0].SelectNodes(".//rest:GeocodePoint/rest:UsageType[.='None']/parent::node()", nsmgr);
latitude = displayGeocodePoints[0].SelectSingleNode(".//rest:Latitude", nsmgr).InnerText;
longitude = displayGeocodePoints[0].SelectSingleNode(".//rest:Longitude", nsmgr).InnerText;
 }

 dblLatitude = Convert.ToDouble(latitude);
 dblLongitude = Convert.ToDouble(longitude);
}

private static XmlDocument GetXmlResponse(string requestUrl)
{
 Trace.WriteLine("Request URL (XML): " + requestUrl);
 HttpWebRequest request = WebRequest.Create(requestUrl) as HttpWebRequest;

 using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
 {
if (response.StatusCode != HttpStatusCode.OK)
 throw new Exception(String.Format("Server error (HTTP {0}: {1}).", response.StatusCode, response.StatusDescription));

XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(response.GetResponseStream());

return xmlDoc;
 }
}

System.Runtime.Serialization エラーが発生したら

Windows Server 2008 R2 SP1　x64 に .NET Framework 4.0 をインストールし、IIS上に.NET Framework 4.0 のWEBサイト構築した後、WCFにアクセスすると、こちらのエラーが発生する事があります。

Could not load file or assembly 'System.Runtime.Serialization, Version=2.0.5.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e, Retargetable=Yes' or one of its dependencies. The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047)

このエラー、もしくは「Could not load file or assembly」関連のエラーが発生する場合は、NDP40-KB2468871-v2-x64.exe　をインストールする事で解決できます。

string変数はnullで初期化する

string変数を""で初期化すると、メモリが解放されない為、値代入と、""による初期化を、数十万回繰り返すと、MemoryOverFlowが発生する。

""で初期化し、MemoryOverFlowが発生したソース。

int iRowCnt = 0;
string strRowData1000 = "";
string strRowData = "";
string strTmp = "";

foreach (DataGridViewRow row in grid.Rows)
{
 strRowData = "";
 for (int iCol = i開始列; iCol < row.Cells.Count; iCol++)
 {
  strRowData += "\"" + row.Cells[iCol].FormattedValue.ToString() + "\"";

  if ((grid.Columns.Count - 1) > iCol)
  {
   strRowData += ",";
  }
 }

 strTmp = strRowData.Replace(",", "").Replace("\"", "");
 if (strTmp.Length == 0)
  continue;
 else
  strRowData1000 += strRowData;

 if (iRowCnt < 2000)
 {
  strRowData1000 += "\r\n";
  iRowCnt++;
 }
 else
 {
  File.AppendAllText(str保存先ファイルパス, strRowData1000 + "\r\n", Encoding.GetEncoding("Shift_JIS"));
  iRowCnt = 0;
  strRowData1000 = "";
 }
}

 

nullで初期化し、MemoryOverFlowが発生しなかったソース。

int iRowCnt = 0;
string strRowData1000 = "";
string strRowData = "";
string strTmp = "";

foreach (DataGridViewRow row in grid.Rows)
{
 strRowData = "";
 for (int iCol = i開始列; iCol < row.Cells.Count; iCol++)
 {
  strRowData += "\"" + row.Cells[iCol].FormattedValue.ToString() + "\"";

  if ((grid.Columns.Count - 1) > iCol)
  {
   strRowData += ",";
  }
 }

 strTmp = strRowData.Replace(",", "").Replace("\"", "");
 if (strTmp.Length == 0)
  continue;
 else
  strRowData1000 += strRowData;

 if (iRowCnt < 2000)
 {
  strRowData1000 += "\r\n";
  iRowCnt++;
 }
 else
 {
  File.AppendAllText(str保存先ファイルパス, strRowData1000 + "\r\n", Encoding.GetEncoding("Shift_JIS"));
  iRowCnt = 0;
  strRowData1000 = null;
 }
}

C#のファイル書き込み高速化

DataGridViewに5万行を表示し、選択可能最大行数（5万行）を、現実的な処理時間で、CSVファイルとして保存するコードが出来た！

1.最初はStreamWriterクラスを用いたが、.Net framework のバグで、メモリを限界まで使用する現象が発生し、アプリケーションが必ずハングする。試行錯誤したが、StreamWriterクラスでは無理だった。

2.FileクラスのAppendAllTextを用いる事で、安定的にファイル書き込み処理が実行されるようになったが、書き込みが終わるまで数時間かかってしまう。

3.FileクラスのAppendAllTextを、1行単位に実行するのではなく、1000行単位に実行するようにしたら、2分程度で5万行のファイル書き込みが完了するようになった。

 

最終的に出来上がったソースはこちら。

using System.IO;

private static void Writeグリッド明細_Selected(string str保存先ファイルパス, DataGridView grid, int i開始列)
{
 int iRowCnt = 0;
 string strRowData = "";

 foreach (DataGridViewRow row in grid.SelectedRows)
 {
  for (int iCol = i開始列; iCol < row.Cells.Count; iCol++)
  {
   strRowData += "\"" + row.Cells[iCol].FormattedValue.ToString() + "\"";

   if ((grid.Columns.Count - 1) > iCol)
    strRowData += ",";
  }

  string strTmp = strRowData.Replace(",", "").Replace("\"", "");
  if (strTmp.Length == 0)
   continue;

  if (iRowCnt < 1000)
  {
   strRowData += "\r\n";
   iRowCnt++;
  }
  else
  {
   File.AppendAllText(str保存先ファイルパス, strRowData + "\r\n", Encoding.GetEncoding("Shift_JIS"));
   iRowCnt = 0;
   strRowData = "";
  }
 }

 if (strRowData != "")
  File.AppendAllText(str保存先ファイルパス, strRowData + "\r\n", Encoding.GetEncoding("Shift_JIS"));
}

bing map のWPF 開発メモ

WPFベースで開発する際に必要な、SDKをダウンロード。
http://www.microsoft.com/en-us/download/details.aspx?id=27165

BING MAP KEY 取得。
http://www.bingmapsportal.com/application/index/1173201?status=NoStatus

新規プロジェクト作成から、map表示までの手順とサンプルコード。
http://msdn.microsoft.com/en-us/library/hh830433.aspx

アセンブリバージョンが自動的にインクリメントされるようにする。

EXEやDLLのプロパティにある「バージョン情報　⇒　Assembly Version」を、リビルドした際に自動インクリメントするには、AssemblyInfo.cs ファイルの AssemblyVersion 項目を以下の形式に変更する必要がある。

【デフォルト】
[assembly: AssemblyVersion("1.0.0.0")]

【変更後】
[assembly: AssemblyVersion("1.0.0.*")]

モノクロビットマップに変換して保存できるSaveMonoBitmapメソッド

色情報のあるビットマップを、モノクロ（２値）ビットマップに変換して、ビットマップファイルを保存できる、SaveMonoBitmapメソッドを作成しました。

using System;
using System.Collections.Generic;
using System.Text;
using System.Drawing;
using System.Drawing.Imaging;

public static void SaveMonoBitmap(Bitmap bmpColor, string strSaveBitmapPath)
{
  Bitmap bmpMono = Get1bppIndexedImage(bmpColor);
  bmpMono.SetResolution(200, 200);

  ImageCodecInfo imageCodecInfo;
  imageCodecInfo = GetEncoderInfo("image/bmp");

  EncoderParameters encoderParameters = new EncoderParameters(2);
  encoderParameters.Param[0] = new EncoderParameter(System.Drawing.Imaging.Encoder.ColorDepth, 1L);
  encoderParameters.Param[1] = new EncoderParameter(System.Drawing.Imaging.Encoder.Compression, (long)EncoderValue.CompressionCCITT4);

  bmpMono.Save(strSaveBitmapPath, imageCodecInfo, encoderParameters);
}

private static Bitmap Get1bppIndexedImage(Bitmap bmp)
{
  Bitmap monoBmp = new Bitmap(bmp.Width, bmp.Height, PixelFormat.Format1bppIndexed);
  BitmapData data = monoBmp.LockBits(
    new Rectangle(0, 0, monoBmp.Width, monoBmp.Height), ImageLockMode.ReadWrite, PixelFormat.Format1bppIndexed);

  for (int x = 0; x < bmp.Width; x++)
  {
    for (int y = 0; y < bmp.Height; y++)
    {
      if (bmp.GetPixel(x, y).GetBrightness() > 0.5)
        SetIndexedPixel(x, y, data, true);
      else
        SetIndexedPixel(x, y, data, false);
    }
  }

  monoBmp.UnlockBits(data);
  return monoBmp;
}

private static unsafe void SetIndexedPixel(int x, int y, BitmapData bmd, bool pixel)
{
  byte* p = (byte*)bmd.Scan0.ToPointer();
  int index = y * bmd.Stride + (x >> 3);
  byte mask = (byte)(0x80 >> (x & 0x7));

  if (pixel)
    p[index] |= mask;
  else
    p[index] &= (byte)(mask ^ 0xff);
}

private static ImageCodecInfo GetEncoderInfo(string mineType)
{
  ImageCodecInfo[] encs = ImageCodecInfo.GetImageEncoders();

  foreach (System.Drawing.Imaging.ImageCodecInfo enc in encs)
    if (enc.MimeType == mineType)
      return enc;

  return null;
}

SaveMonoBitmapメソッドはこの形で利用できます。

Bitmap bmpColor = new Bitmap("c:\test.bmp");
SaveMonoBitmap(bmpColor, "c:\mono.bmp");

 

WEBサイトが接続可能になっているかどうかを、チェックできるメソッドを作りました。

using System;
using System.Collections.Generic;
using System.Text;
using System.Net;
using System.Reflection;
using System.Windows.Forms;

public bool isWebEnable(string strURL)
{
  try
  {
    Uri siteUri = new Uri(strURL);
    WebRequest wr = WebRequest.Create(siteUri);
    wr.Timeout = 10000;
    WebResponse myResponse = wr.GetResponse();
    myResponse.Close();

    return true;
  }
  catch (WebException ex)
  {
    string ERRMSG = ex.Message + "\r\n\r\n URL:" + strURL;
    MessageBox.Show(ERRMSG, "WEB接続エラー"
      , MessageBoxButtons.OK, MessageBoxIcon.Warning);

    return false;
  }
  catch (Exception ex)
  {
    throw ex;
  }
}

Copyメソッドを使用して、DataSetを複製する場合

Copyメソッドを使用して、DataSetを複製する場合は、AcceptChangesメソッドを先に実行しておかないと、ソース上で変更したデータが、複製先にコピーされない。という現象に遭遇しました。

dsMsg.AcceptChanges();
DataSet dsMsgTmp = dsMsg.Copy();