October 5, 2014 at 6:12 pm
Sorry, I realised I hadn't provided related data for the example tables so I removed the post. It's back now with some data.
This query is running very slowly. The real tables are large (millions of records). I'd be very grateful if anyone can suggest an how to approach improving performance. I'm thinking the query currently in the left outer join could be nested in the case statement.
Script for tables below the query.
DECLARE @QDATE datetime;
SET @QDATE = '2012-12-01';
select
ac.acc_id,
ac.cus_id,
CASE
WHEN ac.ent_date < @QDATE
THEN ISNULL(ac.acc_balance, 0)
ELSE
ISNULL(rc.rec_bal, 0)
END as balance
from
tblAccount ac
LEFT OUTER JOIN
( select
rc1.acc_id,
rc1.rec_bal,
rc1.rec_date
from tblRecord rc1
INNER JOIN
(select rc2.acc_id, max(rc2.rec_id) as maxrecid
from tblRecord rc2
where rc2.rec_date < @QDATE
group by rc2.acc_id) as rc3
ON rc1.acc_id = rc3.acc_id
and rc1.rec_id = rc3.maxrecid ) as rc
ON ac.acc_id = rc.acc_id
CREATE TABLE [dbo].[tblAccount](
[acc_id] [int] NOT NULL,
[cus_id] [int] NULL,
[ent_date] [datetime] NULL,
[acc_balance] [decimal](12, 2) NULL
) ON [PRIMARY]
GO
INSERT INTO tblAccount values ( 90 , 42 ,'2012-10-01 14:27:53.000', 399 );
INSERT INTO tblAccount values ( 69 , 20 ,'2012-07-13 07:00:05.000', 136 );
INSERT INTO tblAccount values ( 86 , 38 ,'2012-06-14 02:32:38.000', 84 );
INSERT INTO tblAccount values ( 89 , 41 ,'2012-04-03 18:41:54.000', 658 );
INSERT INTO tblAccount values ( 59 , 10 ,'2012-03-28 00:05:56.000', 687 );
INSERT INTO tblAccount values ( 88 , 40 ,'2012-03-14 03:26:42.000', 203 );
INSERT INTO tblAccount values ( 87 , 39 ,'2012-03-14 00:31:37.000', 187 );
INSERT INTO tblAccount values ( 84 , 36 ,'2012-03-13 06:16:09.000', 245 );
INSERT INTO tblAccount values ( 54 , 5 ,'2012-03-08 19:16:50.000', 485 );
INSERT INTO tblAccount values ( 19 , 70 ,'2012-02-28 23:53:59.000', 235 );
INSERT INTO tblAccount values ( 85 , 37 ,'2012-02-23 21:50:49.000', 594 );
INSERT INTO tblAccount values ( 83 , 35 ,'2012-02-15 00:05:03.000', 406 );
INSERT INTO tblAccount values ( 82 , 34 ,'2012-02-14 20:57:05.000', 568 );
INSERT INTO tblAccount values ( 75 , 27 ,'2012-02-14 20:49:16.000', 169 );
INSERT INTO tblAccount values ( 81 , 33 ,'2012-02-07 22:20:35.000', 552 );
CREATE TABLE [dbo].[tblRecord](
[rec_id] [int] NOT NULL,
[rec_date] [datetime] NULL,
[rec_bal] [decimal](12, 2) NULL,
[acc_id] [int] NULL
) ON [PRIMARY]
GO
INSERT into tblRecord values( 44,'2012-09-24 04:30:02.000' ,395 , 81 );
INSERT into tblRecord values( 43,'2012-09-18 09:35:32.000' ,645 , 82 );
INSERT into tblRecord values( 42,'2012-09-03 04:30:28.000' ,679 , 83 );
INSERT into tblRecord values( 40,'2012-08-31 04:29:54.000' ,294 , 84 );
INSERT into tblRecord values( 41,'2012-08-31 04:29:54.000' ,24 , 85 );
INSERT into tblRecord values( 39,'2012-08-31 04:29:53.000' ,311 , 86 );
INSERT into tblRecord values( 38,'2012-08-18 04:28:06.000' ,635 , 75 );
INSERT into tblRecord values( 37,'2012-08-14 13:19:33.000' ,396 , 20 );
INSERT into tblRecord values( 36,'2012-08-09 04:28:11.000' ,161 , 87 );
INSERT into tblRecord values( 35,'2012-08-05 04:28:13.000' ,505 , 88 );
INSERT into tblRecord values( 44,'2012-07-20 04:28:23.000' ,122 , 90 );
INSERT into tblRecord values( 33,'2012-07-15 04:28:26.000' ,681 , 72 );
INSERT into tblRecord values( 32,'2012-07-01 04:28:37.000' ,142 , 54 );
INSERT into tblRecord values( 31,'2012-06-14 04:28:46.000' ,482 , 91 );
INSERT into tblRecord values( 29,'2012-06-13 17:49:17.000' ,234 , 81 );
October 6, 2014 at 6:05 am
Index definitions and execution plan 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
October 6, 2014 at 6:49 am
Several alternatives to the query you've posted are available, and posting the execution plan as Gail suggests will probably help to determine which might be best. Here's one of them for the record:
SELECT
ac.acc_id,
ac.cus_id,
CASE
WHEN ac.ent_date < @QDATE THEN ISNULL(ac.acc_balance, 0)
ELSE ISNULL(rc.rec_bal, 0)
END as balance
FROM tblAccount ac
OUTER APPLY (
SELECT TOP 1 r.rec_bal
FROM tblRecord r
WHERE ac.acc_id = r.acc_id
AND r.rec_date < @QDATE
ORDER BY r.rec_id DESC
) rc
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 6, 2014 at 7:15 am
GilaMonster (10/6/2014)
Index definitions and execution plan please.
Sorry, forgot this. Please see below.
Execution plan - I tried to paste this in but I keep getting code replaced with smileys:
|--Compute Scalar(DEFINE:([Expr1009]=CASE WHEN [dbo].[tblAccount].[ent_date] as [ac].[ent_date]<[@QDATE] THEN isnull([dbo].[tblAccount].[acc_balance] as [ac].[acc_balance],(0.00)) ELSE isnull([Expr1008],(0.00)) END))
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([ac].[acc_id]))
|--Clustered Index Scan(OBJECT:([dbo].[tblAccount].[PK_tblAccount] AS [ac]))
|--Compute Scalar(DEFINE:([Expr1008]=[dbo].[tblRecord].[rec_bal] as [rc1].[rec_bal]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1006]))
|--Stream Aggregate(DEFINE:([Expr1006]=MAX([dbo].[tblRecord].[rec_id] as [rc2].[rec_id])))
| |--Clustered Index Scan(OBJECT:([dbo].[tblRecord].[PK_tblRecord] AS [rc2]), WHERE:([dbo].[tblRecord].[acc_id] as [rc2].[acc_id]=[dbo].[tblAccount].[acc_id] as [ac].[acc_id] AND [dbo].[tblRecord].[rec_date] as [rc2].[rec_date]<[@QDATE]))
|--Clustered Index Seek(OBJECT:([dbo].[tblRecord].[PK_tblRecord] AS [rc1]), SEEK:([rc1].[rec_id]=[Expr1006]), WHERE:([dbo].[tblAccount].[acc_id] as [ac].[acc_id]=[dbo].[tblRecord].[acc_id] as [rc1].[acc_id]) ORDERED FORWARD)
Indexes:
ALTER TABLE [dbo].[tblAccount] ADD CONSTRAINT [PK_tblAccount] PRIMARY KEY CLUSTERED
(
[acc_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblRecord] ADD CONSTRAINT [PK_tblRecord] PRIMARY KEY CLUSTERED
(
[rec_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
October 6, 2014 at 7:21 am
Text plan? You have a SQL 2000 server here?
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
October 6, 2014 at 8:51 am
No, I have the XML, just trying to get it to a place where I can upload it - pasting text is easier. I'll try and get that uploaded.
October 6, 2014 at 9:06 am
Actual plan please, not estimated. The text plans are near useless because they're missing everything except the operator. Plus they're a pain to read.
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
October 6, 2014 at 4:02 pm
Suggested query below. If you're willing to add a nonclustered index, you can try the affect of adding the index below before running the query. I can't tell for sure if that index definition is best, since I don't know the cardinality of the data, but it should help in any case.
/*
CREATE NONCLUSTERED INDEX tblRecord__IX_acc_id
ON dbo.tblRecord ( rec_date, acc_id, rec_id ) INCLUDE ( rec_bal )
*/
select
ac.acc_id,
ac.cus_id,
CASE
WHEN ac.ent_date < @QDATE
THEN ISNULL(ac.acc_balance, 0)
ELSE
ISNULL(rc.rec_bal, 0)
END as balance
from
tblAccount ac
LEFT OUTER JOIN
( select
rc1.acc_id,
rc1.rec_bal,
rc1.rec_date,
ROW_NUMBER() over(PARTITION BY rc1.acc_id ORDER BY rec_id DESC) AS row_num
from tblRecord rc1
where rc1.rec_date < @QDATE
) AS rc
ON ac.acc_id = rc.acc_id and rc.row_num = 1
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 16, 2014 at 5:54 am
I wasn't able to get the actual EP from the production system for this, but just wanted to say thank you for the responses. Both suggested solutions were really helpful.
Thanks again!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply