愚者の経験

「また今度」はほとんどこない

月別アーカイブ: 12月 2012

Viewに全列指定の「*」は使わない[SQL Server]

何かと便利ですがパフォーマンスの観点からよく議題に挙がる全列指定のアスタリスク「*」
列が多い場合、めんどくさくて使ってしまいがちですがSQL ServerのViewにおいては
これで直接列名を取るのはやめたほうがいいです。特に開発中。

パフォーマンスが理由ではなく、「テーブルの変更に追従してこない」からです。
オプションとか探せばあるのかもしれませんが、SQL Server 2008 R2の既定では

1.テーブルを作成し

CREATE TABLE [dbo].[Table_3](
	[int1] [nchar](10) NULL,
	[int2] [nchar](10) NULL,
	[int3] [nchar](10) NULL,
	[int4] [nchar](10) NULL,
	[int5] [nchar](10) NULL
) ON [PRIMARY]

2.Viewを作成(*で全列指定)

CREATE VIEW [dbo].[View_1]
AS
SELECT                  dbo.Table_3.*
FROM                     dbo.Table_3

↓表示するとこんな感じ
2

3.列を追加

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Table_3 ADD
	int6 nchar(10) NULL
GO
ALTER TABLE dbo.Table_3 SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

↓デザインで見るとこんな感じ
3

ですがViewを見てみても
2
全く変わりがなくこのままなんです。

テーブルで列を増やして自動的にViewにも現れると思ったらハマります。
実際はViewを保存しなおさない限り(Alterしない限り)、View側の列は変更されないのです。

じゃあ列削除するとどうなるかというと…このようにエラーで開けません。
4

エラー「ビューまたは関数には定義された列数よりも多くの列名が指定されています。」
が発生します。列名はちゃんと指定しましょう。

WebMatrix2で正規表現を使った置換が出来ない

これ何とかならないんですかね。
それともやり方が間違っているのかな?

例えばNotepad++だと

<img src=”” alt=””>

に対して『(&ltimg.*?)>』と正規表現で検索で検索するとヒットします。
これは「MebMatrix2」でもヒットします。
ですが困ったことに置換後の文字列を指定できません。
『\1 />』みたいにしてとりあえず閉じようと思ってもできません。

これがないと結構片手落ちな気がします…

InputParametersの文字数制限を回避する方法[adp]

Mictrosoft Access プロジェクト(adp)はAccess2013でサポートされないみたいですが…

adpのフォームやレポートには「InputParameters」プロパティが存在します。
レコードソースに指定したストアドプロシージャやテーブル値関数にパラメータが
ある場合、ここに記述してパラメータを渡せます。

