Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2011
    Posts
    3

    Unanswered: Poor performance

    Hey All -

    Im generating a query which returns a ratio of sales over time. The resulting records essentially give a big picture view of how the company is doing vs the same time in previous years.

    To accomplish this I have a DB table that has every date from 2000 to 2020 (I use this for other tasks as well) and I join it with our sales summary table filtering it from 2005 until today. It takes about 20 seconds to return the data on a relatively small dB (sumsales=~61k records, dateinfo=~7k records)

    The calculation of quotient below is the killer. Any hints as to how I can speed this up?

    SELECT CAST(dateinfo.date_val AS DateTime) AS Date, 1 AS Baseline,
    (SELECT Sum(sumsales.sales_dol) sales_dol FROM sumsales
    WHERE sumsales.sale_date <= dateinfo.date_val AND
    sumsales.sale_date > CAST(dateinfo.date_val AS DateTime) 365) /
    (SELECT Sum(sumsales.sales_dol) sales_dol FROM sumsales
    WHERE sumsales.sale_date <= CAST(dateinfo.date_val AS DateTime) - 365 AND sumsales.sale_date > CAST(dateinfo.date_val AS DateTime) - 730) quotient
    FROM dateinfo
    WHERE CAST(dateinfo.date_val AS DateTime) <= GetDate() AND
    CAST(dateinfo.date_val AS DateTime) >= '01/01/2005'
    ORDER BY Date

    Thx!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bgoldstine View Post
    To accomplish this I have a DB table that has every date from 2000 to 2020
    why are you casting the date column to datetime???

    i'd've thought that a calendar or date table would use an appropriate datatype that wouldn't need casting

    also, have you done a "Show Execution Plan" on the query to find out where the bottlenecks are?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2011
    Posts
    3
    Ive attached the execution plan if it helps anyone (Im not at all studied in the db tools available nowadays). Still, from how Im reading this, its the calculation of "quotient" that is the issue. Ive also eliminated some of the non-essential stuff to make reading the SQL easier.

    There's got to be a better/smarter way to do this. The datasets arent big enough to warrant waiting 20 seconds to get this info!?

    SELECT
    (SELECT Sum(sumsales.sales_dol) sales_dol FROM sumsales
    WHERE sumsales.sale_date <= dateinfo.date_val AND sumsales.sale_date >
    CAST(dateinfo.date_val AS DateTime) - 365) /
    (SELECT Sum(sumsales.sales_dol) sales_dol FROM sumsales
    WHERE sumsales.sale_date <= CAST(dateinfo.date_val AS DateTime) - 365 AND
    sumsales.sale_date > CAST(dateinfo.date_val AS DateTime) - 730) quotient
    FROM dateinfo
    WHERE CAST(dateinfo.date_val AS DateTime) <= GetDate() AND
    CAST(dateinfo.date_val AS DateTime) >= '01/01/2005'

    PS - I can rebuild the dateinfo table to use DateTime for date_val if you think its relevant ... or I can do something entirely different for the dates if its relevant to the issue at hand. Im open to any and all suggestions.
    Attached Files Attached Files

  4. #4
    Join Date
    Apr 2011
    Location
    Pakistan
    Posts
    28

    Why do you need a query

    you didnt join these Select queries because queries are running separate and worknig as a function,why do u need to make a query its just giving a result and then divide to another ,if is there any join between tables then you will join to the particular table then create a query

    is there any key to join these tables

    sumsales and dateinfo

    OtherWise you can execute this query also,I didnt check because I dont have table Def

    SELECT Sum(sumsales.sales_dol) sales_dol into #tmp1 FROM sumsales
    WHERE sumsales.sale_date <= dateinfo.date_val AND sumsales.sale_date >
    CAST(dateinfo.date_val AS DateTime) - 365

    SELECT Sum(sumsales.sales_dol) sales_dol into #tmp2 FROM sumsales
    WHERE sumsales.sale_date <= CAST(dateinfo.date_val AS DateTime) - 365 AND
    sumsales.sale_date > CAST(dateinfo.date_val AS DateTime) - 730

    SELECT
    (select top 1 sales_dol from #tmp1) / (select top 1 sales_dol from #tmp2) quotient
    FROM dateinfo
    WHERE CAST(dateinfo.date_val AS DateTime) <= GetDate() AND
    CAST(dateinfo.date_val AS DateTime) >= '01/01/2005'

    what is the use of this where clause

    WHERE CAST(dateinfo.date_val AS DateTime) <= GetDate() AND
    CAST(dateinfo.date_val AS DateTime) >= '01/01/2005'


    Regards,
    Syed Jahanzaib Bin Hassan
    MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    A|U|R|E|U|S – S|A|L|A|H

  5. #5
    Join Date
    Apr 2011
    Posts
    3
    The goal of this query is not to return a single row, but rather a record of summarized sales data for each day within the filter. IE, if the filter is:

    WHERE "Date" <= GetDate() AND "Date" >= '01/01/2005'

    ... then the query should calculate and return a quotient for each of the ~2300 days in that timeframe. I can then use that info to create graphs that show a high level view of the overall health of the business. Anything above 1 means that business is up, anything below 1 means that business is down (See attached).

    Ive done some additional benchmarking and the casting of the date field has absolutely zero overhead on the query. The issue is that Im (twice) doing a sum on a table and then executing that same procedure 2300 times. There are ecomomies of scale, but each years worth of data appears to add about 2-3 seconds to the query until it plateaus out around 6 years.

    There has to be a smarter way to do this beyond my basic sql knowledge. Its just a guess, but Im thinking maybe stored procedures or something of that ilk. If there dB already did the calculations and stored them somewhere, I could then simply provide the "where" and return everything instantly I would think?!?

    BTW - Im new here, so if this issue is beyond the scope of this newsgroup let me know.

    Thx
    Attached Thumbnails Attached Thumbnails moz-screenshot.jpg  

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bgoldstine View Post
    I can rebuild the dateinfo table to use DateTime for date_val if you think its relevant
    absolutely, yes it is relevant

    (curious what datatype it might be now)

    actually, DATE would be better than DATETIME (if you're on 2005+ which supports DATE)

    no, this question is not beyond the scope of this forum

    p.s. your zip file was "invalid or corrupted"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2011
    Location
    Pakistan
    Posts
    28
    The goal of this query is not to return a single row

    but query will return always a single record because of aggregate function is there and there is no extra column to group this sales,I mean group by clause

    Regards,

    Syed Jahanzaib Bin Hassan
    MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    A|U|R|E|U|S – S|A|L|A|H

  8. #8
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    SJahanzaib, look again. This query will produce as many rows as exist in the dateinfo table.

    Bgoldtine, I've been thinking about this all weekend and besides the basic fixes that r937 has suggested, I am stumped, except for the following possibilities . . .

    If daily sales_dol never changes retroactively then, once a quotient is calculated for a particular date, you never have to calculate it again. You could, therefore, write the results of this query to a permanent table and have your query only act to append records that do not exist in the permanent table, If you then run this on, say, a weekly basis, the query is only having to process for seven records rather the 7K records.

    Another thing you could do is to store the sales_dol in the permanent table and only recalculate if the sales_dol changed for a particular day. Then, if daily sales_dol do occassionally change retroactively, those dates would be recalculated (you would have to update those records in the permanent table, rather then inserting new ones).

    Bottom line, I see the solution to this problem in re-architecting the process rather than optimizing the query.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  9. #9
    Join Date
    Sep 2010
    Posts
    15
    hi bgoldstine

    Using join rather than subqueries ....

    Could you post sample data of the 2 tables and the output you expected?

    It is better to have sample data because people can test it before posting solution.

Posting Permissions

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