I have a category table and need to do pagination for the same. I am using JSF, and query in XML. Is there any way to optimize this query better.  SELECT     * FROM     (         SELECT             categories.*,             ROWNUM rn         FROM             (                 SELECT                     *                 FROM                     (                         SELECT                             *                         FROM                            categories                         WHERE                           name LIKE 'FAMILY%'                         ORDER BY                             id                     )                 WHERE                     ROWNUM &lt; 21             ) categories     ) WHERE     rn &gt; 10;
13936
Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select *  from sometable order by name limit 20,10  to get the 21st to the 30th rows (skip the first 20, give the next 10). The rows are selected after the order by, so it really starts on the 20th name alphabetically. In Oracle, the only thing people mention is the rownum pseudo-column, but it is evaluated before order by, which means this: select *  from sometable where rownum &lt;= 10 order by name  will return a random set of ten rows ordered by name, which is not usually what I want. It also doesn't allow for specifying an offset.
22294
We have a large database on which we have DB side pagination. This is quick, returning a page of 50 rows from millions of records in a small fraction of a second.  Users can define their own sort, basically choosing what column to sort by.  Columns are dynamic - some have numeric values, some dates and some text.  While most sort as expected text sorts in a dumb way. Well, I say dumb, it makes sense to computers, but frustrates users.  For instance, sorting by a string record id gives something like:  rec1 rec10 rec14 rec2 rec20 rec3 rec4   ...and so on.  I want this to take account of the number, so:  rec1 rec2 rec3 rec4 rec10 rec14 rec20   I can't control the input (otherwise I'd just format in leading 000s) and I can't rely on a single format - some are things like "{alpha code}-{dept code}-{rec id}".  I know a few ways to do this in C#, but can't pull down all the records to sort them, as that would be to slow.  Does anyone know a way to quickly apply a natural sort in Sql server?    We're using:  ROW_NUMBER() over (order by {field name} asc)   And then we're paging by that.  We can add triggers, although we wouldn't. All their input is parametrised and the like, but I can't change the format - if they put in "rec2" and "rec10" they expect them to be returned just like that, and in natural order.    We have valid user input that follows different formats for different clients.  One might go rec1, rec2, rec3, ... rec100, rec101  While another might go: grp1rec1, grp1rec2, ... grp20rec300, grp20rec301  When I say we can't control the input I mean that we can't force users to change these standards - they have a value like grp1rec1 and I can't reformat it as grp01rec001, as that would be changing something used for lookups and linking to external systems.  These formats vary a lot, but are often mixtures of letters and numbers.  Sorting these in C# is easy - just break it up into { "grp", 20, "rec", 301 } and then compare sequence values in turn.  However there may be millions of records and the data is paged, I need the sort to be done on the SQL server.  SQL server sorts by value, not comparison - in C# I can split the values out to compare, but in SQL I need some logic that (very quickly) gets a single value that consistently sorts.  @moebius - your answer might work, but it does feel like an ugly compromise to add a sort-key for all these text values.
42400
I have implemented custom search and displaying 10000 records on page using pagination. Pagination is done by using StandardSetController only. I also have implemented sorting of data. Can anybody suggest any solution to reduce view state?
54711
This question is related to . The below query was taking 10 to 15 seconds to execute:  SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id] FROM [company].dbo.[customer] WHERE (Charindex('123456789',CAST([company].dbo.[customer].[Phone no] AS VARCHAR(MAX)))&gt;0)    In some articles I saw that using CAST and CHARINDEX will not benefit from indexing. There are also some articles that say using LIKE '%abc%' will not benefit from indexing while LIKE 'abc%' will:      In my case I can rewrite the query as:  SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id] FROM [company].dbo.[customer] WHERE [company].dbo.[customer].[Phone no]  LIKE '%123456789%'   This query gives the same output as the previous one. I have created a nonclustered index for column Phone no. When I execute this query it runs in just 1 second. This is a huge change compared with 14 seconds previously.  How does LIKE '%123456789%' benefit from indexing?  Why do the listed articles state that it will not improve performance?  I tried rewriting the query to use CHARINDEX, but performance is still slow. Why does CHARINDEX not benefit from the indexing as it appears the LIKE query does?  Query using CHARINDEX:  SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]  FROM [Company].dbo.[customer]  WHERE ( Charindex('9000413237',[Company].dbo.[customer].[Phone no])&gt;0 )    Execution plan:    Query using LIKE:  SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]  FROM [Company].dbo.[customer]  WHERE[Company].dbo.[customer].[Phone no] LIKE '%9000413237%'   Execution plan:
15927
I used this code to get a pager.     if (isset($_GET['tid'])) {      $id=($_GET['tid']);      $query = new EntityFieldQuery();      $query-&gt;entityCondition('entity_type', 'node');      $query-&gt;entityCondition('bundle', 'escorts_product');      $query-&gt;pager(2,1);      $query-&gt;propertyCondition('status', 1);      $query-&gt;fieldCondition('field_product_sub_cat', 'tid', $id,'=');      $result = $query-&gt;execute();     //print "&lt;pre&gt;";print_r($result);     }   When I used print "&lt;pre&gt;";print_r($result), it gave me 2 results on y page, whereas I added more than 2; this is right.   How should I get links for next page and also previous, next? Is there any other way to get a pager?
104961
So, I tried to run this query    which has many views (so I assume it's popular), but I get a message saying:     The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.   Now, true, that's not a trivial query, but is it really that bad? There isn't even a Join or anything.
74456
I want to write a query like this:  SELECT o.OrderId, MAX(o.NegotiatedPrice, o.SuggestedPrice) FROM Order o   But this isn't how the MAX function works, right?  It is an aggregate function so it expects a single parameter and then returns the MAX of all rows.     Does anyone know how to do it my way?
123106
I want to know how to efficiently store query-affecting settings in a large analytical database (MS SQL Server). By efficiency I mean no hardcode (1) and fast/optimal query processing (2).  My example  I've got a massive table of sales orders. Let's simplify it to two columns:  SalesOrder  OrderID      int      Primary Key  OrderDate    date   I need to retrieve the list of orders for the last X days and this X should be configurable. That's why I introduce a table of settings where I define it:  Admin_Configuration  SettingName   (PK)|      SettingValue  ------------------|------------------  DaysBack          |          30      It allows me not to hardcode this "30" in query definitions. Then I would run queries like:  SELECT OrderId   FROM SalesOrder as S   JOIN Admin_Configuration as A     ON A.SettingName = 'DaysBack'   WHERE [OrderDate] &gt; DATEADD(DAY, -A.SettingValue, GETDATE());   Performance  It avoids hardcoding, but leads to a performance problem. This query runs much slower than it would've, had I simply hard-coded 30 in the WHERE clause.  My understanding is that optimizer doesn't know how large my SettingValue is and assumes the worst - that all rows would be retrieved from the SalesOrders table, thus leading to a suboptimal query plan.  Trying a scalar function yielded even worse results. I'm desperate enough to have a separate view which stores these hardcoded settings but I've been refraining from it with hopes for a better solution.  Here is the original query plan (with a join): 4 seconds elapsed   Here is the plan with subquery, as :  SELECT orderid FROM [SalesOrder] as s WHERE s.OrderDate &gt; dateadd(DAY,    (SELECT -NumValue    FROM Admin_Configuration where SettingName = 'DaysBack'   )     , getdate())   7 seconds elapsed, slightly slower   As for the ideal performance: I just hardcoded 20, the query took 2 seconds. It needs mentioning that the Admin_Configuration table contains only 18 rows, hence it shouldn't affect performance.  SELECT OrderId   FROM SalesOrder as S   JOIN Admin_Configuration as A     ON A.SettingName = 'DaysBack'   WHERE [OrderDate] &gt; DATEADD(DAY, -20, GETDATE());   Using parameters would mean that I'd have to replace views (that I use now) with stored procedures/table-valued functions, which is unacceptable in my case
29108
What is the best way (performance wise) to paginate results in SQL Server 2000, 2005, 2008, 2012 if you also want to get the total number of results (before paginating)?
90233
I'm trying to design a widget which is a dropdown single selection containing a very large set of data. Because there could be 2000 records in the dropdown. Loading them at once will make the system very slow. So below is my first path. This widget is used to select an option in the database.  By default, it looks just like a dropdown. When users click in the field, the dropdown list will open and a cursor will appear in the field. The 2000 options are divided into pages and that's why you see the pagination at the bottom.    When people type in the field, it will filter/auto-suggest matching results. The total number will reduce to 1000. If people also use the filter on the search result, it will reduce to 50.   However, I'm not sure if the pagination is really helpful to people who want to pick an option. I don't think they'll ever use pagination when there are 1000 of them. The reason I keep it here is because sometimes people might not know what to type in the field...  Here is another option: Add a "More" button at the bottom and get rid of pagination. Any thoughts, comments or suggestion?
108409
When I write a query like this...  select * from table1 t1 join table2 t2 on t1.id = t2.id   Does the SQL optimizer, not sure if that is the correct term, translate that to...  select * from table1 t1, table2 t2 where t1.id = t2.id   Essentially, is the Join statement in SQL Server just an easier way to write sql? Or is it actually used at run-time?  Edit: I almost always, and will almost always, use the Join syntax. I am just curious what happens.
67049
What is the most preferred and easiest way to do pagination in ASP.NET MVC?  I.e. what is the easiest way to break up a list into several browsable pages.  As an example lets say I get a list of elements from a database/gateway/repository like this:  public ActionResult ListMyItems() {     List&lt;Item&gt; list = ItemDB.GetListOfItems();     ViewData["ItemList"] = list;      return View(); }   For simplicity's sake I'd like to specify just a page number for my action as parameter. Like this:  public ActionResult ListMyItems(int page) {    //... }
113045
I've been  about entity field queries.  What are the advantages of using entity field queries (EFQ) over views?  What are the largest pain points/disadvantages of using EFQ ?
108609
I have a table that takes up a full page, but I want it to start on a page that already has some text and then continue onto the next page (i.e. it's ok if the table gets broken up and spans two pages). It is a simple two column table:  \begin{table}[h] \centering \begin{tabular}{| p{.20\textwidth} | p{.80\textwidth} |}  \hline foo &amp; bar \\ \hline  foo &amp; bar \\ \hline  ... \end{tabular} \end{table}   I guess I need longtable? How do I use it?
109172
What is it best to handle pagination? Server side or doing it dynamically using javascript?  I'm working on a project which is heavy on the ajax and pulling in data dynamically, so I've been working on a javascript pagination system that uses the dom - but I'm starting to think it would be better to handle it all server side.  What are everyone's thoughts?
83405
I know there are similar questions to this, but they either did not cover what I was looking for or I did not understand them fully, therefore I am asking this here.  My situation  At the moment I am using rel prev and rel next as pagination.  &lt;link rel="prev" href="http://www.example.com/category/blablabla/page/2" /&gt; &lt;link rel="next" href="http://www.example.com/category/blablabla/page/4" /&gt;   I currently also add a canonical link which refers to the first page, i.e:  &lt;link href="http://www.example.com/category/blablabla" rel="canonical"&gt;   The problem  The situation is like this:  The first page has only 20 items (as all other pages do).  With this canonical link i.e to the first page, only the first page gets indexed by Google.  I would like all the pages to get indexed because important content might get lost if they are not indexed.  Should I just change the canonical link to the current URL? i.e the current page, in this case:  &lt;link href="http://www.example.com/category/blablabla/page/3" rel="canonical"&gt;   Or should I do something else, if yes, then what would it be?
64730
--Setup, actual queries after the space --create table fltrind (a integer,b integer) truncate table fltrind DROP INDEX fltrind.nf DROP INDEX fltrind.f DECLARE @i integer SET @i=0 while @i&lt;100 BEGIN INSERT INTO fltrind(a) values(@i) SET @i=@i+1 END INSERT INTO fltrind(a,b) values(9000,0900)  insert into fltrind(a,b) select top 100000 f1.a,f1.b from fltrind f1 , fltrind f2  create nonclustered index nf on fltrind(b) INCLUDE(a) create nonclustered index f on fltrind(b) INCLUDE(a) where b is not null  UPDATE STATISTICS fltrind WITH FULLSCAN  select a from fltrind where b is not null  DROP INDEX fltrind.nf  UPDATE STATISTICS fltrind WITH FULLSCAN  select a from fltrind where b is not null   Looking at the query plan, the filtered index is not used as long as the non-filtered index is present.  Any idea why, and how can I get it to use the filtered index?  Dropping the non filtered index nf makes the optimizer use the filtered index f.  In fact, increasing the rowcount so that >10% of the rows qualify results in a table scan when the non filtered index is dropped.