| Author |
Topic |
|
Vignesh
Starting Member
20 Posts |
 Posted - 2006-04-28 : 08:55:22
|
| Hello alli have written a dynamic SQL query, its working fine but would like to rewrite it as it is hurting the performance. the query is declare @sql varchar(500)set @sql='select top 1 country, ' + @ratingid + ' as rates from ratelist where charindex(code,' + @callto + ')=1'exec(@sql)this is the code inside a stored procedure where i pass the ratingid and the no called(@callto) as variable.Now the problem is the rating id that i pass is of varchar datatype and then based on the ratingid i passed then i select the particular country and the rate( which is of datatype money ). so as to get the rate i call this stored procedure in another stored procedure and fill the values returned in a temp table. and then retrive the values.which hurts the performance. also from the no called i search for the code to get the country where the call was made. i read in one site that using string functions affects the performance. so i wanted to know is there any other way to do it.Vignesh |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-04-28 : 09:51:11
|
| Looks like a telecoms rating system.The problem is getting a single rate at a time if you are rating a lot of calls.You need to do everything as a batch rather than as a single call.You probably have a ratecard for the user which has the destination (and possibly source).It's better to treat calls as a batch. Get the destination codes for the calls, ratecard ids for the users then using that you can rate the call.Getting the ratecard should be simple.For the destination you probably have a dialling codes table and need to match on the longest value possible.That's easiest by taking the longest length of a dialling code - matching on that and looping reducing the length by one until all calls are matched (you should have a catch all dialling code).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Vignesh
Starting Member
20 Posts |
Posted - 2006-04-28 : 11:56:59
|
| Hi NrU got it right its a telecomm Rating System. Yes we were Planning for a batch process to do the same but i guess we have to do it in a prepaid scenario where every call has to be rated and processed on a per call basis. Right now we are using postpaid solution were in we process the complete days solution once a day. but then we select the calls made in a particar day and the n process it call by call using a cursor. i know using a cursor is not a good idea but as u say cursor are useful if you dont know SQL. I guess i dont know sql. if you help us out and give us a alternative of how it can be done that will be really helpfullThanksVignesh |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-04-28 : 12:01:58
|
| I did a system which would rate a million calls a day on a desktop m/c (getting all calls to customer services within 5 mins) but the flow was a bit complicated.Just follow what I said.The important thing is to do as much work as possible on data while it is in memory and to tailor batch sizes to the amount of memory you have.I don't know how many calls you are dealing withy but if you try to rate them one at a time you will struggle to keep up whatever throughput you have.Prepaid is different as you have to rate while the call is going on - but for that you retain information about the call when it is first made then recalculate from that on every poll.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-28 : 13:26:28
|
| Sorry, wrote this hours ago, and didn't press SUBMIT, not sure if still relevant?Its isn't hurting performance that you are calling one Sproc from another.It may be hurting performance that you are using dynamic SQL to make the query.It is also certainly hurting performance that you are using:charindex(code,' + @callto + ')=1to locate the appropriate record!So you want any value in [ratelist] table where [code] column contains the value in @callto?No way to speed that up, and if there are lots of values in [ratelist] it will be slow.However, if there are other columns in [ratelist] that you can include in your WHERE clause, which will "narrow" the scope, then that would probably help, particularly if those columns are indexed.Other possibility is to have an index for [country], [code] and the column(s) used in @ratingid; [code] should be the first column in the index, and it would probably make a difference whether basically all the values in [code] are unique, or whether there are lots of rows in [ratelist] for a given [code] value."i read in one site that using string functions affects the performance"Lets assume you have an index on a column called [ColumnA]WHERE [ColumnA] = 'foo' - very fastWHERE SomeFunction([ColumnA]) = 'foo' - usually very slow, because the index won't be used any more.An option might be to replace your charindex() with LIKE:WHERE [ColumnA] LIKE '%' + @callto + '%'If you can NOT use the leading '%' then the index lookup will still be quick-ish.And even if you can't, AND you can "cover" the other columns with an index as I described above, it may very well help significantly.Kristen |
|
|
|
Vignesh
Starting Member
20 Posts |
Posted - 2006-04-28 : 15:16:26
|
| Hi Kristenthanks for the reply. yes i know that the kind of coding i have done will surely hurt the performance. i went through some performance improvement tips on www.sql-server-performance.com but i am not sure how i can implement them. i want to search whether the code exist in the number called eg if the no called is 6129... i search for the code from the list and i get a maximum match for 6129 which is the code for sydney. so i guess here i cannot use where code like .. because the code will be contained in the caled no. also since there is a mis match between the variable rating id(varchar) and the rates(money) i had to use temp table to get the rates into a variable.. i dont know how do i get out this mess and save my poor SQL Server from getting spanked every time i run this query. please HelpVignesh |
|
|
|
Vignesh
Starting Member
20 Posts |
Posted - 2006-04-28 : 15:29:43
|
| Hi Nryou processed a million calls in 5 mins??? Man thats hugewe process about a lakh calls a day. its just a newbie into telecomm and so are we but we have great plans. for processing about a lakh calls it takes us about a half hour. can you give us some hints/examples on how to process the calls in batches. also we used many things that we shouldnt have used like Cursors and Temp Table. could you just give us some idea of how the calls should be processed in a better waythanks for your helpVigneshVignesh |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-28 : 15:31:05
|
"because the code will be contained in the caled no"Will you get the ones you want if you do:WHERE [code] LIKE @callto + '%'(i.e. "starts with" the @callto value - because that would be more efficient (assuming that [code] is indexed)."i had to use temp table to get the rates into a variable."Sounds like you ought to post the WHOLE code you are using, in case we can spot something elsewhere in the query that can be speed-ed up."i dont know how do i get out this mess and save my poor SQL Server from getting spanked every time i run this query"Hehehe ... I presume your SQL Server is fed up with getting spanked?  Kristen |
|
|
|
Vignesh
Starting Member
20 Posts |
Posted - 2006-04-28 : 16:10:33
|
| Hi Kirsten Thanks for the prompt reply. can you please explain the code a little bit more WHERE [code] LIKE @callto + '%'. can this search the code from the called number. here is my code where i call the stored procedure and create a temp table to get the rates as i said earliercreate table #temptable(country varchar(100),rates money)insert into #temptableexec country_rates @ratingid,@calltoselect @country=country,@rates=rates from #temptabledrop table #temptableis there a way where i can remove the text function and the temp table to improve the performanceThanks for the helpVignesh |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-28 : 16:40:57
|
| "can this search the code from the called number"Provided that @callto matches the START of [code] it will work."create table #temptable(country varchar(100),rates money)"IME it helps performance to add a PK to #TempTables"select @country=country,@rates=rates from #temptable"So is "exec country_rates @ratingid,@callto" only returning ONE row? If so sue OUTPUT parameters to country_rates Sproc, otherwise how are you controlling WHICH row you are processing?"is there a way where i can remove the text function"What "text function" ??Kristen |
|
|
|
Vignesh
Starting Member
20 Posts |
Posted - 2006-04-28 : 17:48:14
|
| Hi kristen i tried that code but its not working. there is no match return when there is one i could have used a output variable in my stored procedure but the problem is the rating id i pass is a varchar datatype and then it used in a query as variable which return the country and the rate which is of datatype money so i cant directly assign it to variable in the select statement because then it gives me a error that implicit conversion from datatype varchar to money not allowed. and by text function i meant string function (charindex one)Vignesh |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-29 : 02:00:46
|
| "i tried that code but its not working. there is no match return when there is one"Please provide an example of a @callto and a matching [code]result"i cant directly assign it to variable in the select statement because then it gives me a error that implicit conversion from datatype varchar to money not allowed"You can use CONVERT(money, @MyVarcharVariable)Kristen |
|
|
|
Vignesh
Starting Member
20 Posts |
Posted - 2006-04-29 : 04:15:35
|
| Hi Kristenhere is a example of the set of codes country code rates1 rates2 rates3United States 1 .018 .015 .012United Kingdom 44 .018 .015 .012Australia 61 .018 .015 .010Sydney 6129 .022 .020 .018so suppose if i called a number 612921345345.. so i scan the table and i find a maximum match for Sydney the code being 6129 which occurs at the first position. also the table is clustered index on the column code and is arranged in desc order. so in this the @callto='612912345345..' this is my stored procedure which finds a match for the country based on the called numberCREATE proc country_rates @ratingid varchar(50),@callto varchar(100)asdeclare @sql varchar(500)set @sql='select top 1 country, ' + @ratingid + ' as rates from ratelist where charindex(code,' + @callto + ')=1'exec(@sql)now the problem here is that the the rating id i pass is of datatype varchar but then it is actually a column in the table which is of type money ( rates1, rates2, rates3 are the ratingid ) so i tried doing a convert but its not working. Vignesh |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-29 : 10:49:22
|
I would do something like (note that your TOP 1 will not produce repeatable results without an ORDER BY):CREATE PROCEDURE dbo.country_rates @ratingid varchar(50), @callto varchar(100)ASSET NOCOUNT ONSELECT TOP 1 [country], CASE @ratingid WHEN 'rates1' THEN rates1 WHEN 'rates2' THEN rates2 WHEN 'rates3' THEN rates3 ELSE NULL -- Error! END AS [rates]FROM dbo.ratelist WHERE @callto LIKE code + '%'ORDER LEN(code) DESC -- Pick longest matching code first Kristen |
|
|
|
Vignesh
Starting Member
20 Posts |
Posted - 2006-05-02 : 14:08:12
|
| hi KirstenThanks for the reply. WHERE @callto LIKE code + '%' works fine. but the problem is CASE @ratingid WHEN 'rates1' THEN rates1 WHEN 'rates2' THEN rates2 WHEN 'rates3' THEN rates3the no of rating id that we have currently is 12. then i have to write 12 when statements. and also the number is sure to increase.what do i doVignesh |
|
|
|
Vignesh
Starting Member
20 Posts |
Posted - 2006-05-02 : 14:14:10
|
| Forgot to tell that the table has been clusterd index on code column and the codes are arranged in DESC orderVignesh |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-02 : 14:34:31
|
| Normalise them into a Sub Table of [ratelist], rather than multiple columns in [ratelist], together with a "RatingID" column, and you can then just Select/Join on that!Kristen |
|
|
|
Vignesh
Starting Member
20 Posts |
Posted - 2006-05-02 : 14:40:29
|
| Thanks Kirsteni guess thats a better ideathanks for your all your repliesVignesh |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-03 : 01:49:02
|
| "i guess thats a better idea"'Fraid so! But might be a bit painful in the short term. You could move the existing [ratelist] table to a new table called, say, [ratelist_V2] and put the [rates1], [rates2] ... columns into a sub table at that time, and then create a View with the original name [ratelist] (i.e. OUTER LEFT JOINing the new Sub Table multiple times to get values for [rates1], [rates2] ..., and then you existing applications would continue to work, but new stuff could use the new Sub Table, and old stuff could be refactored as/when appropriate.Kristen |
|
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2006-05-03 : 04:09:05
|
| I worked on telecom rating system in the past. My solution was kind of the oposite of your approach.I started processing with rateList (normalised one) ordered by codes in descending order. I loop trough rates and for each code I selected numbers prefixed by current code and then applied rates and marked them as processed. Next code in the loop may be one that is the prefix of the previous one. However, rows that were already rated were marked as processed so only numbers with a shorter prefix that doesn't match codes with previous already processed longer prefix were taking into consideration. That way I could use effectively indexes on the bigger cdr table. The way you try to do it, indexes can't really be effectively used.I hope I was able to explain clearly enough, but I am not quite sure. |
|
|
|
Vignesh
Starting Member
20 Posts |
Posted - 2006-05-03 : 08:58:34
|
| Hi mmaroviccan you let us know with this approach how much calls could you processin a second. we could process about 75 calls/ sec on a normal desktop m/c. if possible can you give us some tips on Telecomm Rating system.Thanks in advanceVignesh |
|
|
|
Next Page
|
Help with Improving Performance