Cursor Vs Temp Tables which is better

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Cursor Vs Temp Tables which is better

Author

Topic

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2001-11-29 : 04:31:33

hi

i may sound funny.... but i have to
i am developing a billing system for telecom company...
i have a huge base of records (call details)
for every call i should check the hour of call rate it and get the price based of the tier and charge....
i tried to use cursors and store in temp table....
i took very long then i tried out with a phisical table but it to took the same time......

several time i heard and read cursors are slow and temp table are good but not sure how can any one help me out
below is the brief description of what i am doing

1) declare imsi_lst cursor for select subIMSI,subFleet,dateUsage,hrCall ,sum(totalUsage) FROM subDaily group by subIMSI,subFleet,dateUsage,hrCall order by subimsi

2) fetch next from imsi_lst into @imsi,@fleet,@dt,@hr,@Call_dur
loop
2)select @own_Id=OWNER_ID,@acc_ID=ACCOUNT_ID from t006_subscriber where subscriber_id=@imsi and ACTIVATION_DATE<=@dt

if record not found get it from diffrent set

3)select @own_Id=OWNER_ID,@acc_ID=ACCOUNT_ID from diffrent set of table

4) if record not found write to notfound table

if record found then continue

5) get the rate scheme and other details
select @rate=rate_scheme_code,@mem_id=mem_id from last_month_backward where accounting_id=3102 and account_ID=@acc_id and left(bill_to_id_old,8)=left(@own_id,8)

select @imei=DEVICE_ID from t006_subscriber where ACCOUNT_ID=@acc_id and left(OWNER_ID,8)=left(@own_id,8)

6) get the type of hour and price to charge

7) check the date of call for holiday and weekends

8) calculate the charge and usage

9) write to temp table / physical table

loop end

i think i mad it very clear u can forget about the querys..
all table are index and have PK and FK.....

is there a way i can speen up things....

wating for help urgent....

thanks in advance

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2001-11-29 : 05:31:48

A forum search for Cursor Performance (all words), turn up quite a few hits, one of which is http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=9126

It may help you......

Searching for "Temp tables" should also bring up some existing advice in this arena.

mono
Starting Member

36 Posts

Posted - 2001-11-29 : 08:48:24

Cursors are considerably slower and are only occasionally the better solution

I don't fully understand the process, but I can't find any part of it that looks impossible using SQL. For example:

2)select @own_Id=OWNER_ID,@acc_ID=ACCOUNT_ID from t006_subscriber where subscriber_id=@imsi and ACTIVATION_DATE<=@dt

if record not found get it from diffrent set 

3)select @own_Id=OWNER_ID,@acc_ID=ACCOUNT_ID from diffrent set of table

could be done in one query using something like this:


select top 1 OWNER_ID, ACCOUNT_ID 
from (select 0 as TableNumber, OWNER_ID, ACCOUNT_ID from t006_subscriber where subscriber_id=@imsi and ACTIVATION_DATE<=@dt)
			union all
			select 1 as TableNumber, OWNER_ID, ACCOUNT_ID from [diffrent set of table] where subscriber_id=@imsi and ACTIVATION_DATE<=@dt)
			)
order by TableNumber ASC

The busdiness of writing records to this or taht table depending on some condition could be done more efficiently (assuming we drop the cursor approach in favour of SQL) by writng them all to the same table and with an extra colun recording the condition value. Afterwards it becomes trivial to separate them.

The other bits you already have in SQL. You should be able to JOIN the SELECTs you already have. You would then end up with a single SQL query that would certainly run a lot faster. Currently, you are accessing verious tables one record at a time. Each time you read a record, a lock is generated and released and SQL server, like any relational DB is designed to work with sets of records not individual records.

Post the table definitions (as DDL) and some sample data (as INSERTs) for more help.

m.

nr
SQLTeam MVY

12543 Posts

Posted - 2001-11-29 : 10:14:21

I have created a few telecom billnig systems (rating calls within about 10 mins of completion).

Depends on how many calls you make but if you have anything other than the smallest company you will have no hope of getting the performance with a cursor.

