Don't know how build a fast query

Question

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 Number 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)

Look at the guesstimated and actual query plans -- it even gives hints on potential indexes! Easily viewed from within SQL Management Studio and includes cute graphs. — , Sep 13 '11 at 08:35

score 1 · Answer 1 · answered Sep 13 '11 at 08:34

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.

score 0 · Answer 2 · answered Sep 13 '11 at 08:35

0

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

answered Sep 13 '11 at 08:35

Mark 909

1,825
1
18
27

score 0 · Answer 3 · answered Sep 13 '11 at 08:38

0

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

answered Sep 13 '11 at 08:38

WraithNath

17,658
10
55
82

2

one would hope that the query optimiser would be able to automatically make your suggested optimisation. – Mitch Wheat Sep 13 '11 at 08:39

score 0 · Answer 4 · answered Sep 13 '11 at 09:43

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.

score 0 · Answer 5 · answered Sep 13 '11 at 16:05

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)

Don't know how build a fast query

5 Answers5