November 26, 2007 at 1:24 am
I have a query with a calculated field. The field uses IsNull function because the view join two tables and the result for that field need to be from the second table if there is no record from first table.
CREATE VIEW v1
AS
SELECT IsNull(a.Name, b.Name) Name
FROM a
LEFT OUTER JOIN b
ON a.ID = b.ID
This is a saved query.
When I'm using that view I want to filter the calculated column.
Select * from v1
Where Name = 'ABC'
This query take a long time to execute.
If I remove the where cond the view open very fast.
How can I improve my query.
I have to save the query and then filter it when calling it.
My server can be 2000/2005
Thanks,
Sharon
November 26, 2007 at 2:13 am
use Case Statement in View.
November 26, 2007 at 2:18 am
Hi,
I tried also the CASE statement.
The same.
Sharon
November 26, 2007 at 5:30 am
You need to re-think your approach. Because the column is calculated (using a function, case, concatenation, etc.) you will always end up with a table scan of some sort when filtering on the column.
Since a scalar function can change the value of something passed into it, it is impossible for the server to know if an index is valid for that column. So, it must resolve the function for every row in the table to be able to compare your criteria rather than making determinations based on whether the value is greater than or less than your criteria.
Something like this would have a better chance of using an index (depending on the data in the tables):
CREATE VIEW v2
AS
SELECT
IsNull(a.Name, b.Name) Name
, a.Name AS AName
, b.Name AS BName
FROM
a
LEFT OUTER JOIN b ON a.ID = b.ID
GO
SELECT
*
FROM
v2
WHERE
AName = 'ABC'
OR (AName IS NULL AND BName = 'ABC')
Try something like this and comparing the execution plans.
November 26, 2007 at 6:01 am
I understand.
If I have SQL 2005, is it possible to have an index on that view?
Will it be faster?
November 26, 2007 at 6:04 am
You will not be able to index that view.
You really do have to either spend the time to change the logic, or create a table called v1 and re-populate it on a regular basis.
November 26, 2007 at 6:12 am
Thank you for your reply.
It was very helpful.
Sharon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply