paging on a large query

[jez]

Ok, so i have taken a look at many threads on here and MSDN about paging in SQL 2005.

All seem to be working on fairly simple queries getting one or two columns.

I have a query that is very large, it gets at least 15 columns from 3 tables and then adds search related items to the where clause and finally orders by a user selected field.

My question is how to know where to start with the paging hacks....

I am from a mysql, postgres, oracle background and finding this to be a bit of an oversight in SQL 2005... my recordset total is inxs of 20k records, so clientside paging is simply not an option.

Here is the query;-

$resultArr = array();
		$limit = "TOP (100)";
		$seriesSql = "	SELECT  $limit
				st.Series_Id,
				st.Title_Id,
				st.Series_Name,
				st.Programme_Name,
				st.Synopsis,
				st.Language_Code,
				st.Demographic_Id,
				st.Channel_Code,
				st.PDS_Common_Property_Id,
				M.Material_Id,
				M.Title_Id,
				M.Series_Id,
				MT.Marketing_Title,
				CONVERT(VARCHAR(10), MT.Start_Date, 111) AS Start_Date,
				CONVERT(VARCHAR(10), MT.Start_Date, 108) AS Start_Time,
				CONVERT(VARCHAR(10), MT.End_Date, 111) as End_Date,
				CONVERT(VARCHAR(10), MT.End_Date, 108) as End_Time
				
				FROM Series_Titles st
								
				LEFT JOIN Material M ON
				st.Series_Id = M.Series_Id AND
				st.Title_Id = M.Title_Id AND
				st.Language_Code = M.Language_Code AND
				st.Channel_Code = M.Channel_Code
				
				LEFT JOIN Marketing_Titles MT ON
				MT.Series_Id = st.Series_Id AND
				MT.Title_Id = st.Title_Id
				";
				
				
		if($filter_text){
			$extraSql[] = " M.Material_Id LIKE '%".$filter_text."%'";
		}
		if($this->langCodeSearchTerm){
			$extraSql[] = "st.Language_Code = '".$this->langCodeSearchTerm . "'";
		}
		if($this->channelCodeSearchTerm){
			$extraSql[] = "st.Channel_Code = '".$this->channelCodeSearchTerm . "'";
		}
		if($this->titleStringSearchTerm){
			$extraSql[] = "st.Programme_Name LIKE '%".$this->titleStringSearchTerm . "%'";
		}
		if($this->seriesStringSearchTerm){
			$extraSql[] = "st.Programme_Name LIKE '%".$this->seriesStringSearchTerm . "%'";
		}
		
		if(count($extraSql) > 0 ){
			$seriesSql .= " WHERE " . implode(" AND ", $extraSql);
		}
		print_r($seriesSql);

Suggestions greatly appreciated.

Jez

 

[hmckillop]

I do all paging through DB using the following format.
A few notes - 1 if you are ordering by a different column you need to include this in your "paging" variables and also your where clause.
For this example I assume you are ordering by clustered index

CREATE PROC usp_MyPageProc
(

@pi_InputParam1 int,
...
--other params required for query
@pi_PageNo int,
@pi_RowsPerPage int,
@po_TotalItems int output
)
AS
BEGIN
--declare your local variables needed for paging
DECLARE @v_FirstID     	int
DECLARE @v_LastID     	int
DECLARE @v_MaxRowNumber    INT

--set the max row number
    	SET @v_MaxRowNumber = @pi_PageNumber * @pi_RowsPerPage 


--first query used to set the "edge" of the pages
SELECT @po_TotalItems = @po_TotalItems + 1
                      ,@v_FirstID = CASE WHEN ((@po_TotalItems <= @v_MaxRowNumber) AND ((@po_TotalItems % @pi_RowsPerPage = 1) OR @pi_RowsPerPage = 1)) THEN tmp.ID
                                ELSE @v_FirstID 
                           END
            ,@v_LastID = CASE WHEN @po_TotalItems <= @v_MaxRowNumber THEN tmp.ID
                               ELSE  @v_LastID
  END

FROM (

--insert in here the query you need to return all the results , For my example I have used "ID" as the ordering unique field -- if you need more to make this unique it gets more complicated
SELECT tst.ID from MyTestTable Order by ID) tmp
Order by tmp.ID


--next you need to execute the query ensuring you only return rows between the page "edges" set in above query

Select tst.ID, tst.field1, tst.field2
from MyTestTable
WHERE 
tst.ID >= @v_FirstID and tst.ID <= @v_LastID
--any other where clauses you need
Order by tst.ID


