Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Got an interesting one for all of you SQL guru's out there. Now this search is only taking a couple of seconds, but it's quite intensive and there must be a better way. Maybe I'm expecting too much?

Simple holiday search app. 2 million holidays. Paging/Sorting around 600,000 rows.

This is the schema of the table

CREATE TABLE [dbo].[Holiday](
        [Id] [int] NOT NULL,
        [PropertyId] [int] NOT NULL,
        [Price] [int] NOT NULL,
        [Rating] [int] NOT NULL,
        [Country] [char](2) NOT NULL,
        [ResortId] [int] NOT NULL,
        [DepartureAirport] [char](3) NOT NULL,
        [DestinationAirport] [char](3) NOT NULL,
        [DepartureDate] [datetime] NOT NULL,
        [Basis] [char](2) NOT NULL,
        [Duration] [int] NOT NULL,

     CONSTRAINT [PK_Holiday] PRIMARY KEY CLUSTERED ([Id] ASC)
  )

As you can see, quite simple. We have a property, a price, duration, departure/destination airports, etc. Now, the more field that are provided the faster the search. If I have a Departure Airport, Property and Date then the search is very fast. However, if I just have a Country and nothing else, there is a lot of data to work through.

Using this CSV export of my table, there are 2 million rows in total, and around 666k just with a country code of FR, which is my example.

This, is the search query. Which returns two tables. The first is a summary, so total number of holidays that match your criteria and how many unique properties. The second table contains the actual results from the search.

--Build a temp table, and store everything we need in it
CREATE TABLE #Pricing (PropertyId int, Duration int, HolidayId int, Rating int, Price int, StartDate datetime, PropertyRow int);

INSERT INTO #Pricing
  SELECT 
    PropertyId, Duration, [Id], [Rating], [Price], DepartureDate,
    ROW_NUMBER() OVER (PARTITION BY PropertyId ORDER BY Price ASC) as PropertyRow
  FROM 
    dbo.Holiday
  WHERE 
    DepartureDate > GETDATE() AND Country = 'FR'

--Get a total number of holidays, and total number of properties
SELECT 
    COUNT(*) AS TotalHolidaysCount, 
    COUNT(DISTINCT PropertyId) AS PropertyCount
FROM 
    #Pricing

--Build the final table, which will contain all the holidays we actually want to return
DECLARE @FinalResults TABLE (HolidayId int, RowNumber int);

INSERT INTO 
    @FinalResults
  SELECT 
    HolidayId, RowNumber
  FROM
    (SELECT 
         PropertyRow, HolidayId, 
         ROW_NUMBER() OVER (order by (CASE WHEN StartDate <= '01/Apr/2013' THEN 1 ELSE 0 END) ASC, [Price] ASC) as RowNumber
     FROM  
        #Pricing 
     WHERE 
        PropertyRow = 1) as SearchResults
WHERE 
    (RowNumber > (10 * (1 - 1)) and RowNumber <= (1 * 10))
ORDER BY 
    RowNumber;

SELECT
     *
FROM
    @FinalResults
    INNER JOIN dbo.Holiday ON HolidayId = Holiday.Id

DROP TABLE #Pricing

Now, I could look into Indexing which would obviously improve performance. But what worries me, is the incredible use of Temporary tables. Surely this shouldn't be how it's done? Taking 5 seconds to search through what is ultimately a tiny amount of data. They only reason they are used is because of the need to reference the data later.

Would it maybe be worth running the query twice, instead of storing all of the data in memory? It seems a waste to select over 25% of the table into memory again and again.

Any helpful feedback would be appreciated. Not looking for the 'answer', just some assistance.

Many thanks, Dean

share|improve this question

migrated from stackoverflow.com Mar 7 '12 at 19:35

This question came from our site for professional and enthusiast programmers.

4  
Surely this code from the last where clause: (RowNumber > (10 * (1 - 1)) and RowNumber <= (1 * 10)) would be much better written as RowNumber Between 1 and 10 –  Bridge Mar 7 '12 at 17:13
    
Entirely valid. Thanks –  Dean Thomas Mar 7 '12 at 17:23
add comment

2 Answers 2

It is very difficult to accurately suggest a solution with no ability to run testing, or see how the DB is indexed etc. But I'm going to try anyway.

You ideally need to find a balance, if your query is likely to return a lot of data and run quickly then I would err towards running the query on the main table twice, if it is likely to take a long time and return a relatively small number of rows then I would stick with the temp table approach.

Given the information you have provided in the question it appears as though there is no problem with the speed of the select, in which case I would tend to agree with you, the additional cost of inserting into a temp table, then selecting from the temp table is as much overhead as performing the select query twice. The queries can be simplified as follows:

SELECT  COUNT(*) [TotalHolidaysCount],
        COUNT(DISTINCT PropertyID) [PropertyCount]
FROM    dbo.Holiday
WHERE   DepartureDate > GETDATE() 
AND     Country = 'FR'


SELECT  *
FROM    (   SELECT  *, ROW_NUMBER() OVER (ORDER BY(CASE WHEN StartDate <= '01/Apr/2013' THEN 1 ELSE 0 END) ASC, [Price] ASC) [RowNumber]
            FROM    (   SELECT  h.*, ROW_NUMBER(PARTITION BY PropertyID, ORDER BY Price ASC) [PropertyRow]
                        FROM    dbo.Holiday
                        WHERE   DepartureDate > GETDATE() 
                        AND     Country = 'FR'
                    ) h
            WHERE   PropertyRow = 1
        ) h
WHERE   Rownumber BETWEEN 1 AND 10

If you do find this is a performance hit and you would rather cache the results of the select I'd be inclined to use table variables instead of temp tables, and only store the Holiday ID primary key, and join back to holiday on the fast indexed join of primary key = primary key as follows:

DECLARE @Results TABLE (ID INT NOT NULL PRIMARY KEY)
INSERT @Results
SELECT  ID
FROM    dbo.Holiday
WHERE   DepartureDate > GETDATE() 
AND     Country = 'FR'

SELECT  COUNT(*) [TotalHolidaysCount],
        COUNT(DISTINCT PropertyID) [PropertyCount]
FROM    dbo.Holiday h
        INNER JOIN @Results r
            ON r.ID = h.ID

SELECT  *
FROM    (   SELECT  *, ROW_NUMBER() OVER (ORDER BY(CASE WHEN StartDate <= '01/Apr/2013' THEN 1 ELSE 0 END) ASC, [Price] ASC) [RowNumber]
            FROM    (   SELECT  h.*, ROW_NUMBER(PARTITION BY PropertyID, ORDER BY Price ASC) [PropertyRow]
                        FROM    dbo.Holiday h
                                INNER JOIN @Results r
                                    ON r.ID = h.ID
                    ) h
            WHERE   PropertyRow = 1
        ) h
WHERE   Rownumber BETWEEN 1 AND 10

This way you are caching as little data as possible (one column of integers), while still retaining enough data to do a fast indexed search on dbo.Holiday.

It ultimately has to come down to looking at your execution plans, creating proper indexes and testing various approaches to find the one that best suits you.

share|improve this answer
    
Agree with the "plans, indexes, testing" to find best approach. –  pheedbaq Mar 7 '12 at 18:26
add comment

Without indexes, any time the search query is run, all 2 million records have to be searched to find the 650K 'FR' instances. With indexes, the database can essentially go straight to them. Even if you left the search query as-is, I think proper indexes would give you speed improvements.

As far as the temp table, I really don't see why that couldn't be done as a sub in the final results query. Aside from that, I think temp tables would be more useful for persistent connections to the DB, or pools of users. If you're just creating the temp table and immediately destroying it... then it's basically just being used as a subquery.

Update: M_M makes a good point in his comment on this answer. However, I still feel an index would be better if the majority of activity is not just on sets where country is the only criteria. For me (just my opinion) it would come down to how often the 'FR' subset would be needed by itself with no other criteria. Otherwise, indexes could be used in the marjority of the searches.

share|improve this answer
1  
It's unlikely that a nonclustered index would be used if it was returning 25%+ of the table. The cost of 650K bookmark lookups would far outweigh the cost of one 2MM row scan. –  M_M Mar 7 '12 at 18:06
    
Hmm, good point. –  pheedbaq Mar 7 '12 at 18:10
    
Also, indexes do indeed make it faster. Not incredibly so, but faster. The problem still lies with storing 650k rows of data in a temp table. To me, it sounds crazy to be doing that. –  Dean Thomas Mar 7 '12 at 18:21
    
@DeanThomas: Yeah I'm not sure I see the point of the temp table, regardless of what's done with indexes :\, unless there's a persistent connection where the data is re-used. –  pheedbaq Mar 7 '12 at 18:24
1  
Hmm, possibly. But even if you just did the same subquery twice, the server would likely already have an execution plan for it (don't know for sure though). –  pheedbaq Mar 7 '12 at 18:42
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.