Stats

  • Replies 7
  • Subscribers 144
  • Views 954
  • Members are here 0

Export to CSV efficiently? (code review)

UsableLoki

I wrote this function to export a list to a generated CSV file.  However the for loop takes hours to run (albeit I am exporting 100,000+ items, but still, the commands that generate these items change things in my project and those only take a few minutes).  I tried to create a 2nd order string buffer because I figured that fprintf to port may have been a bottleneck but this made it run even slower (ran all night and still wasn't done).  Can someone please offer any improvements to this?  I left the previously direct way of export via fprintf commands commented out.

procedure(writeListsToCSV(headerList ListOfLists fileNameSuffix location)
  let((buffer buffer2 comma currentDateTime DateTimeList TimeList year month day time fileName fileHandle lengthHeader)
  ; Get current date and time;;; getCurrentTime() ; => "Jan 26 18:15:18 1993"
    DateTimeList = parseString(getCurrentTime() )
    year = nth(3 DateTimeList)
    month = nth(0 DateTimeList)
    when(month == "Jan" month = "01")
    when(month == "Feb" month = "02")
    when(month == "Mar" month = "03")
    when(month == "Apr" month = "04")
    when(month == "May" month = "05")
    when(month == "Jun" month = "06")
    when(month == "Jul" month = "07")
    when(month == "Aug" month = "08")
    when(month == "Sep" month = "09")
    when(month == "Oct" month = "10")
    when(month == "Nov" month = "11")
    when(month == "Dec" month = "12")
    day = nth(1 DateTimeList)
    time = buildString( parseString( nth(2 DateTimeList) ":") "")

    currentDateTime = buildString( list(year month day time) "_")

    /* I couldn't get this to work, "/" as last character returns "\/" and the compiler hates "\"
    print(getchar(location strlen(location)))
    printf("\n")
    when(not(getchar(location strlen(location)) == "/")
    strcat(location "/")
    )
    */

    ; todo:need to incorporate isReadable(filePath) and getWorkingDir() for better flow
    fileName = strcat(location
                      "/"
                      currentDateTime
                      "_"
                      fileNameSuffix
                      ".csv")

    ; Write the headers
    buffer = strcat(buildString(headerList ",") "\n")

    printf("\nFor loop begins at: %s\n" getCurrentTime())
    lengthHeader = length(headerList) - 1
    ; Write the list values to the file
    for(j 0 length(ListOfLists)-1
        buffer2 = ""
        for(i 0 lengthHeader
            ;print(type(nth(i nth(j ListOfLists)))) ;;if missing values then check possible value type here
            ;printf("\n")

            if(i < lengthHeader then comma = "," else comma = "\n");buffer = strcat(buffer ",") )
            case(type(nth(i nth(j ListOfLists)))
                ('string
                    buffer2 = strcat(buffer2 nth(i nth(j ListOfLists)) comma)
                    ;fprintf(fileHandle nth(i nth(j ListOfLists)))
                )
                ('flonum
                    buffer2 = strcat(buffer2 sprintf(nil "%f" nth(i nth(j ListOfLists))) comma)
                    ;fprintf(fileHandle sprintf(nil "%f" nth(i nth(j ListOfLists))))
                )
                ('list
                    buffer2 = strcat(buffer2 sprintf(nil "%L" nth(i nth(j ListOfLists))) comma)
                    ;fprintf(fileHandle sprintf(nil "%L" nth(i nth(j ListOfLists))))
                )
                ('float
                    buffer2 = strcat(buffer2 sprintf(nil "%f" nth(i nth(j ListOfLists))) comma)
                    ;fprintf(fileHandle sprintf(nil "%f" nth(i nth(j ListOfLists))))
                )
                ('int
                    buffer2 = strcat(buffer2 sprintf(nil "%d" nth(i nth(j ListOfLists))) comma)
                    ;fprintf(fileHandle sprintf(nil "%d" nth(i nth(j ListOfLists))))
                )
                ('fixnum
                    buffer2 = strcat(buffer2 sprintf(nil "%d" nth(i nth(j ListOfLists))) comma)
                    ;fprintf(fileHandle sprintf(nil "%d" nth(i nth(j ListOfLists))))
                )
                ('t
                    buffer2 = strcat(buffer2 sprintf(nil "%s" nth(i nth(j ListOfLists))) comma)
                    ;fprintf(fileHandle sprintf(nil "%s" nth(i nth(j ListOfLists))))
                )
            )
            ;when(i < lengthHeader fprintf(fileHandle ",") )
        )
        buffer = strcat(buffer buffer2)
        ;fprintf(fileHandle "\n")
    )
    printf("\nFor loop ends at: %s\n" getCurrentTime())

    ; Open the file for writing
    fileHandle = outfile(fileName)
    ; Write
    fprintf(fileHandle buffer)
    ; Close the file
    close(fileHandle)
    printf("\nClosed file handle at: %s\n" getCurrentTime())
    printf("\nCSV GENERATED AT: %s\n" fileName)
    )
)

  • You can use the SKILL profiler to pinpoint where your code has bottlenecks. For example, in the CIW, type the following:

    profile( 'time)
writeListsToCSV(/*enter args here*/)
profileSummary( ?file "/tmp/profile.results")

    Then, an inventory of runtimes will be listed in tabular format in /tmp/profile.results

  • in reply to AaronSymko

    I've already pointed it down to the forloop running for hours- the strcat or the fprintf are the only functions of substance within (unless sprintf() nth(nth)) or tpye() are culprits...).  Will this profiler be applicable if it is rooted within a cloak function?  My large processing script uses it to get anything done in a reasonable time frame

    trans = axlDBTransactionStart();
    axlDBCloak('NetProcessingThatMakesListsForCSV(specifiedLayer) '(disableDisplay shape branch));
    axlDBTransactionCommit(trans);

    Thanks for your response by the way!

  • in reply to AaronSymko

    Btw, how do you format type your code into here so it even picks up the IDE structure tags?

  • It's just a guess, but skill lists are single linked and accessing them by index takes linear time.
    So every time you do a "nth(i nth(j ListOfLists))" it takes j steps to reach the row and then i steps to reach the column inside that row, doing thins inside two loops makes this operation O(N^2), which literally explodes when the lists get large. And you do this expensive thing all over the place.

    If you want to stay with the current structure, you could win alot by doing;

      ; Write the list values to the file
  for(j 0 length(ListOfLists)-1
    buffer2 = ""
    row = nth(j ListOfLists)
    for(i 0 lengthHeader
      col = nth(i row)
      ; use col instead of nth(i nth(j ListOfLists))
      ...

    Though, it is inherently more efficient to use the language constructs to walk over lists, e.g

      foreach(row ListOfLists
    forech(col row
      ; do something with the col
    )
  )

    Regards,

  • in reply to UsableLoki

    Your post is really in the wrong forum - these APIs are from Allegro, so this should be in our PCB forums not in the Custom IC SKILL (which is for Virtuoso). The responses below are good anyway (particularly that from  on the use of nth within a loop), but answering this specific question about how the profiler behaves with axlDBCloak (I have no idea what that does) is hard to answer for those of us without Allegro experience. I expect it will be OK, but I don't know for certain!

  • in reply to Andrew Beckett

    Ah when I searched the community links I simply chose the one that said SKILL, had no idea the other options had sub-options in them.  Thanks for pointing this out!

  • in reply to henker

    This did the trick, you helped immensely, thanks!!

Community Guidelines

The Cadence Design Communities support Cadence users and technologists interacting to exchange ideas, news, technical information, and best practices to solve problems and get the most from Cadence technology. The community is open to everyone, and to provide the most value, we require participants to follow our Community Guidelines that facilitate a quality exchange of ideas and information. By accessing, contributing, using or downloading any materials from the site, you agree to be bound by the full Community Guidelines.