StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POChoosing SQL Server data types for maximum speed
text
Body
copied!<p>I'm designing a database that will need to be optimized for maximum speed.</p> <p>All the database data is generated once from something I call an input database (which holds the data I'm editing, mainly some polylines, markers, etc for google maps).</p> <p>So the database is not subject to editing, but it needs to hold as many data as it can for quickly displaying results to the user (routes across town, custom polylines, etc).</p> <p>The question is: choosing smaller data types for example like smallint over int will improve performance or it will affect it? Space is not quite a problem, after some quick calculations, the database will not exceed 200mb, and there will not be tables with more than 100.000 rows (average will be around 5.000).</p> <p>I'm asking this because I read some articles around the internet and some say that smaller data types improve performance others say that it affects it because additional processing must be done. I'm aware that for smaller databases probably results are not noticeable, but I'm interested in every bit because I'm expecting many requests which will trigger a lot more queries.</p> <p>The hosting environment is gonna be Windows Server 2008 R2 with SQL Server 2008 R2.</p> <p><strong>EDIT 1:</strong> Just to give you an example because I don't have a proper table structure yet: I'm going to have a table which will hold public transportation lines (somewhere around 200), identified by a unique number in real life, and which is going to be referenced in all sorts of tables and on which all sorts of operations are going to be made. These referencing tables will hold the largest amount of data.</p> <p>Because lines have unique numbers, I have thought of 3 examples of designs:</p> <ol> <li><p>The PK is the line number of datatype: smallint</p></li> <li><p>The PK is the line number of datatype: int</p></li> <li><p>The PK is something different (identity for example) and the line number is stored in a different field.</p></li> <li><p>Just for the sake of argument, because I used this on the 'input database' which is not subject to optimization, the PK is a GUID (16 bytes); if you like, you can make a comparison of how bad is this compared to others, if it really is</p></li> </ol> <p>So keep in mind that the PK is going to be referenced in at least 15 tables, some of which will have over 50.000 rows (the rest averaging 5.000 as I said above) which are going to be subject to constant querying and manipulation, and I'm interested in every bit of speed that I can get.</p> <p>I can detail this even more if you need. Thanks</p> <p><strong>EDIT 2:</strong> And another question related to this came to my mind, think it fits into this discussion: </p> <p>Will I see any performance improvements <strong>in this specific scenario</strong> if I use native SQL queries from inside my .NET application rather than using LINQ to SQL? I know LINQ is strongly optimized and generates very good queries performance-wise, but still, sure worth asking. Thanks again.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload