July 12, 2011 at 10:25 am
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
July 12, 2011 at 10:35 am
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)
July 12, 2011 at 10:43 am
Please provide all index definitions and thte actual execution plan of the query you're strugglnig with.
July 12, 2011 at 10:47 am
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
July 12, 2011 at 10:56 am
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'
July 12, 2011 at 11:16 am
do you currently have indexes on this table? could you include them?
July 12, 2011 at 11:20 am
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)
July 12, 2011 at 11:29 am
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
July 12, 2011 at 11:30 am
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
July 12, 2011 at 1:56 pm
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