making t-sql 2012 execute faster

Question

making t-sql 2012 execute faster

wendy elizabeth

SSCoach

Points: 16915
More actions
November 13, 2015 at 9:10 am

#313125

I have been working with t-sql 2012 for awhile. I have noticed that some questions run faster than other queries. I know that this is based upon the way the queries are executed. However I am wondering if certain types of t-sql statements execute faster than others? If so, can you tell me and/or point me to a reference that will show me which t-sql statements run slower and what are the replacement t-sql statements that will make the query execute faster?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

It's not really that simple.

Most of the time, speed comes down to how many rows the query is processing, whether the query can use indexes properly and whether there are useful indexes to support the query.

So make sure that you're not returning data that isn't needed, that you're not writing queries so that they can't use indexes, and make sure that useful indexes exist to support the queries. And stay away from scalar user-defined functions used in queries.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

wendy elizabeth SSCoach Points: 16915 More actions · Answer 2

what are scalar user-defined functions used in queries?

Y.B. SSChampion Points: 11649 More actions · Answer 3

wendy elizabeth (11/13/2015)
what are scalar user-defined functions used in queries?

https://msdn.microsoft.com/en-CA/library/ms191320.aspx

Google is your friend. 😉

SELECT quote FROM brain WHERE original = 1
0 rows returned

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

When functions are necessary, use inline table-valued functions if at all possible. Even if you only have to return a single column (like a scalar function) write it as an ITVF instead of a scalar function.

I remember seeing a reference to an inline scalar function somewhere on the Microsoft site once, but i don't remember where. As far as I know, there's no ISF object type in any version of SQL Server, but someone at Microsoft must have thought of it somewhere along the line.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 5

wendy elizabeth (11/13/2015)
I have been working with t-sql 2012 for awhile. I have noticed that some questions run faster than other queries. I know that this is based upon the way the queries are executed. However I am wondering if certain types of t-sql statements execute faster than others? If so, can you tell me and/or point me to a reference that will show me which t-sql statements run slower and what are the replacement t-sql statements that will make the query execute faster?

There are certain types of queries that generally run more slowly than other types. The faster queries are usually based on what is known as "set-based" logic and the slower stuff is normal caused by RBAR (Row By Agonizing Row). RBAR generally includes While Loops, Recursive CTEs that count, Triangular Joins, non-SARGable predicates, unintended many-to-many joins, and the use of Scalar and Mult-statement Inline Table Values Functions.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 6

Any feedback, Wendy?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)