February 10, 2012 at 7:50 am
Friend of mine told me to order my joins so that the greatest expected number of rows are eliminated soonest. Unfortunately, I've never been clear on just how to do that.
February 10, 2012 at 8:02 am
sherifffruitfly (2/10/2012)
Friend of mine told me to order my joins so that the greatest expected number of rows are eliminated soonest. Unfortunately, I've never been clear on just how to do that.
Your friend isn't exactly correct. The optimizer will re-arrange join order to get the best plan, it will also change LEFT JOIN's to RIGHT JOIN's if that is what it thinks is the fastest way to return the data. You supply the FORCE ORDER hint, but unless you are a superstar with the optimizer like Paul White, I wouldn't recommend it. Let the optimizer do it's job.
I think what your friend means is that if you have a customerTypes table that has 10 rows, put that first and then JOIN to the customers table which has 100000 rows, especially if your WHERE clause has customerType = 'RETAIL' so you are only really looking for one customer type. As I said though, the optimizer will do that automatically.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 10, 2012 at 8:05 am
Oh welp there you go then! 🙂
Now back to figuring out how to get the right passwords in my deployed ssis package's connection managers. #@$@#%@
February 10, 2012 at 1:24 pm
Sometimes you don't need a new index, but simply need to add an additional column or two to an existing index. I've gotten in the habit of looking at all existing indexes first to see if any of them should cover the query.
And yes it is necessary to know your ratio of reads to inserts/updates/deletes. In my case I have one table with millions of rows, and there is only one process doing the inserts & updates, while hundreds of processes read from this table. For this table the numerous indexes are more beneficial, but I don't add one until I've ruled out every other possibility. With only one process doing inserts, I have better control of how the data gets in the table, and I can set & adjust thresholds every so often.
And once in a while, you'll find that the only way to improve performance is to redesign the table layout. I did that just once, because I despise having to scan a table multiple times to get the data I need from it.
Tony
------------------------------------
Are you suggesting coconuts migrate?
February 10, 2012 at 2:33 pm
I don't think the index is as bad as it sounds. Without a good index a process has to lock the whole table (on a table scan) instead of taking a more granular lock. On an insert not all the records need to be re-indexed, (I think just those in the page). But if we are talking about an operational database being heavily queried from end users, then a data warehouse database could be in order where, say, one day old data is acceptable.
----------------------------------------------------
February 10, 2012 at 2:44 pm
Daniel Bowlin (2/10/2012)
So I read the article and ran the query for missing indexes ordered by performance impact.The first 4 missing indexes were for the same table. Take a look at the attachment. The recommendation is for 4 amazing similar and mostly overlapping indexes. In this case I would be inclined to create an index on IWNROV, IWSVCD, IWNBFY, and include columns IWCNBR, IWCEK6, IWSPY8, IWDRAT, IWAKBN, IWBICH
I would appreciate comments on the appropriateness of my approach.
Thanks
That's typical from those DMVs, the next trick is to see if you either have an index that comes close to covering them all, and modify it to do so, or find an index or 2 that will cover all the missing ones.
I like using those DMVs as a starting spot. I then have to work with the data they provide to figure out which indexes really belong there. I remember once when I created a recommended index, and it said that I still needed to make the same one again. Turns out there was a non-SARGable Where clause getting in the way. Changed the query and it picked up the index just fine.
April 12, 2012 at 11:18 am
Good article for Prod DBA's trying to sort out slow running queries created by junior developers. 😉
qh
May 7, 2015 at 12:12 pm
Eric M Russell (2/10/2012)
Another scenario is where the developer includes un-needed table joins and returns additional columns that are never referenced by the application or reporting tool. I've even seen procedures that query an interim result into a temp table, which is then never used. This is often the result of a developer wrting a new stored procedure by copy/pasting from an existing stored procedure, and then failing to remove those table joins, columns, and code that arn't needed.
Copy/paste is a great way to avoid unnecessary work and I use it often. But it does require someone smart enough to figure out what's really needed in the new code and what should be dropped. Unfortunately, some people are too lazy to do that.
Another thing that irks me is when someone copies/pastes from code I've created, including header comments, and then leaves my name as the creator on their code. Especially when it's code I would never have created.
May 7, 2015 at 2:25 pm
marcia.j.wilson (5/7/2015)
Eric M Russell (2/10/2012)
Another scenario is where the developer includes un-needed table joins and returns additional columns that are never referenced by the application or reporting tool. I've even seen procedures that query an interim result into a temp table, which is then never used. This is often the result of a developer wrting a new stored procedure by copy/pasting from an existing stored procedure, and then failing to remove those table joins, columns, and code that arn't needed.Copy/paste is a great way to avoid unnecessary work and I use it often. But it does require someone smart enough to figure out what's really needed in the new code and what should be dropped. Unfortunately, some people are too lazy to do that.
Another thing that irks me is when someone copies/pastes from code I've created, including header comments, and then leaves my name as the creator on their code. Especially when it's code I would never have created.
I see this happen when a developer is maintaining the heavy code of someone long gone. The process is usually obscure and a minor tweak to the code may be needed. All else is left in order to not break any corner cases since as a newcomer you dont want to make business decisions on something that was left in place unless you know everything involved for a fact (100 %) . This other option requires a commitment to time and happens when a re-engineering is called for. In those cases I prefer to get user requirements, and start from scratch. That would be more efficient and I can also leave a proper manual documenting the build. BTW - Joining on other tables without returning columns from the other table is a valid option for the purpose of filtration.
----------------------------------------------------
May 8, 2015 at 7:39 am
marcia.j.wilson (5/7/2015)
Eric M Russell (2/10/2012)
Another scenario is where the developer includes un-needed table joins and returns additional columns that are never referenced by the application or reporting tool. I've even seen procedures that query an interim result into a temp table, which is then never used. This is often the result of a developer wrting a new stored procedure by copy/pasting from an existing stored procedure, and then failing to remove those table joins, columns, and code that arn't needed.Copy/paste is a great way to avoid unnecessary work and I use it often. But it does require someone smart enough to figure out what's really needed in the new code and what should be dropped. Unfortunately, some people are too lazy to do that.
Another thing that irks me is when someone copies/pastes from code I've created, including header comments, and then leaves my name as the creator on their code. Especially when it's code I would never have created.
On a similar note, what irritates me is when I've inherited the job of maintaining a 5,000 line legacy stored procedure and the original developer never bothered to comment their code in the header. I always make a header notation, including date, my name, and TFS ticket number. So it now looks like I created the damn thing.
Sometimes when I make my first modification, I'll start the header off with a notation (disclaimer really :-P) that includes the name of the orignal developer and the approximate date it was first deployed to production, even if that developer has since moved on to other things more in line with their skillset and ambitions (like roadside sign flipping :-P).
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 8, 2015 at 1:02 pm
Eric M Russell (5/8/2015)
marcia.j.wilson (5/7/2015)
Eric M Russell (2/10/2012)
Another scenario is where the developer includes un-needed table joins and returns additional columns that are never referenced by the application or reporting tool. I've even seen procedures that query an interim result into a temp table, which is then never used. This is often the result of a developer wrting a new stored procedure by copy/pasting from an existing stored procedure, and then failing to remove those table joins, columns, and code that arn't needed.Copy/paste is a great way to avoid unnecessary work and I use it often. But it does require someone smart enough to figure out what's really needed in the new code and what should be dropped. Unfortunately, some people are too lazy to do that.
Another thing that irks me is when someone copies/pastes from code I've created, including header comments, and then leaves my name as the creator on their code. Especially when it's code I would never have created.
On a similar note, what irritates me is when I've inherited the job of maintaining a 5,000 line legacy stored procedure and the original developer never bothered to comment their code in the header. I always make a header notation, including date, my name, and TFS ticket number. So it now looks like I created the damn thing.
Sometimes when I make my first modification, I'll start the header off with a notation (disclaimer really :-P) that includes the name of the orignal developer and the approximate date it was first deployed to production, even if that developer has since moved on to other things more in line with their skillset and ambitions (like roadside sign flipping :-P).
Heh, I've done that, add a disclaimer in a sense stating that I am just modifying what was existing. I've heard of some of the past developers that I've taken over for actually move on to lucrative positions at big tech companies. It makes you realize it is not always what you know, but who you know 🙂
----------------------------------------------------
May 13, 2015 at 8:26 am
Great into, thank you.
Viewing 12 posts - 31 through 41 (of 41 total)
You must be logged in to reply to this topic. Login to reply