ただ困ったことにここには文字数制限があります。確か2048文字だったような(^.^;
この文字数制限ですが、一応回避可能です。

やり方はパラメータの値をコントロールやオブジェクトの参照にすることです。

InputParameters=”@p1=xxxxxxxxx”
となっているのを
InputParameters=”@p1=Forms![フォーム1]![テキスト1]”
のようにコントロール参照にしてテキストボックスに値を代入すれば
文字数制限を突破してパラメータを与えられます。

個人的にはTempVarsを使うのがいいと思い始めてます。2007以降になりますが
お手軽で扱い易いと思っています。フォームコントロール以外で参照出来る数少ない
オブジェクトです。

またサブフォームのレコードソースに指定したストアドプロシージャやテーブル値関数
のパラメータと、同名のコントロールが存在する場合、サブフォームが勝手に連動する
困った動作をします。これは結構厄介です。

フォームの詳細セクションのコントロールが必ずフォーカスされる

今まで気づきませんでしたが、大問題です。
フォームには「タブオーダー」がありますが、これは「セクション」毎にわかれています。

どうやっても(というか設定が見当たらないんですが)例えば「フォームヘッダーセクションの
ボタンコントロールに最初にフォーカス」みたいなことはどうやらできません。

詳細セクションの最初にタブストップ出来るコントロールが
フォームオープン時にフォーカスされるようです…なんとか出来ないのかな?orz

情報を求む。

しかもタブ移動する場合セクションを超えられないようですし…。

ストアドプロシージャへ渡したパラメータはそのままwhere句に使わないと遅い

ストアドプロシージャにパラメータを渡すとき
「where句にそのまま使うパラメータ」でないとSQLServerの処理速度は上がらないようです。

例えばUIで「年」と「月」を選んで検索するような場合、

alter PROCEDURE test 
(
	@pYear int,
	@pMonth int
)
AS
BEGIN
	declare @date1 datetime,@date2 datetime
	set @date1=@pYear+'/'+@pMonth+'/'+1
	set @date2=DATEADD(DAY,-1,DATEADD(MONTH,1,@date1))
	
	select X.*
	from dbo.table1
	where X.fDate between @date1 and @date2
END
GO

のようにUI側の入力をパラメータにしてストアドプロシージャ側で実際のSQLを発行する場合と

alter PROCEDURE test 
(
	@pDate1 datetime,
	@pDate2 datetime
)
AS
BEGIN
	select X.*
	from dbo.table1
	where X.fDate between @date1 and @date2
END
GO

アプリケーション側でパラメータを直接SQL文に使える形で渡した場合とでは
「後者の方が高速」らしいです。

パラメータを元に帰ってくる行数やそのメモリの割当を行なって動作を最適化しているようで、
「ストアド内でパラメータの置換をするとパフォーマンスが悪くなる」というようなことが
msdnのどこかに記述してありました…曖昧な情報ですみません。

where句のIsnull関数の多用に注意?

SQLServerである程度データが多いテーブルで「結果が帰ってくるのが異常に
遅くなった」ということはないでしょうか?

select X.*
from dbo.Table1 as X
whereX.field1=isnull(@field1,X.field1)
        and X.field2=isnull(@field2,X.field2)
        and X.field3=isnull(@field3,X.field3)
        and X.field4=isnull(@field4,X.field4)
        and X.field5=isnull(@field5,X.field5)
        and X.field6=isnull(@field6,X.field6)
        and X.field7=isnull(@field7,X.field7)
        and X.field8=isnull(@field8,X.field8)
        and X.field9=isnull(@field9,X.field9)

ストアドでこんな感じにisnullを多用していると遅くなっているケースが幾つか
ありました。
そんな症状が出ている場合は試しに以下の様にコメントアウトしてみてください。

実行すると1分経っても実行中なのにwhere句を削ると
1秒未満で結果が帰ったりします。もちろんパラメータが全部null値で件数が
同じ場合でです。

select X.*
from dbo.Table1 as X
whereX.field1=isnull(@field1,X.field1)
        and X.field2=isnull(@field2,X.field2)
        and X.field3=isnull(@field3,X.field3)
        and X.field4=isnull(@field4,X.field4)
        --and X.field5=isnull(@field5,X.field5)
        --and X.field6=isnull(@field6,X.field6)
        --and X.field7=isnull(@field7,X.field7)
        --and X.field8=isnull(@field8,X.field8) 
        --and X.field9=isnull(@field9,X.field9)

スワップが始まっているのかよくわかりませんが、メモリを増設しても速度が戻ったりします。
「データ件数が増えて遅くなるなんてテーブル設計が間違っている!」は仰るとおりですが、
検索条件を増やすなんてしょっちゅうあります…よね?

CollectionのKeyやIndexを取得

IndexからKeyを取得って出来るんですね。
メモリ操作を伴うので邪道というか禁じ手かもしれませんが。

参考URL:http://www.vbforums.com/showthread.php?570762-Get-Index-Key-in-Collection-from-other

こうゆうのってどこで調べるんでしょうかね?

Option Explicit
 
Private Declare Sub PokeLong Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, Optional ByVal Length As Long = 4)
 
Private Function ItemKey(ByVal Index As Long, Coll As Collection) As String
 
  'optimized get collection sKey by index
  'Private Declare Sub PokeLong Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, Optional ByVal Length As Long = 4)
 
  Dim i     As Long
  Dim Ptr   As Long
  Dim sKey  As String
 
    If Coll Is Nothing Then                             'oops!
        Err.Raise 91                                    'No object
      Else 'NOT COLL...
        Select Case Index
          Case Is  Coll.Count                  'oops!
            Err.Raise 9                                 'Index out of range
          Case Is <= Coll.Count / 2                     'walk items upwards from first
            PokeLong Ptr, ByVal ObjPtr(Coll) + 24       'first Ptr
            For i = 2 To Index
                PokeLong Ptr, ByVal Ptr + 24            'next Ptr
            Next i
          Case Else                                     'walk items downwards from last
            PokeLong Ptr, ByVal ObjPtr(Coll) + 28       'last Ptr
            For i = Coll.Count - 1 To Index Step -1
                PokeLong Ptr, ByVal Ptr + 20            'prev Ptr
            Next i
        End Select
        i = StrPtr(sKey)                                'save StrPtr
        PokeLong ByVal VarPtr(sKey), ByVal Ptr + 16     'replace StrPtr by that from collection sKey (which is null if there ain't no sKey)
        ItemKey = sKey                                  'now copy it to function value
        PokeLong ByVal VarPtr(sKey), i                  'and finally restore original StrPtr
    End If
 
