表查询性能不佳 [英] Poor performance on table query

问题描述

大家好，



我有一个相当简单的数据库，在加载我的记录时表现糟糕。



本质上，我有2个表，它们通过主键链接并保存不同的信息。其中一个表只有1个记录，另一个可以有很多。



我遇到的问题是返回很多结果的查询给出了可怕的结果性能，本地执行需要2-3秒。当该表中只有少数记录时就是这种情况。



我的SQL查询如下： -

  SELECT  
 PID，市政，地址_1，地址_2，地点，州，邮政编码，Sale_date，Sale_price，Capital_value，Land_area，Land_use_code，Room_count ，Building_area，Construction_year，Wall_construction_code，Roof_construction_code，来源，SaleID 
  FROM  SaleInformation 
  ORDER   BY  Sale_date  DESC  

现在该表中有很多记录（例如40,000+），该查询返回匹配PID（链接密钥）的每个字段。 />


对于我的生活，我无法弄清楚它是什么会降低性能并且让我疯狂！



任何帮助非常感谢iated。



干杯，



Joe

解决方案

检查一下...... 如何加快我的选择*查询530,000行 [ ^ ]



此外，我在您的查询中注意到的另一件事是缺少where子句，这可能对提高查询性能有用。



希望提供的链接将为您提供帮助。



干杯

亲爱的Joe ，



您可能需要在表格中添加索引以获得快速性能...



Regard

如果您的表未更新，则创建索引视图可能会提高您的性能经常。你很容易尝试。

通过在SQL Server中使用索引视图来查看性能提升 [ ^ ]



但也许你应该调查一下数据库规范化以提高性能。



数据库规范化基础知识 [ ^ ]



数据库规范化教程 [ ^ ]



数据库规范化 [ ^ ]

Hi all,

I have a fairly simple database which is giving horrible performance when loading up my records.

In essence, I have 2 tables which are linked by a primary key and hold different information. One of these tables has 1 record only per, the other can have many.

The problem I have is that the query which is returning the "many" results gives terrible performance and will take 2-3 seconds to execute locally. This was the case when there was only a handful of records in that table.

My SQL query is as follows:-

SELECT 
PID, Municipality, Address_1, Address_2, Locality, State, Postcode, Sale_date, Sale_price, Capital_value, Land_area, Land_use_code, Room_count, Building_area, Construction_year, Wall_construction_code, Roof_construction_code, Source, SaleID 
FROM SaleInformation 
ORDER BY Sale_date DESC

There is a lot of records in that table now (say 40,000+) and that query returns every field that matches PID (linked key).

For the life of me I cannot work out what it is that is slowing the performance and it is driving me insane!

Any help greatly appreciated.

Cheers,

Joe

解决方案

Hi,

Check this ...How Do I Speed Up My Select * Query Of "530,000" Rows[^]

Also, one more thing i noticed in your query is that absence of where clause which may be useful to improve your query performance.

Hope provided link will help you.

Cheers

dear Joe,

You may need to add index to your table for fast performance...

Regard

Creating an indexed view would probably increase your performance if your tables are not updated too frequently. It is easy enough for you to try out.
See performance gains by using indexed views in SQL Server[^]

But maybe you should look into database normalization in order to improve the performance.

Database Normalization Basics[^]

Database Normalization Tutorial[^]

Normalization of database[^]

这篇关于表查询性能不佳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！