Don't know how build a fast query

I've already asked a similar question yesterday. (Question)

But after customizing the query - the performance is really poor.

Situation

Table Position (Count: 800'000)

Id    Number Leafnode From To   VersionId
-- --------- -------- ---- ---- ---------
 1 100200300        0 NULL NULL        33
 2 100200301        1 NULL NULL        34
 3 100200302        1    5   10        34
 ...

Table Variable (Count: 1'300'000)

Id Number PositionId
-- ------ ----------
 1     01          2
 2     01          3
 3     02          3
 4     03          3
 ....

Table VariableText (Count: 1'300'000)

Id Language       Text VariableId
-- -------- ---------- ----------
 1        1      Hello          1
 2        2      Hallo          1
 3        3      Salut          1
 4        1        Bye          2
 5        2     Tschau          2
 ...

---

I'm looking for a good performing query (View, Stored Procedures, User Function). From my application I'd like to use a query like:

SELECT Id, Number, Text, Variable
FROM <whatever>
WHERE Language = 2 AND Version = 34 AND Nu开发者_开发百科mber IN (100200301, 100200305)

And the result shoul be:

Id     Number   Text Variable
--  --------- ------ --------
 2  100200301  Hallo      01
 3  100200305 Tschau      01
...

---

UPDATE I've uploaded the database backup which contains these three tables described above. (Backup)

I used the following query to get just the 'number' and 'id' back from the position table. No joins and no other things. And this query takes about 8 minutes.

WITH C AS 
(
    SELECT T.Id, CAST(Number AS int) AS Nr, Version
    FROM Position AS T
    WHERE Leafnode = 1

    UNION ALL

    SELECT T.Id, Nr + 1 AS Expr1, T.[Version]
    FROM dbo.Position AS T 
    INNER JOIN C ON C.Id = T.Id AND T.[To] > CAST(STUFF(Nr, 1, 6, '') AS int)
)

SELECT Id, Nr, [Version]
FROM C
WHERE Version = 34 AND Nr = '241521123'
OPTION (maxrecursion 0)

---

Query performance can be improved by creating the correct indexes (clustered / nonclustered / composite / ... ) on the right columns.

You can use the query plans to determine on which column(s) you should create indexes.

Indexes on columns that you search on, sort on and join on, can be candidates for indexes.

---

You might want to add some indexes on columns Language, Version and Number

---

You can enable performance statistics for a query by using SET STATISTICS PROFILE ON. This will show each part of the query and how long it takes which should give you an idea of whats slowing it down.

I suspect its the IN operator you are using. can you change to a WHERE (Number=100200301 OR Number=100200305)

http://wraithnath.blogspot.com/2011/01/getting-performance-data-from-sql.html

---

you should check the order of table using by optmizer (from query plan). I think it should like position table drive the query(it should be outer table) as its filtring most of data. if other table driving the query then force plan and make position table as a outer table. also check proper indexe used by query or not and update statistics of table if it is not updated from long time.

---

This is likely causing a problem

INNER JOIN C ON C.Id = T.Id AND T.[To] > CAST(STUFF(Nr, 1, 6, '') AS int)

Removing the first 6 digits from a number is not SARGable and is likely causing some problems.

There are several ways to deal with this but probably the most expedient would be to create an index on a computed column where the computed column is STUFF(Number, 1, 6, '')

This requires SQL Server 2005 or later (note tsql != sql-server)