Don't know your requirement but I guess rating will be a 3 stage process (dialling codes, customer identification, rate cards) plus promotions/discounts added in.
You can get a very efficient and memory configurable system by using batches in temp tables.
The fastest I crated would cope with about a million calls a day on a desktop machine with 128 M ram and rerate a months calls in a day.

p.s. the rating system took 4 days to develop and implement - but then I did know the requirements well to start off with.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2001-11-29 : 22:31:04

so let me make bit more clear..

1) i have the imsi and call duration and call timming.
2) so i need to link the imsi to the owner and account.
3) rating by the hour of call(peak, off peak, super off peak).
4) get the price and charge..
that it..

but my inital base is very huge say some thing around 100,000 records and my customer base is quite big...

currently i am using cursors. bcos i need to process record by record i know cursors are slow but how can i avoid cursor and still process record by record... i am not clear using temp table instead of cursors

sorry guys i know i can look into early posting .. i will do it and in mean time u can help that will be great...
bye

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-11-30 : 08:52:42

I never thought working for Nextel would actually help me solve a SQL Team problem, but I actually UNDERSTAND what he's talking about!

Basically, you must have a table somewhere that relates IMSI to account number, perhaps more than one (our system has about three tables for call detail and unit/service details, joined by IMSI). So something like this should work:

SELECT C.CallTime, C.Duration, C.IMSI, S.Account
FROM CallDetails C INNER JOIN ServiceDetails S ON (C.IMSI=S.IMSI)

And I also assume that you have one or more tables that relate the Services to a rate plan of some kind:

SELECT C.CallTime, C.Duration, C.IMSI, S.Account, S.RatePlan, R.RateAmount
FROM CallDetails C INNER JOIN ServiceDetails S ON (C.IMSI=S.IMSI)
INNER JOIN RatePlans R ON (S.RatePlan=R.RatePlan)

The tricky part is how you store rates based on call time, and how you determine peak and off-peak times. I'm gonna hard-code some CASE statements for this:

SELECT C.CallTime, C.Duration, C.IMSI, S.Account, S.RatePlan, R.RateAmount,
CASE WHEN DatePart(h,C.CallTime) Between 8 and 18 THEN R.PeakRate
WHEN DatePart(h,C.CallTime) Between 19 and 23 THEN R.OffPeakRate
ELSE R.SuperOffPeakRate END * C.Duration AS Charge
FROM CallDetails C INNER JOIN ServiceDetails S ON (C.IMSI=S.IMSI)
INNER JOIN RatePlans R ON (S.RatePlan=R.RatePlan)

Without knowing your table structures, this is about the best I can do. I'm assuming that there are 3 columns for each type of rate (peak, off-peak, etc.), and I doubt that's how you store it.

HTH

nr
SQLTeam MVY

12543 Posts

Posted - 2001-12-01 : 00:16:42

OK
Not sure what you mean by imsi.
a call record usually has source number, destination number, call start time and duration - and a load of other stuff about type of line routing, continuation info ...
for things like calling cards, prepaid, mobiles, ...... the customer id is also usually presented as the switch has to verify the user.

The processes needed are something like
import call records
find customer - get customer record from source dialling code or whatever info.
get the rate card - just depends on customer
get call rate band - this may be from just the destination number for a local system or source and destination for a wider area.
get the time band for call - may depend on customer, source/destination, type, ...
this should be all you need to rate the call
include promotions/discounts
output billing info

You sound like you have quite a small system to build - but it is easy to develop it so that it can scale to larger systems.
One thing to avoid (a lot of telecoms companies - at least in the UK seem to miss this) is to try and rate all calls in a single query - at some point this will start to take a long time as it runs out of memory and will eventually fail.

