Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Tips to optimize your SQL statements Expand / Collapse
Author
Message
Posted Friday, February 10, 2012 7:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 4:37 PM
Points: 33, Visits: 418
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.
Post #1250318
Posted Friday, February 10, 2012 8:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:26 PM
Points: 10,470, Visits: 13,780
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1250328
Posted Friday, February 10, 2012 8:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 4:37 PM
Points: 33, Visits: 418
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. #@$@#%@
Post #1250330
Posted Friday, February 10, 2012 1:24 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 10:21 AM
Points: 714, Visits: 862
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?
Post #1250544
Posted Friday, February 10, 2012 2:33 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, May 22, 2015 2:00 AM
Points: 571, Visits: 1,224
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.

Post #1250576
Posted Friday, February 10, 2012 2:44 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, May 11, 2015 9:02 AM
Points: 3,688, Visits: 72,445
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.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1250580
Posted Thursday, April 12, 2012 11:18 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 6:57 AM
Points: 2,561, Visits: 1,141
Good article for Prod DBA's trying to sort out slow running queries created by junior developers.

qh


SQL 2K acts like a spoilt child - you need to coax it round with lollipops.
Post #1282613
Posted Thursday, May 7, 2015 12:12 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 10:15 AM
Points: 609, Visits: 1,475
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.
Post #1683636
Posted Thursday, May 7, 2015 2:25 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, May 22, 2015 2:00 AM
Points: 571, Visits: 1,224
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.
Post #1683718
Posted Friday, May 8, 2015 7:39 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:59 AM
Points: 2,235, Visits: 5,854
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 ) 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 ).
Post #1683924
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse