March 15, 2012 at 12:48 pm
Hi,
I am not expert in SQL Query and bit struggling with a query that is taking more time to execute than my expectation. I am not sure whether it will be possible to rewrite the query in different way producing same results but will take much less time. Any help will be appreciated. The following is the query:
SELECT * FROM
(
SELECT T1.CODE AS ACCOUNTREF, T1.TRANDATE AS DATE,
CASE T1.TransactionType WHEN 'B' THEN 'Buy' ELSE 'Sale' END AS Type,
INST.NAME AS DESCRIPTION,
SUM(CASE T1.TransactionType WHEN 'B' THEN T1.NetAmount ELSE 0 END) AS Debit,
SUM(CASE T1.TransactionType WHEN 'S' THEN T1.NetAmount ELSE 0 END) AS Credit
FROM T1, INST
WHERE
T1.TRANDATE >= @FROMDATE AND T1.TRANDATE <= @TODATE AND
T1.CODE = @ACCOUNTCODE AND T1.STATUS = 'Posted' AND
T1.INSTREF = INST.REFERENCE
GROUP BY
T1.CODE, T1.TRANDATE, T1.TRANSACTIONTYPE, INST.NAME
UNION ALL
SELECT ACCOUNTREF, DATE,
Type,
DESCRIPTION,
Debit,
Credit
FROM
(
SELECT T2.CODE AS ACCOUNTREF, T2.TRANDATE AS DATE,
CASE TRANTYPES.TransactionType WHEN 'P' THEN 'Payment' ELSE 'Receipt' END AS Type,
TRANTYPES.DESC AS DESCRIPTION,
SUM(CASE TRANTYPES.TransactionType WHEN 'P' THEN T2.NetAmount ELSE 0 END) AS Debit,
SUM(CASE TRANTYPES.TransactionType WHEN 'R' THEN T2.NetAmount ELSE 0 END) AS Credit
FROM T2, TRANTYPES
WHERE
T2.TRANDATE >= @FROMDATE AND T2.TRANDATE <= @TODATE AND
T2.CODE = @ACCOUNTCODE AND T2.STATUS NOT IN('Pending', 'Request', 'Rejected') AND IsReversal='False' AND
T2.TRANTYPE = TRANTYPES.REFERENCE AND
TRANTYPES.TransactionType <> ''
GROUP BY
T2.CODE, T2.TRANDATE, TRANTYPES.TRANSACTIONTYPE, TRANTYPES.DESC
UNION ALL
SELECT T2.CODE AS ACCOUNTREF, T2.TRANDATE AS DATE,
CASE TRANTYPES.TransactionType WHEN 'P' THEN 'Receipt' ELSE 'Payment' END AS Type,
TRANTYPES.DESC AS DESCRIPTION,
SUM(CASE TRANTYPES.TransactionType WHEN 'R' THEN T2.NetAmount ELSE 0 END) AS Debit,
SUM(CASE TRANTYPES.TransactionType WHEN 'P' THEN T2.NetAmount ELSE 0 END) AS Credit
FROM T2, TRANTYPES
WHERE
T2.TRANDATE >= @FROMDATE AND T2.TRANDATE <= @TODATE AND
T2.CODE = @ACCOUNTCODE AND T2.STATUS NOT IN('Pending', 'Request', 'Rejected') AND IsReversal='True' AND
T2.TRANTYPE= TRANTYPES.REFERENCE AND
TRANTYPES.TransactionType <> ''
GROUP BY
T2.CODE, T2.TRANDATE, TRANTYPES.TRANSACTIONTYPE, TRANTYPES.DESC
) as T3
) AS LEDGER_DETAILS
ORDER BY DATE, DESCRIPTION
*** DATA VOLUME***
T1 = 40,00,000 RECORDS WITH 20,000 TO 50,000 NEW INSERTS AND 40-50 UPDATES EVERYDAY
T2 = 15,00,000 RECORDS WITH 5,000 TO 10,000 NEW INSERTS AND 60-100 UPDATES EVERYDAY
INST = 278 RECORDS. 3-5 NEW INSERTS EVERY YEAR
TRANTYPES = 95 RECORDS. FIXED.
THE TABLE T1, T2, INST AND TRANTYPES HAS A COLUMN "REFERENCE" WHICH IS THE PRIMARY KEY OF THE RESPECTIVE TABLE.
*** PERFORMANCE ****
IT TAKES MORE THAN 3 MINUTES TO EXECUTE THE ABOVE QUERY, USING THE FOLLOWING:
PROCESSOR: INTEL CORE 2 DUO 2.2 GHZ
RAM: 2 GB
SQL SERVER: VERSION 2005 SP1 DEVELOPER EDITION
IS THERE ANYWAY TO IMPROVE THE PERFORMANCE OF THE ABOVE QUERY PRODUCING SAME RESULTS?
BEST REGARDS
March 15, 2012 at 1:38 pm
Are you familiar with reading execution plans? That's where you'll need to start.
If you aren't familiar with them:
In Management Studio, click the option in the toolbar for "Include Actual Execution Plan" (looks like 3 small green squares arranged in an upside-down L).
Run the query.
Go to the Execution plan tab (after Results and Messages).
Right-click and choose "Save Execution Plan as ..." and save it as an XML file.
Attach that file to this forum thread ("Edit Attachments" button at the bottom of the page when you post or reply).
Then we can take a look at what the query is doing, and offer some detailed help on it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 15, 2012 at 2:18 pm
I see things in the code that may be forcing table scans. The use of NOT IN and <> in the WHERE clauses of a couple of the UNION ALL queries.
March 16, 2012 at 4:01 am
Hi,
Thanks for the reply.
Please find the attached Execution Plan in sqlplan format (its not permitting me to upload xml file), and the original query.
It took more than 4 min just to return 33 records! Total records in both T1 and T2 is approx 4 million. It almost freezed my computer while executing the query.
March 16, 2012 at 4:20 am
Table definitions and index definitions please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 16, 2012 at 4:23 am
Lynn Pettis (3/15/2012)
I see things in the code that may be forcing table scans. The use of NOT IN and <> in the WHERE clauses of a couple of the UNION ALL queries.
Ok. So, what do you suggest?
March 16, 2012 at 4:27 am
I recommend to start with create the following indexes:
Table Trade: InvestorACRef, Status, TradeDate, include NetAmount, Instrument, TransactionType
Table TransactionHead: AccountRef, IsReversal, TradeDate Include Status, NetAmount, TRansactionType
That should get you a major improvement.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 16, 2012 at 4:28 am
GilaMonster (3/16/2012)
Table definitions and index definitions please?
CREATE TABLE [dbo].[Trade](
[NetAmount] [decimal](18, 4) NULL DEFAULT ((0.00)),
[Instrument] [varchar](50) NULL DEFAULT (''),
[Status] [varchar](max) NULL DEFAULT (''),
[TransactionDate] [datetime] NULL DEFAULT (getdate()),
[Rate] [decimal](18, 4) NULL DEFAULT ((0.00)),
[IsException] [bit] NULL DEFAULT ((0)),
[Commission] [decimal](18, 4) NULL DEFAULT ((0.00)),
[Tax] [decimal](18, 4) NULL DEFAULT ((0.00)),
[FileName] [varchar](max) NULL DEFAULT (''),
[InvestorPayable] [decimal](18, 4) NULL DEFAULT ((0.00)),
[Laga] [decimal](18, 4) NULL DEFAULT ((0.00)),
[TransactionType] [varchar](max) NULL DEFAULT (''),
[Reference] [varchar](50) NOT NULL,
[TotalAmount] [decimal](18, 4) NULL DEFAULT ((0.00)),
[InvestorACRef] [varchar](max) NULL DEFAULT (''),
[Code] [varchar](max) NULL DEFAULT (''),
[BORN] [int] NULL DEFAULT ((0)),
[FillType] [varchar](2) NULL DEFAULT (''),
[MaturedDate] [datetime] NULL,
CONSTRAINT [PK_Trade_ID] PRIMARY KEY CLUSTERED
(
[Reference] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
The above table is aliased as T1 in my query. The table T2 has similar schema and index definition like above.
March 16, 2012 at 4:29 am
So in other words you have no indexes. No wonder it's slow. See the recommendations that I made in an earlier post
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply