Wayne West (12/4/2008)
Jeff Moden (11/25/2008)
Wayne West (10/22/2008)
Thanks for the article, Ranga, very useful. I look forward to getting our ERP system into 2005 or 2008 as I know they're not doing a good job indexing, but I'm a little leery of altering or adding indexes without some solid backup proof.Oh... be careful... the worst part about an ERP system is that it enables folks who have no clue about how databases work or how to get performance out of the system, including index usage, to write 62 table joins with a built in cross join like the one I've recently seen. The old saying of "If you make something idiot proof, only idiots will use it" has a lot of truth to it when it comes to using the wonderful features of ERP's. 😉
Haven't yet found a 62 table join, the most I've seen in the canned views that they supplied for reporting is probably 7 or 8 as there's pretty much zero T-SQL code in the system. They're doing everything through a 4GL "application server" and wonder why their performance isn't acceptable to us! We just found an error message indicating that they're using cursors in their code, it's going to make for an interesting phone conference this AM....
Director of Technology for my old company was evaluating some "real time replication" software that used triggers to do the replication. I asked if I could take a look... not only were they RBAR, but they couldn't handle batch inserts. No matter how many rows you inserted in a single insert, it would only replicate the "first" row it came across. Than was an "interesting phone conference" in the AM, as well. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.