[SQL Server] Do positions of columns/fields affect performance? - Tilted Forum Project Discussion Community

Stompy · 05-05-2004, 11:33 AM

Say I have a table with 10 fields. The very last field has a data type of DateTime, which I frequently perform searches with.

In addition to indexing, would it make the query faster if I repositioned that field (in the table's design) so that it was in the front? Instead of being:

Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9, DateField

It would be:

DateField, Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9

Kind of a silly question, but I always wondered if field positioning had any effect on how well a query performed. In theory it makes sense because if that date field was at the front as opposed to being behind 9 other fields, it seems the DB wouldn't have to sift through as much data to find what it needs.

On the otherhand.. I have no idea how a DB actually performs the searches, so it might not matter. Just wanted additional input on this.

Thanks!

Rawb · 05-05-2004, 11:15 PM

Taking MySQL and PostgreSQL as my examples, the only way it would make a difference is on full-table scans, and only if the columns in front of it are of variable length (VARCHAR's etc). Anytime you are resorting to full-table scans, your database is going to be slow (after about 50 columns, full-table scans loose out completely to indexed data) so the itty bit of speed you would stand to gain is most likely not worth it.

But if you have an index for that column, it should be stored in a different location from the actual data, and should make it not matter one little bit where the column is located.

From a design perspective however, the most important columns typically go first (at least in the schemas I build), but that's a guideline without any real reason for it.

05-05-2004, 11:33 AM	#1 (permalink)
Stompy Banned from being Banned Location: Donkey	[SQL Server] Do positions of columns/fields affect performance? Say I have a table with 10 fields. The very last field has a data type of DateTime, which I frequently perform searches with. In addition to indexing, would it make the query faster if I repositioned that field (in the table's design) so that it was in the front? Instead of being: Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9, DateField It would be: DateField, Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9 Kind of a silly question, but I always wondered if field positioning had any effect on how well a query performed. In theory it makes sense because if that date field was at the front as opposed to being behind 9 other fields, it seems the DB wouldn't have to sift through as much data to find what it needs. On the otherhand.. I have no idea how a DB actually performs the searches, so it might not matter. Just wanted additional input on this. Thanks! __________________ I love lamp.

05-05-2004, 11:15 PM	#2 (permalink)
Rawb Crazy Location: Salt Town, UT	Not a real DBA, but a MySQL guy Taking MySQL and PostgreSQL as my examples, the only way it would make a difference is on full-table scans, and only if the columns in front of it are of variable length (VARCHAR's etc). Anytime you are resorting to full-table scans, your database is going to be slow (after about 50 columns, full-table scans loose out completely to indexed data) so the itty bit of speed you would stand to gain is most likely not worth it. But if you have an index for that column, it should be stored in a different location from the actual data, and should make it not matter one little bit where the column is located. From a design perspective however, the most important columns typically go first (at least in the schemas I build), but that's a guideline without any real reason for it.