Best way to serve page results? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Best way to serve page results?

Author

Topic

V8S
Starting Member

3 Posts

Posted - 2011-10-19 : 09:13:38

Hi,

Reading across all the SQL guides and forums out there, there doesn't seem to be an established best practice for serving up records based on variable inputs - seemingly a basic requirement of any database driven website. As a struggling amateur, this is somewhat exasperating and very confusing. I see websites like Amazon and wonder how they do it with millions of products and many many categories and ways of searching.

Everyone bemoans dynamic SQL, but no one gives an alternative way that's useful.

Our company has a database of properties for sale in various countries: France, Spain, Italy, etc. Our search results page needs to pass variables such as Country, Region and Page Number into the database to generate a results list of 10 properties per page. Sometimes we want to search all of France, but sometimes we want to search just the Aquitaine region of France. So, the system has to work if Region is specified or blank, and also if Country is specified or blank.

At present, the best solution I have is a stored procedure which builds an @SQL variable based on parameters (PageNumber, Country, Region, District, SortColumn) that I pass to it from an ASP page.

CURRENT SOLUTION


GO

CREATE PROCEDURE GetProperties
       @PageNumber int,
       @Country VARCHAR(100),
       @Region VARCHAR(100)
       @SortColumn VARCHAR(20)
AS

DECLARE @SQL AS NVARCHAR(max)

BEGIN
SET @PageNumber=(@PageNumber-1)*10
SELECT @SQL = 'SELECT TOP(10) * FROM ('
SELECT @SQL = @SQL + 'SELECT RowID=ROW_NUMBER() OVER (ORDER BY '

IF @SortColumn = 'DESC'
   SELECT @SQL = @SQL + 'Price DESC),'
IF @SortColumn = 'ASC'
   SELECT @SQL = @SQL + 'Euros ASC),'
  
SELECT @SQL = @SQL + 'PropertyID,Country,Region,Price FROM Properties WHERE Hide <> ''Yes'''

IF @Country is not null
   SELECT @SQL = @SQL + ' AND Country=''' + @Country + ''''
  
IF @Region is not null
   SELECT @SQL = @SQL + ' AND Region=''' + @Region + ''''
  
SELECT @SQL = @SQL + ') TAB '
SELECT @SQL = @SQL + 'WHERE TAB.RowId > ' + CAST(@PageNumber AS VARCHAR)

EXECUTE SP_EXECUTESQL @SQL
END

EXAMPLE RECORDS

1,France,Normandy,100000

2,France,Aquitaine,150000

3,France,Aquitaine,120000

4,France,Aquitaine,600000

So, it's selecting 10 rows based on PageNumber from a separate SELECT statement that returns the full list of properties.

This is approximately 80% faster than our previous method of using an ASP recordset with a cursor.

I have to check for null values (and thus make the procedure dynamic) because if if I try to find property records where Country = France and Region = null, then no records will be found because all our records have a Region specified.

QUESTION

Is there actually a better way that keeps the flexibility of the above but allows me to drop the dynamic SQL?

If not, is this how Amazon and other large websites perform their database searches?

Many thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-19 : 09:41:56

the other way to do is to use boolean logic using OR like


 WHERE Hide <> 'Yes'
 AND (Country= @Country OR @Country IS NULL)
 AND (Region=@Region OR @Region IS NULL)
...

but using this method will not generate an optimised query plan always and will affect performance

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-10-19 : 16:28:10

If I understand your requirement, it is not "a basic requirement of any database driven website". If your table is small, your query is ok, but if the table has million rows (as you mention Amazon.com), it will be very very slow (just not acceptable), regardless it is dynamic query or not.

Your query search a table without any index (too many columns for search, and no order of columns in the search). This is not working for large table. Also, look like the value of search column is not selective. They are not good to create indexes.

If you want the search run fast, I think you have to redesign tables or application so that the search is narrow down with indexes.

Another solution is switch to Oracle (big change). Oracle has bitmap-index that may work fine for your case. Or waiting for next SQL Server version, which has bitmap-index, according to rumor

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources