Tuning Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Tuning Query

Author

Topic

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2014-05-15 : 18:13:04

Hello,

I am just brainstorming out an idea on how to make this query run more efficiently. All of the data is being pulled from the same table from different columns and has to be separated into separate rows.

So far I have read about unpivot and cross applies but have not seen good examples that have the same table with different where conditions... most of them are the same few columns or pulling multiple items into the same row.

Here is the reference that I am digging into now but any more specific recommendations are highly appreciated.

http://dba.stackexchange.com/questions/21724/is-there-any-way-to-speed-up-a-query-with-3x-union-on-the-same-large-table


--sample data
INSERT INTO database.dbo.table (pkey, T_FACE, TC_FACE, T_NECK, TC_NECK,t_larm, TC_LARM)
VALUES ('AAAA69AAA000001', '1', 'TEST FACE', '1', 'TEST NECK', '0', null)
	,('AAAA69AAA000002', '1', 'TEST FACE 2', '1', 'TEST NECK 2', '1', 'test larm')
GO

--start of query to pull rows back that have tattoos 
SELECT
[pkey],
[Type] = 'Tattoos',
[Name] = 'Face',
[T_FACE] AS Present,
[TC_FACE] As [Description]
FROM [database].[dbo].[table] WITH (NOLOCK)
WHERE  [T_FACE] = 1

UNION ALL

SELECT
[pkey],
[Type] = 'Tattoos',
[Name] = 'Neck',
[T_NECK] AS Present,
[TC_NECK] As [Description]
FROM [database].[dbo].[table] WITH (NOLOCK)
WHERE [T_NECK] = 1

UNION ALL

SELECT
[pkey],
[Type] = 'Tattoos',
[Name] = 'Left Arm',
[T_LARM] AS Present,
[TC_LARM] As [Description]
FROM [database].[dbo].[table] WITH (NOLOCK)
WHERE  [T_LARM] = 1

UNION ALL

SELECT
[pkey],
[Type] = 'Tattoos',
[Name] = 'Right Arm',
[T_RARM] AS Present,
[TC_RARM] As [Description]
FROM [database].[dbo].[table] WITH (NOLOCK)
WHERE [T_RARM] = 1

UNION ALL

SELECT
[pkey],
[Type] = 'Tattoos',
[Name] = 'Chest',
[T_CHEST] AS Present,
[TC_CHEST] As [Description]
FROM [database].[dbo].[table] WITH (NOLOCK)
WHERE  [T_CHEST] = 1

UNION ALL

SELECT
[pkey],
[Type] = 'Tattoos',
[Name] = 'Back',
[T_BACK] AS Present,
[TC_BACK] As [Description]
FROM [database].[dbo].[table] WITH (NOLOCK)
WHERE [T_BACK] = 1

UNION ALL

SELECT
[pkey],
[Type] = 'Tattoos',
[Name] = 'Left Leg',
[T_LLEG] AS Present,
[TC_LLEG] As [Description]
FROM [database].[dbo].[table] WITH (NOLOCK)
WHERE  [T_LLEG] = 1

UNION ALL

SELECT
[pkey],
[Type] = 'Tattoos',
[Name] = 'Right Leg',
[T_RLEG] AS Present,
[TC_RLEG] As [Description]
FROM [database].[dbo].[table] WITH (NOLOCK)
WHERE [T_RLEG] = 1

UNION ALL

SELECT
[pkey],
[Type] = 'Tattoos',
[Name] = 'Right Hand',
[T_RHAND] AS Present,
[TC_RHAND] As [Description]
FROM [database].[dbo].[table] WITH (NOLOCK)
WHERE [T_RHAND] = 1

UNION ALL

SELECT
[pkey],
[Type] = 'Tattoos',
[Name] = 'Left Hand',
[T_LHAND] AS Present,
[TC_LHAND] As [Description]
FROM [database].[dbo].[table] WITH (NOLOCK)
WHERE [T_LHAND] = 1

UNION ALL

SELECT
[pkey],
[Type] = 'Tattoos',
[Name] = 'Buttocks',
[T_BUTTOCKS] AS Present,
[TC_BUTTOCKS] As [Description]
FROM [database].[dbo].[table] WITH (NOLOCK)
WHERE [T_BUTTOCKS] = 1

UNION ALL

SELECT
[pkey],
[Type] = 'Tattoos',
[Name] = 'Stomach',
[T_STOMACH] AS Present,
[TC_STOMACH] As [Description]
FROM [database].[dbo].[table] WITH (NOLOCK)
WHERE [T_STOMACH] = 1
go

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-05-15 : 18:57:53

[code]

SELECT
[pkey],
'Tattoos' AS [Type],
[Name],
[Present],
[Description]
FROM dbo.table1
CROSS APPLY (
SELECT 'Face' AS Name, T_FACE AS Present, TC_FACE AS Description, 1 AS sequence UNION ALL
SELECT 'Neck', T_NECK, TC_NECK, 2 UNION ALL
SELECT 'Left Arm', T_LARM, TC_LARM, 3
/*
UNION ALL
SELECT T_RARM, TC_RARM UNION ALL
SELECT T_CHEST, TC_CHEST UNION ALL
SELECT T_BACK, TC_BACK UNION ALL
SELECT T_RLEG, TC_RLEG UNION ALL
SELECT T_LLEG, TC_LLEG UNION ALL
SELECT T_RHAND, TC_RHAND UNION ALL
SELECT T_LHAND, TC_LHAND UNION ALL
SELECT T_BUTTOCKS, TC_BUTTOCKS UNION ALL
SELECT T_STOMACH, TC_STOMACH
*/
) AS ca1
WHERE
Present = '1'
ORDER BY
pkey, sequence

[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources