I am very new to TSQL and the program that follows loops many times over several hours. In task manager I have noticed that the PF usage has grown from 1.08 to 2.05 over several hours and results in a virtual memory shortage error being displayed. I am inclined to believe I am doing something wrong and accumulating some stack space. Can anyone tell me what I am doing wrong? PF does drop back to normal when I drop out of SQL.
thanks in advance
-Soup-
use sm
declare @.test_date as smalldatetime
declare @.ticker char(6)
declare @.buy_date smalldatetime
declare @.buy_price smallmoney
declare @.sell_date_5d smalldatetime
declare @.sell_price_5d smallmoney
declare @.sell_date_10d smalldatetime
declare @.sell_price_10d smallmoney
declare @.sell_date_20d smalldatetime
declare @.sell_price_20d smallmoney
if object_id('sm.dbo.Open_High_v2')is not null
drop table sm.dbo.Open_High
create table sm.dbo.Open_High
( ticker char(6),
detect_date smalldatetime,
buy_date smalldatetime,
buy_price smallmoney,
sell_date_5d smalldatetime,
sell_price_5d smallmoney,
sell_date_10d smalldatetime,
sell_price_10d smallmoney,
sell_date_20d smalldatetime,
sell_price_20d smallmoney,
)
if object_id('sm.dbo.Temp_Price')is null
create table sm.dbo.Temp_Price
(
price char (6),
Trade_Date smalldatetime,
[Open] smallmoney,
High smallmoney,
Low smallmoney,
[Close] smallmoney,
Volume int
)
if object_id('date_list') is not null
begin
close date_list
deallocate date_list
end
declare date_list cursor for
select distinct ticker, trade_date
from price
order by Ticker asc, Trade_Date asc
open date_list
fetch next from date_list into @.ticker,@.test_date
while (@.@.fetch_status=0)
begin
set @.buy_date=NULL
set @.buy_price=NULL
set @.sell_date_5d=NULL
set @.sell_price_5d=NULL
set @.sell_date_10d=NULL
set @.sell_price_10d=NULL
set @.sell_date_20d=NULL
set @.sell_price_20d=NULL
insert into sm.dbo.temp_price
select Top 5 ticker,trade_date,[open],high,low,[close],volume
from price as p
where p.Ticker=@.Ticker and p.Trade_date>@.Test_date
order by p.Trade_Date asc
set @.buy_date=(select min(trade_date)from sm.dbo.temp_price)
set @.buy_price=(select [open] from sm.dbo.temp_price where trade_date=@.buy_date)
set @.sell_price_5d=(select max(high)from sm.dbo.temp_price)
set @.sell_date_5d=(select top 1 (trade_date) from sm.dbo.temp_price where high = @.sell_price_5d order by trade_date asc)
truncate table sm.dbo.temp_price
insert into sm.dbo.temp_price
select Top 10 ticker,trade_date,[open],high,low,[close],volume
from price as p
where p.Ticker=@.Ticker and p.Trade_date>@.Test_date
order by p.Trade_Date asc
set @.sell_price_10d=(select max(high)from sm.dbo.temp_price)
set @.sell_date_10d=(select top 1 (trade_date) from sm.dbo.temp_price where high = @.sell_price_5d order by trade_date asc)
truncate table sm.dbo.temp_price
insert into sm.dbo.temp_price
select Top 20 ticker,trade_date,[open],high,low,[close],volume
from price as p
where p.Ticker=@.Ticker and p.Trade_date>@.Test_date
order by p.Trade_Date asc
set @.sell_price_20d=(select max(high)from sm.dbo.temp_price)
set @.sell_date_20d=(select top 1 (trade_date) from sm.dbo.temp_price where high = @.sell_price_5d order by trade_date asc)
truncate table sm.dbo.temp_price
BEGIN TRANSACTION;
insert into sm.dbo.open_high_v2
values
(
@.ticker,
@.test_date,
@.buy_date,
@.buy_price,
@.sell_date_5d,
@.sell_price_5d,
@.sell_date_10d,
@.sell_price_10d,
@.sell_date_20d,
@.sell_price_20d
)
COMMIT TRANSACTION;
fetch next from date_list into @.ticker,@.test_date
end
close date_list
deallocate date_list
select * from sm.dbo.Open_High
One cause may be you're using transactions without error checking which reserves memory. If you're just inserting without error checking, you don't need to use transactions.
Adamus
|||transactions was a desparate attempt to stop pf growth. pf growth was present before transaction start and commit were added.
Thanks for the idea.
-Soup-
|||After looking at the code further, it appears the overhead you're concerned with is a necessary evil in order to accomplish your task.
Although performance should always be a concern, sometimes the elephant in the living room serves a productive and inevitable purpose.
Adamus
|||Without further informaion on what I am doing incorrectly in TSQL, I guess it is time to turn in a bug report to the SQL developement team. Repeated loops of a program appear to discover a problem with TSQL or SQL server. Is this an acceptable trouble report process, or do I need to go somewhere else to enter this as a formal trouble report?
-Soup-
|||I didn't understand your trouble..
There are someother way there to achive your desired output.. why not, you can try to rewrite your query in PROPER & best way.. as other says..
|||I have to withdraw previous post about memory growing over time. Something other than this program must have been the problem. Regarding Proper & best way, as I said I am new to TSQL and have know idea how this program differs from Proper & best way. I do know that this program may have been a learning experience for me, but I allowed it to execute for 11 Days, yes Days and it did not complete. After only 8 hours a C++ program did the job. I suspect my skill at TSQL is at fault.|||For improving performance of above SQL queries, You can use "table" data type instead of physical temp tables temp_price and Open_High_v2. Another problem is cursor. First you can take all data from "price" table into another temp table. This temp table also derived by "table" datatype. And apply cursor on this table. Next you can remove "Begin Transaction" and "commit transaction" statements...
Next check performance of TSQL...
Jefy
|||Lay off the crack pipe Jefy
No comments:
Post a Comment