Click here to Skip to main content
14,697,418 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Problem

I work on SQL server 2012 I have table have 300 hundred million rows

when select from table any records it is too much heavy

I need any way can enhance performance of table at least if i select small amount of data as 1000 rows

can be access quickly and not take too much time

so if there are any idea i can accept to access this table quickly

because when select data it is hanging and take too much time to show data .

so how to do that please ?

What I have tried:

CREATE TABLE [Parts].[Nop_Part](
	[PartID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[PartNumber] [nvarchar](70) NOT NULL,
	[PartNumberNon] [nvarchar](60) NOT NULL,
	[CompanyID] [int] NOT NULL,
	[Active] [bit] NOT NULL,
	[Deleted] [bit] NOT NULL,
	[PartsFamilyID] [int] NOT NULL,
	[PackageID] [int] NULL,
	[PinOutId] [int] NOT NULL,
	[GroupID] [bigint] NULL,
	[Equation] [varchar](500) NULL,
	[Masked_ID] [int] NULL,
	[CreatedDate] [datetime] NULL,
	[CreatedBy] [int] NULL,
	[ModifiedDate] [datetime] NULL,
	[Modifiedby] [int] NULL,
	[DeletedDate] [datetime] NULL,
	[DeletedBy] [int] NULL,
	[LatestCompanyID] [bigint] NOT NULL,
	[DateOfLatestCompanyID] [datetime] NULL,
 CONSTRAINT [PK_Nop_Part] PRIMARY KEY CLUSTERED 
(
	[PartID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UC_Partnon_LatestCompanyID] UNIQUE NONCLUSTERED 
(
	[PartNumberNon] ASC,
	[LatestCompanyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [uc_partnumbernonCompany] UNIQUE NONCLUSTERED 
(
	[PartNumberNon] ASC,
	[CompanyID] 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
GO

ALTER TABLE [Parts].[Nop_Part] ADD  CONSTRAINT [DF_Nop_Part_PinOutId]  DEFAULT ((0)) FOR [PinOutId]
GO

ALTER TABLE [Parts].[Nop_Part] ADD  DEFAULT (getdate()) FOR [DateOfLatestCompanyID]
GO

ALTER TABLE [Parts].[Nop_Part]  WITH NOCHECK ADD FOREIGN KEY([Masked_ID])
REFERENCES [dbo].[Core_Masked] ([ID])
GO
Posted
Updated 19-Feb-20 3:56am
v3
Comments
F-ES Sitecore 18-Feb-20 4:05am
   
There is no "magic bullet" answer. Determine what you are querying that is slow (ie what is in your WHERE arguments) and build an index on those fields. Note that indexes may speed up querying but will slow down inserting. Google for tuning sql queries to get ideas, it's a massive subject.
ahmed_sa 18-Feb-20 4:14am
   
Thanks for reply i ask here only to give me some idea about what i make only and give me public reasons for that
Richard MacCutchan 18-Feb-20 4:41am
   
Make sure your tables are indexed so the server can quickly find records.
Patrice T 18-Feb-20 6:45am
   
Define "too long"
Richard Deeming 18-Feb-20 15:09pm
   
"300 hundred million rows"

Are you sure about that? That would be 30 billion rows.

You may be interested in paging; this allows to only fetch a numbered subset of rows.
Paging a Query with SQL Server - TechNet Articles - United States (English) - TechNet Wiki[^]

You can also take advantage of the WHERE clause to filter the results according to your data.
sql filtering[^]

I would also advise to avoid instructions like SELECT *, especially when you're not interested in fetching all columns. Only select those columns which are relevant. Plus, SELECT * prevents you from specifying the order in which you want the columns to appear.

Finally, when it come to performance, there is a tool which allows you to get some insights about timings in queries:
sql execution plan[^]
And here are some links about SQL performance and its management: sql performance[^]
   
if the table had to kept unindexed or kept as is for business purposes.

One solution is utilizing views with an index for defined selections.

Create Indexed Views - SQL Server | Microsoft Docs[^]


Another is creating A history table, data and business logic permitting.

Creating a System-Versioned Temporal Table - SQL Server | Microsoft Docs[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900