MySQL LIMIT clause equivalent for SQL Server 2008 so slow

Question

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement.

I looked for an equivalent of the LIMIT clause for SQL server(2008) and found this one :

SELECT * 
FROM   (SELECT TOP 30 field1, 
                      field2 
        FROM   (SELECT TOP 10 field1, 
                              field2 
                FROM   matable 
                ORDER  BY mycolumn ASC) AS tbl1 
        ORDER  BY mycolumn DESC) AS tbl2 
ORDER  BY mycolumn ASC

it displays 30 rows from the 10 row

It works fine!! but when I increase the number from where I want to extract data it takes a lot of time. Does someone have any idea on how to improve this query for SQL server 2008? (my table contains lots of rows and I use this request with PHP and Jquery to display data in the browser)

Your query is returning the `top 30` from a subquery that is pulling `top 10`. That doesn't make sense. What does your real query look like? — Gordon Linoff, Dec 27 '13 at 15:09
You are selecting from the entire table. Maybe a where clause will speed things up. — Dan Bracuk, Dec 27 '13 at 15:20
@Gordon Linoff:the resultset is 20 rows and my query look like this one just i change Field1,... with real column name. — MDIT, Dec 27 '13 at 17:46
@Bogdan Sahlean: i didn't, but on wich column i have to create the index ? — MDIT, Dec 27 '13 at 17:47
@Dan Bracuk: i agree with you, but maybe the user may need to selet the whole data (this is why i use this query for pagination). but if anyone else any suggestions, I will be grateful. — MDIT, Dec 27 '13 at 17:49

score 0 · Answer 1 · answered Dec 28 '13 at 21:07

0

You pull out top 10 rows with asc order and then form those 10 rows you pull out top 30 rows but with reverse order. Then you take all rows from previous result and again sort it in ascending order. If your query really looks like that it should be equivalent:

SELECT TOP 10 Field1, Field2 FROM  matable  ORDER BY MyColumn asc

ORDER BY can be time-consuming operation if you don't have index on the MyColumn. You can also check execution plan to get details about your query.

answered Dec 28 '13 at 21:07

Luke

2,539
2
23
40

your query just will give me top 10, but for me i want to paginate (from 1-->10, 11-->21,and so on...) this why your query will not help me to resolve the problem. i think i have to index my column. – MDIT Dec 29 '13 at 18:57
Then could you explain how does your query give you more than 10 rows since you've selected `top 30` from `top 10`? Maybe [this](http://stackoverflow.com/questions/187998/row-offset-in-sql-server) will help you achieve desired goal. – Luke Dec 29 '13 at 20:36

score 0 · Answer 2 · answered Dec 30 '13 at 10:35

referring to the @Bogdan Sahlean's comment, i created a new index on my column (MyColumn), and now it's really so fast. and the query still inchanged :

SELECT * FROM ( 
         SELECT TOP 30 Field1, Field2 FROM ( 
         SELECT TOP 10 Field1, Field2 
         FROM  matable 
        ORDER BY MyColumn asc 
        ) AS tbl1 ORDER BY MyColumn desc 
        ) AS tbl2 ORDER BY MyColumn asc

to summarize; if u want an equivalence of the clause LIMIT for SQL Server 2008,

Use the above query
Create an index on your column with who you want to order by (in my case is : MyColumn )

Thanks everyone.

MySQL LIMIT clause equivalent for SQL Server 2008 so slow

2 Answers2