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

Can we do unit testing for the file I/o operations

c# visual-studio-2010 unit-testing file-io

Can we do unit testing for the file I/o operations in C# using VS2010? Since the unit testing is not efficient to access the data layer, do we need to unit test operation or is there any mock or fake

Why are these resources failing to load?

asp.net css png http-status-code-404 google-chrome-devtools

My asp.net project has several .png (and a few .gif) files in the \Content\Images folder.