Bad Performance in my code

I'm having a performance problem in my code. I'm a little new to Python and I can not think of a better way to do the following code.

I have an external database with a table called "cdr" that is not a part of a django project and I need to do some calculations with the rows. To get my variables' values I'm making a query for every row in my cdr table and this is making my code very slow.

Here is my class in view.py

def cdr_adm(request):
        cursor = connections['cdr'].cursor()
        cursor.execute("SELECT calldate, dst, billsec, accountcode, disposition, userfield FROM cdr where calldate >= '%s' and calldate < '%s' and disposition like '%s' and accountcode like '%s' and dst like '%s' and userfield like '%s'" %(start_date, end_date, status, customer, destino, provider))
        result = cursor.fetchall()
        time = 0
        price = 0
        price_prov = 0
        count = 0
        time_now = 0
        ANS = 0
        asr = 0
        rate_cust = 0
        rate_prov = 0

        for call in result:
                if call[3]:
                        #These 2 lines are the problem - It's very slow to run for all rows.
                        rate_cust = User.objects.get(username = call[3])
                        rate_prov = Provider.objects.get(name = call[5])
                        time_now = call[2] / 60
                        time =  time + time_now
                        count = count + 1
                        price = price + (float(rate_cust.first_name) * time_now)
                        price_prov = price_prov + (float(rate_prov.rate) * time_now)
                        if call[4] == "ANSWERED":
                                ANS = ANS + 1
        time = float(time)
        lucro = price - price_prov
        lucro = float(lucro)
        if count > 0:
                asr = (ANS / count)  * 100
        return render_to_response("cdr_all.html",
                {'calls':result,'price':price,'time':time,'count':count,'customers':customers, 'providers':providers,'price_prov':price_prov, 'lucro':lucro, 'asr':asr }, context_instance=RequestContext(request))

I was thinking about creating a dictionary and search in it but I'm not sure about it too.

Answers


You can create a dictionary of all User and Provider objects, indexed by what you're interested in, like this:

users = dict([(u.username, u) for u in User.objects.all()])
providers = dict([(p.name, p) for p in Provider.objects.all()])

(Make sure you do this outside the for call in result: for loop!) You can then change your slow queries to:

                    rate_cust = users[call[3]]
                    rate_prov = provided[call[5]]

I'm guessing there are considerably fewer users and providers than calls, which means that keeping them in a dictionary will be much faster to access than making one query for each call.


Need Your Help

Error message not showing and hiding depending on if else statement

javascript jquery forms table show-hide

I have a function that loops through all of the inputs of my form and checks if they are filled or not. If the field is blank, it makes that specific input pink and returns false.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.