Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2008
    Posts
    5

    Unanswered: Can this web page use less than 1000 queries?

    I'm looking for suggestions on how to keep a web page from needing the 1000 queries it needs now.

    The web page simply displays ecards in order of points and is fully functional here:
    http://www.hdgreetings.com/card-rank.aspx

    What I do now is pseudo-coded below. CardSends is a table with 1 row created each time a user sends a card.

    SELECT all ecards
    For Each Ecard
    SELECT SUM(NumberOfTimesViewed) from CardSends
    Where CardSends.EcardId == CurrentEcard AND
    CardSends.Date > startDate AND
    CardSends.Date > endDate

    This loop above is about 300 select statements for one column. The two other columns also require 300 select statements.

    I know I can cache the results, but the goal is to make it as close to real time as possible, and somehow I think I'm not there... (actually feel dumb admitting my current implementation)

    Any ideas appreciated,
    LTG

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is just a SWAG, but it ought to get you started nicely:
    Code:
    SELECT *
       FROM ecards AS a
       ORDER BY (SELECT Count(*)
          FROM ecard_sends AS b
          WHERE  b.EcardID = a.EcardID
             AND @startDate < b.date
             AND b.date < @endDate) DESC
    -PatP

  3. #3
    Join Date
    Aug 2008
    Posts
    5
    Hi Pat, thanks for your response.

    I'm not sure exactly how your suggestion works yet but I'm going to research nested sql statements more closely and see if I can get it working.

    One question, did you leave out the SUM function on purpose, or not see that part of the original query?

    Best regards,
    LTG

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I may have made a poor assumption... Most sites rank by the number of times a card is sent, which is what my example does. If your site wants to rank by the number of times a card is viewed (meaning that cards that are never colected are never counted, and that someone only needs to retrieve a card many times to skew the order), then you could use your sum instead of the count that I used.

    -PatP

  5. #5
    Join Date
    Aug 2008
    Posts
    5
    Thanks, I wasn't trying to criticize your logic, just making sure there wasn't something sql related I was missing.

    Regards,
    LTG

  6. #6
    Join Date
    Apr 2007
    Posts
    183

    Try this for speed. It makes only one query to the database.

    Code:
    -- Prepare user supplied parameters
    DECLARE	@MonthStart DATETIME,
    	@NextMonthStart DATETIME,
    	@WeekStart DATETIME,
    	@NextWeekStart DATETIME,
    	@Today DATETIME
    
    SELECT	@Today = DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101'),
    	@MonthStart = DATEADD(MONTH, -1, @Today),
    	@NextMonthStart = @Today,
    	@WeekStart = DATEADD(DAY, -7, @Today),
    	@NextWeekStart = @Today
    
    -- Show the final resultset
    SELECT		e.Name,
    		e.Artist,
    		w.AllTimePoints,
    		DENSE_RANK() OVER (ORDER BY w.AllTimePoints DESC) AS AllTimeRank,
    		w.[1MonthPoints],
    		DENSE_RANK() OVER (ORDER BY w.[1MonthPoints] DESC) AS [1MonthRank],
    		w.[1WeekPoints],
    		DENSE_RANK() OVER (ORDER BY w.[1WeekPoints] DESC) AS [1WeekRank]
    FROM		eCard AS e
    INNER JOIN	(
    			SELECT		eCardID,
    					SUM(NumberOfTimesViewed) AS AllTimePoints,
    					SUM(CASE WHEN Date >= @MonthStart AND Date < @NextMonthStart THEN NumberOfTimesViewed ELSE 0 END) AS [1MonthPoints],
    					SUM(CASE WHEN Date >= @WeekStart AND Date < @NextWeekStart THEN NumberOfTimesViewed ELSE 0 END) AS [1WeekPoints]
    			FROM		CardSends
    			GROUP BY	eCardID
    		) AS w ON w.eCardID = e.ID
    It assumes you work against a Microsoft SQL Server 2005 database.

    DENSE_RANK() can be replaced with ROW_NUMBER() or RANK() whichever suit your needs.
    Last edited by Peso; 08-20-08 at 08:02. Reason: You get wrong result by using PARTITION BY. Drop those PARTITIONs.

  7. #7
    Join Date
    Aug 2008
    Posts
    5
    Peso,

    This looks really slick - I'm excited to try it.

    I'll again have to deconstruct it piece by piece to make sure I understand it. This is not your fault, I just want to learn all I can from this rather than blindly taking a solution without understanding.

    Thanks, I'll report back the difference soon.

    Regards,
    LTG

  8. #8
    Join Date
    Apr 2007
    Posts
    183
    I hope you have had time by now to test.

  9. #9
    Join Date
    Aug 2008
    Posts
    5
    Yes, I owe a response.

    The bottom line is that code was essentially a miracle compared to what I had.

    The old approach of 1000 queries took about 6 seconds. The solution above now takes about 0.3 seconds. So it's at least 20x faster, and actually works well without even caching the web page!

    One problem came when I had to make small feature changes, then I actually had to understand the query, and to be honest that took several hours of experimentation and reading. However I consider it foundation knowledge that I needed to know.

    On a related note I think T-SQL is a terrible language that obscures the beauty and power of the set theory that relational systems are based on. However it does give full access to that power, which is what we need to get our jobs done.

    Thanks very much for the help,
    LTG

  10. #10
    Join Date
    Apr 2007
    Posts
    183
    Thank you for the feedback.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •