November 5, 2009 at 5:53 am
Hi,
We have 3 tables in our database and details of table is as follows:
1) Member Table contains MemberID, Status, MemberType, OpeningBalance
2) Transaction Table contains MemberID, Year, Month, Day, Value, TransactionType
3) TransactionTypes Table contains TransactionType, Status (Valid / Invalid)
Using above tables, we need to generate TransDATA table based on below creteria
1) All valid TransactionType records must be fetch from Transaction Table
2) For each member for each transaction type for each day must be copied / created to TransDATA table.
3) If for a particular day for particular transaction type is not found then we need to take it from previous day for same transaction type.
Like this we need to generate complete data in a different table.
We have member database of 30000, Valid TransactionTypes are maximum 50. When we generate TransDATA it is taking minimum 23 hours. Tota records in TransDATA table = 30000 x 50 x 30 = 45000000
Can we optimize it? We tried all the way but it is still taking 23 hours :crying: :crying: :crying:.
06-Nov-2009
I've missed out one more point. If member doesnt have transaction for any day for any TransactionType then we need to create that record in new table.
Regards,
Naresh Kumar
November 5, 2009 at 7:57 am
For a question like yours it would be of great help to those who would try to help you to post the create tables SQL statements, supply some sample data for each table and a short example of the required output. Please refer to the link in my signature for the format requested.
November 5, 2009 at 8:07 am
this is an important piece:
when you say
When we generate TransDATA it is taking minimum 23 hours
can you show us the SQL? is it a cursor going thru 45 million records?(at 23 hours processing time...the answer is yes) I bet that process can be changed to a set based operation that is 4 or more orders of magnitude faster than the current process. I've updated 45M records in set based oeprations lots of times, and it takes minutes at the most;
Lowell
November 6, 2009 at 5:10 am
Hi Lowell,
Thanks for your reply. Please see code below and I've attached one excel file which contains table details (no sample data, this i cannot provide 🙁 )
Code
====
create procedure USP_PrevilegeProcessVAlidTrans
(
@process_da as varchar(6)
@maxdate int,
@year int,
@month int
)
as
begin
declare @Member varchar(20)
declare @broker varchar(20)
declare @TType varchar(20)
declare @processdate varchar(20)
declare @Units float
declare @OpeningBal float
declare @min-2 int
declare @begingbalance float
declare @TodaysDate varchar(15)
declare @ThisDayClosingBalance float
declare @NAV float
set @min-2=1
declare cur_Previlige cursor for
select [MemberID], [TransactionType], ClosingBalance
from temp_SchemeDetails
group by [MemberID], [TransactionType]
open cur_Previlige
FETCH Next From cur_Previlige
Into @Member,@TType, @OpeningBal
while(@@fetch_status='0')
begin
set @min-2=1
WHILE(@min<=@maxdate)
BEGIN
set @Units=0
if exists(select 1 from temp_SchemeDetails where process_da=@process_da+ case len(cast(@min as varchar(2))) when '1' then '0'+cast(@min as varchar(2)) else cast(@min as varchar(2)) end
and [MemberID]=@Member and TransactionType=@TType)
begin
select @Units=Resultant from temp_SchemeDetails where process_da=@process_da+ case len(cast(@min as varchar(2))) when '1' then '0'+cast(@min as varchar(2)) else cast(@min as varchar(2)) end
and [MemberID]=@Member and TransactionType=@TType
end
else
begin
set @OpeningBal= @OpeningBal
end
set @OpeningBal= @OpeningBal + @Units
select @NAV= Nav from temp_nav_Sept where nav_date=@process_da+ case len(cast(@min as varchar(2))) when '1' then '0'+cast(@min as varchar(2)) else cast(@min as varchar(2)) end
and TransactionType=@TType
set @ThisDayClosingBalance=(@OpeningBal*@NAV)
set @TodaysDate=@process_da+ case len(cast(@min as varchar(2))) when '1' then '0'+cast(@min as varchar(2)) else cast(@min as varchar(2)) end
insert into tblAvgLog_Recalculated(Member_ID,TransactionTye,[YEAR],[Month],[Day],[Value],
PreviousDayClosingBalance,PickedUpNavDate,NavForTheDay,ThisDayClosingBalance,TodaysDate)
values(@Member,@TType,convert(int,@year),convert(int,@month),convert(int,@min),isnull(@Units,0.0),
isnull(@OpeningBal,0.0),@TodaysDate,@NAV,@ThisDayClosingBalance,@TodaysDate)
set @min-2=@min+1
end
FETCH Next From cur_Previlige Into @Member,@TType, @OpeningBal
end
close cur_Previlige
deallocate cur_Previlige
end
November 6, 2009 at 5:34 am
Rolling balance problems are tricky
try this link
http://www.sqlservercentral.com/Forums/Topic802558-203-1.aspx
Note that the article is not yet available
Have a quick google for 'quirky update' though
November 6, 2009 at 5:58 am
I've missed out one more point. If member doesnt have transaction for any day for any TransactionType then we need to create that record in TransData table.
Regards,
Naresh Kumar
November 6, 2009 at 7:23 am
need a clarification: your procedure had two spelling errors in it, as well as a syntax error for the cursor declaration;
select [MemberID], [TransactionType], ClosingBalance
from temp_SchemeDetails
group by [MemberID], [TransactionType]
Msg 8120, Level 16, State 1, Procedure USP_PrevilegeProcessVAlidTrans, Line 27
Column 'temp_SchemeDetails.ClosingBalance' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
--should it be ????
declare cur_Previlige cursor for
select [MemberID], [TransactionType], SUM(ClosingBalance ) AS ClosingBalance
from temp_SchemeDetails
group by [MemberID], [TransactionType]
also, it took me a long time to convert your excel spreadsheet info into viable CREATE TABLE statements. help us help you by providing everything we might need in TSQL statements!
it would really help if you could gve us a few INSERT INTO statemetns so we had some sample data to test against.
here's what i built for reference:
CREATE TABLE [dbo].[TBLAVGLOG_RECALCULATED] (
[ID] INT NOT NULL,
[MEMBERID] NVARCHAR(200) NULL,
[TRANSACTIONTYPE] VARCHAR(200) NULL,
[YEAR] INT NOT NULL,
[MONTH] INT NOT NULL,
[DAY] INT NOT NULL,
[VALUE] DECIMAL(18,4) NULL,
[PREVIOUSDAYCLOSINGBALANCE] DECIMAL(18,4) NULL,
[PICKEDUPNAVDATE] INT NULL,
[NAVFORTHEDAY] DECIMAL(18,4) NULL,
[THISDAYCLOSINGBALANCE] DECIMAL(18,4) NULL,
[TODAYSDATE] INT NULL,
[COMBINAN] VARCHAR(200) NULL)
CREATE TABLE [dbo].[TEMP_NAV] (
[NAV] NVARCHAR(1020) NULL,
[NAV_DATE] NVARCHAR(1020) NULL,
[TRANSACTIONTYPE] NVARCHAR(1020) NULL)
CREATE TABLE [dbo].[TEMP_SCHEMEDETAILS] (
[MEMBERID] NVARCHAR(1020) NULL,
[TRANSACTIONTYPE] NVARCHAR(1020) NULL,
[PROCESS_DA] NVARCHAR(360) NULL,
[UNITS] NVARCHAR(1020) NULL,
[CLOSINGBALANCE] DECIMAL(18,4) NULL,
[POSITIVE] FLOAT NULL,
[NEGATIVE] FLOAT NULL,
[RESULTANT] FLOAT NULL,
[COMBINATION] NVARCHAR(2040) NULL)
Lowell
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply