Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Tips to optimize your SQL statements


Tips to optimize your SQL statements

Author
Message
sherifffruitfly
sherifffruitfly
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 427
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.
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11034 Visits: 14858
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
sherifffruitfly
sherifffruitfly
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 427
Oh welp there you go then! Smile

Now back to figuring out how to get the right passwords in my deployed ssis package's connection managers. #@$@#%@
tabinsc
tabinsc
Right there with Babe
Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)Right there with Babe (796 reputation)

Group: General Forum Members
Points: 796 Visits: 915
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?
MMartin1
MMartin1
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1120 Visits: 2015
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.

----------------------------------------------------
How to post forum questions to get the best help
mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4109 Visits: 72512
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
quackhandle1975
quackhandle1975
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2756 Visits: 1227
Good article for Prod DBA's trying to sort out slow running queries created by junior developers. ;-)

qh

Who looks outside, dreams; who looks inside, awakes. – Carl Jung.
Marcia J
Marcia J
SSC Eights!
SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)

Group: General Forum Members
Points: 889 Visits: 1889
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.
MMartin1
MMartin1
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1120 Visits: 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.


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.

----------------------------------------------------
How to post forum questions to get the best help
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4647 Visits: 9579
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).


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search