January 14, 2021 at 7:25 pm
So we have this third party shipping system that is...well...user hostile at best. To add insult to injury for the users the system has been suffering increased performance problems over the last several months. I opened a ticket with the vendor in July and have not received anything resembling a resolution. Just empty hope and increased irritation from our shipping department. Well yesterday I decided enough was enough and I was going to try to tackle this thing myself. I have access to the database and all the folders where there software resides. I discovered the source of the issue and fixed it myself. But the response I got from the shipping department is the kind of encouragement that people in our position need from time to time.
Whatever you did, it is an insane difference. Orders are now running in seconds instead of long minutes. Sweet!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 14, 2021 at 7:40 pm
So we have this third party shipping system that is...well...user hostile at best. To add insult to injury for the users the system has been suffering increased performance problems over the last several months. I opened a ticket with the vendor in July and have not received anything resembling a resolution. Just empty hope and increased irritation from our shipping department. Well yesterday I decided enough was enough and I was going to try to tackle this thing myself. I have access to the database and all the folders where there software resides. I discovered the source of the issue and fixed it myself. But the response I got from the shipping department is the kind of encouragement that people in our position need from time to time.
Whatever you did, it is an insane difference. Orders are now running in seconds instead of long minutes. Sweet!!
Nice job, Sean. Yeah, hearing from the end users that you made their life better is a nice thing to hear every once in a while.
January 14, 2021 at 8:09 pm
So we have this third party shipping system that is...well...user hostile at best. To add insult to injury for the users the system has been suffering increased performance problems over the last several months. I opened a ticket with the vendor in July and have not received anything resembling a resolution. Just empty hope and increased irritation from our shipping department. Well yesterday I decided enough was enough and I was going to try to tackle this thing myself. I have access to the database and all the folders where there software resides. I discovered the source of the issue and fixed it myself. But the response I got from the shipping department is the kind of encouragement that people in our position need from time to time.
Whatever you did, it is an insane difference. Orders are now running in seconds instead of long minutes. Sweet!!
Did you find some indexes were missing?
January 15, 2021 at 7:38 am
Sean Lange wrote:So we have this third party shipping system that is...well...user hostile at best. To add insult to injury for the users the system has been suffering increased performance problems over the last several months. I opened a ticket with the vendor in July and have not received anything resembling a resolution. Just empty hope and increased irritation from our shipping department. Well yesterday I decided enough was enough and I was going to try to tackle this thing myself. I have access to the database and all the folders where there software resides. I discovered the source of the issue and fixed it myself. But the response I got from the shipping department is the kind of encouragement that people in our position need from time to time.
Whatever you did, it is an insane difference. Orders are now running in seconds instead of long minutes. Sweet!!
Nice job, Sean. Yeah, hearing from the end users that you made their life better is a nice thing to hear every once in a while.
It's always good to hear that you've made the users' lives better. "I don't know what you did, but it's so much faster now". Of course, every so often the silent response from me is "I don't know what I did because I haven't touched it but if you're happy, I'm happy."
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 15, 2021 at 10:10 am
Ok, own up, which one of us was it?
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 15, 2021 at 10:10 am
Ed Wagner wrote:Sean Lange wrote:So we have this third party shipping system that is...well...user hostile at best. To add insult to injury for the users the system has been suffering increased performance problems over the last several months. I opened a ticket with the vendor in July and have not received anything resembling a resolution. Just empty hope and increased irritation from our shipping department. Well yesterday I decided enough was enough and I was going to try to tackle this thing myself. I have access to the database and all the folders where there software resides. I discovered the source of the issue and fixed it myself. But the response I got from the shipping department is the kind of encouragement that people in our position need from time to time.
Whatever you did, it is an insane difference. Orders are now running in seconds instead of long minutes. Sweet!!
Nice job, Sean. Yeah, hearing from the end users that you made their life better is a nice thing to hear every once in a while.
It's always good to hear that you've made the users' lives better. "I don't know what you did, but it's so much faster now". Of course, every so often the silent response from me is "I don't know what I did because I haven't touched it but if you're happy, I'm happy."
Not forgetting the classic from the users, after hours of you working on something for them in the background: "Never mind, it's fixed itself!"
"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?
January 15, 2021 at 2:10 pm
Sean Lange wrote:So we have this third party shipping system that is...well...user hostile at best. To add insult to injury for the users the system has been suffering increased performance problems over the last several months. I opened a ticket with the vendor in July and have not received anything resembling a resolution. Just empty hope and increased irritation from our shipping department. Well yesterday I decided enough was enough and I was going to try to tackle this thing myself. I have access to the database and all the folders where there software resides. I discovered the source of the issue and fixed it myself. But the response I got from the shipping department is the kind of encouragement that people in our position need from time to time.
Whatever you did, it is an insane difference. Orders are now running in seconds instead of long minutes. Sweet!!
Did you find some indexes were missing?
Haha I was actually initially thinking something along those lines. But this software is...well...interesting. It turned out that they were storing an archive copy of the xml in a file for every single package. In addition they were also saving the dynamic sql that they generated for every single package. This folder had over 3.5 million files in it. No wonder it was so damn slow. Windows has to make sure every file name is unique, and with that many to parse through of course it is slow. I renamed the folder and created a new one with the same name. I found 3 other folders with the same kind of craziness going on. Today I am writing some scheduled tasks to clear this insanity out of these folders. Gotta love third party software that decimates a system like that. Sheesh!!! And since Ed Wagner will ask, no this isn't our ERP doing this. It is another choice piece of vendor work.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 15, 2021 at 2:19 pm
Jonathan AC Roberts wrote:Sean Lange wrote:So we have this third party shipping system that is...well...user hostile at best. To add insult to injury for the users the system has been suffering increased performance problems over the last several months. I opened a ticket with the vendor in July and have not received anything resembling a resolution. Just empty hope and increased irritation from our shipping department. Well yesterday I decided enough was enough and I was going to try to tackle this thing myself. I have access to the database and all the folders where there software resides. I discovered the source of the issue and fixed it myself. But the response I got from the shipping department is the kind of encouragement that people in our position need from time to time.
Whatever you did, it is an insane difference. Orders are now running in seconds instead of long minutes. Sweet!!
Did you find some indexes were missing?
Haha I was actually initially thinking something along those lines. But this software is...well...interesting. It turned out that they were storing an archive copy of the xml in a file for every single package. In addition they were also saving the dynamic sql that they generated for every single package. This folder had over 3.5 million files in it. No wonder it was so damn slow. Windows has to make sure every file name is unique, and with that many to parse through of course it is slow. I renamed the folder and created a new one with the same name. I found 3 other folders with the same kind of craziness going on. Today I am writing some scheduled tasks to clear this insanity out of these folders. Gotta love third party software that decimates a system like that. Sheesh!!! And since Ed Wagner will ask, no this isn't our ERP doing this. It is another choice piece of vendor work.
Hey, Sean. I don't remember that particular ERP ever doing such a thing, but it sounds horrible. It's a "choice piece" of vendor something, all right. 😀 3.5 million is more than enough to slow down any file system.
Sounds like a great find and fix. Keep the files for a short time and blast them. If you can't delete them outright because management "needs them for future reference", move them to an archive folder where they can live until the drive fills up and forces management to make a decision. By all means, get the long-running enumeration of files to be independent of the database.
January 15, 2021 at 2:38 pm
I can think of 2 questions to ask the vendor.
Was this for testing?
Was it in case of a breach to see what was submitted?
They probably would never generate the volume needed to run into this in testing. So unless they did troubleshooting on site, it might be difficult to replicate.
January 15, 2021 at 2:44 pm
So we have this third party shipping system that is...well...user hostile at best. To add insult to injury for the users the system has been suffering increased performance problems over the last several months. I opened a ticket with the vendor in July and have not received anything resembling a resolution. Just empty hope and increased irritation from our shipping department. Well yesterday I decided enough was enough and I was going to try to tackle this thing myself. I have access to the database and all the folders where there software resides. I discovered the source of the issue and fixed it myself. But the response I got from the shipping department is the kind of encouragement that people in our position need from time to time.
Whatever you did, it is an insane difference. Orders are now running in seconds instead of long minutes. Sweet!!
Tuned a reporting query once that was running for 17+ hours, brought it down to 1.5 seconds. The day after I was asked if something had changed. I responded by asking "why do you ask?" with the reply of "no particular reason, just asking"
😎
It is nice to have recognition of good work, but it is equally important to have some tolerance when things get tough!
January 15, 2021 at 2:46 pm
It would have been far too obvious deleting just handful of records, had to be masked....
😎
January 15, 2021 at 2:50 pm
Sean Lange wrote:Jonathan AC Roberts wrote:Sean Lange wrote:So we have this third party shipping system that is...well...user hostile at best. To add insult to injury for the users the system has been suffering increased performance problems over the last several months. I opened a ticket with the vendor in July and have not received anything resembling a resolution. Just empty hope and increased irritation from our shipping department. Well yesterday I decided enough was enough and I was going to try to tackle this thing myself. I have access to the database and all the folders where there software resides. I discovered the source of the issue and fixed it myself. But the response I got from the shipping department is the kind of encouragement that people in our position need from time to time.
Whatever you did, it is an insane difference. Orders are now running in seconds instead of long minutes. Sweet!!
Did you find some indexes were missing?
Haha I was actually initially thinking something along those lines. But this software is...well...interesting. It turned out that they were storing an archive copy of the xml in a file for every single package. In addition they were also saving the dynamic sql that they generated for every single package. This folder had over 3.5 million files in it. No wonder it was so damn slow. Windows has to make sure every file name is unique, and with that many to parse through of course it is slow. I renamed the folder and created a new one with the same name. I found 3 other folders with the same kind of craziness going on. Today I am writing some scheduled tasks to clear this insanity out of these folders. Gotta love third party software that decimates a system like that. Sheesh!!! And since Ed Wagner will ask, no this isn't our ERP doing this. It is another choice piece of vendor work.
Hey, Sean. I don't remember that particular ERP ever doing such a thing, but it sounds horrible. It's a "choice piece" of vendor something, all right. 😀 3.5 million is more than enough to slow down any file system.
Sounds like a great find and fix. Keep the files for a short time and blast them. If you can't delete them outright because management "needs them for future reference", move them to an archive folder where they can live until the drive fills up and forces management to make a decision. By all means, get the long-running enumeration of files to be independent of the database.
It wasn't the ERP that you have the "pleasure" of working with. It is the shipping system which is a separate but equally "interesting" piece of work. The utility will take all the files and zip them once a day and store them in another folder. Then it deletes them from their location. And finally will go to the archive folder and delete anything over a year old. So simple I really don't understand why more developers can't write this kind of thing into their software as it really isn't all that difficult. I wrote all three utilities in less than half a day. I had to use three different sets of logic because there are some files in each of these locations that need to be kept so the system will keep working.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 15, 2021 at 4:05 pm
Ed Wagner wrote:Sean Lange wrote:Jonathan AC Roberts wrote:Sean Lange wrote:So we have this third party shipping system that is...well...user hostile at best. To add insult to injury for the users the system has been suffering increased performance problems over the last several months. I opened a ticket with the vendor in July and have not received anything resembling a resolution. Just empty hope and increased irritation from our shipping department. Well yesterday I decided enough was enough and I was going to try to tackle this thing myself. I have access to the database and all the folders where there software resides. I discovered the source of the issue and fixed it myself. But the response I got from the shipping department is the kind of encouragement that people in our position need from time to time.
Whatever you did, it is an insane difference. Orders are now running in seconds instead of long minutes. Sweet!!
Did you find some indexes were missing?
Haha I was actually initially thinking something along those lines. But this software is...well...interesting. It turned out that they were storing an archive copy of the xml in a file for every single package. In addition they were also saving the dynamic sql that they generated for every single package. This folder had over 3.5 million files in it. No wonder it was so damn slow. Windows has to make sure every file name is unique, and with that many to parse through of course it is slow. I renamed the folder and created a new one with the same name. I found 3 other folders with the same kind of craziness going on. Today I am writing some scheduled tasks to clear this insanity out of these folders. Gotta love third party software that decimates a system like that. Sheesh!!! And since Ed Wagner will ask, no this isn't our ERP doing this. It is another choice piece of vendor work.
Hey, Sean. I don't remember that particular ERP ever doing such a thing, but it sounds horrible. It's a "choice piece" of vendor something, all right. 😀 3.5 million is more than enough to slow down any file system.
Sounds like a great find and fix. Keep the files for a short time and blast them. If you can't delete them outright because management "needs them for future reference", move them to an archive folder where they can live until the drive fills up and forces management to make a decision. By all means, get the long-running enumeration of files to be independent of the database.
It wasn't the ERP that you have the "pleasure" of working with. It is the shipping system which is a separate but equally "interesting" piece of work. The utility will take all the files and zip them once a day and store them in another folder. Then it deletes them from their location. And finally will go to the archive folder and delete anything over a year old. So simple I really don't understand why more developers can't write this kind of thing into their software as it really isn't all that difficult. I wrote all three utilities in less than half a day. I had to use three different sets of logic because there are some files in each of these locations that need to be kept so the system will keep working.
It's not as simple as a super-verbose logging option set for troubleshooting, hidden away in a config file is it? Have been bitten by that in the past.
"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?
January 15, 2021 at 4:53 pm
Neil Burton wrote:Ed Wagner wrote:Sean Lange wrote:So we have this third party shipping system that is...well...user hostile at best. To add insult to injury for the users the system has been suffering increased performance problems over the last several months. I opened a ticket with the vendor in July and have not received anything resembling a resolution. Just empty hope and increased irritation from our shipping department. Well yesterday I decided enough was enough and I was going to try to tackle this thing myself. I have access to the database and all the folders where there software resides. I discovered the source of the issue and fixed it myself. But the response I got from the shipping department is the kind of encouragement that people in our position need from time to time.
Whatever you did, it is an insane difference. Orders are now running in seconds instead of long minutes. Sweet!!
Nice job, Sean. Yeah, hearing from the end users that you made their life better is a nice thing to hear every once in a while.
It's always good to hear that you've made the users' lives better. "I don't know what you did, but it's so much faster now". Of course, every so often the silent response from me is "I don't know what I did because I haven't touched it but if you're happy, I'm happy."
Not forgetting the classic from the users, after hours of you working on something for them in the background: "Never mind, it's fixed itself!"
Many, many years ago as a computer operator at Ent Credit Union while working days on Friday and Saturday the phone would ring. The first thing I would do is walk over to the console and type 3 or 4 U's then answer the phone. Almost always the response from the teller on the other end was "Never mind, you already did it."
The U was to unlock their terminal (any terminal actually).
January 15, 2021 at 5:01 pm
david.edwards 76768 wrote:Neil Burton wrote:Ed Wagner wrote:Sean Lange wrote:So we have this third party shipping system that is...well...user hostile at best. To add insult to injury for the users the system has been suffering increased performance problems over the last several months. I opened a ticket with the vendor in July and have not received anything resembling a resolution. Just empty hope and increased irritation from our shipping department. Well yesterday I decided enough was enough and I was going to try to tackle this thing myself. I have access to the database and all the folders where there software resides. I discovered the source of the issue and fixed it myself. But the response I got from the shipping department is the kind of encouragement that people in our position need from time to time.
Whatever you did, it is an insane difference. Orders are now running in seconds instead of long minutes. Sweet!!
Nice job, Sean. Yeah, hearing from the end users that you made their life better is a nice thing to hear every once in a while.
It's always good to hear that you've made the users' lives better. "I don't know what you did, but it's so much faster now". Of course, every so often the silent response from me is "I don't know what I did because I haven't touched it but if you're happy, I'm happy."
Not forgetting the classic from the users, after hours of you working on something for them in the background: "Never mind, it's fixed itself!"
Many, many years ago as a computer operator at Ent Credit Union while working days on Friday and Saturday the phone would ring. The first thing I would do is walk over to the console and type 3 or 4 U's then answer the phone. Almost always the response from the teller on the other end was "Never mind, you already did it."
The U was to unlock their terminal (any terminal actually).
Was that after you did the L[number] to lock it 😉
😎
Viewing 15 posts - 65,371 through 65,385 (of 66,816 total)
You must be logged in to reply to this topic. Login to reply