Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
Silver Contributor
Posts: 835
Registered: ‎03-24-2009

Picklist Collection vs SQL retrieval

I thought that picklists were in a collection that was accessed by the Client using the Picklist Object.....

 

Why is SLX running SQL to retrieve the data every time the user clicks on the picklist control button?

 

---------- Client SQL ---------
SELECT ID, TEXT, SHORTTEXT, ITEMID, USERID FROM PICKLIST WHERE PICKLISTID = 'k6UJ9B00006V'   ORDER BY ID 
---------- Executed SQL ----------
SELECT PICKLIST.ID, PICKLIST.TEXT, PICKLIST.SHORTTEXT, PICKLIST.ITEMID, PICKLIST.USERID FROM PICKLIST  WHERE PICKLIST.PICKLISTID = 'k6UJ9B00006V'
 ORDER BY PICKLIST.ID

 

For some reason I thought this might be faster simply going to the collection of picklists......

RJ Samp
Gold Super Contributor
Posts: 3,087
Registered: ‎03-19-2009

Re: Picklist Collection vs SQL retrieval

Remember the PickList object is something that was added later on and existing code still does a SQL access.
--
RJ Ledger - rjledger@rjlSystems.net +1 603.369.3047 x101

".. Innovators in Mobility - Experts in Workflow Automation..."
http://www.rjlSystems.net - blog: www.rjlSystems.net/blog.html
Bronze Super Contributor
Posts: 349
Registered: ‎03-24-2009

Re: Picklist Collection vs SQL retrieval

There is only one picklist control so Sage only has to fix it once. We need every ounce of performance improvement so I hope Sage addresses this sooner than later.

Nice catch Samp.
Timmus Agersea
Black Moth CRM
Highlighted
Silver Contributor
Posts: 835
Registered: ‎03-24-2009

Re: Picklist Collection vs SQL retrieval

I had to write a function to load the picklist items into an array, store the array as a GlobalInfo variable. The picklist gets loaded into the array OnLoginComplete Global Script which may or may not run with every Ctrl-F5 refresh or changed the picklist values SLX refresh (whichever kind reloads the picklist objects).

 

I'd hate to see a couple hundred arrays or globalinfo variables of arrays in memory, maybe the picklist object and picklist item objects can reference a massive array of picklist values and get their values from there instead of SQL retrieve after SQL retrieve. recall the Aussie developer who loaded up the entire postal code table for OZ into the Account Detail and Contact Detail View???

 

'strPicklist - Picklist Name as defined in the picklist manager
' Result is the count of items in the picklist,
' also returns an array of the picklist rows datavalues as a GlobalInfo variable.
Function PutPicklistValuesIntoArray(ByVal strPicklist)
    Dim objPicklist
    DIM pklArray, pklName, pklCN, pklRS, pklSQL
    PutPicklistValuesIntoArray = -1

    SET pklCN = NEW SLX_DB
    SET pklRS = pklCN.GetNewRecordSet

    pklSQL = " SELECT [ITEMID], [ID] , [TEXT] , [SHORTTEXT], [PICKLISTID] "
    pklSQL =  pklSQL & " FROM PICKLIST WHERE PICKLISTID =  "
    pklSQL = pklSQL & " ( SELECT ITEMID FROM PICKLIST WHERE PICKLISTID = 'PICKLISTLIST' AND [TEXT] = '" & strPicklist  & "' ) "
    pklSQL = pklSQL & " ORDER BY ID "
    application.Debug.WriteLine " PutPicklistValuesIntoArray SQL: " & pklSQL
    pklRS.OPEN pklSQL, pklCN.Connection
    IF NOT pklRS.EOF THEN
        pklArray = pklRS.GETROWS()
        PutPicklistValuesIntoArray = pklRS.recordCount
    ELSE
        REDIM pklArray(0,0)
        pklArray(0,0) = ""
    END IF
    pklName = "Picklist_Array" '''' this could be the picklist name, but I don't like GlobalInfo variables with SPACES in them.......REPLACE()
    application.GlobalInfo.Add pklName, pklArray
    SET pklCN = Nothing
    SET pklRS = Nothing
End Function

RJ Samp