Solved

10774: URL (7 performance tips for faster SQL queries)

Posted on 2014-10-08
4
150 Views
Last Modified: 2014-10-12
hi experts:

i am reading
http://www.infoworld.com/article/2628420/database/7-performance-tips-for-faster-sql-queries.html

please,i need T-SQL for
1. Don't use UPDATE instead of CASE
This issue is very common, and though it's not hard to spot, many developers often overlook it because using UPDATE has a natural flow that seems logical.

and
4. Don't double-dip
Here's another one I've seen more times than I should have: A stored procedure is written to pull data from a table with hundreds of millions of rows
0
Comment
Question by:enrique_aeo
  • 2
4 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40368928
1. Case when town = 'New York' then ''East' else 'West' end

2. instead of select* from table use select column1,column2,column3 from table
0
 
LVL 13

Accepted Solution

by:
Russell Fox earned 334 total points
ID: 40368932
For the first one, it's saying that it may be better to change a value in your query rather than in the table; for example, say there was an error where 100 years is added to some people's ages in a temporary table. You could UPDATE to remove 100 years, or you could use a CASE statement in your query:
DECLARE @myTable TABLE (FirstName VARCHAR(25), Age INT)

INSERT INTO @myTable
		( FirstName, Age )
VALUES	( 'Bob', 25 ),
	( 'Frank', 20 ),
	( 'Ralph', 120 ),
	( 'Abe', 162 )

SELECT FirstName, 
	CASE WHEN Age > 100 THEN Age - 100 ELSE Age END AS CurrentAge
FROM @myTable

Open in new window

0
 
LVL 13

Assisted Solution

by:Russell Fox
Russell Fox earned 334 total points
ID: 40368949
For the second one, the following queries return the same results, but the first query does it in a single SELECT statement:
DECLARE @myTable TABLE (FirstName VARCHAR(25), Age INT)

INSERT INTO @myTable
		( FirstName, Age )
VALUES	( 'Bob', 25 ),
	( 'Frank', 20 ),
	( 'Ralph', 120 ),
	( 'Abe', 162 ),
	( 'Benjamin', 59)

SELECT FirstName, 
	CASE WHEN Age > 100 THEN Age - 100 ELSE Age END AS CurrentAge
FROM @myTable
WHERE FirstName LIKE 'B%'
	AND Age < 100

SELECT FirstName, 
	CASE WHEN Age > 100 THEN Age - 100 ELSE Age END AS CurrentAge
FROM @myTable
WHERE FirstName LIKE 'B%'
AND FirstName IN(
	SELECT FirstName
	FROM @myTable
	WHERE Age < 100
	)

Open in new window

0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 166 total points
ID: 40369688

1. Use CASE expression (not an UPDATE)


The author is referring to a temp table.

He suggests that UPDATE to the temp table is not good technique, it is simpler and more efficient to just use CASE expression.

select case when sales>100000 then 'Preferred' else 'Standard' end from customers

2. Don't Double-Dip


Again the author is referring to use of temp tables.

What he is saying this is silly:

select * INTO #California from customers where state = 'California'
select * INTO #FortyK from customers where income >=40000

select *
from #California C
inner join #FortyK F on C.id = F.id

Instead:

select * from customers where  state = 'California' and income >=40000
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question