Is there a way to speed up this query using two cursors? I need the result set from the history cursor to change on each pass of the loop. I tried to just open and close the cursor each loop but it didn't work so I delclared it and dealocated each loop, which works but at a very slow pace. Is there a command to refresh the result set? or a way to do this as a batch?
set nocount on
Declare @.entry bigint, @.mobile int, @.fid int, @.rid int, @.pin char(10), @.commission tinyint,
@.barred tinyint, @.barred_acse tinyint, @.les char(10), @.mobile1 int, @.fid1 int,
@.rid1 int, @.pin1 char(10), @.commission1 tinyint, @.barred1 tinyint, @.barred_acse1 tinyint,
@.les1 char(10)
DECLARE changes CURSOR
FOR
select mobile, fid, rid, pin, commission, barred, barred_acse, les
from mobile_changes
OPEN changes
FETCH NEXT from changes
INTO @.mobile, @.fid, @.rid, @.pin, @.commission, @.barred, @.barred_acse, @.les
WHILE @.@.FETCH_STATUS = 0
BEGIN
DECLARE history CURSOR
FOR
select max(entry), mobile, fid, rid, pin, commission, barred, barred_acse, les
from mobile_history
where mobile = @.mobile
group by mobile, fid, rid, pin, commission, barred, barred_acse, les
OPEN history
FETCH NEXT FROM history
INTO @.entry, @.mobile1, @.fid1, @.rid1, @.pin1, @.commission1, @.barred1, @.barred_acse1, @.les1
IF (
@.mobile = @.mobile1 and @.fid = @.fid1 and @.rid = @.rid1 and @.pin = @.pin1 and
@.commission = @.commission1 and @.barred = @.barred1 and @.barred_acse = @.barred_acse1 and
@.les = @.les1)
BEGIN
DELETE FROM mobile_changes where mobile = @.mobile
END
CLOSE history
deallocate history
FETCH NEXT FROM changes
INTO @.mobile, @.fid, @.rid, @.pin, @.commission, @.barred, @.barred_acse, @.les
END
close changes
deallocate changesTo speed up your processing, you can avoid the cursors. Use temp table instead.
Try the following steps:
1. Create temp table from mobile_history with all required fields.
select max(a.entry) MaxEntry, a.mobile, a.fid, a.rid,
a.pin, a.commission, a.barred, a.barred_acse, a.les
into #temp_history
from mobile_history a, mobile_changes b
where a.mobile = b.mobile
group by mobile, fid, rid, pin, commission, barred, barred_acse, les
2. Delete records from the table mobile_changes using temp table.
delete mobile_changes
from mobile_changes a, #temp_history b
where a.mobile_no = b.mobile_no
and a.fid = b.fid
and a.rid = b.rid
and a.pin = b.pin
and a.commission = b.commission
and a.barred = b.barred
and a.barred_acse = b.barred_acse
and a.les = b.les
The above will do the same thing. Here, my only confusion is why do you need max(entry) in mobile_history table. Anyway, you are not using it anywhere. If i assume that u are not using it anywhere, our query can be even simpler. Just use the following query.
delete mobile_changes
from mobile_changes a, #mobile_history b
where a.mobile_no = b.mobile_no
and a.fid = b.fid
and a.rid = b.rid
and a.pin = b.pin
and a.commission = b.commission
and a.barred = b.barred
and a.barred_acse = b.barred_acse
and a.les = b.les
Thanks.
Prabhanjan|||The Entry field is a auto numbered primary key. The max(entry) returns the latest record grouped by mobile. Thanks for the help the cursor method was taking up to 50 minutes to complete, your method takes < 10 seconds.
Showing posts with label history. Show all posts
Showing posts with label history. Show all posts
Thursday, March 29, 2012
Sunday, March 25, 2012
Cursor in an SP
I am doing a compare last history query that I'm seeing only a cursor as a
way to do.
Data to return: custID, CustRegion, # orders, Total volume,
#monthsExpectedUse
1) Look at all orders by customer within a date range. ( I get col 1)
Greates cursor
2) Establish prior order per customer outside of the dates, and give time
diff for their ( I get cols 3,4,5)
How do I combine inital data @.CustAccount, @.qty, @.TotVolume, @.MonthsUse into
a final return set?It might be that a cursor is the best way, but that is rarely the case, and
even more rare that it is the only way. Could you provide detailed specs,
sample data, and desired results? This way, we can probably come up with an
alternative to a cursor which will be much more efficient and easier to
maintain. Please see http://www.aspfaq.com/5006
"Stephen Russell" <srussell@.transactiongraphics.com> wrote in message
news:uQf6lajrFHA.3264@.TK2MSFTNGP12.phx.gbl...
>I am doing a compare last history query that I'm seeing only a cursor as a
>way to do.
> Data to return: custID, CustRegion, # orders, Total volume,
> #monthsExpectedUse
> 1) Look at all orders by customer within a date range. ( I get col 1)
> Greates cursor
> 2) Establish prior order per customer outside of the dates, and give time
> diff for their ( I get cols 3,4,5)
> How do I combine inital data @.CustAccount, @.qty, @.TotVolume, @.MonthsUse
> into a final return set?
>|||
*** Sent via Developersdex http://www.examnotes.net ***|||> *** Sent via Developersdex http://www.examnotes.net ***
Nice one. You might try a newsreader, they're a bit more reliable.|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OvknW5jrFHA.2588@.tk2msftngp13.phx.gbl...
> Nice one. You might try a newsreader, they're a bit more reliable.
Crud!|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OvknW5jrFHA.2588@.tk2msftngp13.phx.gbl...
> Nice one. You might try a newsreader, they're a bit more reliable.
Can a select with in a select return 2 columns?|||> Can a select with in a select return 2 columns?
Once again, you will need to be more specific. If you post table structure,
sample data, and what you are trying to do, it will be much easier than
answering word problems...|||"Stephen Russell" <srussell@.transactiongraphics.com> wrote in message
news:OWokKDkrFHA.3080@.TK2MSFTNGP15.phx.gbl...
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:OvknW5jrFHA.2588@.tk2msftngp13.phx.gbl...
> Can a select with in a select return 2 columns?
I want : returns 2 columns here not 1
Select a.col1, a.col2, (select b.col3, b.col4 from table orders b where b.id
= a.col1 and b.id2 = a.col2)
From orders a
Left join ....
Where ...
Group by ...
Order by ...|||I do not understand what "returns 2 columns here not 1" means.
***PLEASE*** GO READ http://www.aspfaq.com/5006
"Stephen Russell" <srussell@.transactiongraphics.com> wrote in message
news:Owz2IIkrFHA.2552@.TK2MSFTNGP10.phx.gbl...
> "Stephen Russell" <srussell@.transactiongraphics.com> wrote in message
> news:OWokKDkrFHA.3080@.TK2MSFTNGP15.phx.gbl...
> I want : returns 2 columns here not 1
> Select a.col1, a.col2, (select b.col3, b.col4 from table orders b where
> b.id = a.col1 and b.id2 = a.col2)
> From orders a
> Left join ....
> Where ...
> Group by ...
> Order by ...
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
way to do.
Data to return: custID, CustRegion, # orders, Total volume,
#monthsExpectedUse
1) Look at all orders by customer within a date range. ( I get col 1)
Greates cursor
2) Establish prior order per customer outside of the dates, and give time
diff for their ( I get cols 3,4,5)
How do I combine inital data @.CustAccount, @.qty, @.TotVolume, @.MonthsUse into
a final return set?It might be that a cursor is the best way, but that is rarely the case, and
even more rare that it is the only way. Could you provide detailed specs,
sample data, and desired results? This way, we can probably come up with an
alternative to a cursor which will be much more efficient and easier to
maintain. Please see http://www.aspfaq.com/5006
"Stephen Russell" <srussell@.transactiongraphics.com> wrote in message
news:uQf6lajrFHA.3264@.TK2MSFTNGP12.phx.gbl...
>I am doing a compare last history query that I'm seeing only a cursor as a
>way to do.
> Data to return: custID, CustRegion, # orders, Total volume,
> #monthsExpectedUse
> 1) Look at all orders by customer within a date range. ( I get col 1)
> Greates cursor
> 2) Establish prior order per customer outside of the dates, and give time
> diff for their ( I get cols 3,4,5)
> How do I combine inital data @.CustAccount, @.qty, @.TotVolume, @.MonthsUse
> into a final return set?
>|||
*** Sent via Developersdex http://www.examnotes.net ***|||> *** Sent via Developersdex http://www.examnotes.net ***
Nice one. You might try a newsreader, they're a bit more reliable.|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OvknW5jrFHA.2588@.tk2msftngp13.phx.gbl...
> Nice one. You might try a newsreader, they're a bit more reliable.
Crud!|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OvknW5jrFHA.2588@.tk2msftngp13.phx.gbl...
> Nice one. You might try a newsreader, they're a bit more reliable.
Can a select with in a select return 2 columns?|||> Can a select with in a select return 2 columns?
Once again, you will need to be more specific. If you post table structure,
sample data, and what you are trying to do, it will be much easier than
answering word problems...|||"Stephen Russell" <srussell@.transactiongraphics.com> wrote in message
news:OWokKDkrFHA.3080@.TK2MSFTNGP15.phx.gbl...
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:OvknW5jrFHA.2588@.tk2msftngp13.phx.gbl...
> Can a select with in a select return 2 columns?
I want : returns 2 columns here not 1
Select a.col1, a.col2, (select b.col3, b.col4 from table orders b where b.id
= a.col1 and b.id2 = a.col2)
From orders a
Left join ....
Where ...
Group by ...
Order by ...|||I do not understand what "returns 2 columns here not 1" means.
***PLEASE*** GO READ http://www.aspfaq.com/5006
"Stephen Russell" <srussell@.transactiongraphics.com> wrote in message
news:Owz2IIkrFHA.2552@.TK2MSFTNGP10.phx.gbl...
> "Stephen Russell" <srussell@.transactiongraphics.com> wrote in message
> news:OWokKDkrFHA.3080@.TK2MSFTNGP15.phx.gbl...
> I want : returns 2 columns here not 1
> Select a.col1, a.col2, (select b.col3, b.col4 from table orders b where
> b.id = a.col1 and b.id2 = a.col2)
> From orders a
> Left join ....
> Where ...
> Group by ...
> Order by ...
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Saturday, February 25, 2012
Cube History
I am trying to build a cube to monitor the sales performance. My measure is the sale amount. My dimensions include 1) Time 2) Sale hierarchy (salesman, sales office, region), and 3) Product Line. The problem I have is that when the salesman move from one office to another, the sales occured before this movement should continue to be credited to the old office. Only sales happen after the movement should be credited to the new office.
How should I implement the cube to achieve this effectively? We have more that 40,000 sales, about 500 offices, in the company to monitor. So I don't think keeping a copy of the sales hierarchy whenever there is a change is feasible.
Any inputs will be highly appreciated!
Thanks.You will need to Consider creating another dim for salesman.
you apear to need to collect when an employee was at a particular site for a particular sale so you will have to add a location value to each ORDER and not rely on the salesman's location. it has to be tied to the sale itself so the location attribute should be derived for the order
if this is a seldom problem you may want to make your cube holap (part relational and part cube)instead of pure olap and then you can use the drill down feature for a particular sale or set of sales to find the distinction between sites.|||Thank you very much for the information. I can see how that will fix the problem. However, I am not sure how to make the drilldown work using holap as you have suggested. Could you please elaborate it a bit about your last paragraph? Thanks. Sorry, I am new in the DWH field...|||check out those topics in BOL for analysis services
i will try to reply later
How should I implement the cube to achieve this effectively? We have more that 40,000 sales, about 500 offices, in the company to monitor. So I don't think keeping a copy of the sales hierarchy whenever there is a change is feasible.
Any inputs will be highly appreciated!
Thanks.You will need to Consider creating another dim for salesman.
you apear to need to collect when an employee was at a particular site for a particular sale so you will have to add a location value to each ORDER and not rely on the salesman's location. it has to be tied to the sale itself so the location attribute should be derived for the order
if this is a seldom problem you may want to make your cube holap (part relational and part cube)instead of pure olap and then you can use the drill down feature for a particular sale or set of sales to find the distinction between sites.|||Thank you very much for the information. I can see how that will fix the problem. However, I am not sure how to make the drilldown work using holap as you have suggested. Could you please elaborate it a bit about your last paragraph? Thanks. Sorry, I am new in the DWH field...|||check out those topics in BOL for analysis services
i will try to reply later
Subscribe to:
Posts (Atom)