13,825
社区成员
发帖
与我相关
我的任务
分享
SELECT dbo.Today.Task AS TaskDescr, dbo.Today.No, dbo.Today.Downno,
dbo.Today_2.time4 AS time4, dbo.Today.Via AS bypass, dbo.Today.Hos AS Hos,
dbo.Today.time1 AS time1, dbo.Today.time2 AS time2,
dbo.Today.time5 AS Time5, dbo.Today.time3 AS time3,
dbo.Today.Area AS area, '出' AS io, dbo.Today.Status,
CASE WHEN dbo.Today.ber < 0 THEN '' WHEN dbo.Today.ber IN (1) THEN '南' + CONVERT(varchar,
dbo.Today.ber) WHEN dbo.Today.ber > 17 THEN '北' + CONVERT(varchar, dbo.Today.ber)
ELSE CONVERT(varchar, dbo.Today.ber) END AS Ber, dbo.Today.Gate AS Gate,
dbo.Today.Pla AS Pla, dbo.Today.type,
dbo.Reason.ReasonCN AS DisplayReason, CASE WHEN ltrim(rtrim(dbo.Today.Memo)) = '' AND
ltrim(rtrim(dbo.Today.StatMemo)) <> '' THEN dbo.TodayD.StatMemo WHEN ltrim(rtrim(dbo.Today.Memo))
<> '' AND ltrim(rtrim(dbo.Today.StatMemo))
= '' THEN dbo.Today.Memo WHEN ltrim(rtrim(dbo.Todays.Memo)) <> '' AND
ltrim(rtrim(dbo.Today.StatMemo))
<> '' THEN dbo.Today.Memo + ',' + dbo.Today.StatMemo WHEN ltrim(rtrim(dbo.Today.Memo)) = '' AND
ltrim(rtrim(dbo.Today.StatMemo)) = '' THEN '' END AS Remark, ISNULL(dbo.f_MergeVipName(dbo.Today.No,
N'出', dbo.Today.Opday), N'') AS vipinfo, dbo.Today.lines
FROM dbo.Today LEFT OUTER JOIN
dbo.Reason ON dbo.Today.PublishReason = dbo.Reason.ReasonID LEFT OUTER JOIN
dbo.Today_2 ON dbo.Today.Downno = dbo.Today_2.no
WHERE (dbo.Today.Task NOT IN ('货A', '货B'))
这里的dbo.Today和dbo.Today_2都分别只有100多条的记录。字段也差不多都是35个。dbo.Reason里面是31条记录,6个字段。
可能关键是dbo.Today和dbo.Today_2,因为他们的数据是别的存储过程或者客户端实时写进来更新的。
dbo.f_MergeVipName,这个函数如下:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER function [dbo].[f_MergeVipName](@no nvarchar(20),@Io nvarchar(20),@Opday Datetime)
returns nvarchar(Max)
as
begin
declare @r nvarchar(Max)
set @r=''
select @r=@r+';'+( Name+','+ Class) from Vip where no=@no and Io = @Io and [Date]=@opday
return stuff(@r,1,1,'')
end
会不会是视图里面的memo判断导致的?还是这个dbo.f_MergeVipName函数导致的?
若是函数导致的,我测试了select Name+','+ Class as test from Vip where no like '%%' and Io = '出' and [Date]='2013-07-25',执行时间是0秒。
这个VIP表里有2万条记录,但是每天的记录也就10几条,很少的。
我写的视图很烂,还请大侠们费心看看。
SELECT dbo.Today.Task AS TaskDescr, dbo.Today.No, dbo.Today.Downno,
dbo.Today_2.time4 AS time4, dbo.Today.Via AS bypass, dbo.Today.Hos AS Hos,
dbo.Today.time1 AS time1, dbo.Today.time2 AS time2,
dbo.Today.time5 AS Time5, dbo.Today.time3 AS time3,
dbo.Today.Area AS area, '出' AS io, dbo.Today.Status,
CASE WHEN dbo.Today.ber < 0 THEN '' WHEN dbo.Today.ber IN (1) THEN '南' + CONVERT(varchar,
dbo.Today.ber) WHEN dbo.Today.ber > 17 THEN '北' + CONVERT(varchar, dbo.Today.ber)
ELSE CONVERT(varchar, dbo.Today.ber) END AS Ber, dbo.Today.Gate AS Gate,
dbo.Today.Pla AS Pla, dbo.Today.type,
dbo.Reason.ReasonCN AS DisplayReason, CASE WHEN ltrim(rtrim(dbo.Today.Memo)) = '' AND
ltrim(rtrim(dbo.Today.StatMemo)) <> '' THEN dbo.TodayD.StatMemo WHEN ltrim(rtrim(dbo.Today.Memo))
<> '' AND ltrim(rtrim(dbo.Today.StatMemo))
= '' THEN dbo.Today.Memo WHEN ltrim(rtrim(dbo.Todays.Memo)) <> '' AND
ltrim(rtrim(dbo.Today.StatMemo))
<> '' THEN dbo.Today.Memo + ',' + dbo.Today.StatMemo WHEN ltrim(rtrim(dbo.Today.Memo)) = '' AND
ltrim(rtrim(dbo.Today.StatMemo)) = '' THEN '' END AS Remark, ISNULL(dbo.f_MergeVipName(dbo.Today.No,
N'出', dbo.Today.Opday), N'') AS vipinfo, dbo.Today.lines
FROM dbo.Today LEFT OUTER JOIN
dbo.Reason ON dbo.Today.PublishReason = dbo.Reason.ReasonID LEFT OUTER JOIN
dbo.Today_2 ON dbo.Today.Downno = dbo.Today_2.no
WHERE (dbo.Today.Task NOT IN ('货A', '货B'))
这里的dbo.Today和dbo.Today_2都分别只有100多条的记录。字段也差不多都是35个。dbo.Reason里面是31条记录,6个字段。
可能关键是dbo.Today和dbo.Today_2,因为他们的数据是别的存储过程或者客户端实时写进来更新的。
dbo.f_MergeVipName,这个函数如下:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER function [dbo].[f_MergeVipName](@no nvarchar(20),@Io nvarchar(20),@Opday Datetime)
returns nvarchar(Max)
as
begin
declare @r nvarchar(Max)
set @r=''
select @r=@r+';'+( Name+','+ Class) from Vip where no=@no and Io = @Io and [Date]=@opday
return stuff(@r,1,1,'')
end
会不会是视图里面的memo判断导致的?还是这个dbo.f_MergeVipName函数导致的?
若是函数导致的,我测试了select Name+','+ Class as test from Vip where no like '%%' and Io = '出' and [Date]='2013-07-25',执行时间是0秒。
这个VIP表里有2万条记录,但是每天的记录也就10几条,很少的。
__fastcall TGetView::TGetView(bool CreateSuspended,TADOQuery * ADOQ_Post_Out,TADOQuery * ADOQ_Post_In)
: TThread(CreateSuspended)
{
ADOConn = new TADOConnection(NULL);
ADOConn->ConnectionString = AnsiString("Provider=SQLOLEDB.1;Password=801;\
Persist Security Info=True;Auto Translate=false;\
User ID=sa;\
Initial Catalog=New;\
Data Source=192.168.226.5");
ADOConn->ConnectionTimeout = 600;
ADOConn->CommandTimeout = 600;
ADOConn->KeepConnection = false;
ADOConn->LoginPrompt = false;
ADOQ_Get_View_Out = new TADOQuery(NULL);
ADOQ_Get_View_Out->Connection = ADOConn;
ADOQ_Get_View_Out->CommandTimeout = 600;
ADOQ_Get_View_In = new TADOQuery(NULL);
ADOQ_Get_View_In->Connection = ADOConn;
ADOQ_Get_View_In->CommandTimeout = 600;
ADOQ_Get_View_Out = ADOQ_Post_Out;
ADOQ_Get_View_In = ADOQ_Post_In;
Out = AnsiString( "Select * from View_Ter_Out_20130708");
In = AnsiString("Select * from View_Ter_In_20130708");
}
//---------------------------------------------------------------------------
void __fastcall TGetView::Execute()
{
//---- Place thread code here ----
CoInitialize(NULL);
ADOConn->Connected = true;
Synchronize(MyExe);
CoUninitialize();
}
//---------------------------------------------------------------------------
void __fastcall TGetView::MyExe()
{
while( !Suspended || !Terminated )
{
ADOQ_Get_View_Out->Close();
ADOQ_Get_View_Out->SQL->Clear();
ADOQ_Get_View_Out->SQL->Add(Out);
ADOQ_Get_View_Out->Open();
ADOQ_Get_View_In->Close();
ADOQ_Get_View_In->SQL->Clear();
ADOQ_Get_View_In->SQL->Add(In);
ADOQ_Get_View_In->Open();
::Sleep(30000);
}
}
__fastcall TGetView::TGetView(bool CreateSuspended,TADOQuery * ADOQ_Post_Out,TADOQuery * ADOQ_Post_In)
: TThread(CreateSuspended)
{
ADOQ_Get_View_Out = ADOQ_Post_Out;
ADOQ_Get_View_In = ADOQ_Post_In;
}
//---------------------------------------------------------------------------
void __fastcall TGetView::Execute()
{
//---- Place thread code here ----
Synchronize(MyExe);
}
//---------------------------------------------------------------------------
void __fastcall TGetView::MyExe()
{
CoInitialize(NULL);
while( !Suspended || !Terminated )
{
ADOQ_Get_View_Out->Close();
ADOQ_Get_View_Out->Open();
ADOQ_Get_View_In->Close();
ADOQ_Get_View_In->Open();
::Sleep(30000);
}
CoUninitialize();
}
//线程类的头文件:
#ifndef Unit_GetViewH
#define Unit_GetViewH
//---------------------------------------------------------------------------
#include <Classes.hpp>
#include <ADODB.hpp>
#include <DB.hpp>
//---------------------------------------------------------------------------
class TGetView : public TThread
{
private:
protected:
void __fastcall Execute();
public:
__fastcall TGetView(bool CreateSuspended,TADOQuery * ADOQ_Post_Out,TADOQuery * ADOQ_Post_In);
TADOQuery * ADOQ_Get_View_Out;
TADOQuery * ADOQ_Get_View_In;
void __fastcall MyExe();
};
但是还是不行,程序进去了,但是主窗口的表格都看不见了,出不来,然后马上整个界面就白了,程序卡死了,又没有弹出出错框,只好杀了进程,程序无法执行。
故障完全一样。
请妖哥帮忙啊!
__fastcall TGetView::TGetView(bool CreateSuspended,TDataSource *DataSource_Post_Out,TDataSource *DataSource_Post_In)
: TThread(CreateSuspended)
{
ADOConn = new TADOConnection(NULL);
ADOConn->ConnectionString = AnsiString("Provider=SQLOLEDB.1;Password=801;\
Persist Security Info=True;Auto Translate=false;\
User ID=sa;\
Initial Catalog=New;\
Data Source=192.168.226.5");
ADOConn->ConnectionTimeout = 600;
ADOConn->CommandTimeout = 600;
ADOConn->KeepConnection = false;
ADOConn->LoginPrompt = false;
DataSource_Out = DataSource_Post_Out;
DataSource_In = DataSource_Post_In;
ADOQ_Get_View_Out = new TADOQuery(NULL);
ADOQ_Get_View_Out->Connection = ADOConn;
ADOQ_Get_View_Out->CommandTimeout = 600;
ADOQ_Get_View_In = new TADOQuery(NULL);
ADOQ_Get_View_In->Connection = ADOConn;
ADOQ_Get_View_In->CommandTimeout = 600;
DataSource_Out->DataSet = ADOQ_Get_View_Out;
DataSource_In->DataSet = ADOQ_Get_View_In;
Out = AnsiString( "Select * from View_Ter_Out_20130708");
In = AnsiString("Select * from View_Ter_In_20130708");
}
//---------------------------------------------------------------------------
void __fastcall TGetView::Execute()
{
//---- Place thread code here ----
Synchronize(MyExe);
}
//---------------------------------------------------------------------------
void __fastcall TGetView::MyExe()
{
CoInitialize(NULL);
ADOConn->Connected = true;
while( !Suspended || !Terminated )
{
ADOQ_Get_View_Out->Close();
ADOQ_Get_View_Out->SQL->Clear();
ADOQ_Get_View_Out->SQL->Add(Out);
ADOQ_Get_View_Out->Open();
ADOQ_Get_View_In->Close();
ADOQ_Get_View_In->SQL->Clear();
ADOQ_Get_View_In->SQL->Add(In);
ADOQ_Get_View_In->Open();
::Sleep(30000);
}
CoUninitialize();
}
//线程类的头文件:
#ifndef Unit_GetViewH
#define Unit_GetViewH
//---------------------------------------------------------------------------
#include <Classes.hpp>
#include <ADODB.hpp>
#include <DB.hpp>
//---------------------------------------------------------------------------
class TGetView : public TThread
{
private:
protected:
void __fastcall Execute();
public:
__fastcall TGetView(bool CreateSuspended,TDataSource *DataSource_Post_Out,TDataSource *DataSource_Post_In);
TADOQuery * ADOQ_Get_View_Out;
TADOQuery * ADOQ_Get_View_In;
TADOConnection *ADOConn;
TDataSource *DataSource_Out;
TDataSource *DataSource_In;
AnsiString Out,In;
void __fastcall MyExe();
};
然后,在DM模块里拉了两个DataSource控件:DM->DataSource_Ter_Out,DM->DataSource_Ter_In
然后把主窗口类里面的两个表格控件的DataSource属性设置为:DM->DataSource_Ter_Out,DM->DataSource_Ter_In
接着在主窗口类的FormShow里面:
Thread_GetView = new TGetView(false,DM->DataSource_Ter_Out,DM->DataSource_Ter_In);
主窗口类的头文件里声明了:TGetView * Thread_GetView;
现在情况是这样子,程序进去了,但是主窗口的表格都看不见了,出不来,然后马上整个界面就白了,程序卡死了,又没有弹出出错框,只好杀了进程,程序无法执行。
求求妖哥帮忙啊!