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 ««1234»»»

Tips to optimize your SQL statements Expand / Collapse
Author
Message
Posted Friday, July 30, 2010 7:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 21, 2014 6:44 AM
Points: 184, Visits: 216
Very good information, thanks for sharing.
Don
Post #961367
Posted Friday, July 30, 2010 7:42 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 865, Visits: 2,381
philcart (7/29/2010)

Would have thought that this is part of an investigation/monitoring phase. Once you've identified a query that needs attention, you run though the great list provided by Brian.

It's amazing how often you don't need to worry about re-writing SQL when all that's needed is a new index, or updated statistics. If either of these two meet your needs, why go through the hassle of re-writing the query/procedure?



Updated statistics, certainly; that's a normal operational cost.

However, adding an index adds to the operational costs of writes to that table for the lifetime of the index; perhaps a simple, or even not so simple, rewrite would allow the query to improve performance without adding any additional costs?

Perhaps more importantly, how will you learn what types of query implementations work better in which circumstances on your particular environment without testing them? This is critically important in some cases, particularly for writing new SQL in an environment that's heavily loaded for whatever reason; if there's little headroom, then using as little as possible of what's left is vital, in addition to freeing up more headroom.
Post #961371
Posted Friday, July 30, 2010 11:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 12:02 PM
Points: 42, Visits: 152

It's amazing how often you don't need to worry about re-writing SQL when all that's needed is a new index, or updated statistics. If either of these two meet your needs, why go through the hassle of re-writing the query/procedure?



Indexes add a marginal performance hit as each row insert/update has to then go and update all the affected indexes. They are an essential tool, but shouldn't be used in place of sane queries.
Post #961548
Posted Friday, July 30, 2010 1:44 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:41 PM
Points: 2,693, Visits: 1,203
@wbrianwhite & @Nadrek

The cost of adding and updating the indexes is actually a lot smaller than it's made out to be. Only under extreme change (meaning insert/update/delete) activity will you notice performance degradation from index updates. I think it would be safe to say that a vast majority of SQL Server databases don't fit into the "extreme" category.

For most businesses, the cost of adding the index is much less than the cost of re-developing a query/procedure. Especially when it's vendor supplied code and they have to pay for code changes ;)

Yes, in some cases a re-write is warranted, but why not give the end users some benefit straight away and put any re-writes on the work queue?


Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
Post #961613
Posted Friday, July 30, 2010 2:23 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 865, Visits: 2,381
philcart (7/30/2010)
@wbrianwhite & @Nadrek

The cost of adding and updating the indexes is actually a lot smaller than it's made out to be. Only under extreme change (meaning insert/update/delete) activity will you notice performance degradation from index updates. I think it would be safe to say that a vast majority of SQL Server databases don't fit into the "extreme" category.

For most businesses, the cost of adding the index is much less than the cost of re-developing a query/procedure. Especially when it's vendor supplied code and they have to pay for code changes ;)

Yes, in some cases a re-write is warranted, but why not give the end users some benefit straight away and put any re-writes on the work queue?


The operational cost includes disk space to house the index, disk space and time to back it up, time to correctly figure out the right fillfactor, and maintenance window time to defragment the index regularly (however often or rare that happens to be - the default fillfactor of 100 tends to be pretty bad about requiring maintenance).

When writing new SQL, why not take a little more time and do it well, instead of randomly? For existing bad SQL, most often I see that a few minutes rewriting is extremely beneficial, and has no bad side effects.

In one nontrivial case, 45 minutes of rewriting resulted in four orders of magnitude of performance improvement. In many cases, 5 to 10 minutes of rewriting results in anywhere between double and an order of magnitude more performance.

Essentially: Why add cost in one place to reduce cost in another before you attempt to reduce cost without adding any?
Post #961625
Posted Friday, July 30, 2010 6:20 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:41 PM
Points: 2,693, Visits: 1,203
Nadrek (7/30/2010)

...
When writing new SQL, why not take a little more time and do it well, instead of randomly? For existing bad SQL, most often I see that a few minutes rewriting is extremely beneficial, and has no bad side effects.
...


That may be fine if you're the one in control of the code being written. However, if you're like the majority of DBA's in the workplace, quite often that is not the case. You'll have to deal with development team and/or external vendors. Maybe even get managers approval for changing work priorities and any additional charges.

