1

i've got a question about which query is better with higher performance in sql

SELECT

1st comparison:

Declare @Variable VARCHAR(MAX)<BR>
SELECT top 1 @VARIABLE = COLUMN2 FROM TABLE WHERE COLUMN1 like 'Any value'


2nd comparison:

Declare @Variable varchar(MAX)
select top 1 @variable = t.column2 from table t where t.column1 like 'any value'

update

1st comparison:
UPDATE T set column2 = 'any value' from table T where column1 = 'any value'
2nd comparison:

UPDATE TABLE SET COLUMN2 = 'any value' where column 1 = 'any value'

delete

1st comparison:

delete t
from table t
where column1 = 'any value'


2nd comparison

delete from table where column1 = 'any value'

I just need your opinion on which query is better, and if there is a better way to optimize my queries performance, can someone tell me how is it?

Dhanuka
  • 2,826
  • 5
  • 27
  • 38
Stefan
  • 13
  • 3
  • It seems to be a duplicate:http://stackoverflow.com/questions/8363108/how-does-table-alias-names-affect-performance – Dalex Jun 01 '15 at 11:49
  • 2
    Indexing is a key component to query performance so all of these queries will benefit by an index (ideally clustered) on the column specified in the `WHERE` clause. Note that the use of `TOP` without `ORDER BY` is not deterministic; you may get different rows for each execution by happenstance. – Dan Guzman Jun 01 '15 at 12:01

2 Answers2

1

The comparison doesn't matter for performance.

Almost all the time sql performance is about lookup. In other words , how fast data can be retrieved by sql database. In other times, it's about how fast you can insert or remove.

So, how do you make sql server perform fast?
By making it's look up perform faster and you do that by providing with an index, similar to the one in any book, indexing can help you quickly find chapters.

So, in your query, if you create an index for column1 than the sql database can quickly seek the value from the index and make your select, update and delete query run faster.

Having an alias just makes your code readable. It has nothing to do with performance.

ANewGuyInTown
  • 5,957
  • 5
  • 33
  • 45
  • Thank you for your answer :) – Stefan Jun 01 '15 at 16:02
  • and by the way, what if the table i use have over 5k rows? Will indexes affect it? – Stefan Jun 01 '15 at 16:03
  • Yes, of course. Again, using the book analogy, if you have a book with 2000 pages and one with 100 pages. Indexing will benefit both and you'll find required pages quickly in both. But, if you have a book with 2 pages, it's not very useful to have indexes, you could just `scan` pages quickly, than trying to `seek` something with an `index`. – ANewGuyInTown Jun 01 '15 at 23:38
0

Queries under comparison you posted differ only in syntax of writing them. There won't be any different in performance for both the queries. Syntax parser will generate same syntax tree for these queries.

You can think of them as 

int sum = x + y;

can also be written as

int sum = y + x;

syntax tree for them would be

   sum
  -   -
x       y

Difference in performance can be observed only by indexing, statistics collected for tables.

Some of the highlights about indexes

  1. indexes has nothing to do with no of records present in a table instead better parameter to classify index performance is size of a row.

  2. (SizeOfKey attribute x noofrecords) in a table is better parameter to decide whether to use index or not

e.g.

no of records 5000, key column is Age with data type as byte i.e. 1 byte
total size = 5000 bytes i.e. not even 1 page - Indexes won't be useful.

no of records 5000, key column is money i.e. 8 bytes
total size = 5000 * 8bytes = 40000 bytes = 40000/8Kb = 5 pages (1 page = 8 kb)
Index won't be very helpful

no of records 50000, key column is byte i.e. 
50000 bytes = 7 pages, index won't be very helpful

no of records 50000, key column is bigint i.e.
(50000*8bytes)/8Kb = 50 pages, index can be help up to some extent.

no of records 500000, key column is bigint i.e.
(500000*8bytes)/8Kb = 500 pages, index is most likely to be helpful depending upon your search arguments.
Shantanu Gupta
  • 20,688
  • 54
  • 182
  • 286