1、) AS Vals FROM dbo.HerSetting WHERE (Enabled=1) AND (SettingNode=P1),NP1 varchar(200),App.User.Code /节点名根据参数变化2、原因分析1)代码调用function TFormBizSet.GetFunctionValFromHerSetting(SettingNode: string): string;var astr:begin astr := FormOptions.getADOConnectionByActivePage.ConnectionString; StandardDaemon_In
2、it(PCHAR(astr); /问题所在Result := StandardDaemon_GetFunctionSettingVal(PCHAR(SettingNode);end;2)根源在DLL中执行一次StandardDaemon_GetFunctionSettingVal函数,会产生10次查表语句,其中5次是可以省去,4次是可以优化处理。3)分析: aqPeis.SQL.Add ( AND (SettingNode=:SettingNode); 执行后会产生SQL语句,如下所示:SET FMTONLY ON select SettingNode from dbo.HerSetting
3、WHERE 1=2 SET FMTONLY OFF3、调试方法1)打开SQL Profiler2)新建跟踪并设置过滤条件并且跟踪结果,如下所示1选择“事件选择”页签2选中“显示所有列”并单击“列筛选器”按钮3过滤掉冰激凌监控4只监控PEIS数据库5确定后,运行跟踪6在源码中打上断点7运行程序,到断点后观察profiler监控,此时只有2条,程序单步调试后,再观察,如下图所示,会出现大量重复的语句调用。8整个程序完全启动后,以上重复语句会达到30条之多。改进建议1)建议程序只加载一次表查询功能,剩下的过滤等操作可以用filter及filtered属性过滤,这样便可以减少多次访问数据表的操作且可以
4、大大提高执行效率。2)将adoquery.sql.add用字符串连接替换。aqPeis.SQL.Add (语句可以使用字符串连接来避免多出来的一句SQL查询语句 s:= SELECT SettingName, + aVal + AS Vals+ FROM dbo.HerSetting WHERE (Enabled=1) AND (SettingNode=+astr+);aqPeis.SQL.Text:=s; 3)StandardDaemon_GetHerSetting_Inner多次调用同一个表的解决办法修改函数:思路:利用全局表处理优点:只加载一次,效率提高缺点:实时性差,修改配置后,需要重
5、启软件才能得到结果总结:适用于配置后不常变化的数据集加载implementationuses UFunction; / Added by 建 2017-1-11 15:06:47 g_HerSettingTable:TADOTable;-Function StandardDaemon_GetHerSetting_Inner(SettingNode: SettingField: string) : Variant; i : integer; astr, bstr, aVal:string; / Modified by 建 2017-1-11 15:08:12 /利用临时表优化= SettingN
6、ode; bstr := try if not StandardDaemon_CheckNil(aqPeis, aqPeis) then exit; with g_HerSettingTable do begin /空记录集时,则查表并克隆记录集 if Eof and bof then if aqPeis.Active then aqPeis.Close; aqPeis.SQL.Clear; /查找所有变量字段,连接字段字符串 aVal: COALESCE(ValString, ) as ValString + COALESCE(ValEncoded, ) as ValEncoded COAL
7、ESCE(SettingType, ) as SettingType COALESCE(ValInteger, 0) as ValInteger COALESCE(ValBool, 0) as ValBool COALESCE(ValDateTime, 0) as ValDateTime COALESCE(ValMoney, 0) as ValMoney / Modified by 建 2017-1-10 14:32:45 /优化,减少一条SQL查表语句 s : SELECT SettingName,SettingNode, + aVal + + aqPeis.SQL.Text := s; a
8、qPeis.Open; /克隆表 Clone(aqPeis); end;filter: SettingNode=+ + astr + ; filtered:=true; if RecordCount 0 then result := FieldByName(SettingField).Value; end else result := Null; except On E: Exception Do begin / StandardDaemon_GetHerSetting_Inner 错误: + E.Message; if VarIsNull(result) then begin if Same
9、Text(SettingField, ValString) then result : else if SameText(SettingField, ValEncodedSettingTypeValInteger= 0ValBool= falseValDateTime= EncodeDate(2001, 1, 1)ValMoney= 0.0;initialization CoInitialize(nil); / ADO 需要, USES: Ole2 This_ID_Depart := 0; This_DoLogging := true;07:17 /建立临时表 g_HerSettingTable:=TADOTable.Create(nil);finalization37 FreeAndNil(g_HerSettingTable); CoUnInitialize(); 建
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1