Query Performance - 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)

Query Performance

Author

Topic

faijurrahuman
Starting Member

15 Posts

Posted - 2012-03-13 : 00:35:02

Dear all;
Table having 1 million record we have to write the quries like this .

the number of union all to write and finally insert the new table. in case more then 500 union all sql stmt writing and execute . but it take an more time to perform this task .
any one help this case..

Example

SELECT @pubID,
(SELECT entity_item_id from #TMPTABLE WHERE acc_no = '608' AND entity_id = @progID),
(SELECT entity_item_id from #TMPTABLE WHERE acc_no = '11606' AND entity_id = @orgID),
GETDATE(),@userId,@relSupp,1,'Fuel cooled oil cooler'
UNION ALL
SELECT @pubID,
(SELECT entity_item_id from #TMPTABLE WHERE acc_no = '12569' AND entity_id = @orgID ),
(SELECT entity_item_id from #TMPTABLE WHERE acc_no = '608' AND entity_id = @progID ),
GETDATE(),@userId,@relSuppFor,1,'Placards; ID plated markings'
UNION ALL
SELECT @pubID,
(SELECT entity_item_id from #TMPTABLE WHERE acc_no = '608' AND entity_id = @progID),
(SELECT entity_item_id from #TMPTABLE WHERE acc_no = '12569' AND entity_id = @orgID),
GETDATE(),@userId,@relSupp,1,'Placards; ID plated markings'
....etc

Thanks

Share Knowledge team

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-13 : 00:55:41

you can change


SELECT @pubID,
(SELECT entity_item_id from #TMPTABLE WHERE acc_no = '608' AND entity_id = @progID),
(SELECT entity_item_id from #TMPTABLE WHERE acc_no = '11606' AND entity_id = @orgID),
GETDATE(),@userId,@relSupp,1,'Fuel cooled oil cooler'


select	@pubID,
	max(case when acc_no = '608' then entity_item_id end),
	max(case when acc_no = '11606' then entity_item_id end),
	GETDATE(), @userId, @relSupp, 1, 'Fuel cooled oil cooler'
from	#TMPTABLE
where	acc_no		in ('608', '11606')
and	entity_id	= @progID

does all your union query basically the same ? only the acc_no = 'xxx' part that varies ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources