Tips to optimize your SQL statements

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Brian Ellul » Tips to optimize your SQL statements

37 posts, Page 4 of 4

«««1 2 3 4

Tips to optimize your SQL statements

Rate Topic

Display Mode

Topic Options

Author

Message

sherifffruitfly

Posted Friday, February 10, 2012 7:50 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 18, 2013 5:18 AM
Points: 32, Visits: 407

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

Jack Corbett

Posted Friday, February 10, 2012 8:02 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

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

sherifffruitfly

Posted Friday, February 10, 2012 8:05 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 18, 2013 5:18 AM
Points: 32, Visits: 407

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

tabinsc

Posted Friday, February 10, 2012 1:24 PM

Old Hand

Group: General Forum Members
Last Login: Today @ 8:35 AM
Points: 360, Visits: 398

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

mmartin1

Posted Friday, February 10, 2012 2:33 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 4:43 PM
Points: 192, Visits: 640

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

mtassin

Posted Friday, February 10, 2012 2:44 PM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 11:21 PM
Points: 3,226, Visits: 64,195

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

quackhandle1975

Posted Thursday, April 12, 2012 11:18 AM

SSCommitted

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:24 AM
Points: 1,610, Visits: 590

Good article for Prod DBA's trying to sort out slow running queries created by junior developers. Wink

qh

SQL 2K acts like a spoilt child - you need to coax it round with lollipops.

Post #1282613

« Prev Topic | Next Topic »

37 posts, Page 4 of 4

«««1 2 3 4

Permissions