SELECT *比SELECT field1，field2更快...... [英] SELECT * faster than SELECT field1, field2...

问题描述

大家好，

我在SQL Server 2005中编写的视图遇到了一些麻烦。

查询用于报告（Crystal Reports，yikes ！）并且经常被调用（可能每天几百次）。问题是数据加载需要4到6秒。



查询非常简单：

  SELECT  '  SomeField' = dbo。 MyTable.Field1，
 '  SomeOtherField' = dbo.MyTable.Field2 
 < span class =code-string>'  A_Third_Field' = dbo.MyOtherTable.Field1，
 ... 
  FROM  dbo.MyTable 
  LEFT   JOIN  dbo.MyOtherTable  ON  dbo.MyOtherTable.Field1 = dbo.MyTable.OtherTableField 
 ... 
 在哪里东西

有12个 JOIN s和三个 WHERE 条件。 SELECT 列表中有一些函数，如 ISNULL 和 CASE 。

所有这些都无关紧要。



重要的是，当我运行查询时需要SQL Server 4到6秒给我一个结果。

这是奇怪的部分;当我用 SELECT * 替换 SELECT Field1 等时，查询会立即返回结果！



我们不能在视图中使用 SELECT * ，因为它返回具有相同名称的列。当然，我们不想使用 SELECT * 。

如何 SELECT * 虽然快得多吗？

我怎样才能让我的'常规'查询同样快（为什么不是这样？：-s）？



更新：

刚才原始查询在 SELECT * 未运行时立即运行在所有...原始查询现在回到它的6秒...



我理解SQL Server有时可能需要更长的时间，但这不是有时候。



基本上这种奇怪的行为让我很难调整这个查询。



Any想法是什么？

谢谢。



另一个更新：

我刚发现 SELECT * 语句在我们数据库中一个最大的表上进行大约88000次LESS读取。它为什么会这样做？



另一个更新：

看来当我在我的选择列表中放入一个非持久的计算列时炽热的速度......但只有当我将查询作为临时查询运行时。当我向视图添加相同的字段时没有任何反应...

解决方案

在博客中查看更多详细信息

select-vs-select-columns-sql-server.html [ ^ ]

不是真正的解决方案，但确实解决了我的问题。

问题出在 JOINS 中。制作一些 JOINS INNER JOINS 使查询闪电般快速。虽然让所有这些 INNER JOINS 是正确的，但这再次使得查询变慢...拥有所有 LEFT JOINS （因为它）在某种程度上是正确的，但不是必要的。

所以我想这个案子已经结束了。



编辑：

我不知道计算列如何在ad-hoc查询中快速完成所有内容，但不在视图中...我已经放弃了发现它。

Hi all,
I'm having a little trouble with a view someone wrote in SQL Server 2005.
The query is used for reporting (Crystal Reports, yikes!) and is called pretty often (probably a few 100 times a day). The problem is the data takes 4 to 6 seconds to load.

The query is pretty straightforward:

SELECT 'SomeField' = dbo.MyTable.Field1,
       'SomeOtherField' = dbo.MyTable.Field2
       'A_Third_Field' = dbo.MyOtherTable.Field1,
       ...
FROM dbo.MyTable
LEFT JOIN dbo.MyOtherTable ON dbo.MyOtherTable.Field1 = dbo.MyTable.OtherTableField
...
WHERE Something

There's 12 JOINs and three WHERE conditions. There are some functions in the SELECT list, like ISNULL and a CASE.
All that doesn't matter though.

What matters is that when I run the query it takes SQL Server 4 to 6 seconds to give me a result.
Here comes the strange part; when I replace SELECT Field1 etc. with SELECT * the query returns results instantly!

We can't use SELECT * in our view because it returns columns with the same name. And, of course, we don't want to use SELECT *.
How is it that SELECT * is so much faster though?
And how can I make my 'regular' query just as fast (and why isn't it already? :-s)?

UPDATE:
Just now the original query ran instantly while the SELECT * doesn't run at all... The original query is now back to it's 6 seconds...

I understand SQL Server can sometimes take a little longer, but this isn't sometimes.

Basically this weird behaviour makes it pretty impossible for me to tune this query.

Any idea's?
Thanks.

ANOTHER UPDATE:
I just found out the SELECT * statement makes about 88000 LESS reads on one of the biggest tables in our database. Why would it do that?

YET ANOTHER UPDATE:
It seems when I put a non-persisted computed column in my select list everything is blazing fast... But only when I run the query as ad-hoc query. When I add the same field to the view nothing happens...

解决方案

See more details in blog
select-vs-select-columns-sql-server.html[^]

Not really a solution, but it did fix my problem.
The problem was in the JOINS. Making some JOINS INNER JOINS made the query lightning fast. Though it would be correct to make all of them INNER JOINS this once again makes the query slow... Having all LEFT JOINS (as it was) was, in some way, correct, but not necessary.
So I guess that's case closed.

Edit:
I have no idea how the computed column made everything fast in an ad-hoc query, but not in a view... I've given up on finding that out.

这篇关于SELECT *比SELECT field1，field2更快......的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！