Configure the system so that it selects a batch of calls to rate - the number of calls should be small enough so that all data can usually be held in memory for the rating process. You can then change the number of calls depending on the machine (that's how I could rate all the calls on my desktop if necessary). You should also keep a batch rating time small - max a few minutes as you may need to interrupt it.

You also have to look to the business requirements.
How do you get the calls? As they are made, in 10 min batches, as a monthly file, as a direct link to the switch (very dangerous), ...
How many feeds are there?
What is a billing period? Monthly, quarterly, anniversary, on request.
Is the billing period different for each customer.
Do the bills need to be viewable at any time - easy to transfer rated calls to
What promotions does the business want - will they all be post rating?
What are the rerating requirements? When should the rerating be done - during a quiet period or immediately any change to the underlying data is made.

Consider all the parts to be separate as they could and probably will all change at some time.
Especially the data import should be just getting the data into a table and nothing more.
Make sure you build so that the import doesn't block the rating and vice versa.
What outputs do you need - usually to bill production but also to customer services, fraud, credit control, regulatory authority.

All your data is basicallly transient so build the system so that indexes do not need to be maintained - i.e. the indexes are few, follow the data flow or are small and so that large tables do not get fragmented.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2001-12-01 : 00:27:17

I could probably send you a design / code for a suitably large fee.
Your 100,000 recs - if they are calls is not huge - for the system I outlined below I would expect them to be rated in about half an hour without specific optimisation (but then I don't know the requirements) and it is probably overkill - but how do you expect the system to grow.

Another thing I forgot which is a real pain but companies like is being able to rate all calls several times to include comparisons against competitors on the customers bill.

Don't know what period they are for but if that is a billing period and they are turning up in one lump (i.e. you are a reseller) then you might consider doing it in access instead. That might sound funny but being able to take copies of the database easily is quite handy sometimes.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2001-12-01 : 05:31:44

thanks a lot guys .. that was great..
but still i am not clear how can i speed my product..

thank to robvolk.. he was very close
so i will give bit more info

the call details i get in file hourly data.
i have 720 file. using bulk insert the data in table raw data.
table structure is

the problem is already a application is running which bills the customer but the system cannot bill group calls (one to many) so i am developing it. so i need to link the data with those set of tables and it is a huge base around 1 million record and 4 tables i need to join.

amafile format
transaction_type
calling_user_id
calling_user_fleet
calling_user_talkgroup
startdate
starttime
enddate
endtime
call_duration
total_mobile_stations

the existing application table
subscriber which has current data.
and the history is diffrent tables

i have the dateof call and imsi and duration i need to find out the owner on date of call.(hand sets can be transfer or disconnect)

and to the given hour link the ratescheme table and get the peak offpeak and superoff peak and rate .

so the strucure is

rate_scheme table
Sch_Id
Rate_Scheme
Description
Free_Min

rate_scheme detail

Sch_Det_Id
Hour
type
Amt

so linking to history data is very big query

so i have written a stored procedure which open a cursor
which takes record by record link the owner and account_id
get the take scheme and charge..

i have a million record for this month i have to run this application after the existing application run and complete the billing
so i dont have much time to run it current it is taking around 15 - 20 hours to process... i take one record and process the complete job and store in table..
what alternative method can speed up my job

and no guessing please i dont have much time
my application should run by mid of next week

help me out

nr
SQLTeam MVY

12543 Posts

Posted - 2001-12-02 : 11:52:45

I would follow my design suggestion.
If you know the data you should be able to imnplement it by then.
A million calls isn't very many to rate but don't try to rate them all in a single batch. You will run out of memory and it will run like a dog.
Batches of 5000 at a time will take 200 batches to rate a million calls. At one minute per batch (which should be easily attainable) it will take under 3 hours for all calls.
Just keep the various tasks in the rating seperate, accumulating data in a temp table and you will find it easy, efficient and easy to change any part.

I'm not sure what your problem is from the description (in fact I suspect that is the problem).

And this isn't a guess - I have implemented similar systems for 2 telecoms companies and several other transaction processing companies.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2001-12-07 : 01:18:02

i got u point but can any one suggest me a alternative to cursors...
i know cursors are 8 to 9 time slow every one say that what can be the possible alternative to this......

help me out....
i spent this month 20 hours and got the out put...
if i can get in less time...

thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2001-12-07 : 05:55:53

Cursors are slow because they force row by row processing.
The faster alternative is set based - i.e. multi - row processing.

That's why using the temp table will be faster - if you process 5000 raows at a time you wouldn't expect it to be 5000 times faster than the cursor but probably at least hundreds of times faster.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources