Sql中的查询优化 [英] Query optimization in Sql

问题描述

我有一个表``myTable''，其中包含50个字段/列(例如column1，column2，......，第49列，column50).
我只想获取45列的所有记录.

那么，对此的优化查询是什么?

目前，我正在使用类似
的查询

Hi,
I have a table ''myTable'' containing 50 fields/columns (like column1,column2,......, column 49,column50).
I want to fetch all records for 45 columns only.

so, what is the optimized query for this?

currently, I''m using Query like

Select column1,column2,column3,.....,column48,column49 from myTable 

还有其他方法可以缩短此查询吗?
在这里不需要提取column10，column20，column30，column40，column50

谢谢.

Is there any other way to shortened this query?
Here not required to fetch column10,column20,column30,column40,column50

Thanks.

推荐答案

列出每个列的名称是您进行优化查询所需的方法.但是，这并不意味着您需要全部输入.您已经列出了许多不同的SQL版本(Oracle，MySQL，MSSQL等)，因此每种版本都有一些不同，但是在Management Studio(SSMS)中的Microsoft SQL中，您可以右键单击表并说"Script Table As ..."，然后说"SELECT To"，最后说"New Query Editor Window".这将为您提供完整的SELECT语句.现在，您只需要删除五列就可以了.

这样做的另一种方法(虽然不是免费的)是使用Red Gate的SQL Prompt:

http://www.red-gate.com/products/sql-development/sql-prompt / [^ ]

该工具将为您提供SSMS的许多功能.一种是，当您说"SELECT *"时，您可以在星号后打两次制表符，它会为您提供列名(一旦您在FROM语句中指定了表).

Listing out each column name is the way you need to do it in order to have an optimized query. However, that doesn''t mean you need to type it all out. You''ve listed a number of different SQL flavors (Oracle, MySQL, MSSQL, etc.) so this will be a bit different for each but in Microsoft SQL in the Management Studio (SSMS), you can right-click on the table and say "Script Table As..." and then "SELECT To" and finally "New Query Editor Window". This will give you a full SELECT statement. Now you just need to remove your five columns and you will be set.

Another way to do this, although not free, is to use SQL Prompt from Red Gate:

http://www.red-gate.com/products/sql-development/sql-prompt/[^]

This tool will give you a lot of features in SSMS. One is that when you say "SELECT *", you can hit tab twice after the star and it will give you the column names (once you have specified the table in the FROM statement).

我实际上认为您可以传递类似串连的字符串
Column1..50的值(不相关)(不包含相关内容)作为文字类型的单个列或该列的存储桶(例如5-8个集群)，并在目标进程中进行相应处理.

另一个选择(如果使用的是Oracle DBMS)是在列出表或视图中的所有列之后使用动态SQL:

I actually think that you could pass something like a string of concatenated
values of your Column1..50 (excluding irrelevant) as a single column of literal type or bucket of this columns (say 5-8 clusters) and process them accordingly on the destination process.

Another option (if you are using Oracle DBMS) is to use dynamic SQL after listing all the columns in table or view:

select * from all_tab_cols
where upper(table_name) = ''

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