Query optimization in Sql

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

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

SQL

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

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

Thanks.

Posted 19-Jun-12 2:44am

ashriv

Updated 19-Jun-12 2:49am

luisnike19

v2

Add a Solution

Comments

Prasad_Kulkarni 19-Jun-12 8:45am

What's problem with SELECT * FROM MyTable??

amolpatil2243 19-Jun-12 8:48am

yes, use SELECT * FROM MyTable and exclude the columns..

ashriv 19-Jun-12 9:12am

what is the way to exclude the column? since Here not required to fetch column10,column20,column30,column40,column50

Oshtri Deka 19-Jun-12 8:54am

Why don't you rewrite your query to return only columns you are interested in?
This straight-forward -- SELECT [columns you want] FROM [table of your choosing].
So what's the problem?

ashriv 19-Jun-12 9:16am

problem is writing a long select statement. is there any way to exclude the column?
something like --
select * from myTable where column not in (column10,column20,column30,column40,column50)

shek124 19-Jun-12 9:11am

For DB optimization perspective, why you created the table as more number of columns. Better you will make optimize the table/db first.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tim Corey · Answer 1 · 2012-06-19T04:23:00

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).

Mirzakhmet Syzdykov · Answer 2 · 2012-06-20T02:25:00

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:

SQL

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

Query optimization in Sql

2 solutions

Solution 2

Solution 3

Add your solution here

Preview 0