Performance tuning

  • Yes,  dev and test servers hitting the same end point for the services, but the web services are hosted on two web servers, one is for dev web server, the other is for test web server. They have same VM resources like CPU, RAM, and they have installed the web service of same version .

    The web service, is to get a list of route from vendors database and then output the data into another our own database, that is used for reporting. That database on dev and test are exact same version  of copy too.

    The web service process each route, find their stops, and students, and find the bus stop corner information and then insert data into our own database for reporting.

    Vendors database doesnot store stop corner information, and even pull from UI the corner is populated on the fly. So we have to use their web service to get that.

  • Can you post a screen shot of the SSIS package?

     

    It sounds like all it is an execute task that is calling some local web services and that web service is handling everything else, the calls to the vendor, loading the results into the database etc.....

  • Yes, the step is execute process task that has a executable C:\_SSISFILES\Miscellaneous\eLinkUtility\Transportation.ETL.VersaTransToStaging.exe

     

    The executable call the web service that has endpoints.

  • sqlfriend wrote:

    unfortunately it is not something simple that T-SQL can handle. There are complicated logic to calculate stop corners according to time and direction of the bus etc, that is why the vendor created the web service API. And we don't know their logic of how they do it.

    It's funny that people say that T-SQL can't handle something when they don't know what the logic of how a 3rd party does it actually is. 😉  If I were the 3rd party, that's what I'd tell people so I could keep the business.  😉

    Anyway, I have a full appreciation of what you're going through there.  I just think that you're actually spending time on the wrong thing.  The difference between 8 hours and 10 hours can be summarized as "sucks alot" and "sucks more than alot".  I'd be riding herd on the 3rd party vendor to fix things.  I'd also serious be looking for alternatives.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • sqlfriend wrote:

    Yes, the step is execute process task that has a executable C:\_SSISFILES\Miscellaneous\eLinkUtility\Transportation.ETL.VersaTransToStaging.exe

    The executable call the web service that has endpoints.

     

    I'm guessing that snippet of C# code is from the executable you're calling?

     

    You're probably going to have to unravel that whole mess, what that executable is actually doing, what the local web service it's calling is doing and what the vendor services are doing.  Then isolate each component, specifically the calls from your local web servers to the vendor, the db writes and whatever processing is being done(you'll have to figure out whether that's in the executable or in your local webservice).

     

    Try starting with whoever is responsible for the local web service you're calling, figure out what calls it's making to the vendor and get timings on those.  Also verify if those are hitting the same endpoint on the vendor side or if the vendor also has dev/test/prod systems.  It sounds like the vendor has multiple services, getting the route then calling some other services based on that, you should be able to break that down and get a timing for a single call of each one.

    As for the executable, you said it gets a list of routes?  Where does it get that from?  Can it be run in parallel?  That will come down to understanding what it's actually doing.

  • Thanks for your recommendations. What I can say is the .exe is calling our local web application that calls vendor's web service end points. Vendor side web service is the same, no dev, test environment, all the same. We are at a turnover time of .net developers, but I will try to find another one to see if he has ideas.  As a DBA, I have little experience of.net code side. With that being said I posted a piece of code earlier, I wonder if that thread.sleep(1) makes the whole time of the job real long. while (_vtStudentService.GetJobStatus(jobId) == VT.StudentService.JobStatusType.jsPending)

    {// wait...

    Thread.Sleep(1);}

     

    • This reply was modified 3 years, 3 months ago by  sqlfriend.
    • This reply was modified 3 years, 3 months ago by  sqlfriend.

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply