我正在尝试检索表数据，但它耗费了太多时间。总记录是2000行...请任何机构给出解决方案 [英] I am trying retrieve the table data but it is taking too much time.total record is 2000 rows ...please any body give the solution

问题描述

CREATE TABLE [dbo].[TRANSDTL](
	[fin_year] [nvarchar](4) NOT NULL,
	[tran_no] [decimal](18, 0) NOT NULL,
	[tran_dt] [smalldatetime] NULL,
	[part_no] [nvarchar](20) NOT NULL,
	[loc_from] [nvarchar](50) NOT NULL,
	[stor_typ] [nvarchar](2) NOT NULL,
	[gfin_year] [nvarchar](4) NOT NULL,
	[giv_no] [decimal](18, 0) NOT NULL,
	[doc_typ] [nvarchar](2) NOT NULL,
	[indt_no] [nvarchar](20) NOT NULL,
	[pack_size] [nvarchar](50) NULL,
	[tot_qty] [decimal](18, 3) NOT NULL,
	[SentQty] [decimal](18, 3) NULL,
	[loc_to] [nvarchar](20) NULL,
	[CMPY_CD] [int] NOT NULL,
	[tranType] [nvarchar](3) NOT NULL,
	[custo_no] [nvarchar](50) NULL,
	[custo_dt] [datetime] NULL,
	[AcceptDate] [datetime] NULL,
	[CheckField]  AS (hashbytes('SHA1',(((((CONVERT([nvarchar](10),[giv_no],(0))+CONVERT([nvarchar](20),[part_no],(0)))+[gfin_year])+CONVERT([nvarchar](10),[cmpy_cd],(0)))+CONVERT([char](2),[stor_typ],(0)))+case when rtrim(ltrim([indt_no]))='' then 'a' when [indt_no] IS NULL then 'a' else [indt_no] end)+CONVERT([nvarchar](2),[doc_typ],(0)))),
	[checkFieldLoc]  AS (hashbytes('SHA1',((((((CONVERT([nvarchar](10),[giv_no],(0))+CONVERT([nvarchar](20),[part_no],(0)))+[gfin_year])+CONVERT([nvarchar](10),[cmpy_cd],(0)))+CONVERT([char](2),[stor_typ],(0)))+case when rtrim(ltrim([indt_no]))='' then 'a' when [indt_no] IS NULL then 'a' else [indt_no] end)+CONVERT([nvarchar](2),[doc_typ],(0)))+CONVERT([nvarchar](20),[loc_from],(0)))),
	[GRate]  AS ([dbo].[f_GetGivRate](hashbytes('SHA1',(((((CONVERT([nvarchar](10),[giv_no],(0))+CONVERT([nvarchar](20),[part_no],(0)))+[gfin_year])+CONVERT([nvarchar](10),[cmpy_cd],(0)))+CONVERT([char](2),[stor_typ],(0)))+case when rtrim(ltrim([indt_no]))='' then 'a' when [indt_no] IS NULL then 'a' else [indt_no] end)+CONVERT([nvarchar](2),[doc_typ],(0))))),
	[checkFieldDC]  AS (hashbytes('SHA1',(CONVERT([nvarchar](20),[custo_no],(0))+CONVERT([nvarchar](20),[part_no],(0)))+CONVERT([nvarchar](10),[cmpy_cd],(0)))),
	[Gin_Rate] [decimal](18, 3) NOT NULL,
	[Curr_Code] [int] NULL,
	[Exchange_Rate] [decimal](18, 5) NOT NULL,
	[CheckFieldStr]  AS ((((((CONVERT([nvarchar](10),[giv_no],(0))+CONVERT([nvarchar](20),[part_no],(0)))+[gfin_year])+CONVERT([nvarchar](10),[cmpy_cd],(0)))+CONVERT([char](2),[stor_typ],(0)))+case when rtrim(ltrim([indt_no]))='' then 'a' when [indt_no] IS NULL then 'a' else [indt_no] end)+CONVERT([nvarchar](2),[doc_typ],(0))),
	[tdoc_typ] [nvarchar](10) NULL,
 CONSTRAINT [PK_TRANSDTL] PRIMARY KEY CLUSTERED 
(
	[fin_year] ASC,
	[tran_no] ASC,
	[part_no] ASC,
	[loc_from] ASC,
	[stor_typ] ASC,
	[gfin_year] ASC,
	[giv_no] ASC,
	[doc_typ] ASC,
	[indt_no] ASC,
	[CMPY_CD] ASC,
	[tranType] 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 [dbo].[TRANSDTL] ADD  CONSTRAINT [DF_TRANSDTL_tot_qty]  DEFAULT ((0)) FOR [tot_qty]
GO

ALTER TABLE [dbo].[TRANSDTL] ADD  CONSTRAINT [DF_TRANSDTL_SentQty]  DEFAULT ((0)) FOR [SentQty]
GO

ALTER TABLE [dbo].[TRANSDTL] ADD  CONSTRAINT [DF_TRANSDTL_CMPY_CD_1]  DEFAULT ((0)) FOR [CMPY_CD]
GO

ALTER TABLE [dbo].[TRANSDTL] ADD  CONSTRAINT [DF_TRANSDTL_Rate]  DEFAULT ((0)) FOR [Gin_Rate]
GO

ALTER TABLE [dbo].[TRANSDTL] ADD  CONSTRAINT [DF_TRANSDTL_Exchange_Rate]  DEFAULT ((0)) FOR [Exchange_Rate]
GO

我尝试了什么：



i我正在尝试检索表格数据但是花了太多时间

What I have tried:

i am trying retrieve the table data but it is taking too much time

推荐答案

您需要了解如何配置/调试SQL查询。我假设这是sql-server。



如何使用sql profiler - Google搜索 [ ^ ]



如何：使用SQL事件探查器 [ ^ ]



还要查看您的查询执行计划，看看它正在做什么/占用了大量资源/时间。

执行计划基础 - 简单谈话 [ ^ ]



从那里你需要评估你是否需要更多地模式化你的模式（或者可能在奇怪的情况下反规范化），考虑添加索引（如果适用），并考虑重写你的查询以提高效率（例如：你可能正在进行子查询并且应该查看连接，或者相反）。



只有你可以调试/提高你的数据库性能，因为我们这里没有人可以访问你的数据库服务器所以你需要做腿部工作来改善你觉得你的数据库表现不佳。

You need to learn how to profile/debug sql queries. I am assuming this is sql-server.

How to use sql profiler - Google Search[^]

How To: Use SQL Profiler[^]

Also look into your query execution plan and see what it is doing/what is taking a lot of resources/time.
Execution Plan Basics - Simple Talk[^]

From there you need to evaluate if you need to normalize your schema more (or maybe denormalize in weird cases), look into adding indexes if applicable, and also look at rewriting your queries to be more efficient (ex: maybe you are doing sub queries and should look at a join, or the reverse).

Only you can debug/improve your database performance as none of us here have access to your db server so you'll need to do the leg work to improve what you feel is poor performance in your database.

这篇关于我正在尝试检索表数据，但它耗费了太多时间。总记录是2000行...请任何机构给出解决方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！