Question regarding SQL optimzation

Question

This might be a silly question but i just want to make this clear. Does it take longer to preform a SQL query if each row has a large amount of data inside of it?

For instance if i've like 2000 bytes worth of data stored as an blob (we call the colum "Data") in a row of a table containing 10 000 rows as a total (all of them similar with the blob size of "Data") . Will it then take longer to process of an search if i only search for the ID for one row e.g does the server have to like process the whole information stored in each colum of every row it passes by?

Answer 1

In general, there are 2 things that will determine the speed of your query:

• how long does it take to find the record(s) specified? If you're searching by ID, the things Quassnoi and Justin have said are true - assuming your ID is a primary key with an index associated with it.
• how long does it take for me to retrieve the data associated with this record and push it out of the database? In this case, the data types do matter - and BLOBs have a poorer reputation for performance than "native" data types such as integers or varchars. You also need to factor in the effort of transforming the blob into it's actual type at the client side.

For a single record, this should be a tiny overhead; if you ever need to retrieve large amounts of data, it might be slower.

Your database engine should have detailed documentation on the performance of BLOBs.

Answer 2

This depends on the engine you are using.

However, most modern engines are able to store the long data out of row: the actual row tables which need to be scanned in searching only store the pointer to the actual chunk(s) of long data.

Also, if you have an index on id in a heap table, the index will be used for the search. The index records only store the values of id and the record pointer. Even if the table is clustered (the records themselves are ordered by id), then the B-Tree search algorithm will be used to locate the record you're after, only processing the actual records in the final leaf-level page.

So most probably, the long data will not be scanned if you search for id.

If your data are stored in-row and no index is defined on the expression you are searching for, then yes, the engine needs to scan more records which will be more slow if they are large.

Answer 3

Does it take longer to preform a SQL query if each row has a large amount of data inside of it?

On paper, yes. Disk page reads then contain less rows, so you need more IO to extract the rows you're looking for.

In practice, the overhead can be tiny depending on how your database stores its contents. PostgreSQL, for instance, distinguishes between plain vs extended storage for data with variable length such as lengthy varchar, text or bytea.

Answer 4

In general if your ID column is the primary key on the table (or at least has an index) then a simple query like

SELECT ID,Data FROM Table WHERE ID = 1

will be just as fast no matter the size of the Data column