November 7, 2006 at 9:07 pm
Hi all,
I created a query to select data from a table with a where condition using "OR" operator like this below, whereas I want to search rows which some fields contains search key defined. Im using a few tables(using inner join), not only one table.
declare @strSearch varchar(50)
set @strSearch ='%my%'
select * from table where table.fieldA like @strSearch or table.fieldB like @strSearch or table.fieldC like @strSearch or table.fieldD like @strSearch or table.fieldE like @strSearch.
But the problem is the query is very slow, if the data in all tables very large. So, I tried to tuning the T-SQL using union all, like this one :
declare @strSearch varchar(50), @strM nvarchar(4000),@parm nvarchar(100),@strY nvarchar(4000)
set @strSearch ='%my%'
set @strM = N'select * from table '
set @parm = N'@strSearchX varchar(50)'
set @strY = @strM + ' where table.fieldA like @strSearchX'
set @strY = @strY + ' union all '
set @strY = @strY + @strM + ' where table.fieldB like @strSearchX'
set @strY = @strY + ' union all '
set @strY = @strY + @strM + ' where table.fieldC like @strSearchX'
set @strY = @strY + ' union all '
set @strY = @strY + @strM + ' where table.fieldD like @strSearchX'
set @strY = @strY + ' union all '
set @strY = @strY + @strM + ' where table.fieldE like @strSearchX'
execute sp_executesql @strM, @parm, @strSearchX = @strSearch
After I run this query, it run much faster than the first one which using "OR" operator. So, the question is, how come it is faster using "union all" rather than "OR" ? Im using join a few tables(5-10 tables) whereas the data is is large...
Thanks in advance
cheers,
alexia
November 7, 2006 at 10:27 pm
Using UNION ALL can potentially lead to duplicates, if both fieldA and fieldB contains the text "my".
Replace UNION ALL with UNION only to get correct result. But then the query will also run slower.
N 56°04'39.16"
E 12°55'05.25"
November 7, 2006 at 11:29 pm
thanks for ur response....
so, union is better than "where condition using OR operator" ?
November 8, 2006 at 12:35 am
It's better to have proper database design.
If you don't know in which column to search for a value then you don't have a database, just some mess without any order.
It will be always slow. No matter what tricks you'll try.
_____________
Code for TallyGenerator
November 9, 2006 at 7:59 am
You'd probably need to look at the execution plans of the two different types of query to be sure. I believe using 'OR' can prevent SQL Server using indexes in some instances, which using UNION would perhaps get around, but since duplicates have to be removed when doing UNION it will have to do extra operations probably including a sort to do that. The size of your result set could have a big impact on which one works best, among other things. Try them both, check the execution plans and see which looks best. Like most things with databases, there may not be one universal 'best' answer I'm afraid.
November 9, 2006 at 8:13 am
Something else which occurred to me... if you know all the tables and fields in advance so you do not have to build the query dynamically, putting it into a stored procedure and avoiding the use of sp_executesql will probably help performance somewhat. Also, if you're searching for "like '%anything%'" then you'll be doing a full table scan on each table in your query. The first % wildcard stops SQL Server using any indexes at all, it has to read every value of every row. As your database grows, this just gets slower and slower. If you can add any more criteria to your WHERE clause to cut down the set of data it has to search through, that should speed things up and allow it to scale better.
Have you considered using Full-Text Search instead?
November 9, 2006 at 8:19 am
I heard a a recent workshop on performance tuning that the query optimiser in SQL 2000 has only one option for optimising an OR, but it has more than one way to run a union, so often union is faster, sometimes massivly. This only applies to SQL 2000, in 2005 the optimiser has multiple options for both.
The UNION and the OR may, potentially have different outputs, as OR will only retunr a single record, even if it matches more than one condition. union however retrieves all and then removes duplicates based on the columns in the output result set. Test carefully
eg if in a table both rows with an id or 2 and 10 have a name of 'Joe'
SELECT name FROM egTable WHERE id = 2 or id=10
will return 2 rows, however
SELECT name FROM egTable WHERE id = 2
UNION
SELECT name FROM egTable WHERE id=10
will return only one record.
There are times when you do need to use multiple ORs in a query, but it does appear from your query that your problem may be related to poor design.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 9, 2006 at 1:43 pm
OK, MCDBA suppose to know the difference between UNION and UNION ALL.
UNION inclused implicit DISTINCT keyword.
So, your queries are just not equal.
_____________
Code for TallyGenerator
November 9, 2006 at 6:49 pm
thanks guys for all your response...
In some case, the requirements need to view data like that, I mean to view data where contains a key word.
Such as, I want to query location promotion data from tables where the location is located in city or branch or "someone's marketing operation" contains the word '%vegas%'.
And the query should return all location which is located in Vegas, under someone's operation marketing in Vegas and in office branch Vegas.
Somehow when the rows are large, it is becoming very slow to query...
Well, I think I have to redesign some tables...
and to change the ways to search, because it is poor searching when using wildcard "%" in front of the search keyword, which stops the SQL Server using indexes.
November 23, 2006 at 5:13 am
I think you missed the point I was trying to make. I didn't mean union all, and I know the two queries aren't equal. I said as much
When you write a query with or, sql returns 1 row for each distinct row of the table.
If you break the query up and use a union instead (not union all, which is a very different query) then only the output columns are considered for the purposes of distincting the results.
So, a slightly better example
CREATE TABLE TestTable (
id int,
name varchar(10),
status int
)
GO
Insert into TestTable
values (1,'Tom',0)
Insert into TestTable
values (2,'Joe',0)
Insert into TestTable
values (3,'Dave',5)
Insert into TestTable
values (4,'Joe',1)
SELECT id, name from testtable where ID=2 or status=1
will return two rows, ie
2, Joe
4, Joe
SELECT id, name from testtable where ID=2
UNION
SELECT id, name from testtable where status=1
Will also return two rows, ie
2, Joe
4, Joe
However, if id is removed from the select list, the OR will still return 2 rows (it checks for duplicates based on the entire row) however the union (which checks for duplicates only at the level of the final resultset) will only return 1 row.
It is possible to replace OR with unions and the results are usually equivalent, but you do have to be careful that 'duplicate' rows don't get excluded.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply