sql Query Performace [英] sql Query Performace
问题描述
我有两张桌子
CREATE TABLE [dbo]。[表1](
[col1] [ varchar ]( 20 ) NOT NULL ,
[col2] [ varchar ]( 20 ) NOT NULL ,
[col3] [ varchar ]( 20 ) NOT NULL ,
[col4] [ varchar ]( 100 ) NOT NULL ,
[col5] [ varchar ]( 100 ) NOT NULL ,
[col6] [ varchar ]( 100 ) NOT NULL ,
[col7] [ varchar ]( 20 ) NOT NULL ,
[col8] [ varchar ]( 40 ) NOT NULL ,
[col9] [ varchar ]( 20 ) NOT NULL ,
[项目] [ varchar ]( 20 ) NULL ,
[EmpNo] [数字]( 18 , 0 ) NULL
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[col1] ASC ,
[col2] ASC ,
[col3] ASC ,
[col4] ASC ,
[col5] ASC ,
[col6] ASC ,
[col7] ASC ,
[col8] ASC ,
[col9] ASC
) WITH (PAD_INDEX = OFF ,STATISTICS_NORECOMPUTE = OFF ,IGNORE_DUP_KEY = OFF ,ALLOW_ROW_LOCKS = ON ,ALLOW_PAG E_LOCKS = ON ) ON [ PRIMARY ]
) ON [ PRIMARY ]
CREATE TABLE [dbo]。[Table_2](
[col1] [ varchar ]( 20 ) NOT NULL ,
[col2] [ varchar ]( 20 ) NOT NULL ,
[col3] [ varchar ]( 20 ) NOT NULL ,
[col4] [ varchar ]( 20 )不 NULL ,
[col5] [ smallint ] NULL ,
[col6] [数字]( 18 , 0 ) NULL ,
[状态] [数字]( 18 , 0 ) NULL ,
CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED
(
[col1] ASC ,
[col2] ASC ,
[col3] ASC ,
[col4] ASC
) WITH (PAD_INDEX = OFF ,STATISTICS_NORECOMPU TE = OFF ,IGNORE_DUP_KEY = OFF ,ALLOW_ROW_LOCKS = ON ,ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ]
) ON [ PRIMARY ]
i have Two tables 这篇关于sql Query Performace的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
Table_1包含40,000多条记录
table_2包含约5000条记录
< pre lang =sql> 选择 A.col5 来自表_2 内部 join 表1 B A.col1 = B.col1 AND A.col2 = B.col2 AND A.col3 = B.col3 和 A.col4 = B.col4 AND B.EmpNo =
如果我运行上面的sql查询需要大约3到4秒才能运行
如何才能提高上面的SQL查询性能查看执行计划/查询优化器。查看链接以获取更多信息。
https:// technet。 microsoft.com/en-us/magazine/2007.11.sqlquery.aspx [ ^ ]
https://www.simple-talk.com/sql/sql-training/the-sql-server-query-optimizer/ [ ^ ]
祝您好运!
您的问题是您的主要密钥,由多个varchar列组成。作为Tomas Takac,我怀疑查询时间会很糟糕,插入(可能更糟糕)。
使用整数单列主键;这并不妨碍你在所有varchar列上放置一个唯一索引,但至少你有机会获得不错的表现。
在表之间使用适当的关系来改善性能,您的查询明智没问题使用正确的索引到表。
检查您的执行计划,在这种情况下,它会为您提供详细的报告。
CREATE TABLE [dbo].[Table_1](
[col1] [varchar](20) NOT NULL,
[col2] [varchar](20) NOT NULL,
[col3] [varchar](20) NOT NULL,
[col4] [varchar](100) NOT NULL,
[col5] [varchar](100) NOT NULL,
[col6] [varchar](100) NOT NULL,
[col7] [varchar](20) NOT NULL,
[col8] [varchar](40) NOT NULL,
[col9] [varchar](20) NOT NULL,
[Project] [varchar](20) NULL,
[EmpNo] [numeric](18, 0) NULL
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[col1] ASC,
[col2] ASC,
[col3] ASC,
[col4] ASC,
[col5] ASC,
[col6] ASC,
[col7] ASC,
[col8] ASC,
[col9] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Table_2](
[col1] [varchar](20) NOT NULL,
[col2] [varchar](20) NOT NULL,
[col3] [varchar](20) NOT NULL,
[col4] [varchar](20) NOT NULL,
[col5] [smallint] NULL,
[col6] [numeric](18, 0) NULL,
[Status] [numeric](18, 0) NULL,
CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED
(
[col1] ASC,
[col2] ASC,
[col3] ASC,
[col4] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Table_1 contains more than 40,000 records
table_2 contains around 5000 records
select A.col5 from Table_2 A inner join Table_1 B on A.col1 = B.col1 AND A.col2 = B.col2 AND A.col3 = B.col3 and A.col4 = B.col4 AND B.EmpNo = 1256 AND A.Status=0
if i run above sql Query it takes around 3 to 4 seconds to run
how i can improve performance of the above sql QueryCheck out the execution plan / query optimizer. Check out the links for more info.
https://technet.microsoft.com/en-us/magazine/2007.11.sqlquery.aspx[^]
https://www.simple-talk.com/sql/sql-training/the-sql-server-query-optimizer/[^]
Good luck!Your problem is your primary key, made of several varchar columns. As Tomas Takac, I suspect query times will be awful, as well as inserts (which may be even worse).
Use an integral single-column primary key; that does not prevent you from putting a unique index on all your varchar column, but at least you will have a chance to get decent performances.Use proper relation ship between tables that will improve the performance, your query wise no problem use proper indexes to the table.
Check your execution plan, in which case it will give you detailed report.