Performance Difference between SELECT * and SELECT col1, col2, ...coln

Roy, I understand the point you are trying to make, but this part of
your argument just doesn't hold up to scrutiny:

> But if you see * in one of my SELECT lists in production code it does
> not mean I was lazy. It means that the REQUIREMENT is to return EVERY
> row in the table.
If it's a requirement, why don't you type the columns out? Even if a
column is added, it's no easier (or more difficult) to recompile the
view than it is to add the column as required.
I understand the point that you're trying to make, and I think you're
right about the potential value of SELECT * when used correctly, but
I'm hard pressed to find a scenario where SELECT * is preferable to
typing column names out.
Stu
Roy Harvey wrote:
> On Mon, 03 Jul 2006 13:41:44 -0700, darter <dd@.email.com> wrote:
>
> Personally I am quite tired of this conventional wisdom.
> Of course, if you do not need every column returned you should specify
> just the columns you need. I have no problem with that.
>
> Simple example: a view that starts with an existing table and adds
> some columns by joining or a subquery. This calls for a qualified *:
> CREATE VIEW Something_V
> AS
> SELECT A.*,
> (<subquery> ) as X,
> (<subquery> ) as Y
> FROM Something as A
> If a new column is added to A there is no question that it belongs in
> the view, and no question that a recompile will add it.
> With * the sequence of the columns is predictable. There is no chance
> that a column will be left out. There is no chance that a comma will
> be left out, skipping a column and assigning its name to a different
> column.
> Yes, * is often abused, but it is also a valuable feature when used
> correctly.
> Roy Harvey
> Beacon Falls, CT> How about comparing a table against an Oracle or DB2 table? Naturally
> you would want to grab all the columns, as any disrepancy in column
> names or their count would mean tables are not idential.
This sounds like an administrative task, not something that should naturally
appear in your application.
Anyway, I would rely on the metadata tables / catalog views to describe my
tables, not SELECT *.|||On 3 Jul 2006 19:42:49 -0700, "Stu" <stuart.ainsworth@.gmail.com>
wrote:

>Roy, I understand the point you are trying to make, but this part of
>your argument just doesn't hold up to scrutiny:
>
>If it's a requirement, why don't you type the columns out?
Because it is no longer obvious that it is a requirement.

>Even if a
>column is added, it's no easier (or more difficult) to recompile the
>view than it is to add the column as required.
Adding the column name is certainly not hard, but pulling up the ALTER
script and hitting execute is certainly easier, and provides less
opportunities for errors.

>I understand the point that you're trying to make, and I think you're
>right about the potential value of SELECT * when used correctly, but
>I'm hard pressed to find a scenario where SELECT * is preferable to
>typing column names out.
OK, suppose I have a table with 30 columns. Rather than use *, I have
included all 30 column names. Six months later, someone else is
spending their day going through the code making sure they understand
what it does. They see all those column names, and they wonder, is
that all of them? Or only most of them? And they have to start
counting, ormatching them up. And now imagine that the order was
changed. I simply do not see what has been accomplished by replacing
the clarity of * with the relative obscurity of a list column names.

>Stu
Roy

Performance Comparison - Code vs SqlDataSource, Gridview etc vs PlainControl

There are so many ways to use database in asp.net/ado.net, I'm a bit confused about their difference from the performance point of view.

So apparently SqlDataSource in DataReader mode is faster than DataSet mode, at a cost of losing some bolt-on builtin functions.

What about SqlDataSource in DataReader mode vs manual binding in code? Say creating a SqlDataSource ds1 and set "DataSourceID" in Gridview, vs manually creating the SqlConnection, SqlCommand, SqlDataReader objects and mannually bind the myReader object to the gridview with the Bind() method.

Also Gridview is a very convenient control for many basic tasks. But for more complex scenarios it requires lots of customization and modification. Now if I do not use gridview at all and build the entire thing from scratch with basic web controls such as table and label controls, and mannually read and display everything from a DataReader object, how's the performance would be like compared to the Gridview-databind route?

I don't have a tested answer for you, just an opinion. Re your own connect vs SqlDataSource, there should be no differenct. The SqlDataSource has to do everything you do so I don't see any performance implication on a single screen. With that said, though, I could see somepotential connection pooling issues since minor differences in connection strings prevent asp.net from reusing the same connection. Without going into details, I think that awell writtenconnection object (that gets reused) is more likely to allow pooling than typing your connection details into wizards all the time. In any case, I wouldn't worry about it at this stage of the game.

Re GridView vs writing all that stuff yourself. If you use all or most or even some of that functionality, I don't think you should attempt to rewrite it -- it's just not worth it. You may even make performance worse if you write it wrong, and frankly, programmer time is worth more than cpu time.

If you're interested in pursuing the issue, Farhan Muhammed wrote a book which gives some fairly detailed numbers comparing different access methods and different controls -- he did real performance comparisons. Real World ASP.NET Best Practiceshttp://www.amazon.com/Real-World-ASP-NET-Best-Practices/dp/1590591003

|||

Thanks a lot for your opinion. By "well written connection object that gets reused" do you mean creating an SqlConnection object once, and try to re-use that same connection for as many command objects (select,insert,update tasks etc) as possible within the same scope?

|||

ilovecats:

do you mean creating an SqlConnection object once, and try to re-use that same connection for as many command objects (select,insert,update tasks etc) as possible within the same scope

Well, what I really mean is that you have a common object used by all pages & modules in your application that manages connections for you, along the lines of an Application Block like this onehttp://msdn2.microsoft.com/en-us/library/aa480458.aspx. Using something like this not only hides the details of the connection from the programmer, it goes a long way towards assuring consistency in connection strings -- which is something you need if asp.net is to be able to pool connections (ie, getting a connection is very expensive, so asp.net keeps a pool of connections around for reuse, if you request a connection and one is available from the pool that has an identical connection string, it gets that one instead of creating a new one).

Now, I'm sure that MS uses good programming when they obtain connections, but it used to be the case -- and I think still is largely the case -- that any differences in your connection string (even cosmetic ones, like case and extra white space) prevent sharing (pooling) connections, so if 2 people put in the same connection strings but with different case, they could not share connections in the pool. I don't know anymore how true this, I seem to recall reading that leading and trailing spaces don't matter anymore, but I'm not sure. A data connection application block would typically get the connection info from a config file, which is to say that everyone uses the same config file, which is to say everyone who connects to sqlserver1.mydatabase has an identical connection string. OTOH, if you use the SqlDataSource, every programmer is entering the connectio info (isn't that right -- perhaps I'm missing something because I haven't used them very much, but I think that's how it has to work -- if I'm wrong I hope someone will correct me), raising the possibility that the connection strings will be a little different, hence they cannot share connections.

I think I'll post a question on this topic to see if my understanding is current.

However, I frankly wouldn't worry about it too much. If you get into it, fine, but unless you're working on a high volume application it won't make a meaningful difference.

In my shop, we do use a data access application block that someone else wrote, but that didn't stop me from using the"no programming" features of asp.net 2.0 SqlDataSource because we just aren't a high volume app.

perform floating point addition in SQL stored procedure

Hi..
Is there any way to add the value of 4 column and at the same time print the result together with another column?

I have this stored procedure:

CREATE PROCEDURE sp_queuelist AS
BEGIN
DECLARE @.temp1 As Decimal
DECLARE @.temp2 As Decimal
DECLARE @.cash As Decimal
DECLARE @.cheque As Decimal
DECLARE @.card As Decimal
DECLARE @.nets As Decimal
DECLARE @.bill As Decimal
DECLARE @.company As Decimal

SELECT
@.cash=Cash,@.cheque=Cheque,@.card=Card,@.nets=Nets,
@.bill=Bill,@.company=Company
FROM QUEUE
ORDER BY QNo

SET @.temp1 = @.cash + @.cheque + @.card + @.nets
SET @.temp2 = @.bill +@.company

Select QNO,
PCNo,
PName,
@.temp1 As totalCash,
@.temp2 As totalContract,
Doctor

FROM QUEUE
ORDER BY QNo
END
GO

Basically I want to add 4 columns: cash+cheque+card+nets into totalCash and bill+company to totalContract.

All the 6 field types are decimal.

I want to calculate temp1 and temp2 and then select the rest of the column to be displayed in the datagrid.

However, this stored procedure gives me 2 problems:
1. It gives the rounding result of the addition of decimal number, not the decimal itself.
2. All the rows in datagrid display the same result for totalCash and totalContract, which is the total from the last row in the table.

I seldom use stored procedure.
Is there any way to solve this problem?
Any suggestion is most welcomed.
Thank you in advanced.
Sincerely

Agustina(1) you can try changing the decimal to float.

(2) in the design of your table, you can set the formula for the column as sum of the other 4 columns. that way you dont need to worry abt doing the addition. anytime you make any change in any of the columns, the computed column is automatically updated.
if you need more help in this approach, let me know.

HTH.

perform calculation for only desired rows

hi all, i wasnt quite sure where to look to answer my particular problem so i am posting up this thread in the hopes that someone can point me in the right direction. in my report, i am showing sales figures for an area. i added a table to display sales from this year, sales from last year, and then the comparable percentage('this' divided by 'last' and then minus one). to account for some ppl who didnt have sales last year, i was able to use an IIF expression to return "N/A" in the textbox. my problem is i want the compared percentage to show for the area total, but not including ppl who were an 'N/A'. i am assuming that some sort of expression will be needed for the area total row, i.e. i want the area total to sum up this year and last year and then get the percentage, but i need to filter out the individuals who didnt have data for last year.

as an example

nsty nsly percentage

total: 15 7 X

A: 5 4 25%

B: 5 3 66%

C: 5 0 N/A

the percentage for total, X, should be (10/7)-1, with p0 as the format; right now it is summing rows A, B, and C.. how can i exclude row C from the calculation?

am i on the right track by researching filters and expressions, or is this a matter for the query

thanks in advance for any help

Hi

An expression will be able to handle this fine.
Use the IIF function to only include nsty if nsly is greater than zero.
For your total % sum use something like this:

= (Sum(IIF(Fields!nsly.value > 0,Fields!nsty.value,0))/Sum(Fields!nsly.Value)) -1

Hope this helps

Cheers
Mark
|||

thanks for the response; i think i still need to expand on the expression a little bit but at least i know how to go about it now.