CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Ghost Master
Ghost Master

Reputation: 442

Optimize select query for non-indexed view

I want to select rows from non-indexed view by their IDs. Here is view definition:

CREATE VIEW [dbo].[_V_V3] 
AS 
SELECT (CONVERT(varchar,T1.PK_ID)+','+CONVERT(varchar,T2.PK_ID)) as ID,
T1.[Id] as [T1_Id],
T1.[V1] as [T1_V1],
T2.[Id] as [T2_Id],
T2.[V1] as [T2_V1] 
FROM [T1]  INNER JOIN [T2] ON (T2.V1=T1.V1)

where T2.V1, T1.V1 - nvarchar.

My select query:

SELECT * FROM [dbo].[_V_V3] WHERE ID IN ('1,1', '2,3', ....)

It works very slowly. With 1000 IDs this query could perform several minutes.

Is there any way to optimize this select?

Upvotes: 0

Views: 146

Answers (1)

Arion
Arion

Reputation: 31239

Can you just do this:

View

CREATE VIEW [dbo].[_V_V3] 
AS
T1.PK_ID as PK_ID1,
T2.PK_ID as PK_ID2,
T1.[Id] as [T1_Id],
T1.[V1] as [T1_V1],
T2.[Id] as [T2_Id],
T2.[V1] as [T2_V1] 
FROM [T1]  INNER JOIN [T2] ON (T2.V1=T1.V1)

Query

SELECT * FROM [dbo].[_V_V3] 
WHERE
    ( 
        PK_ID1=1 AND PK_ID2=1
    )
    OR
    (
        PK_ID1=2 AND PK_ID2=3
    )

I would think that an integer compare is fast then varchar compare

Upvotes: 1

Related Questions