paging complex query - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

paging complex query

Author

Topic

Danny__T
Starting Member

27 Posts

Posted - 2005-10-27 : 18:54:43

I have a very complex query (attached below and no doubt full of inefficiancies for anyone willing to point out the 'deliberate' mistakes) which is working and doing its job.

I've read up on several approaches to paging but all seem to be based on relatively simple queries. None seem to explain how to take a complex procedure and add paging to it, if there is such a practice. Unfortunately the working procedure has been fudged from time to time to accomodate changing requirements and the db would benefit from a total re-work however under the scope and time scales we're under this isn't possible at this time.

So any help as to how i could implement paging into a complex query such as the following would be hugely appreciated

Dan

CREATE PROCEDURE [dbo].[sproc_select_events_search]
(
@event_search varchar(255),
@music_style int,
@date_from datetime,
@date_to datetime,
@city_id int,
@area int
)
AS
set DATEFIRST 1
declare @datediff int
set @datediff = DATEDIFF(dd, @date_from, @date_to)
SELECT DISTINCT
CASE WHEN b.event_id IS NULL THEN a.event_id ELSE b.event_id END AS event_id,
CASE WHEN b.event_name IS NULL THEN a.event_name ELSE b.event_name END AS event_name,
CASE WHEN b.image IS NULL THEN a.image ELSE b.image END AS image,
CASE WHEN b.event_date IS NULL THEN a.event_date ELSE b.event_date END AS event_date,
CASE WHEN b.venue_id IS NULL THEN a.venue_id ELSE b.venue_id END AS venue_id,
CASE WHEN b.venue_name IS NULL THEN a.venue_name ELSE b.venue_name END AS venue_name,
CASE WHEN b.city IS NULL THEN a.city ELSE b.city END AS city,
CASE WHEN b.music_style IS NULL THEN a.music_style ELSE b.music_style END AS music_style,
CASE WHEN b.featured_artists IS NULL THEN a.featured_artists ELSE b.featured_artists END AS featured_artists,
CASE WHEN b.offer_count IS NULL THEN a.offer_count ELSE b.offer_count END AS offer_count
FROM
(
-- UDFs retrieve values based on day of week of date provided
(SELECT TOP 200 '-' + CONVERT(char(8), DATEADD(dd, seq.id, @date_from),112) + cast(v.venue_id as varchar) AS event_id,
dbo.udfGetEventName(DATEADD(dd, seq.id, @date_from),v.venue_id) as event_name,
dbo.udfGetEventImage(DATEADD(dd, seq.id, @date_from),v.venue_id) as image,
DATEADD(dd, seq.id, @date_from) AS event_date,
v.venue_id as venue_id,
v.venue_name as venue_name,
c.city_id as city_id,
c.city as city,
v.area_id as area_id,
dbo.udfGetEventMusicStyle(DATEADD(dd, seq.id, @date_from),v.venue_id) as music_style,
dbo.udfGetEventFeaturedArtists(DATEADD(dd, seq.id, @date_from),v.venue_id) as featured_artists,
0 as offer_count
FROM
fn_sequence(0, @datediff) as seq, -- generates a row per day based on the datediff
tbl_venue v
LEFT JOIN tbl_city c ON v.city_id = c.city_id
WHERE v.is_deleted = 0
AND v.is_live = 1
AND dbo.udfGetEventName(DATEADD(dd, seq.id, @date_from),v.venue_id) LIKE '%' +LOWER(@event_search) + '%'
AND (v.city_id = CASE WHEN @city_id > -1 THEN @city_id
ELSE v.city_id
END)
AND (v.area_id = case WHEN @area > -1 THEN @area
ELSE v.area_id
END)
AND (music_style LIKE case
WHEN @music_style > -1 THEN (SELECT '%' + music_style + '%' FROM tbl_music_style WHERE music_style_id = @music_style)
ELSE music_style
END
OR music_style IS NULL)
AND dbo.udfGetEventName(DATEADD(dd, seq.id, @date_from),v.venue_id) <> ''
ORDER BY event_date
) as a

FULL JOIN
(SELECT DISTINCT TOP 200 tbl_event.event_id as event_id,
tbl_event.event_name as event_name,
tbl_event.image as image,
tbl_event.event_date as event_date,
tbl_venue.venue_id as venue_id,
tbl_venue.venue_name as venue_name,
tbl_city.city as city,
tbl_event.music_style as music_style,
tbl_event.featured_artists as featured_artists,
(SELECT COUNT(offer_id) FROM tbl_offers o LEFT JOIN tbl_event_offers eo ON eo.event_offer_id = o.offer_id WHERE eo.event_id = tbl_event.event_id AND o.is_deleted = 0 AND o.is_live = 1 AND o.start_date >= @date_from AND o.end_date <= @date_to) as offer_count
FROM tbl_event
INNER JOIN tbl_venue ON tbl_event.venue_id = tbl_venue.venue_id
INNER JOIN tbl_city ON tbl_venue.city_id = tbl_city.city_id
WHERE tbl_event.event_name LIKE '%' +LOWER(@event_search) + '%' and tbl_event.is_deleted=0
AND (tbl_venue.city_id = CASE WHEN @city_id > -1 THEN @city_id
ELSE tbl_venue.city_id
END)
AND ( tbl_venue.area_id = case WHEN @area > -1 THEN @area
ELSE tbl_venue.area_id
END)
AND tbl_event.event_date >= @date_from
AND tbl_event.event_date <= @date_to
AND (tbl_event.music_style LIKE case
WHEN @music_style > -1 THEN (SELECT '%' + music_style + '%' FROM tbl_music_style WHERE music_style_id = @music_style)
ELSE tbl_event.music_style
END
OR tbl_event.music_style IS NULL)
ORDER BY event_date
) b on a.venue_id=b.venue_id
)
WHERE (a.music_style <> '' OR b.music_style <> '')
ORDER BY event_date, event_name ASC, venue_name ASC
GO

aiken
Aged Yak Warrior

525 Posts

Posted - 2005-10-28 : 00:06:10

My usual approach here is to create a table variable, and simplify the big query so it only stores the PK and any expensive rows that are necessary for sorting into the temp table. The temp table has an identity column, and then you just join it back against the remainder of the big query, limiting its rows to the result range you want. There's no sense doing expensive joins against any table that's not necessary for figuring out pagination until you can limit it to only those rows that will show up on the page.

Cheers
-b

Danny__T
Starting Member

27 Posts

Posted - 2005-10-28 : 06:58:51

that makes sense aiken cheers, could you provide a quick example for me to reference by any chance?

Kristen
Test

22859 Posts

Posted - 2005-10-28 : 07:22:02

I like Jeff's method:

Say I want rows 20 - 30

Select the first 30 rows (ORDER BY "whatever" to achieve that)
Select 10 rows from the above ordered by the same "whatever" but DESCending.

You can do this without actually generating an intervening recordset PROVIDED you can just get some sort of "key" columns that define the row at position 21. So the above becomes:

Select Key Columns into @Variables for first 21 rows [i.e. the first 20 stored into the variables will be overwritten by the 21st, so no actual "recordset" is created]
Now select the TOP 10 rows from the underlying table(s) greater-than-or-equal to the KEY fields.

Even if you can't do that you can pull the PKs for rows 21-30 into a temporary table by method one, and then JOIN them back to the original tables to get all the other "presentation" columns - which is as aiken said.

http://weblogs.sqlteam.com/jeffs/archive/2003/12/22/672.aspx

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-10-28 : 11:59:16

Your biggest issue with ANY method is going to be the expensive, inefficient convoluted SELECT that you are dealing with.

FULL OUTER JOINS, criteria with LIKE '%xx%' and CASE statements, lookups using UDF's instead of joins, SELECT DISTINCT in a many-column resultset -- all are signs of a SELECT that really needs to be re-worked from scratch. Any of those indicate issues, but ALL of them is definitely a scary thing!

Also, your function that returns a sequence of dates will be more efficient if you permanently store them in a table.

You might not have time to re-design your DB, but I guarantee you that if you step back and re-write the SELECT logically you will have much better performance using ANY paging method and also your code with be easier to read and maintain. If you provide the DDL of relevant tables, some sample data, a brief description, and expected results from this SELECT I am sure we can help you out.

aiken
Aged Yak Warrior

525 Posts

Posted - 2005-10-28 : 18:37:46

quote:
Even if you can't do that you can pull the PKs for rows 21-30 into a temporary table by method one, and then JOIN them back to the original tables to get all the other "presentation" columns - which is as aiken said.

Yep, that's my preferred method, with one caveat: table variables rather than temp tables. Since it's generally just a few ints, these are in-memory and pretty darned fast.

Cheers
-b

Danny__T
Starting Member

27 Posts

Posted - 2005-11-01 : 03:28:01

Thanks for the responses all. jsmith I certainly will come back and post relevant ddl, data and what i want to acheive in the very near future as performance will definitely be the next thing to work on.

Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources