August 14, 2020 at 6:10 pm
Thanks. This overnight update process is a part of a big project we had and hired several contract developers and project team. The web service/applications does very complicated process for business end. I am not a developer but as I understand in the front code there is business matrix needs to evaluate and process goes from .net code and backend database. So it is not something only involves stored procedures.
August 15, 2020 at 8:59 am
Thanks. This overnight update process is a part of a big project we had and hired several contract developers and project team. The web service/applications does very complicated process for business end. I am not a developer but as I understand in the front code there is business matrix needs to evaluate and process goes from .net code and backend database. So it is not something only involves stored procedures.
That has similarities to a system we use, tons of application logic, much of which could have been done far more efficiently in the database.
I'm just going to re-iterate what many have said based on my usual sequence of checks when users say "it's really slow":
Quick glance at SQL server for obvious signs of distress (almost always fine), then look at the four front end servers (load balanced by number of connections only), almost invariably IIS or one of the App pools is having a meltdown.
I would be extremely surprised if you will improve things by concentrating on the SQL server. Maybe get the (expensive?) consultants you employed to explain the seemingly inefficient design and see if it could be improved? Even 5 hours on a good day seems excessive 😉
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
August 15, 2020 at 10:20 pm
Thanks. This overnight update process is a part of a big project we had and hired several contract developers and project team. The web service/applications does very complicated process for business end. I am not a developer but as I understand in the front code there is business matrix needs to evaluate and process goes from .net code and backend database. So it is not something only involves stored procedures.
It's funny how many people say and think that... and are frequently wrong. 😀 Just sayin'... 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
August 17, 2020 at 3:17 pm
Going with the assumption that this needs to be an application job, I highly doubt a web server is the right approach for this type of work. Even a C# application that is called by a windows scheduled task would have a performance boost over a web thing. Reduce the technology stack and you will get a performance boost.
Having a web service do anything complicated is very likely to be a bottleneck. Not for sure, but every time I have worked with web services, they need to be very "quick and light" processes or they become incredibly slow. Processing 50,000 records, to me, sounds like a good use case for SSIS. The way I would have designed that if it was a super complex process is to have a front end tool to define the business matrix (custom built or possibly even something in excel), and the next step depends on how complex the matirx is. If I can parse and deal with the matrix in SSIS, then SSIS would be my next tool. If it is too complicated for SSIS, then I'd have a C# application that is likely going to be a windows service. The windows service would watch for a specific trigger, and when the trigger is fired it would process the file, dump it out to an excel/CSV file and fire up SSIS to do the import. If more logic is needed on the SQL side, the import would go to a staging table and a stored procedure would fire after to do the final bit of processing (or SSIS... again, depends on the complexity). Even if your stored procedure is still doing things in a row-based operation rather than set-based, you now have removed several steps that can be time consuming for large data sets - your connect and disconnect between each row being updated.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 17, 2020 at 3:39 pm
Thanks much, it makes sense to me.
The project was a big one and a complicated one. And an entire project team with several good developers and BA and Project manager for that, I remember it is the biggest time we hired so many contractors. I think they do that for a reason.
August 17, 2020 at 4:00 pm
Thanks much, it makes sense to me.
The project was a big one and a complicated one. And an entire project team with several good developers and BA and Project manager for that, I remember it is the biggest time we hired so many contractors. I think they do that for a reason.
Any good SQL developers and/or good "normal" developers in there, or just Web developers? 😉
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
August 17, 2020 at 4:06 pm
Yes, there was a very experienced SQL developer contractor too. He designed the whole database model, build up the database, create stored procedures and created some SSIS packages.
August 17, 2020 at 4:37 pm
While I agree that hiring a lot of contractors for a complicated project produces a complicated result; it does really depends on the contractors. If you hire web developers to build up a process that should be done in SQL or SSIS or a windows service, they are going to use web developer mindset to get the result you need and not use the right tools for the job. If all you know about is a hammer, every problem looks like a nail.
The nice part is you currently have a working solution. It is incredibly slow, but it works. If you can reproduce the result set with a more efficient tool set, why not?
Well, that is assuming you have access to the source code so you can see what they did. Which I really hope you have and can support as it would suck if a Windows update came out that killed the process and you needed to hire the consultant team back in to support the tool.
Just because consultants built the tool, doesn't mean it is as efficient as it could be. At my workplace, a 5 hour process, even if it runs during company downtime which we currently have more than a 5 hour window it could run in, is a huge risk. What happens when your company expands and your downtime windows shrink and 5 hours is too long? If you have spare development resources (SQL and whatever that tool was written in), I would be looking at ways to optimize it. If you can take that 5 hour process and make it complete in 1 hour (or less), your future self will thank you. AND much better to get the performance improved now while you have time and resources rather than when you get an email saying that the process is broken because ti runs too long. If I have spare resources to work on a slow application, I work on it. I have an application that does row-based operations on SQL and changing it is going to be overly complex and will introduce problems in other tools. I had some free time though and spent a bit working on it and I got a 30 minutes process down to 2 minutes. Did it NEED to be a 2 minute process? No, but speeding that process up resulted in other work being able to be done rather than sitting and waiting for the tool to give results for half an hour. And when the person who uses the tool gets busy at work, he was using 2 computers so one could process the data for half an hour and he wasn't just stuck waiting for it to complete. Now, waiting 2 minutes is not a big deal. Could make it faster (it is still row based operations), but the 2 minute wait is "good enough".
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 17, 2020 at 4:48 pm
I'm confused... in your first reply, you said there was no SSIS package and now you are saying there are SSIS packages?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 18, 2020 at 12:18 pm
This was removed by the editor as SPAM
August 19, 2020 at 9:20 pm
For 50,000 records, I wouldn't be happy if it took more than a few minutes to process.
August 19, 2020 at 11:36 pm
Getting back to the original question, the reason why it's not using so much CPU is because it seems to be IO bound (IMHO) due to the RBAR nature of it's calls. Until someone changes to set-based code, this code just won't be fast.
And even just a few minutes to process 50,000 rows would having me looking for a fix. Of course, "It Depends" but I'm thinking this should be and could be sub-second.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 20, 2020 at 12:04 am
Thanks all for the analysis and tips!
August 20, 2020 at 4:33 am
Back in the times when I started as a junior SQL developer I faced pretty much identical situation.
There was a Time-and-Attendance match task, which had a solution implemented by 3 qualified front-end developers.
Initially it was done in Application, and it was "overnight" process, taking 8+ hours to complete. If we're lucky and there was no a network issue during this time causing the process to fail.
Those failures made them to move the solution to SQL. They pretty much repeated their VB.Net code but with SQL syntax. OK,. it eliminated network issues, but the process was long as it used to be, actually, even longer, because the customer base was growing. Process won't finish before next morning, and you can imagine the debugging task, when every test run is an EVENT.
At that stage the boss decided to ask that junior SQL sucker if he can do anything to improve performance. It was not a simple task, the logic was pretty complex, with multi-stage iterations, all implemented with cursors and loops. I had to change everything, not a single line of old code left there, but when the task started to complete within 15-20 minutes they could not believe it.
Now we could actually debug the thing, and instead of manually correct mishaps we could tweak the logic of the code and rerun it.
Several months later, after learning some more tricks (mostly from the good folks on sqlservercentral.com) I used a window of opportunity and re-wrote the procedure, so it would complete the task within 30 seconds.
Yes, that's right, 10 hours to 30 seconds. It's not a miracle, I've done similar "magic" many times since then, and I'm sure (I know for sure) I'm not the only one who is capable of such "magic".
This kind of performance improvement you should expect from a correct overall solution. But I must say - if your SQL developer goes with Developing SSIS packages - there is a very little hope you're gonna get that correct effective solution.
_____________
Code for TallyGenerator
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply