?
Solved

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

Posted on 2014-10-08
4
Medium Priority
?
201 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 14

Accepted Solution

by:
Russell Fox earned 1336 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 14

Assisted Solution

by:Russell Fox
Russell Fox earned 1336 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 49

Assisted Solution

by:PortletPaul
PortletPaul earned 664 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

593 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