Way too slow wxPython application getting data from Google Spreadsheet and User input needs speed up solution
Question: I want to make an application that reads out city names, road names, and
distances between them from a Google Spreadsheet with several worksheets.

So far I have the code bellow working right. It reads out from spreadsheet,
receives input also from a user who wants to find out the road name (like Rode
60) between two cities and also the distance between them. However when I run
the application it is incredibly SLOW.

I think I have a server side-user side issue, but after reading tons of
documentation I'm very confused. Maybe I should consider an entirely different
approach. Maybe I need to read out all the spreadsheet with gspread and to
work only user side. Anyway. Now it's slow and I want to have like thousands
of city's in my spreadsheet later on and probably I will put there some more
data about them, like is it a country road or is it a highway, national road
etc. it will take ages until it will return the result with my current code.

Please help and please mind that I'm new to python, wxPython, Google API's or
to IGraph if you suggest that I should do these things with graphs. Today I
did set up IGraph as well for my Python 2.7. Maybe it's the key to my problem?
Please at least give me the right way, the right tutorials. I'm not expecting
anyone to do the dirty job for me. Thank you in advance!!!

    
    
    import gdata.spreadsheet.service
    import gdata.service
    import atom.service
    import gdata.spreadsheet
    import atom
    import gspread
    import wx
    import gdata.docs
    import gdata.docs.service
    import re
    import os
    import csv
    import math
    
    class laci(wx.Frame):
    
        def __init__(self,parent,id):
            wx.Frame.__init__(self,parent,id,'distance calculator',size=(500,500))
            panel=wx.Panel(self)
    
            test=wx.TextEntryDialog(None,"Beginning point: ",'Name of beginning point','...')
            if test.ShowModal()==wx.ID_OK:
                all1=test.GetValue()
    
            test2=wx.TextEntryDialog(None,"Finishing point: ",'Name of finish','...')
            if test2.ShowModal()==wx.ID_OK:
                all2=test2.GetValue()
    
    
    
            c = gspread.Client(auth=('......@gmail.com','.............'))
            c.login()
            # open spreadsheet
            sht=c.open_by_key('.................................')
            worksheet = sht.get_worksheet(0)
    
            print worksheet
    
            i = 1
            j = 1
    
            What = None
    
    
    
            first_col = worksheet.col_values(1)
            print first_col
            stopper = 0
            n = 3
            m = 3
    
            while worksheet.cell(i,1).value != None and stopper != 1:
                if worksheet.cell(i,1).value == all1:
                    print all1
                    stopper = 1
                else:
                    i = i+1
                    print i
                if worksheet.cell(i,1).value == None:
                    boxy=wx.MessageDialog(None,'Wrong start point. You wanna see correct start points list?','Mistake?',wx.YES_NO)
                    answer=boxy.ShowModal()
                    boxy.Destroy
                    if answer == 5103:
                        boxl=wx.SingleChoiceDialog(None,'Accepted Starting point names:','In case of mistake',
                                                   ['City1','City2','City3','City4','City5','City6','City7','City8'])
                        if boxl.ShowModal()==wx.ID_OK:
                            all1=boxl.GetStringSelection()
                            stopper = 0 # figyelj
                            i = 1
                            print all1
                        boxl.Destroy
                    else:
                        print 'how unfortunate'
                if stopper == 1:
                    sline = []
                    while worksheet.cell(i,n).value != None:
                        line = worksheet.cell(i,n).value
                        sline.append(line)
    
                        n = n + 1
                        print sline
                        slinestr = str(sline) 
    
    
            stopper2 = 0
            print sline
            while worksheet.cell(j,1).value != None and stopper2 != 1:
                if worksheet.cell(j,1).value == all2:
                    print all2
                    stopper2 = 1
                else:
                    j = j+1
                    print j
                if worksheet.cell(j,1).value == None:
                    boxz=wx.MessageDialog(None,'Wrong Finish point? Wanna see correct choices?','Mistake?',wx.YES_NO)
                    answer2=boxz.ShowModal()
                    boxz.Destroy
                    if answer2 == 5103:
                        boxl2=wx.SingleChoiceDialog(None,'Accepted Finishing point names:','In case of mistake',
                                                    ['City1','City2','City3','City4','City5','City6','City7','City8'])
                        if boxl2.ShowModal()==wx.ID_OK:
                            all2=boxl2.GetStringSelection()
                            print all2
                        boxl2.Destroy
                    else:
                        print 'how unfortunate'
                if stopper2 == 1:
                    sline2 = []
                    while worksheet.cell(j,m).value != None:
                        line2 = worksheet.cell(j,m).value
                        sline2.append(line2)
                        m = m + 1
                        print sline2
                        slinestr2 = str(sline2)
    
    
            print sline
            print sline2
            t = list(set(sline) & set(sline2))
            print t
            t = t[0]
            t = str(t)
            worksheet2 = sht.worksheet(t)
            print worksheet2
            print worksheet2.cell(2,2)
            i = 2
            j = 2
            iszam = 1
            iszam2 = 1
            stopi = 0
            stopi2 = 0
            km = 0
            while worksheet2.cell(i,2).value != None and stopi != 1:
                if worksheet2.cell(i,2).value == all1:
                    iszam = i
                    print iszam
                    print worksheet2.cell(i,3)
                    stopi = 1
                i = i + 1
                print i
            while worksheet2.cell(j,2).value != None and stopi2 != 1:
                if worksheet2.cell(j,2).value == all2:
                    iszam2 = j
                    print iszam2
                    print worksheet2.cell(j,3)
                    stopi2 = 1
                j = j + 1
                print j
            if iszam2 < iszam:
                while iszam2 != iszam:
                    km = km + int(worksheet2.cell(iszam2+1,3).value)
                    iszam2 = iszam2 + 1
                print km
    
            elif iszam2 > iszam:
                while iszam != iszam2:
                    km = km + int(worksheet2.cell(iszam+1,3).value)
                    iszam = iszam + 1
                print km
            else:
                km = 0
                print km
    
            km = str(km)            
            wx.StaticText(panel, -1, all1, (20,30))
            wx.StaticText(panel, -1, slinestr, (80,30))
            wx.StaticText(panel, -1, all2, (20,60))
            wx.StaticText(panel, -1, slinestr2, (80,60))
            wx.StaticText(panel, -1, 'Path =', (20,90))
            wx.StaticText(panel, -1, t, (80,90))
            wx.StaticText(panel, -1, 'Distance =', (20,120))
            wx.StaticText(panel, -1, km, (80,120))
    
    
    if __name__=='__main__':
        app=wx.PySimpleApp()   #runs it
        frame=laci(parent=None,id=-1)   #face of programme
        frame.Show()
        app.MainLoop()
    


Answer: The first place to start IMHO is to find **where** the delay is.

I'd start by moving the code into different functions/methods - that way
you'll be able to profile the code and see where the slowness is.

eg. (probably want broken down further than this)

  * in the constructor just set up the wx objects
  * then have a function that communicates with Google
  * and another to write the data to wx.

After that you'll be able to do some profiling either yourself or using the
python profiler: (<http://docs.python.org/2/library/profile.html>). The most
important thing is finding what is taking the time and then you will know what
needs improving.

My _guess_ would be that you don't want to be doing it all remotely but should
grab as much as you think you'll need straight away and then do searching
locally - as I wouldn't be surprised if each function call on the spreadsheet
results in communication to the server.But I've not used the Google
Spreadsheet API so that is **_just a hunch_**

