只需要从大约五个月的庞大数据中选择一个日期 [英] Need to select only a single date from a huge data of around five months

问题描述

Hi Dotnet Geeks，

我只需要从大约五个月的庞大数据中选择一个日期即可.
我已经为它写了一个存储过程，现在我想从中检索一个日期.因为在访问该存储过程中检索大量数据时，我将收到超时异常.

只是我需要在查询中放置一个条件，如
C.log_date =''2012-03-02''


如果您不理解，请通过SP并在那里查看C.log_date.
请给我我可以得到特定约会的小条件

Hi Dotnet Geeks,

I need to select only a single date from a huge data of around five months.
I have written a stored procedure for the same and now i want to retrieve a single date from that; since i will be getting timeout exception while accessing that stored procedure retreiving huge data.

Just i need to put a condition in query like
C.log_date= ''2012-03-02''


if you don''t understand please go through SP and see C.log_date there.
Please give me that small condition where i can get a particular date

create PROCEDURE [dbo].[Get_op_meter_data]
  	@Point_location VARCHAR(50),
	@sw_name VARCHAR(30),
	@p_equip_id CHAR(10),
	@equip_id CHAR(10),	
	@source_type_id CHAR(10),
	@log_date date
AS
Declare @log_time varchar(20),@ASE_Reading numeric,@G_Reading numeric
SELECT T.Point_location,T.sw_name, T.p_equip_id, T.equip_id, log_date, log_time, SUM(E_Reading) ASE_Reading , SUM(G_reading) AS G_Reading
FROM (
SELECT A.Point_location,SW.sw_name, A.p_equip_id, A.equip_id, CONVERT(DATE,C.log_date) AS log_date, CONVERT(VARCHAR(20), C.log_hrs)+ ':' + CONVERT(VARCHAR(20),C.log_min) AS log_time, C.Act_value AS E_Reading, 0 AS G_reading
FROM om_equipment A, om_equip_meter_map B, om_service_window SW, om_sw_location_map S, tt_raw_meter C
WHERE A.equip_id = B.equip_id

And C.log_date= '2012-03-02'


AND CONVERT(VARCHAR(20), C.log_hrs)+ ':' + CONVERT(VARCHAR(20),C.log_min) = CONVERT(VARCHAR(20), s.start_hrs)+ ':' + CONVERT(VARCHAR(20),s.end_hrs)
AND SW.sw_id=S.sw_id
AND   B.point_location = C.point_Location
AND   B.point_device = C.point_device
AND   B.functionblock = C.FunctionBlock
AND B.source_type_id = 'RAW_POW'
UNION
SELECT A.Point_location,SW.sw_name, A.p_equip_id, A.equip_id, CONVERT(DATE,C.log_date) AS log_date, CONVERT(VARCHAR(20), C.log_hrs)+ ':' + CONVERT(VARCHAR(20),C.log_min) AS log_time, 0 AS E_Reading, C.Act_value AS G_reading
FROM om_equipment A, om_equip_meter_map B, om_service_window SW, om_sw_location_map S, tt_raw_meter C
WHERE A.equip_id = B.equip_id
AND SW.sw_id=S.sw_id
AND CONVERT(VARCHAR(20), C.log_hrs)+ ':' + CONVERT(VARCHAR(20),C.log_min) = CONVERT(VARCHAR(20), s.start_hrs)+ ':' + CONVERT(VARCHAR(20),s.end_hrs)
AND   B.point_location = C.point_Location
AND   B.point_device = C.point_device
AND   B.functionblock = C.FunctionBlock
AND B.source_type_id = 'GAS') T
GROUP BY T.Point_location, T.sw_name, T.p_equip_id, T.equip_id, log_date, log_time
ORDER BY T.Point_location, T.sw_name, T.p_equip_id, T.equip_id, log_date, log_time

Declare @TempTableVariable TABLE
(
Point_location  VARCHAR(50),
sw_name VARCHAR(30), 
p_equip_id CHAR(10), 
equip_id CHAR(10),
log_date datetime,
log_time datetime, 
ASE_Reading  numeric, 
G_Reading  numeric
)
Begin
insert into @TempTableVariable(Point_location,sw_name,p_equip_id,equip_id,log_date,log_time,ASE_Reading,G_Reading) 
values(@Point_location,@sw_name,@p_equip_id,@equip_id,@log_date,@log_time,@ASE_Reading,@G_Reading)
	End

推荐答案

更改连接的CommandTimeout属性.

最好的祝福，
Pablo.

Change the CommandTimeout property of your connection.

Best wishes,
Pablo.

您要让SP进行大量工作……也许在创建另一个由触发器填充的表时可能会花费更多的精力，这会创建更多可用的值?

它上面写有索引和内部联接

you''re asking your SP to do an awful lot of work ... perhaps there might be more mileage in creating another table, populated by trigger, which creates more useable values?

This has indices and inner join written all over it

这篇关于只需要从大约五个月的庞大数据中选择一个日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！