Case in point, a vendor application we have running had a report that took 10-15 minutes to run. The end-user spoke directly to the vendor who quoted 4 hours of work to fix the report. This work was done and implemented and the report took just a few minutes. In the next invoicing cycle the extra 4 hour charge was rejected as it hadn't been approved. Naturally the vendor wasn't pleased about this and they reversed their change and left the user with the slow report.

At this stage the user decided to speak with me and within 5 minutes I had identified a lack of indexes (there were none on the reporting tables). After creating some indexes the same report took 2-3 minutes to run. Later that week, when I had some free time, I decrypted the vendors procedure, pulled apart the mess and got the report to run in less than 30 secs. Naturally being vendor code I couldn't deploy the changes I'd made, but given I have a good relationship with them, I sent the changes through their developer.

Three months later when we performed an upgrade on the application, the report that was running in 2-3 minutes started to run in less than 30 sec.


Nadrek (7/30/2010)

...
Essentially: Why add cost in one place to reduce cost in another before you attempt to reduce cost without adding any?


Easy answer, add the minimal (yes minimal, despite your assertions) cost in the short term (ie: getting the end-user a report they require), then see about change approval, testing, etc... of re-written code.




Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
Post #961669
Posted Friday, July 30, 2010 10:36 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:10 PM
Points: 21,642, Visits: 15,311
Thanks for the article.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #961693
Posted Saturday, July 31, 2010 6:44 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 12:02 PM
Points: 42, Visits: 152
http://blogs.msdn.com/b/sqlazure/archive/2010/07/27/10043069.aspx

Just a note, non-clustered indexes (what you make when you do a covered index) reduce the performance of your writes. This is because on insertion or updates, the indexes need to be updated. So you need to balance your covered index creation with the ratio of reads and writes to your database. Databases with a disproportion amount of reads to writes gain more performance from covered indexes.


Indexes DO affect your write performance. It is not theoretical. You have to seek a balance between reducing I/O by adding indexes and reducing insert efficiency by adding indexes. If you add a new covering index for every bad query it will have significant effects. If you can improve the query without changing the indexes, it is a win/win. In my shop the DBAs monitor poor-performing queries and make recommendations to the devs for re-writes and/or indexes. Some common problems in queries that can be fixed in the code: non-SARGable clauses, writing a where clause that tests where a or b instead of writing two selects with simple where clauses that are unioned together, cursor usage, not specifying a known column in the where clause when that would let you take advantage of a clustered index.
Post #961830
Posted Tuesday, August 3, 2010 1:44 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 17, 2014 8:17 AM
Points: 859, Visits: 516
Thanks for the info
Post #962629
Posted Tuesday, August 3, 2010 2:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:41 PM
Points: 2,693, Visits: 1,203
wbrianwhite (7/31/2010)
http://blogs.msdn.com/b/sqlazure/archive/2010/07/27/10043069.aspx

Just a note, non-clustered indexes (what you make when you do a covered index) reduce the performance of your writes. This is because on insertion or updates, the indexes need to be updated. So you need to balance your covered index creation with the ratio of reads and writes to your database. Databases with a disproportion amount of reads to writes gain more performance from covered indexes.


Indexes DO affect your write performance. It is not theoretical. You have to seek a balance between reducing I/O by adding indexes and reducing insert efficiency by adding indexes. If you add a new covering index for every bad query it will have significant effects. If you can improve the query without changing the indexes, it is a win/win. In my shop the DBAs monitor poor-performing queries and make recommendations to the devs for re-writes and/or indexes. Some common problems in queries that can be fixed in the code: non-SARGable clauses, writing a where clause that tests where a or b instead of writing two selects with simple where clauses that are unioned together, cursor usage, not specifying a known column in the where clause when that would let you take advantage of a clustered index.


As previously stated, the cost of adding and updating the indexes is actually a lot smaller than it's made out to be. This is the same sort of thing spouted by people that say you should always rebuild the indexes when generally updating statistics is all thats required.

More often than not, the problem with a poorly performing query are the cases you've specified, but what if you're dealing with a vendor application where code changes could take months, if they ever happen at all?

Better to wear the impact of applying an index rather than leaving the query to perform poorly.



Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
Post #962637
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse