May 20, 2011 at 7:26 am
Happy Friday everyone!
I am working on an old ticket tracking system we have here. I had hoped the system would be rewritten and we could do a redsign but alas it is not in the cards right now. So instead I am working on trying to imrpove existing reports so they finish in a reasonable amount of time. Below is a small query that takes about 12 seconds to return 1,614,890 rows. Before I started the Query used three seperate UDF's which made the query run for over five minutes. while 12 seconds is good I still feal like it could be better but I may just be expecting to much so I thought I would ask the experts. The execution plan is a merge and a couple of idex scans but nothing shocking.
Query
select
t.ticket_id
,isnull(sum(tp.payment_amount),0) as totalPayments
from ticket t
inner join payment_to_fine tp
on t.ticket_id = tp.ticket_id
group by t.ticket_id
DDL's
USE [TTS_NI]
GO
/****** Object: Table [dbo].[ticket] Script Date: 05/20/2011 08:25:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ticket](
[ticket_id] [int] IDENTITY(1,1) NOT NULL,
[citation_number] [bigint] NOT NULL,
[parking_meter_id] [int] NOT NULL CONSTRAINT [DF_ticket_parking_meter_id] DEFAULT (1),
[location_id] [int] NOT NULL CONSTRAINT [DF_ticket_parking_location_id] DEFAULT (1),
[violation_code_id] [int] NOT NULL CONSTRAINT [DF_ticket_inactive_violation_code_id] DEFAULT (1),
[officer_id] [int] NOT NULL CONSTRAINT [DF_ticket_officer_id] DEFAULT (1),
[enforcement_group_id] [int] NOT NULL CONSTRAINT [DF_ticket_inactive_enforcement_group_id] DEFAULT (1),
[entered_by_tts_user_id] [int] NOT NULL,
[validated_enter_tts_user_id] [int] NULL,
[lic_expiration_year] [int] NOT NULL CONSTRAINT [DF_ticket_lic_expiration_year] DEFAULT ((-9999)),
[lic_expiration_month] [int] NOT NULL CONSTRAINT [DF_ticket_lic_expiration_month] DEFAULT ((-9999)),
[lic_plate_desc_id] [int] NOT NULL CONSTRAINT [DF_ticket_lic_plate_desc_id] DEFAULT (1),
[inactive_flag] [bit] NOT NULL CONSTRAINT [DF_ticket_inactive_flag] DEFAULT (0),
[validated_flag] [bit] NOT NULL CONSTRAINT [DF_ticket_ticket_validated_flag] DEFAULT (0),
[closed_flag] [bit] NOT NULL CONSTRAINT [DF_ticket_closed_flag] DEFAULT (0),
[del_notice_sent] [bit] NOT NULL CONSTRAINT [DF_ticket_del_notice_sent] DEFAULT (0),
[issued_date] [datetime] NOT NULL CONSTRAINT [DF_ticket_issued_date] DEFAULT (getdate()),
[due_date] [datetime] NULL,
[insert_stamp] [datetime] NOT NULL CONSTRAINT [DF_ticket_insert_stamp] DEFAULT (getdate()),
[hearing_docket_number] [varchar](10) NULL,
[location_description] [varchar](250) NOT NULL,
[del_notice_sent_calendar_bd_id] [int] NULL,
CONSTRAINT [PK_ticket] PRIMARY KEY CLUSTERED
(
[ticket_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],
CONSTRAINT [IX_ticket_citation_number] UNIQUE NONCLUSTERED
(
[ticket_id] ASC,
[citation_number] ASC,
[issued_date] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [SECONDARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[ticket] WITH NOCHECK ADD CONSTRAINT [FK_ticket_enforcement_group] FOREIGN KEY([enforcement_group_id])
REFERENCES [dbo].[enforcement_group] ([enforcement_group_id])
GO
ALTER TABLE [dbo].[ticket] CHECK CONSTRAINT [FK_ticket_enforcement_group]
GO
ALTER TABLE [dbo].[ticket] WITH NOCHECK ADD CONSTRAINT [FK_ticket_officer] FOREIGN KEY([officer_id])
REFERENCES [dbo].[officer] ([officer_id])
GO
ALTER TABLE [dbo].[ticket] CHECK CONSTRAINT [FK_ticket_officer]
GO
ALTER TABLE [dbo].[ticket] WITH NOCHECK ADD CONSTRAINT [FK_ticket_system_calendar_bd] FOREIGN KEY([del_notice_sent_calendar_bd_id])
REFERENCES [dbo].[system_calendar_bd] ([calendar_bd_id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[ticket] CHECK CONSTRAINT [FK_ticket_system_calendar_bd]
GO
ALTER TABLE [dbo].[ticket] WITH NOCHECK ADD CONSTRAINT [FK_ticket_ticket_parking_meter] FOREIGN KEY([parking_meter_id])
REFERENCES [dbo].[ticket_parking_meter] ([parking_meter_id])
GO
ALTER TABLE [dbo].[ticket] CHECK CONSTRAINT [FK_ticket_ticket_parking_meter]
GO
ALTER TABLE [dbo].[ticket] WITH NOCHECK ADD CONSTRAINT [FK_ticket_ticket_violation_lkup] FOREIGN KEY([violation_code_id])
REFERENCES [dbo].[ticket_violation_lkup] ([violation_code_id])
GO
ALTER TABLE [dbo].[ticket] CHECK CONSTRAINT [FK_ticket_ticket_violation_lkup]
GO
ALTER TABLE [dbo].[ticket] WITH NOCHECK ADD CONSTRAINT [FK_ticket_tts_user_entered] FOREIGN KEY([entered_by_tts_user_id])
REFERENCES [dbo].[tts_user] ([tts_user_id])
GO
ALTER TABLE [dbo].[ticket] CHECK CONSTRAINT [FK_ticket_tts_user_entered]
GO
ALTER TABLE [dbo].[ticket] WITH NOCHECK ADD CONSTRAINT [FK_ticket_tts_user_validated] FOREIGN KEY([validated_enter_tts_user_id])
REFERENCES [dbo].[tts_user] ([tts_user_id])
GO
ALTER TABLE [dbo].[ticket] CHECK CONSTRAINT [FK_ticket_tts_user_validated]
GO
ALTER TABLE [dbo].[ticket] WITH NOCHECK ADD CONSTRAINT [FK_ticket_vehicle_lic_plate_desc_lkup] FOREIGN KEY([lic_plate_desc_id])
REFERENCES [dbo].[vehicle_lic_plate_desc_lkup] ([lic_plate_desc_id])
GO
ALTER TABLE [dbo].[ticket] CHECK CONSTRAINT [FK_ticket_vehicle_lic_plate_desc_lkup]
USE [TTS_NI]
GO
/****** Object: Table [dbo].[payment_to_fine] Script Date: 05/20/2011 08:25:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[payment_to_fine](
[payment_to_fine_id] [int] IDENTITY(1,1) NOT NULL,
[payment_credit_transaction_id] [int] NOT NULL CONSTRAINT [DF_payment_to_fine_payment_credit_transaction_id] DEFAULT (1),
[fine_penalty_fee_transaction_id] [int] NOT NULL CONSTRAINT [DF_payment_to_fine_fine_penalty_fee_transaction_id] DEFAULT (1),
[ticket_status_type_id] [int] NOT NULL CONSTRAINT [DF_payment_to_fine_ticket_status_type_id] DEFAULT (1),
[ticket_id] [int] NOT NULL CONSTRAINT [DF_payment_to_fine_ticket_id] DEFAULT (1),
[calendar_bd_id] [int] NOT NULL CONSTRAINT [DF_payment_to_fine_calendar_bd_id] DEFAULT (1),
[payment_amount] [money] NOT NULL,
[insert_stamp] [datetime] NOT NULL CONSTRAINT [DF_payment_to_fine_insert_stamp] DEFAULT (getdate()),
CONSTRAINT [PK_payment_to_fine] PRIMARY KEY CLUSTERED
(
[payment_to_fine_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],
CONSTRAINT [IX_payment_to_fine_pct_pfpft_ticket] UNIQUE NONCLUSTERED
(
[payment_to_fine_id] ASC,
[payment_credit_transaction_id] ASC,
[ticket_id] ASC,
[insert_stamp] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [SECONDARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[payment_to_fine] WITH CHECK ADD CONSTRAINT [FK_payment_to_fine_payment_credit_transaction] FOREIGN KEY([payment_credit_transaction_id])
REFERENCES [dbo].[payment_credit_transaction] ([payment_credit_transaction_id])
GO
ALTER TABLE [dbo].[payment_to_fine] CHECK CONSTRAINT [FK_payment_to_fine_payment_credit_transaction]
GO
ALTER TABLE [dbo].[payment_to_fine] WITH CHECK ADD CONSTRAINT [FK_payment_to_fine_payment_fine_penalty_fee_trans] FOREIGN KEY([fine_penalty_fee_transaction_id])
REFERENCES [dbo].[payment_fine_penalty_fee_trans] ([fine_penalty_fee_transaction_id])
GO
ALTER TABLE [dbo].[payment_to_fine] CHECK CONSTRAINT [FK_payment_to_fine_payment_fine_penalty_fee_trans]
GO
ALTER TABLE [dbo].[payment_to_fine] WITH CHECK ADD CONSTRAINT [FK_payment_to_fine_payment_system_calandar_bd] FOREIGN KEY([calendar_bd_id])
REFERENCES [dbo].[system_calendar_bd] ([calendar_bd_id])
GO
ALTER TABLE [dbo].[payment_to_fine] CHECK CONSTRAINT [FK_payment_to_fine_payment_system_calandar_bd]
GO
ALTER TABLE [dbo].[payment_to_fine] WITH NOCHECK ADD CONSTRAINT [FK_payment_to_fine_ticket] FOREIGN KEY([ticket_id])
REFERENCES [dbo].[ticket] ([ticket_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[payment_to_fine] CHECK CONSTRAINT [FK_payment_to_fine_ticket]
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
May 20, 2011 at 7:31 am
Can you post the actual plan and how much data is in each table (rows and pages).
May 20, 2011 at 7:49 am
Here is the plan and the table stats
Ticket Table
Rows 1764750
Payment to fine
Rows 2447146
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
May 20, 2011 at 7:57 am
Any reasons why there's no filter on dates?? Other than tuning the db setup of hd I don't see much you can do to tune this. It's stilla good chunk of data to process.
May 20, 2011 at 8:02 am
The report is for all outstanding balances. The people who designed this DB took normalization to the extreme this unfortunatley means that a simple matter of a balance is not recorded anywhere but rather has to be calculated. This is actualy just one part of the puzzle but it is the larger part (Time wise). I did not think there was much I could do but I thought it was worth checking to see if I have overlooked something.
Thanks for the responce!
Have a Great Weekend!
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
May 20, 2011 at 8:04 am
I dont' see an index on the ticket_id column of payment_to_fine table. Unless I missed it.
CREATE INDEX IX_YourindexName ON dbo.payment_to_fine(ticket_id) INCLUDE(payment_amount)
May 20, 2011 at 8:05 am
Dan
Although I suspect it won't make any difference to performance, I don't think you need the ISNULL, since SUM ignores NULLs.
John
May 20, 2011 at 8:07 am
John Mitchell-245523 (5/20/2011)
DanAlthough I suspect it won't make any difference to performance, I don't think you need the ISNULL, since SUM ignores NULLs.
John
I was thinking the same thing, but since he's not using a left join here I'm assuming that the payment rows are pre-created with nulls on the payment...
and >
select sum(1 / null)
returns NULL.
May 20, 2011 at 8:45 am
This is one of those DBs that make you want to bang you head on the wall. I do the isnull becuase there are records out there that are in the payments but are not actually payments there projections. I tried to understand it once and it made my head hurt to I just accepted it. Anyway as I said before this is but a small part of the overall query and I can not have a ticket total return zero or it will cause me problems later down the road.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
May 20, 2011 at 8:50 am
Dan.Humphries (5/20/2011)
This is one of those DBs that make you want to bang you head on the wall. I do the isnull becuase there are records out there that are in the payments but are not actually payments there projections. I tried to understand it once and it made my head hurt to I just accepted it. Anyway as I said before this is but a small part of the overall query and I can not have a ticket total return zero or it will cause me problems later down the road.
Ok now I get the Avatar :w00t:.
May 22, 2011 at 5:11 pm
How about calculate the summary first (to the payment table), then inner join to the ticket table?
I am not sure about this.
I think by this design it's a little bit hard to improve the performance. Unless you do some extra works like indexed view,or denormalization (save the result to the ticket table),...
May 23, 2011 at 8:34 am
Perhaps you can override the designers intentions, and provide a total payments column, calculated with triggers.
I'd expect a performance improvement if you drop the SUM calculation.
May 23, 2011 at 8:41 am
ofirk (5/23/2011)
Perhaps you can override the designers intentions, and provide a total payments column, calculated with triggers.I'd expect a performance improvement if you drop the SUM calculation.
Not sure I'd go down that road. Best case scenario is the shave off 0.5-1.5 second on 1 report. Not sure it's worth all the troubles to "fix" something that's not really broken...
triggers have a perf downside too you know. And since I expect this to be very transactionnal, I'm really not sure this is a good trade off.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply