Tag Archives: database

How database index works

Ever wonder when you trigger a query that took 2 minutes to complete then when you applied index as suggested by the SQL Server Database Engine Tuning Advisor then it suddenly runs under a second?.

Think of it like this, when a data is stored in a disk based storage devices, the entirety of the data is stored as blocks of data. When running a query against unindexed field which value is not unique, to search a value it would require to scan the entire blocks of data (at worst N).

With an indexed field, a new blocks of data is created to store the indexed field which value is already sorted. Therefore binary search is performed when trying to find a value that in the indexed fields (log2 N).

Now for example if you have a table schema like the following

Person

Field Data type Size in disk
Id (primary key) unsigned int 4 bytes
FirstName char(50) 50 bytes
LastName char(50) 50 bytes

with that schema, to store a record in a disk it would take 104 bytes, or in one disk block (1024 bytes) it can store 1024/104 = 9 records in a disk block. If you have 1.000.000 records then it would take 1.000.000/9 = 111.111 disk block to store all of those data.

Now depending on the type of query that you run against the table you would get different result in performance, for example if you do a search query against the Id field it would perform a binary search (log2 n) which results in log2 111.111 = 16 block access. This is possible because the Id field is a primary key which value has to be unique and also has been sorted.

Compare it with a query against the FirstName field, since the FirstName field is not sorted a binary search would not be possible, thus it would require exactly 111.111 block access to find the value,  a huge difference.

Creating an index would help greatly in slow performing query, but once again you have to keep mind that creating index would also mean creating a new data structure that is stored in the disk. For example if we are to create an index for the FirstName field

Field Data type Size in disk
FirstName char(50) 50 bytes
(record pointer) special 4 bytes

Based on that schema then it would require 54 bytes for each record. 1024/54 = 18 record in a disk block.  1.000.000/18 = 55.555 disk block. Manage your index wisely, the more fields the index contains or the more index that you created the more disk space that it’s going to take.

reference:

http://stackoverflow.com/questions/1108/how-does-database-indexing-work/1130#1130

SQL Connection Pooling

It is expensive to create an SQL connection, a socket connection must be established, a handshake must occurred, the connection credential must be check against the list of known credentials. To optimize this a technique called SQL Connection Pooling is implemented.

Every time a connection is required, we request it from the connection pool, if there’s exist the connection by the specified connection string then it’s going to return that connection rather than creating a new one. Every time we are going to close a connection we do not close it but instead we returned it to the connection pool. This way we can save the connection for later use.

reference: http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.80).aspx

Sitecore–update existing file based media to database storage

If you implement a separate server for Content Management Server (CM) and the Content Delivery Server (CD), it is advised that you disable the file based media storage so that CD can retrieve the image information.

The following code shows how to update the existing file based media to database storage, from it you could create a Sitecore app to make a handy tool to update your existing media files.

 

What is SQL Injection ?

It’s a technique which is used to attack a software, be it a desktop application or a website as long as it uses a database server behind it. It is done by inputting a malicious input in attempt to get a dangerous query to run.

Example:

This will get all products where the product name is Car

Imagine the Car value is retrieved from an input text on a form somewhere, what would happen if we input something like this.

This query will delete –assuming the running user has access- all data from our products table. which is clearly not good.

Issues like this are commonly happens on code that concatenates string to form query

What should we do ?

  • Do not concatenates string to form a query
  • Use a parameterized query to execute query, ORM tools such as EF uses parameterized query
  • Turn on custom error page on production to avoid giving crucial information to malicious users
  • Give the running user appropriate permissions, do not give access to modify table or creating new record in table if you only want a read only operation

Granting Execute permission to all SP

Was shown a quick way to grant Execute permission to SP today by a colleague, it seems i’ve been doing it the hard way all this time.

Imagine  i had this list of SP.

image

the list still goes longer than that. now the story is that i need to give execute permissions to those SP, and the way that i know is to do this.

or by doing this

i would get this

image

copy paste that on the sql editor and life is good.

OR,  as my colleague showed to me

i could do this instead

  • Right click on the database, choose properties
  • From permissions, search the user that you want to give permission
  • then on the Execute row, tick the grant checkbox
  • click ok and the permissions is setup for that user

image

now life is even better!.

Issue with getting database via Sitecore API

i’ve spend the last couple hours trying to figure out what’s wrong with my code. I was trying something simple, getting an item from the Sitecore Web database. Which turns out not so simple after all, frequently i cannot retrieve any field values from the item that i retrieved.

After a long time trying out different things, renaming the section, rename the item name, change the template, change the content tree structure, checkout if any custom pipeline might be the isssue…

It turns out that this line of code -which looks ok at first glance- has an issue

in other area of the software that we develop we were using this code to get the database instead

it turns out that those two ways of getting the database instance is totally different from what i originally thought.

By using the constructor approach we only create an empty database object while using the latter approach it will build the database instance with all of the configuration values within the config file via reflection.

thanks to this Stackoverflow post my misery has ended.