January 16, 2011 at 7:05 pm
Hi !
I have a query like this (see below).
This query most of the time alwasy time out if there are many users tries to execute this query.
Is there a way to make this query faster ?
Can I use union all instead of OR?
I did add indexes to improve performance.
If i add index on column name , will the performance will be better?
I'm not sure replace function will cancel the index feature on column name.
Thanks
SELECT count(*)
FROM tableA
WHERE
( REPLACE(name, ' ', '') like @name OR
EXISTS(SELECT 1 FROM tableB
WHERE tableB.orderid = tableA.id AND (ISNULL(tableB .lastname, '') + ISNULL(tableB .firstname, '')) like @fullname1
) OR
EXISTS(SELECT 1 FROM tableC
WHERE tableC.orderid = tableA.id AND (ISNULL(tableC.lastname, '') + ISNULL(tableC.firstname, '')) like @fullname2
) OR
EXISTS(SELECT 1 FROM tableD
WHERE tableD.orderid = tableA.id AND (ISNULL(tableD.lastname, '') + ISNULL(tableD.firstname, '')) like @fullname3
)
)
January 17, 2011 at 2:39 am
Without any further information (e.g. actual execution plan) I'd expect table scans due to the non-SARGable columns in your WHERE clause.
Furthermore, the part "like @fullname1" might not work as you expect, depending on the value of @fullname1.
Missing indexes might be another issue.
Please attach the actual execution plan as sqlplan file. Maybe that'S enough to give you a few hints. If not, we'd need table defs including index definitions.
January 19, 2011 at 5:18 am
I agree with LutzM but lets try breaking this down a little:
Since many may not understand "non-SARGable" lets just put it this way:
If you put a column in a function then an index will do you no good.
REPLACE(name, ' ', '') like @name
You don't make clear if @name will have wildcards, but from a performance point of view it would make more sense to put the space in @name rather than remove it from the name column
name like @name
If that is imposible then you could:
add a calculated column to tableA: name_no_space as REPLACE(name, ' ', '')
index that column
use name_no_space like @name in the where clause
Keep in mind that if @name begins with a wildcard then you will get a table scan every time.
Same logic applies to using the isnull() function in a where clause
Does this help you at all?
January 19, 2011 at 8:49 am
Without diving into the details of your case I will answer one of the questions you had: "Can I use union all instead of OR".
Absolutely. Recently I helped a friend who had similar problem and instead of messing with crazy logic in the WHERE clause (with extremaly poor performance) I suggested using UNION and it worked like a charm.
Using UNION is his case helped because the criteria in the separate SELECTs used different indexes and trying to pull it off in a single query killed the index usage.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply