CODEWITHSUNDEEP
sqlsql-serverperformancet-sql
Sam
Sam

Reputation: 4339

Query with a lot of joins - porting C# to SQL Server

I am trying to port some C# to TSQL because the performance is unsatisfactory. The C# makes multiple database calls.

This is for a scraper of accommodation properties. The schema is as follows:

Scrapes

ScrapeResults

Essentially the whole point of this is that the database tracks internal room prices compared to near by competitors. The fkRoomId column on ScrapeResults defines each room uniquely. Each time a competitor is scraped there is a new entry in the Scrapes table, and the results of the scrape is saved in ScrapeResults. Start and End dates are a 1 week period. The scrapes weekly prices for a 3 month period. The scrape happens once a week, which is the purpose of fkSessionIdwhich relats to a unique scraping session.

The C# code builds a report that looks like this:

I'm not quite sure if it's possible to build a SQL query for this. I've written a stored procedure that builds up a dynamic SQL string. Here was my first attempt:

select sr.ScrapeId,
    sr.fkProviderId,
    sr.startDate,
    sr.endDate,
    sr1.price,
    sr2.price,
    sr3.price,
    sr4.price,
    sr5.price,
    sr6.price,
    sr7.price,
    sr8.price,
    sr9.price,
    sr10.price,
    sr11.price,
    sr12.price 
    from Scrapes sr 
    left join ScrapeResults sr1 on sr1.fkScrapeId = sr.ScrapeId 
    left join ScrapeResults sr2 on sr2.fkScrapeId = sr.ScrapeId 
    left join ScrapeResults sr3 on sr3.fkScrapeId = sr.ScrapeId 
    left join ScrapeResults sr4 on sr4.fkScrapeId = sr.ScrapeId 
    left join ScrapeResults sr5 on sr5.fkScrapeId = sr.ScrapeId 
    left join ScrapeResults sr6 on sr6.fkScrapeId = sr.ScrapeId 
    left join ScrapeResults sr7 on sr7.fkScrapeId = sr.ScrapeId 
    left join ScrapeResults sr8 on sr8.fkScrapeId = sr.ScrapeId 
    left join ScrapeResults sr9 on sr9.fkScrapeId = sr.ScrapeId 
    left join ScrapeResults sr10 on sr10.fkScrapeId = sr.ScrapeId 
    left join ScrapeResults sr11 on sr11.fkScrapeId = sr.ScrapeId
    left join ScrapeResults sr12 on sr12.fkScrapeId = sr.ScrapeId 
    WHERE sr.fkSession = 25
    and sr.startDate='2014-03-22'
    and sr.fkLocationId = 1 
    and sr1.fkRoomId = 11 
    and sr2.fkRoomId = 15 
    and sr3.fkRoomId = 19 
    and sr4.fkRoomId = 23 
    and sr5.fkRoomId = 25 
    and sr6.fkRoomId = 27 
    and sr7.fkRoomId = 32 
    and sr8.fkRoomId = 39 
    and sr9.fkRoomId = 41 
    and sr10.fkRoomId = 45
    and sr11.fkRoomId = 47 
    and sr12.fkRoomId = 50

I know I'm off the mark here but would appreciate pointers. Thanks.

Upvotes: 0

Views: 81

Answers (2)

Ron Savage
Ron Savage

Reputation: 11079

You can do this simpler with the PIVOT option, pivoting on the fkRoomID column. You can do it manually with a single query but that annoys me because you have to specify the fkRoomID value in the SQL for the PIVOT statement so if the number of rooms changes you have to change the SQL, like this:

select 
   sc.ScrapeId,
   sc.fkProviderId,
   sc.startDate,
   sc.endDate,
   scr.fkRoomId,
   scr.price
from 
   Scrapes sc

   join ScrapeResults scr
      on ( scr.fkScrapeId = sr.ScrapeId )
pivot (max(price) for fkRoomId in ([11],[15],[19],[23],[25],[27],[32],[39],[41],[45],[47],[50]))
where
   sr.fkSession = 25
   and sr.startDate = '2014-03-22'
   and sr.fkLocationId = 1

I prefer to use this stored procedure I wrote named pivot_query . To use it you would format your query like this:

declare @mySQL varchar(MAX);

set @mySQL = '
select 
   sc.ScrapeId,
   sc.fkProviderId,
   sc.startDate,
   sc.endDate,
   scr.fkRoomId,
   scr.price
from 
   Scrapes sc

   join ScrapeResults scr
      on ( scr.fkScrapeId = sr.ScrapeId )
where
   sr.fkSession = 25
   and sr.startDate = ''2014-03-22''
   and sr.fkLocationId = 1
';

exec pivot_query @mySQL, 'StartDate, EndDate, fkProviderId','fkRoomId','max(price)'

That's the basic concept but if you have another reference table with the Room Names it it you could also join that table and use the Room Names to pivot on, so the columns would have the room names at the top.

There are some examples for using the pivot_query proc here.

Upvotes: 1

paparazzo
paparazzo

Reputation: 45096

You were breaking the left join by an srX.fkRoomId =
So might as well just do a join
Or in this format you actually can do a left join and not break it
The query optimizer can typically do a better job with the syntax
I see not problem with building this up in C# 

  select sr.ScrapeId,
         sr.fkProviderId,
         sr.startDate,
         sr.endDate,
         sr1.price,
         sr2.price,
         ...
    from Scrapes sr 
    join ScrapeResults sr1 
      on sr1.fkScrapeId = sr.ScrapeId 
     and sr1.fkRoomId = 11
    join ScrapeResults sr2 
      on sr2.fkScrapeId = sr.ScrapeId 
     and sr2.fkRoomId = 15
    ....
   WHERE sr.fkSession = 25
     and sr.startDate='2014-03-22'
     and sr.fkLocationId = 1

Upvotes: 1

Related Questions