END

This will return the output parameter containing how many items in who query and will then return only the items you need for each page.

Hope this helps in the right direction.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[jez]

Sorry, maybe my SQL is not all that, but i cannot understand that... I have the same trouble with the MSDN articles... i simply don't see how that is working since it seems to to need two queries and rely on a unique incrementing ID field being sorted on.

Can you break down the first query a bit for me

Also i cannot use stored procedures as i don't have admin access to the DB.

 

[hmckillop]

If you cant use stored procedures then DB implemented paging is not really that practical

For you then the best approach is stick to your "top N" approach but change it slightly to include the last item in the page your previously retrieved for when you need the next page. Add a WHERE clause to ensure that anything greater than this item is then the start point for your next page.

Personally I dont think this is a SQL issue, as you can execute whatever query you want to get the information. If you cant process the information in a stored proc and are sending query each time its going to be tough to get the same and a good response each time


"I'm living so far beyond my income that we may almost be said to be living apart

 

[jez]

If you cant use stored procedures then DB implemented paging is not really that practical

I dont want to sound too critical of MS SQL, but this is quite a significant flaw compared to other databases.
I am used to using LIMIT and OFFSET in MySql to get a dataset and then limit the return value to a sub set of that dataset (all at the database tier, so quite efficient).

Or in Oracle there is ROWNUM, which allows something similar.

Even with the stored proc approach, it still seems like a lot of code to simply limit the dataset returned.


I really appreciate your input on this, and it seems the only option left to me is to try and get access to add some stored procedures.

 

[hmckillop]

my point wasnt around you needing access to sps to do paging, just if you wanted to implement in its entirety that stored procedures would be the way forward.


e.g. a simplistic version of what you are doing is this

--get the top XX rows where XX is the page size

Data Query from your app -
"SELECT TOP 10 field1, field2, field3 .... FROM Mytable order by field1"

-- Capture the 10th row ID in your code @PassedParameter, or the field where you order by + the ID
--send this field to the next query
"Select top 10 field1, field2, field3, .... from Mytable
WHERE field1 > @PassedParameter
ORDER by field1"

This is in effect your paging implemented (but in simplistic terms).

What I was stating was that this is inefficient in terms of queries as its a dynamic query run and compiled at runtime. SQL is not as efficient at doing this, as implementing a pre-compiled SP.



"I'm living so far beyond my income that we may almost be said to be living apart

 

[jez]

Ahh i see what you mean, but there is another problem i have which is down to the database design...
There is no unique incrementing key in the data, there are unique keys but they are not incremental.

Also when it comes to sorting, for instance on a date field, the PK values may not be in order.

Given that it is a multi-table query, any ID is not necessarily relevant to the query resultset.

Am i correct in thinking that there is no added system counter type key, and i can only use actual fields for paging and sorting?
I am currently getting the TOP 100... ideally if TOP took two parameters then it could work like limit.

 

[hmckillop]

SQL can use a different method to provide a ranking

have a look at

http://msdn.microsoft.com/en-us/library/ms189798.aspx

it explains much better than I can


"I'm living so far beyond my income that we may almost be said to be living apart

 

[markros]

See

http://www.code-magazine.com/Article.aspx?quickid=0703041

and

http://weblogs.asp.net/scottgu/archive/2006/01/01/434314.aspx

 

[jez]

Thanks for your suggestions, i have looked long and hard at those links.

I cannot say i have it working yet, but I have started with CTEs and Ranking.

It does seem to be a very complex hack to do something straightforward and it does appear to slow the query dramatically. It is not helpful that i did not build the database and i do not have full admin access to it, but then that is sometimes how it is.

I appreciate your help, but my fondness of MS SQL has not been improved by this and for any online app i would seriously caution using MS sql.

 

[jbenson001]

and for any online app i would seriously caution using MS sql.

What do you propose, mysql or ACCESS. The problem is your understanting and exprience with MS SQL. Also the fact that the db is most likely designt poorly.

I work for a major real estate company and we use MS SQL for all our back ends with no problems and the queries are very complex and fast. When searching through millions of rows of properties, it has to be.

You just need to take the time to reseach and gain experience.

 

[hmckillop]

I agree with jbenson001. I work for a bank and we have hundreds of millions of rows of data doing complex calculations and permissions, the procedures are used for online web applications and provide real-time sub second results.
(obviously we have good hardware behind it), but design and performance tuning is at the key keeping the database healthy


"I'm living so far beyond my income that we may almost be said to be living apart

 

[jez]

You just need to take the time to reseach and gain experience

.. which i am, this thread being part of that research.


I appreciate that my understanding of MS SQL is less than my understanding of other databases... but seriously i would not use ACCESS although I have used MySql for serious online apps (1000+ concurrent search queries on complex data structures).
Also thanks for taking exception to my criticism of MS Sql, but not adding anything to my attempt to gain greater understanding... (troll? )

I stated that based on my own tests, where I have a query which i have timed, then added the sub query functionality and paging and it was slowed massively. By comparison, i have done similar things in MySql and found little impact on the performance, due to only using 1 query.
Additionally i am basing this timing on NON-SP queries (as i stated at the very start of this thread).

Thank you hmckillop, I do agree that design and tuning are the key, but in some real world applications (such as the one i find myself) a DBA is responsible for all things database and i have work around that.

 

[jbenson001]

(troll? )

???
I don't know what you are trying to say. What I am saying is that your original quote:

and for any online app i would seriously caution using MS sql.

is a very broad and ignorant statement to say about any technology that you obviously have little experience with.

You know My SQL better than MS SQL and cannot figure out how to do in MS SQL what you know how to do in My SQL and therefore criticized it. What I am saying is that before making such a broad staement, take the time and research and explore on your own. What ever you do in MySQL you can do in MS SQL.

 

[jez]

What ever you do in MySQL you can do in MS SQL.

The reason for this thread is that you cannot do the same in both databases, or at least not in the same way.

I am very willing to do research and try things, i have been tinkering with this issue since mid last week.
It seems that to do the same as

SELECT ... LIMIT 0,100

which is valid in MySql, you must effectively do two queries in MS SQL, which leads (in my case) to a drop in performance.

My troll comment was about the fact that your only contribution here is to tell me off for saying bad things about MS SQL.

I am not really trying to offend anyone, but in terms of broad statements, i think it is a fairly valid comment based on my tests and trials... which are noted throughout this thread and relate specifically to my situation.
In my opinion, based on my experience, in situations where you have not got any control over the database (like me), when you are not using .NET ... i would caution the use of MS SQL.

 

[jbenson001]

In my opinion, based on my experience, in situations where you have not got any control over the database (like me), when you are not using .NET ... i would caution the use of MS SQL.

It has nothing to do with control over the database. That statment would be true of any DB. If the DB is desinged correctly, OR, if you have the resources to change the DB for you, if you do not have access to do so, then this would not be an issue. The DB Server, MS SQL in this case is not the problem. The problem is that the DB is designed poorly an cannot be changed.

My troll comment was about the fact that your only contribution here is to tell me off for saying bad things about MS SQL.

I don't care if someone bashes MS SQL if their comment is well founded. In this case it is not. It is just one small isolated incident which you said cannot be controlled by you.

 

[jez]

This is simply a difference of opinion. My comment is well founded based on my situation which is what it describes, nothing more.

The reason it is well founded is that in the same situation but with a different database, i could easily overcome the problem from within the SQL.

My experience with databases covers quite a broad range including Oracle, Postgres, SQLite, MySql and DB2, so i am not simply bashing MS SQL for no reason but based on considered comparisions. Maybe you try using other (non-MS) systems, its quite enlightening.

 

[jbenson001]

Maybe you try using other (non-MS) systems, its quite enlightening

I have. I have yous Oracle sightly, MS SQL, DB2, SYBASE, and other propriatary DBs.

The reason it is well founded is that in the same situation but with a different database, i could easily overcome the problem from within the SQL.

It is not well founded because it is just one small incidient you had. If you had control over the DB or someone else did that could help you, this discussion would not be going on. I reiterate, it is not a limitation of the DB, it is a limitation of being able to change the DB to do what you need it to do.

 

[hmckillop]

Jez,

ok, based on the discussions you have had - define your requirements again and we can have another go. I can see both sides of the arguments, but dont believe the problem lies with SQL Server. For all languages, databases etc there are differences, some which advantage one over the other based on what it is you are looking for.
I personally prefer ASP .NET over php, I like SQL Server over ORacle or mysql and i could go into many reasons why but its what you are used.

State what it is you need to do again and how your ideal way of working and we can repost.

PS - you say you dont have access to DB, are you able to create procedures remotely e.g. through executing SQL from app?

"I'm living so far beyond my income that we may almost be said to be living apart