从表中选择*是什么在SQL中返回？ [英] Select * from table quary what is return in SQL?
问题描述
我的表只有5列，5000条记录...
如果我运行
My table has only 5 columns, and 5000 records...
If I run
Select name, mobile, City, State, Cont, eamil from tbl_client
查询仅在30秒内执行...
但如果我运行
the query executes only in 30 second...
But if I run
Select * from tbl_client
此查询执行超过1分钟。
请解决我
我的尝试：
查询执行时间
从tbl_client中选择名称，手机，城市，州，续，eamil 30 Second
从tbl_client选择* 1分钟以上
this query executes over 1 min.
Please Solve me
What I have tried:
Query Execute Time
Select name,mobile,City,State,Cont,eamil from tbl_client 30 Second
Select * from tbl_client 1 min above
推荐答案
首先，你的表中有5列，但是你列出了6个要返回的值...
当我在我的数据库上运行类似的查询时，我为每个查询获得了相同的数量级响应，总共28767行。
First off, there are 5 columns in your table, but you list 6 values to return...
When I run a similar query on my DB, I get the same order of magnitude responsefor each for a total of 28767 rows for each query.
Stopwatch s1 = new Stopwatch();
Stopwatch s2 = new Stopwatch();
using (SqlConnection con = new SqlConnection(strConnect))
{
con.Open();
SqlCommand cmd1 = new SqlCommand("SELECT ID, UserID, EnterDate, Total, AU1, AU2, DE, ED, EN, OR, PA FROM dbo.MyTable", con);
using (SqlDataAdapter da = new SqlDataAdapter(cmd1))
{
DataTable dt = new DataTable();
s1.Start();
da.Fill(dt);
s1.Stop();
}
SqlCommand cmd2 = new SqlCommand("SELECT * FROM dbo.MyTable", con);
using (SqlDataAdapter da = new SqlDataAdapter(cmd2))
{
DataTable dt = new DataTable();
s2.Start();
da.Fill(dt);
s2.Stop();
}
}
Console.WriteLine("{0}:{1}", s1.ElapsedTicks, s2.ElapsedTicks);
我连续运行的价值是：
The values I get for successive runs are:
5105014:2635936
3679955:6183447
5110977:4361559
4260305:3936449
所以......我仔细看看你是如何获取行的，以及你如何计时。
So...I'd look closely at exactly how you are fetching the rows, and how you are timing it.
你好Kaushik，
在SSMS中，他们已经有了执行时间工具。 br />
转到 - >菜单：查询 - >单击包含客户端统计信息。
运行两者的sql查询并检查客户端统计信息选项卡将显示在消息选项卡旁边。
查看网络统计和时间统计。
1）
Hi Kaushik,
In SSMS they already have a Tool for Execution Time .
Goto ->Menu: Query -> Click on "Include client Statistics".
Run the sql query for both and check the "Client Statistics" tab will appear in beside messages tab.
check the "Network Statistics" and "Time Statistics" .
1)
Select * from tbl_client
2）
2)
Select name, mobile, City, State, Cont, eamil from tbl_client
如果我们运行select *查询，则select查询将执行完整的扫描表。
而对于其他查询，请选择col1，col2，col3 ，它简单地扫描所选列的索引，网络流量也很快。
如果您非常确定列名始终保持不变，最好用select col1，col2定义查询，...其他明智的用户选择*。（但网络流量会很慢）。
谢谢
： ）
If we run the select * query , The select query will do a full table of scan.
While for other query select col1,col2,col3, It simple scans the index of selected columns and the network traffic will also be fast.
If you're are very sure the column name's always will be the remain same , Better to define the query with select col1, col2,...other wise user select *.(But the network traffic will be slow).
Thanks
:)
你可以自己解决。您必须阅读以下文章才能找出纯查询性能的原因：
针对TSQL性能的七种罪行 - 简单的谈话 [ ^ ]
分析慢速查询的核对表 [ ^ ]
You can solve it by yourself. You have to read below articles only to kfind out the reasons of pure query performance:
The Seven Sins against TSQL Performance - Simple Talk[^]
Checklist for Analyzing Slow-Running Queries[^]
这篇关于从表中选择*是什么在SQL中返回？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
