Commercial Software with All Tables as Heaps and Terrible Performance
-
It's not Oracle. It's unaware front-end developers creating tables using Visual Studio. It's amazing what a poor job it does if you don't actually specify things explicitly.
--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) -
Eirikur Eiriksson (9/14/2015)
Jeff Moden (9/14/2015)
Eirikur Eiriksson (9/13/2015)
Using heap to speed the inserts is like a very bad Credit Card deal, no transaction fees but X000% Annual interest rate:pinch:😎
Jeff Moden (9/12/2015)
It's a really good thing that I don't live within driving distance of that, ummm... person.Jeff, what happened to the PoIP (Porkchop over IP) you were working on?:-D
I decided to go whole hog on a new porkchop launcher project, instead 😀
That's quite "striking":hehe:
😎
That's not bad but still - it's a medium range delivery system. You might consider thinking about adapting "cruise porkchops" onto the upcoming Navy platform (I think this would be dubbed LRASM-P given the current funding from defense dept). cruising speed is 2.8M. and effective range in the 3-5K miles.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Matt Miller (#4) (9/14/2015)
Eirikur Eiriksson (9/14/2015)
Jeff Moden (9/14/2015)
Eirikur Eiriksson (9/13/2015)
Using heap to speed the inserts is like a very bad Credit Card deal, no transaction fees but X000% Annual interest rate:pinch:😎
Jeff Moden (9/12/2015)
It's a really good thing that I don't live within driving distance of that, ummm... person.Jeff, what happened to the PoIP (Porkchop over IP) you were working on?:-D
I decided to go whole hog on a new porkchop launcher project, instead 😀
That's quite "striking":hehe:
😎
That's not bad but still - it's a medium range delivery system. You might consider thinking about adapting "cruise porkchops" onto the upcoming Navy platform (I think this would be dubbed LRASM-P given the current funding from defense dept). cruising speed is 2.8M. and effective range in the 3-5K miles.
There is a Titan II silo south of Tucson in Green Valley. I don't think it would be too difficult to retrofit it to deliver a porcine package. Of course fueling and launching it, much less making sure that all world powers know that you're only delivering a porkchop, albeit in a rather aggressive manner, and that you're not lobbing a nuke. Though there are probably times when the nuke might be the appropriate response: after all, if you can throw it from orbit, it's the only way to be sure. 😀
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
Wayne West (9/14/2015)
Matt Miller (#4) (9/14/2015)
Eirikur Eiriksson (9/14/2015)
Jeff Moden (9/14/2015)
Eirikur Eiriksson (9/13/2015)
Using heap to speed the inserts is like a very bad Credit Card deal, no transaction fees but X000% Annual interest rate:pinch:😎
Jeff Moden (9/12/2015)
It's a really good thing that I don't live within driving distance of that, ummm... person.Jeff, what happened to the PoIP (Porkchop over IP) you were working on?:-D
I decided to go whole hog on a new porkchop launcher project, instead 😀
That's quite "striking":hehe:
😎
That's not bad but still - it's a medium range delivery system. You might consider thinking about adapting "cruise porkchops" onto the upcoming Navy platform (I think this would be dubbed LRASM-P given the current funding from defense dept). cruising speed is 2.8M. and effective range in the 3-5K miles.
There is a Titan II silo south of Tucson in Green Valley. I don't think it would be too difficult to retrofit it to deliver a porcine package. Of course fueling and launching it, much less making sure that all world powers know that you're only delivering a porkchop, albeit in a rather aggressive manner, and that you're not lobbing a nuke. Though there are probably times when the nuke might be the appropriate response: after all, if you can throw it from orbit, it's the only way to be sure. 😀
Someone has leaked the details
😎
-
I was going to say I'm sure that the software house will admit full responsibility for their shoddy work and give me a script to quickly create all the indexes that should have been there in the first place and, "pigs might fly" but looking at the posts above it looks like this is quite possible so I had better retract that statement!:-P
-
Eirikur Eiriksson (9/14/2015)
Someone has leaked the detailsSPOM!!! 🙂
--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) -
robinwilson (9/14/2015)
I was going to say I'm sure that the software house will admit full responsibility for their shoddy work and give me a script to quickly create all the indexes that should have been there in the first place and, "pigs might fly" but looking at the posts above it looks like this is quite possible so I had better retract that statement!:-PHeh... actually, the "porkchop" thing came from a long-ago post where some manager was almost literally whining about a "developer" that he couldn't control and wouldn't fire. After several iterations of making a suggestions to him and him coming up with yet another excuse as to why he couldn't man up and take control, I suggested that he take the "developer" out for a nice porkchop dinner. It would occur in a shed with the "developer" tied to a chair and would be fed the porkchops using a 2 banded wrist rocket at point blank range until he was ready to cooperate as a viable team member. "Porkchops" have become kind of a shortcut here on SSC for "Take control" on the giving side and "Get real" on the receiving side.
I do, however, agree with the analogy of "when Pigs fly". As the picture depicts, that's also possible. 😛
--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) -
Hello Jeff
Thanks for explaining this as I did wonder where the rocket propelled pigs had come from!
I've just been in touch with another organisation that uses the same software and they are currently restarting their SQL Server twice a day to keep the system operational.
We wanted a guide on something and the software supplier sent one dated 2002 so it is obvious the software hasn't changed much since then as the screenshots look the same apart from the fact it is obvious it is running on Windows 2000.
Robin
-
I changed all my 450 tables from clustered to heap and got a big boost in efficiency. Most of my customers have several hundred gigs of data but only 8-16 GB of memory. The clustered pages were always getting swapped out of data cache. The customers always worked on the most recent few months of data which are *NOW* all on the most recent pages in cache. The trick is to include(pk) in most of the non-clustered indexes.
This is easy real life performance gains using heaps after years of incrementally improving a clustered implementation.
Did all you complainers actually test performance?
-
Hello Bill
Thanks for your response. It is interesting that not having clustered indexes helped in your system.
People do complain but have mostly got used to putting up with waiting an unacceptable length of time for something to happen.
Writing reports is tricky when returning data is slow and speed is extremely variable (i.e. the same query can take 1 second or 30 minutes).
I have also developed some web-based frontends to the database and these are also very slow so I have to get around it by caching lots of data with jQuery to avoid too many database calls (they just need to wait when they first open it).
The server has 8 cores and 128GB of ram.
I do hope we can set up clustered indexes on the database without breaking SLAs and I really hope it works as after that and numerous IT consultants who couldn't find anything, we are out of options.
I have never known SQL Server to behave so badly and unpredictably and it tends to just work.
Robin
-
robinwilson (9/15/2015)
I have never known SQL Server to behave so badly and unpredictably and it tends to just work.Because all your, previous, databases were written using Brest Practices perhaps?
-
Dunno if it might help get your form A to B, but would reporting off a day-old copy be possible / acceptable?
I'm thinking an overnight copy/restore to A.N.Other server, which also created Clustered Indexes, might be a way to trial various hypothesis. its unlikely to be any slower, and current user expectation is "unpredictable".
It might turn out to be a bit tough on the users, but it might well be that 80% of the time the reports run much much faster and they would forgive you the other 20% 🙂
Back in the '80s I had a mate who worked on traffic light synchronisation software. I asked him how the heck they tested whether Algorithm-A was better than Algorithm-B and his answer was "We load the new version and go out on the street at rush hour to see how far the traffic has backed up" !!
-
Hello Kristen
Thanks, that is a good idea and is kind of what we have tried.
We have a test system set up which is the same as the live system and is used for testing patches and upgrades.
We created clustered indexes on the most commonly used tables and reports are noticibly faster as is the system in general.
We ran a process on both systems twice and it was between 50-60% faster after we had created the indexes.
The software company blamed our SQL writing skills and said we needed to use their visual tool to write the queries (which is terrible by the way) but I have tested and table scans occur with that too and that is actually even slower still!
I think I just need to show them my evidence and hope they will consider changing their software to use a better data model without heaps but I am thinking they are unlikely to change.
I'm pleased I have finally got to the bottom of the performance issues as it has been mystifying us for ages and been so frustrating. I didn't think to check something as obvious as if the software company had added primary keys to their tables!
I feel that they have tried to build the database so it is object oriented rather than relational but that it hasn't really worked. It has the most unfriendly UI to match as well!
I have found it hard to benchmark performance as what some users deem slow can be fast for others.
-
robinwilson (9/19/2015)
We ran a process on both systems twice and it was between 50-60% faster after we had created the indexes.I would have expected a considerably better improvement than that. Hopefully more will be found with further tuning 🙂
I have found it hard to benchmark performance as what some users deem slow can be fast for others.
That sounds strange. Might just be accounted for by the data SQL needs being cached in memory at the time that the second user needed it? But if User-2 is consistently faster than User-1 (for a given task) I'd be wanting to investigate. They might have some stone-age ODBC/etc driver in their Client Config, or perhaps some conflict between A and B on their PC that the faster user didn't have. Lots of other possible reasons for course.
We've just rolled out a major upgrade to a 3rd party APP and their "Client Install" (a bunch of Batch files, String and Chewing-gum!!) made a complete mess of getting the right DLLs onto each client, but it did at least bring to light some inconsistencies in the client builds which I would have, previously, sworn was impossible 🙁
-
robinwilson (9/15/2015)
I do hope we can set up clustered indexes on the database without breaking SLAs and I really hope it works as after that and numerous IT consultants who couldn't find anything, we are out of options.I have never known SQL Server to behave so badly and unpredictably and it tends to just work.
Hmm... That sounds like a challenge of the type I enjoy. 🙂
If you have any interest in making that numerous+1 (though I'm pretty sure I'll find something), drop me a PM.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 15 posts - 16 through 30 (of 69 total)
You must be logged in to reply to this topic. Login to reply