Reputation: 1784

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

Upvotes: 0

Answers (5)

Conrad Frix

Reputation: 52645

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)

Upvotes: 0

Gopal Sanodiya

Reputation: 204

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.

Upvotes: 0

WraithNath

Reputation: 18013

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

Upvotes: 0