Is the SugarCRM Web Service really that slow?

[timothyp]

Hi,

I'm using the SugarCRM web service to retreive data from SugarCRM and store it in Evolution Mail.
However, it seems to me this service is really slow as compared to other XML WebServices I've used.
At some point I was even considering to create my own XML WebService to retreive data directly
from the SugarCRM database, but instead I'd rather solve this problem.

Is anybody else using the WebService to retreive data ?
Do you have the same problem ?

Getting 600 contacts, in groups of 20 (to prevent a timeout during the get_entry_list call)
takes up the better part of an hour.

A code sample. For example, the code is called by the GetContacts() method on the client until
all contacts have been received, hence the Offset parameter. I'm aware limiting the maxresults
to 20 causes at least 30 round trips, but increasing this value causes timeouts.

Please note this is tested on a server with 100Mbps upload and 100Mbps download capacity
and the client can upload at 512kbps and download at 4Mbps.

The result is the same on both Windows (.NET 2.0) and Linux (Mono)

        private int getNextEntries(string Module, string[] Fields, string Query, string OrderBy, int Offset, int MaxResults, bool GetDeleted, ref DataTable Entries)

        {

            //Set the number of records to get for each request

            int maxResultsInLoop = (MaxResults < 20 ? MaxResults : 20);



            //Get the first results
            Console.WriteLine("Getting next entries. [{0}]",System.DateTime.Now.ToShortTimeString());

            get_entry_list_result entryList = this.sugarClient.get_entry_list(this.sessionId, Module, Query, OrderBy, Offset, Fields, maxResultsInLoop, Convert.ToInt32(GetDeleted));



            if (entryList.result_count > 0)

            {

                foreach (entry_value entry in entryList.entry_list)

                {

                    //Create a new DataRow

                    DataRow entryRow = Entries.NewRow();



                    //Loop trough the columns

                    foreach (name_value value in entry.name_value_list)

                    {

                        entryRow[value.name] = value.value;

                    }



                    //Add the DataRow to the DataTable

                    Entries.Rows.Add(entryRow);

                }



                return entryList.next_offset;

            }

            else

            {

                return -1;

            }

        }

Any ideas, feedback, etc... would be welcome.
Thank you.

[timothyp]

If you increase the ammount of records to get in a single rountrip performance improves a bit,
I've found 290 to be a good value. However the service seems to use way to much memory to achieve this.
Perhaps it's simply the PHP implementation of the service....

[jagilbert]

I've found it to be very slow, but not that slow.
For example, in my .NET app, I get 400 records, each of about 40 misc fields in about 80 seconds over a 1mb connection. checking the sugarcrm.log, i see that about 6 seconds has elapsed between the first and the last query, which is pretty fast considering that huge number of queries required to satisfy the request. The rest of the time must be taken as the server packages it all up and sends it to me. I monitor the cpu of my client machine, and i see very little activity, so I dont think its a problem on the receiving end. Of course XML is a very inefficient transport, with all the opening and closing tags etc. (please correct me if I'm wrong) but even if the resulting XML is 3x as large as its payload, that still wouldn't be enough to account for that much slowness. I can retrieve the same record set in 5.6 seconds if i connect to MYSQL directly and execute a select.
-Jeff

[timothyp]

Hi,
Yes indeed, getting the data yourself is a lot faster,
that's why I have considered writing my own webservice which gets the data,
because even if I run the code on the same server as the sugarcrm installation, it's still rather slow.

XML does create a lot of overhead, but this can easily be taken care of by applying the bz2 algorithm
to the entire XML data. 100MB can become a few hundred KB. So perhaps I should simply write my
own Web Service (c#/mono) and get data that way it would be faster....

[bnp]

really really slow yup,
the trouble with implementing your own data interface is ofcause that you lose any business logic you might have attached to logic hooks in sugar. currently i'm trying to get around this issue by getting data directly from the db, and then using sugars soap to insert and update in async calls.

[stasilo]

I agree.
Participated already in 2 projects where we had to replace services with direct connection to database.
Waiting 2-3 seconds for one page to open because of soap call is quite annoying.

[eggsurplus]

Sounds like 5.5 has addressed many of these issues.

[PittsburghCRM]

I'm in the middle of a data import right now and am regretting my choice to use the webservice for Sugar transactions. I'm averaging a very low rows/second average. Use direct DB access whenever possible.

[cmould]

I am embarking on a project to import 83,000 accounts and contacts into Sugarcrm. Early im my research the SOAP API seemed the way to go. We also use the Pentaho BI tool set which has an ETL tool that include a Web services component. Seemed a nice fit, even wrote a wrapper for the Sugarcrm soap functions because Pentaho could not handle complex types. Before jumping in I got curious about performance because I could not send complex types to the set_entry function. Reading this thread leads me to believe that the SOAP API for data import is a mistake.

Please advise. I saw earlier recommendations to use the SOAP API, but I need this done efficiently.

[eggsurplus]

Without knowing where the data is coming from I highly advise doing a database to database conversion. If the source data is not currently in some sort of database get it into one and then manually create the needed scripts to push the data into Sugar's database. This will take a few test runs especially if you aren't familiar with Sugar's data structure.