Need help optimizing the performance of a query

  • How can I change optimize query below to reduce the time it takes to run it against the table described below which has about 50 million records. It takes about 30 seconds to run it. I would like to reduce that time as much as possible.

    TABLE STRUCTURE

    ---------------------------------------------

    customer_id varchar(13) INDEXED

    product_name varchar(256)INDEXED

    product_code char(10) INDEXED

    paid_amount(decimal(18,2) INDEXED

    date_of_sale(datetime) INDEXED

    SAMPLE TABLE RECORDS

    --------------------------------------------

    customer_idproduct_nameproduct_codepaid_amountdate_of_sale

    12345DSEprod112345C666620.042010-01-01

    12345DSEprod112345C666621.042010-04-02

    34345WSFprod274545C213435.002010-01-02

    57345GRTprod274545C213455.002010-02-03

    76845FREprod5G4545T2554100.002011-06-03

    SAMPLE OUTPUT

    ------------------------------------------------

    ProductNameTotalPaidAmountTotalAllPaidAmountCustomersTotalAllCustomersTransactionCountTotalAllTransactionsdate_of_sale_year

    prod141.08131.0813242010

    prod290131.0823242010

  • What is the query that takes 30 seconds ... you didn't include it.

    Does your query pull every record from the database? Or is there a where clause you're not telling us about (by date or customer for example)

  • Please provide all index definitions and thte actual execution plan of the query you're strugglnig with.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • i ran the provided data thru my data formatting macro so we can have sample definitions, but we are still waiting for indexes and actual execution plan, which will tell the whole story, i think

    CREATE TABLE [dbo].[MYSAMPLEDATA] (

    [CUSTOMER_ID] VARCHAR(13) NULL,

    [PRODUCT_NAME] VARCHAR(256) NULL,

    [PRODUCT_CODE] CHAR(10) NULL,

    [PAID_AMOUNT] DECIMAL(18,2) NULL,

    [DATE_OF_SALE] DATETIME NULL)

    INSERT INTO MySampleData

    SELECT '12345DSE','prod1','12345C6666','20.04','2010-01-01' UNION ALL

    SELECT '12345DSE','prod1','12345C6666','21.04','2010-04-02' UNION ALL

    SELECT '34345WSF','prod2','74545C2134','35.00','2010-01-02' UNION ALL

    SELECT '57345GRT','prod2','74545C2134','55.00','2010-02-03' UNION ALL

    SELECT '76845FRE','prod5','G4545T2554','100.00','2011-06-03'

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • I apologize! I forgot to include the query

    QUERY

    ---------------------------------

    SELECT distinct product_name AS ProductName,

    (SELECT SUM(paid_amount)FROM transactions tr2 WHERE tr1.product_code = tr2.product_code) AS TotalPaidAmount,

    (SELECT SUM(paid_amount) FROM transactions WHERE date_of_sale >= '2010-01-01' AND date_of_sale < '2011-01-01' ) AS TotalAllPaidAmount,

    (SELECT COUNT(DISTINCT customer_id) FROM transactions tr3 WHERE tr1.product_code = tr3.product_code) AS Customers,

    (SELECT COUNT(DISTINCT customer_id) FROM transactions WHERE date_of_sale >= '2010-01-01' AND date_of_sale < '2011-01-01') AS TotalAllCustomers,

    (SELECT COUNT(customer_id) FROM transactions tr4 WHERE tr1.product_code = tr4.product_code) AS TransactionCount

    ,(SELECT COUNT( customer_id) FROM transactions) AS TotalAllTransactions

    FROM transactions tr1

    WHERE date_of_sale >= '2010-01-01' AND date_of_sale < '2011-01-01'

  • do you currently have indexes on this table? could you include them?

  • I have 5 non clustered index(s) on each of the following columns in ASC order

    customer_id varchar(13)

    product_name varchar(256)

    product_code char(10)

    paid_amount(decimal(18,2)

    date_of_sale(datetime)

  • Try something like this. You're doing two seperate calculations, might be better to split them.

    DECLARE @TotalAllPaidAmount INT

    DECLARE @TotalAllCustomers INT

    DECLARE @TotalTransactions INT

    SELECT @TotalAllPaidAmount = SUM(paid_amount), @TotalAllCustomers = COUNT(DISTINCT(customer_id)), @TotalTransactions = COUNT(*)

    FROM transactions

    WHERE date_of_sale >= '2010-01-01' AND date_of_sale < '2011-01-01'

    SELECT

    product_name AS ProductName,

    SUM(paid_amount) AS TotalPaidAmount,

    @TotalAllPaidAmount AS TotalAllPaidAmount,

    COUNT(DISTINCT(customer_id)) AS Customers,

    @TotalAllCustomers AS TotalAllCustomers,

    COUNT(customer_id) AS TransactionCount,

    @TotalTransactions AS TotalAllTransactions

    FROM transactions

    WHERE date_of_sale >= '2010-01-01' AND date_of_sale < '2011-01-01'

    GROUP BY product_name

  • is there a clustered index, maybe on customer_id? can you give us the actual CREATE TABLE definition so we don't have to guess?

    the actual execution plan, saved as a .sqlplan file and attached to teh thread would give us the most information...we could tell lots of things, like missing indexes, out of date statistics, and so much more.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Declare @Results Table (ProductName varchar(50) primary key, TotalPaidAmount decimal(22,2), TransactionCount int, Customers int)

    INSERT INTO @Results

    SELECT ProductName, SUM(paid_amount) AS TotalPaidAmount, count(*) as TransactionCount, distinct(customer_ID) as Customers

    FROM transactions tr1

    WHERE date_of_sale >= '2010-01-01' AND date_of_sale < '2011-01-01'

    GROUP BY ProductName WITH ROLLUP

    The overall totals will be in a row with NULL as a Product Name.

    To get the overalls totals on each row of your final results set, ou can either

    a) JOIN @results to itself

    b) Select totals into variables (where productname is null) and use the variables in the final query.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply