Month: February 2012

Query Plan (01) – T-SQL vs Programming Language

by

A computer is a programmable hardware unit with many components integrated. CPU is one of the most important components which provides set of functionalities for calculations and memory/IO access. CPU can load data from memory to registries(memory units in CPU), perfrom calculation based on loaded data, and then following instructions within loaded data saves the result back to memory. Let’s say you want to add 2 values together, assuming they are all in the stack. You need to move them to AX and BX registry, perform ADD, and then move result back to stack.

POP AX //read an integer from stack to AX register
POP BX //read another integer from stack to BX register
ADD AX,BX // add 2 32-bit itegers together and save the result into AX
PUSH AX //save result back to stack

After result is saved to the stack, other functions can POP and perform code to show it on the screen. (back old days when MS DOS was prevalence, INT 21 can show charactors on the screen).

Read more

Performance Comparison – Tables, Temp Tables, and Table Variables

by

People are confused about the differences amoung user tables, temp tables, and table variables in terms of inserting performance. I did a test today on my laptop – Core I7 Q720, 8GB RAM, SSD. I have both tempdb and user db on the same hard drive. I tested following scenarios over permanent tables, permanent tables in tempdb, global temp tables, local temp tables and table variables.

  1. Heap: inserting records to a heap one by one, each insert is an auto commit operation
  2. Primary Key: clustered primary key over an identity column. each insert is an auto commit operation
  3. Heap – Transaction: all insertions are in one transaction for each batch, table is a heap
  4. PK – Transaction: all insertions are in one transaction for each batch, table has clustered primary key on an identity column.
  5. Heap BulkLoad: Bulk load data to a heap
  6. PK BulkLoad: Bulk load data to a table with clustered index on an identity column

Read more

Few Things About Pooled Connections

by

Pooled Connections are not a new concept of SQL Client. It reuses pool of opened connections for any new T-SQL requests. It provides a logically fresh new connection to application as you physically open a new connection. The fact behind it is that when you close a pooled connection, the connection is merely returned back to the pool without physically closing the tunnel(SQL Server doesn’t know the connection is closed by the client), and, when you open a new connection, SQL Client will open a new connection if there is no available connections in the pool. Otherwise SQL Client will get the connection from the pool. In this case, SQL Server still does not know that the connection is assigned for new purposes. Whenever you run the first command through a connection which assigned from the pool, SQL Client will “inject” execution of sp_reset_connection before the execution of your SQL statement or procedure to reset resources and mimik a brand new connection. A lot of discussions regarding sp_reset_connection in the internet. I will excerpt them at the end of this blog post. Apart from that, you must know few other things about it.

Read more