|
|
Posted by Erland Sommarskog on 10/01/87 11:17
--CELKO-- (jcelko212@earthlink.net) writes:
> 1) One old and still good heuristic was not to write over 50 lines in a
> proc. T-SQL is a simple one-pass compiler and was not designed to be
> an application development language. Do not stress it.
Ehum, a single SQL statement can easily exceed 50 lines - particulary if
one is to go by the advice and use not temp tables.
For the record, the longest stored procedure we have is some 3000 lines
of code. This procedure had a predecessor, which was shorter for the
simple reason that it called plenty of subprocedures. Those subprocedures
are now incorporated in the big one. Why?
Because the original procedure accepted scalar input in terms of
variables. Variables are easy to pass around as parameters. The new
version instead reads its input from a table, and make an extensive
use of table variables - there's 43 of them. Tables are difficult to
pass as parameters.
> 4) Avoid materializing physical storage like the proprietary temp
> tables and table variables. Use derived tables and CTEs instead, so the
> optimizer can see everything.
Sometimes this is a good idea. Sometimes it's better to store
intermediate data in a temp table/table variable. This is particularly
true if you need to repeat the same derived table in the query. The
optimizer computes it for each occurrence. The same applies to CTEs
in SQL 2005. But it can also be good to use a temp table for intermediate
storage, since a temp table has statistics, and this can help the
optimizer.
Speaking of proprietary issues, here's another thing. Avoid the ANSI
way:
UPDATE tbl
SET col = (SELECT SUM(col2) FROM tbl2 WHERE tbl2.keycol1 = tbl.keycol
Instead use the proprietary MSSQL way:
UPDATE tbl
SET col = d.sum2
FROM tbl t
JOIN (SELECT keycol, sum2 = SUM(col2)
FROM tbl2
GROUP BY keycol) d ON t.keycol = d.keycol
My experience is that this gives better performance.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|