question

Mukesh_Kumar avatar image
Mukesh_Kumar asked

How to create a view for table having more then million row

Hello Friends,

I have two table, one is master and another is transaction. Transaction table having more then million rows and continuously added approx. 10k rows every days. Now I'm facing performance issue with report. please find below schema and sample data

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Chargedetails](
[chargetypeid] [int] NULL,
[cnmt] [varchar](10) NULL,
[amount] [decimal](12, 2) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[ChargeType] Script Date: 02-03-2020 08:40:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ChargeType](
[chargetypeid] [int] NULL,
[chargetype] [varchar](10) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Chargedetails] ([chargetypeid], [cnmt], [amount]) VALUES (1, N'10001', CAST(25.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Chargedetails] ([chargetypeid], [cnmt], [amount]) VALUES (2, N'10001', CAST(10.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Chargedetails] ([chargetypeid], [cnmt], [amount]) VALUES (3, N'10001', CAST(75.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Chargedetails] ([chargetypeid], [cnmt], [amount]) VALUES (4, N'10001', CAST(110.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Chargedetails] ([chargetypeid], [cnmt], [amount]) VALUES (5, N'10001', CAST(20.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Chargedetails] ([chargetypeid], [cnmt], [amount]) VALUES (1, N'10002', CAST(25.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Chargedetails] ([chargetypeid], [cnmt], [amount]) VALUES (2, N'10002', CAST(10.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Chargedetails] ([chargetypeid], [cnmt], [amount]) VALUES (3, N'10002', CAST(75.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Chargedetails] ([chargetypeid], [cnmt], [amount]) VALUES (4, N'10002', CAST(110.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Chargedetails] ([chargetypeid], [cnmt], [amount]) VALUES (1, N'10003', CAST(25.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Chargedetails] ([chargetypeid], [cnmt], [amount]) VALUES (2, N'10003', CAST(10.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Chargedetails] ([chargetypeid], [cnmt], [amount]) VALUES (3, N'10003', CAST(75.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Chargedetails] ([chargetypeid], [cnmt], [amount]) VALUES (4, N'10003', CAST(110.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Chargedetails] ([chargetypeid], [cnmt], [amount]) VALUES (5, N'10003', CAST(20.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Chargedetails] ([chargetypeid], [cnmt], [amount]) VALUES (1, N'10004', CAST(25.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Chargedetails] ([chargetypeid], [cnmt], [amount]) VALUES (2, N'10004', CAST(10.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Chargedetails] ([chargetypeid], [cnmt], [amount]) VALUES (3, N'10005', CAST(75.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Chargedetails] ([chargetypeid], [cnmt], [amount]) VALUES (4, N'10005', CAST(110.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Chargedetails] ([chargetypeid], [cnmt], [amount]) VALUES (5, N'10004', CAST(20.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[ChargeType] ([chargetypeid], [chargetype]) VALUES (1, N'BC')
GO
INSERT [dbo].[ChargeType] ([chargetypeid], [chargetype]) VALUES (2, N'LC')
GO
INSERT [dbo].[ChargeType] ([chargetypeid], [chargetype]) VALUES (3, N'RC')
GO
INSERT [dbo].[ChargeType] ([chargetypeid], [chargetype]) VALUES (4, N'OSC')
GO
INSERT [dbo].[ChargeType] ([chargetypeid], [chargetype]) VALUES (5, N'DC')
GO

and query which I used for generate any report

with cn (cnmt)
as (select distinct cnmt from Chargedetails)
select cnmt, isnull((select amount from Chargedetails where cnmt = a.cnmt and chargetypeid = 1),0) bc ,
isnull((select amount from Chargedetails where cnmt = a.cnmt and chargetypeid = 2),0) Lc ,
isnull((select amount from Chargedetails where cnmt = a.cnmt and chargetypeid = 3),0) RC,
isnull((select amount from Chargedetails where cnmt = a.cnmt and chargetypeid = 4),0) Osc,
isnull((select amount from Chargedetails where cnmt = a.cnmt and chargetypeid = 5),0) Dc
from cn a

actually I want create a view like below structure so that I can generate report whenever we need with best performance and join with other transaction table having unique cnmt record with some other details.

cnmt bc Lc RC Osc Dc 10001 25 10 75 110 20 10002 25 10 75 110 0 10003 25 10 75 110 20 10004 25 10 0 0 20 10005 0 0 75 110 0

kindly suggest, Is schema level changes will give us best performance then suggest.

Regards

Mukesh

views
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·

Do you have any indexes on the table(s)?

0 Likes 0 ·

1 Answer

·
mksbw avatar image
mksbw answered

@ThomasRushton Yes. Schema like this

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Chargedetails](
[ChargeDetailID] [int] IDENTITY(1,1) NOT NULL,
[chargetypeid] [int] NULL,
[cnmt] [varchar](10) NULL,
[amount] [decimal](12, 2) NULL,
CONSTRAINT [PK_Chargedetailid] PRIMARY KEY CLUSTERED
(
[ChargeDetailID] 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
/****** Object: Table [dbo].[ChargeType] Script Date: 03-03-2020 08:23:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ChargeType](
[chargetypeid] [int] NOT NULL,
[chargetype] [varchar](10) NULL,
CONSTRAINT [PK_chargetypeid] PRIMARY KEY CLUSTERED
(
[chargetypeid] 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
ALTER TABLE [dbo].[Chargedetails] WITH CHECK ADD FOREIGN KEY([chargetypeid])
REFERENCES [dbo].[ChargeType] ([chargetypeid])
GO

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.