Select * from table quary what is return in SQL?

Question

0.00/5 (No votes)

See more:

My table has only 5 columns, and 5000 records...

If I run

SQL

Select name, mobile, City, State, Cont, eamil from tbl_client

the query executes only in 30 second...

But if I run

SQL

Select * from tbl_client

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

Posted 1-Aug-16 23:55pm

kaushik ahir

Updated 4-Aug-16 22:08pm

Kornfeld Eliyahu Peter

v3

Add a Solution

Comments

RossMW 2-Aug-16 6:03am

You need to look at the SQL server. 5000 records in 30 is poor performance. Indexing will help that I would suggest there is something fundamentally wrong with your SQL server configuration, setup, or spec

Kornfeld Eliyahu Peter 2-Aug-16 6:06am

Actually - reading table without where clause should be very fast even without index. It is a simple loop over the data pages...
5000 records in 30 seconds sounds like a bad server setup (no memory?)!
And this is not to say anything against index - index is your friend in a true application...

Kornfeld Eliyahu Peter 2-Aug-16 6:03am

Just a note: 5000 records are not even medium, for sure not large!!!

kaushik ahir 2-Aug-16 6:12am

my Question is:
if i will Select * From Table so get only column Or otherwise

kaushik ahir 2-Aug-16 6:14am

Because Above Query Execute 1 Min. Time in sql Server 2012

kaushik ahir 2-Aug-16 6:22am

my table only Two Cell what is different between 2 cell and *(all) in Select Quary in table

3 solutions

Solution 2

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.

C#

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.

Posted 2-Aug-16 0:15am

OriginalGriff

Comments

kaushik ahir 2-Aug-16 6:45am

that's my question...why are get difference time...Query both are same..!! Why?????

OriginalGriff 2-Aug-16 7:01am

Well...I don't - and my times are far shorter than yours for many, many more rows. Plus, the DB I was using was on an internet-based server, so my "round-trip" times include internet latency as well.
If you look at my numbers, they aren't consistent for that reason: sometimes one is faster, sometimes the other.
I suspect that the "*" query may in practice be slightly quicker, because there is less command parsing to do and the column selection is simpler, but the difference I get is trivial: in terms of 1/10ths or even 1/1000ths of a second most of the time.
I think you need to look at exactly how you are accessing your data, and how you are timing it - because unless you are on a heavily used, slow SQL server the numbers you show don't make a lot of sense.

F-ES Sitecore 5-Aug-16 4:59am

You'd expect the select * to be slower as it has to discover the field names to return before it executes, but as you say we're talking the smallest fraction of a second.

I suspect the OP is timing his queries by seeing how long the take to run in SQL Management Studio with the results set to "grid". That is, of course, not an accurate reflection of how long the query takes and probably would take those times to show a lot of data.

Solution 4

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[^]

Posted 4-Aug-16 22:08pm

Maciej Los

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)

vani suresh · Accepted Answer · 2016-08-04T21:55:00

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)

SQL

Select * from tbl_client

2)

SQL

Select name, mobile, City, State, Cont, eamil from tbl_client

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