Geek Answers Handbook

Sql wildcard: service overhead?

I asked this question and cannot find consistent opinions or opinions based on solid data. I would just like to know if using a wildcard in an SQL SELECT statement involves more overhead than an individual call to each element. I compared the execution plans in several different test requests, and it seems that the grades always read the same way. Is it possible that some overhead is incurred elsewhere or are they really handled the same way?

What I specifically mean:

SELECT *

vs.

 SELECT item1, item2, etc.
+3
source share
5 answers
 SELECT * FROM...

and

 SELECT every, column, list, ... FROM...

will do the same because both are unoptimized scans

The difference is as follows:

  • additional search in sys.columns to solve *
  • contract / signature change upon table schema change
  • the inability to create a coverage index. In fact, no settings at all, really
  • need to refresh views needed if not schemabound
  • cannot index or schematize a view using *
  • ... and other things

Other SO questions on the same issue ...

+9
source

Do you mean select * from ... instead of select col1, col2, col3 from ... ?

I think it’s always better to name the column and get the minimum amount of information, because

  • your code will work regardless of the physical order of the columns in db. The column order should not affect your application, but it will be so if you use * . This can be dangerous in case of db migration etc.
  • if you name the columns, the DBMS can optimize further execution. For example, if there is an index that contains all the data you are interested in, the table will not be available at all.

If you mean something else with a “wildcard”, just ignore my answer ...

+2
source

EDIT: If you're talking about a wild asterisk map, as in Select * From ... , then see other answers ...

If you are talking about wildcards in predicate clauses or other query expressions using the Like operator, (_ , % ) , as described below, then:

This is because whether using a wildcard affects whether SQL is "SARG-ABLE" or not. SARGABLE, (Search-ARGument-able) means whether query search or sort options can be used as input parameters for an existing index. If you add a wild card at the beginning of the argument

  Where Name Like '%ing'

Then there is no way to cross the index in the name field to find the nodes that end in 'ing'.

If you add a wildcard to the end,

 Where Name like 'Donald%'

then the optimizer can still use the index in the name column, and the query is still available SARG

+1
source

If you call an SQL wild car *. This does not mean performance overhead. However, if the table is expanded, you can find the necessary fields that you are not viewing. In general, not being specific in the fields you are looking for or inserting, this is a bad habit. Consider

 insert into mytable values(1,2)

What happens if the table is expanded to three fields?

0
source

It can no longer be work in terms of an implementation plan. But if you retrieve columns that you really don't need, this extra network bandwidth is used between the database and your application. In addition, if you use a high-level client API that does some work with returned data (for example, Perl selectall_hashref ), then these additional columns will impose client-side performance costs. How many? It depends.

0
source

More articles:


All Articles