End Function
 
Private Function ItemIndex(ByVal Key As String, Coll As Collection, Optional ByVal Compare As VbCompareMethod = vbTextCompare) As Long
 
  'get collection index by key
  'Private Declare Sub PokeLong Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, Optional ByVal Length As Long = 4)
 
  Dim Ptr   As Long
  Dim sKey  As String
  Dim aKey  As Long
 
    If Coll Is Nothing Then                             'oops!
        Err.Raise 91                                    'No object
      Else 'NOT COLL...
        If Coll.Count Then
            aKey = StrPtr(sKey)                         'save StrPtr
            PokeLong Ptr, ByVal ObjPtr(Coll) + 24       'first Ptr
            ItemIndex = 1                               'walk items upwards from first
            Do
                PokeLong ByVal VarPtr(sKey), ByVal Ptr + 16
                If StrComp(Key, sKey, Compare) = 0 Then 'equal
                    Exit Do                             'found
                End If
                ItemIndex = ItemIndex + 1               'next Index
                PokeLong Ptr, ByVal Ptr + 24            'next Ptr
            Loop Until Ptr = 0                          'end of chain
            PokeLong ByVal VarPtr(sKey), aKey           'restore original StrPtr
        End If
        If Ptr = 0 Then
            ItemIndex = -1                              'key not found
        End If
    End If
 
End Function

ちょっと危険な気もしますが、コレクションをセットで用意せずともいいメリットがあります。
いちいちこんな感じで用意するのはめんどくさいです。Removeとか同期取る必要ありますし。

    Values Add Value, Key
    Keys.Add Key, Key

また若干処理速度が速そうです。簡単に実験してみました。


Private Function ItemIndex1(ByVal key As String, _
                Coll As Collection, _
                Optional ByVal Compare As VbCompareMethod = vbBinaryCompare) As Long
 
  'get collection index by key
 
  Dim Ptr   As Long
  Dim sKey  As String
  Dim aKey  As Long
 
    aKey = StrPtr(sKey)                         'save StrPtr
    RtlMoveMemory Ptr, ByVal ObjPtr(Coll) + 24       'first Ptr
    ItemIndex1 = 1                               'walk items upwards from first
    Do
        RtlMoveMemory ByVal VarPtr(sKey), ByVal Ptr + 16
        If StrComp(key, sKey, Compare) = 0 Then 'equal
            Exit Do                             'found
        End If
        ItemIndex1 = ItemIndex1 + 1               'next Index
        RtlMoveMemory Ptr, ByVal Ptr + 24            'next Ptr
    Loop Until Ptr = 0                          'end of chain
    RtlMoveMemory ByVal VarPtr(sKey), aKey           'restore original StrPtr
 
End Function

Private Function ItemIndex2(ByVal key As String, _
                Coll As Collection, _
                Optional ByVal Compare As VbCompareMethod = vbBinaryCompare) As Long
  
    Dim sKey As Variant
    
    For Each sKey In Coll
        ItemIndex2 = ItemIndex2 + 1
        If StrComp(key, sKey, Compare) = 0 Then 'equal
            Exit For                             'found
        End If
    Next
End Function

Public Sub test()
    Dim c As Collection
    Dim k As Collection
    Dim i As Long
    
    Dim tm As Single
    
    Set c = New Collection
    Set k = New Collection
    
    For i = 0 To 10000
        c.Add i, CStr(i)
        k.Add CStr(i), CStr(i)
    Next
    
    tm = Timer
    For i = 0 To 1000
        ItemIndex1 10000, c
    Next
    
    Debug.Print Timer - tm
    
    tm = Timer
    For i = 0 To 1000
        ItemIndex2 10000, k
    Next
    
    Debug.Print Timer - tm
End Sub

イミディエイト
test
1.574219
2.679688
1.5625
2.660156
1.558594
2.691406