Better option than UNION - 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

General SQL Server Forums

New to SQL Server Programming

Better option than UNION

Author

Topic

asbharadwaj
Starting Member

27 Posts

Posted - 2006-08-29 : 01:49:05

Hi there,

I am selecting few records from a particular table and however I need to run the same query using UNION.But its taking long to execute the query.If I run the query as 2 parts then it works fine but if I put in UNION to both queries then I have problem.Can anyone help?

eg:

Select '' as name,'' as userid,'' as firstname from table1 where user like 'A%'(works fine)

Select name,userid,firstname from table1 where user like 'A%'(also works fine)

but if I user

Select '' as name,'' as userid,'' as firstname from table1 where user like 'A%'
UNION
Select name,userid,firstname from table1 where user like 'A%'

its taking too long

Somebody please help me

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-29 : 01:52:46

"Select '' as name,'' as userid,'' as firstname from table1 where user like 'A%'"
What is this line suppose to do ? it is returning empty string for every match record of user like 'A%' ?

Also try UNION ALL instead of UNION

asbharadwaj
Starting Member

27 Posts

Posted - 2006-08-29 : 01:57:02

quote:
Originally posted by khtan

"Select '' as name,'' as userid,'' as firstname from table1 where user like 'A%'"
What is this line suppose to do ? it is returning empty string for every match record of user like 'A%' ?

Also try UNION ALL instead of UNION

KH

Yes.I just need few empty strings before I get the exact values in the second statement.

I tried UNION ALL as well...it doesnt work

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-29 : 02:15:32

tried this ? Since you are using UNION, the first query will only return one line. So the from table1 etc is not required

Select '' as name,'' as userid,'' as firstname
UNION
Select name,userid,firstname from table1 where user like 'A%'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-29 : 02:18:59

by the way.. is there index created for column user ?

asbharadwaj
Starting Member

27 Posts

Posted - 2006-08-29 : 02:22:26

quote:
Originally posted by khtan

tried this ? Since you are using UNION, the first query will only return one line. So the from table1 etc is not required
Select '' as name,'' as userid,'' as firstname
UNION
Select name,userid,firstname from table1 where user like 'A%'

KH

Hi KH,

I tried that...but of no use.It dont work.Its still slow...any other suggestions????

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-29 : 02:38:15

Make sure the table is properly index and check the execution plan.

there should not be any major different in performance between this

Select name,userid,firstname from table1 where user like 'A%'

and

Select '' as name,'' as userid,'' as firstname
UNION
Select name,userid,firstname from table1 where user like 'A%'

How is the execution time like for both query ? How many records you have in the table ?

asbharadwaj
Starting Member

27 Posts

Posted - 2006-08-29 : 02:43:45

Hi KH,

I have around 800K records and the field I am trying is indexed.Actually the second query is using few tables.But that should not be a porblem even if I am using other tables.Right?

its almost 8 min since I ran the query and its still running