UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> How Do Fields Affect Table Performance?    
 
   
robert_trace
post Mar 31 2012, 11:07 AM
Post #1

UtterAccess Member
Posts: 39



How does the number of fields that you have in a table affect query performance?
I have heard that if I ran a simple query like...
CODE
SELECT email, password
FROM user;

...that it would run slower in a table that was "wide" (i.e. has tons of fields) versus a table that was "skinny" (i.e. has a few fields)?!
For example, if I had 100 User attributes like First_name, Last_Name, Address, Interests, Hometown, Favorite_Team, etc that it would provide a lot of overhead even if I wasn't including those fields in my query like above.
Is that True or False?
Sincerely,
Rob
Go to the top of the page
 
+
Doug Steele
post Mar 31 2012, 11:28 AM
Post #2

UtterAccess VIP
Posts: 18,565
From: St. Catharines, ON (Canada)



As far as I'm aware, it's false.
Go to the top of the page
 
+
HiTechCoach
post Apr 3 2012, 10:27 AM
Post #3

UtterAccess VIP
Posts: 18,666
From: Oklahoma City, Oklahoma



It usually is actually true. This has to do with how the database reads multiple records and uses buffering. It is about RAM speed versus hard drive access speed. RAM is much faster
Back in the 80's is when I first became aware of record size and how to match it to the way a database read records.We used to pad records with trailing fields with spaces. This forces the record size to there were evenly divisible into the buffer read size. This would make the database read a-heads/buffering more efficient. The key to smaller record sizes is that is allows a larger number if record to be read from RAM before another read has to be done from the disk drive.
I have found that event with JET/ACE and most SQl server that if you match your record lengths to match how things like read buffer size of the database and drive controller , disk sector size, etc. you can improve performance.
Go to the top of the page
 
+
nuclear_nick
post Apr 3 2012, 12:12 PM
Post #4

UtterAccess Guru
Posts: 582
From: Ohio, USA



What about indexes on the table? I'm under the impression that indexes, done properly, can speed up searches on a table, as long as you include an index in the query. For instance, adding a UserID as an index and putting it in the query results would speed up the query results.
Go to the top of the page
 
+
niesz
post Apr 3 2012, 12:32 PM
Post #5

Utter A-fishin'-ado
Posts: 17,957
From: Cincinnati, Ohio, USA . . . ><((((°>



Indexing can improve query speed, but slows down write speed. Also indexes need to be placed on fields selectively. For instance, let's say you have a 1 milllion row table and a field called Active, (boolean or True/False). It wouldn't make sense to index that field because the values within it aren't diverse enough.
Think of indexes as hints to the database as to where to start looking for further details about a record.
Go to the top of the page
 
+
niesz
post Apr 3 2012, 12:40 PM
Post #6

Utter A-fishin'-ado
Posts: 17,957
From: Cincinnati, Ohio, USA . . . ><((((°>



@ robert_trace,
While it's true that selecting specific fields from your table can improve speed (mostly because less data has to move across the network), that doesn't provide one a license for table with 100 fields. Normalization best practices still trump ease of initial development.
Go to the top of the page
 
+
HiTechCoach
post Apr 3 2012, 07:30 PM
Post #7

UtterAccess VIP
Posts: 18,666
From: Oklahoma City, Oklahoma



Index do help with performance when used properly. The original question was about how the number of fields (record size) has to do with performance.
ven with good indexing, a table with lots of field or a really long record can have a negative impact performance.
It is my understand and experience that the database engine has to read the entire record to get data from any fields. Index allow the record to be found faster but the record still must be read. Even is you want to return just a few fields from a table in your query that is the entire record is still read in. The database engine discards the fields not needed and then returns the back just what you requested. With a Client/Server database it is process on the server side so only the desired fields are passed to the client over the network. This still must be done even with an index.
Ofind that having a shorter record lengths help with performance more with the Access JET/ACE format databases than with Client/Server databases. Properly Normalized tables is key. Then indexing can really help. Indexes on a poorly normalized database will still be slower than if properly normalized and indexed. It comes down to the fact that indexes can't fix normalization issues.
Go to the top of the page
 
+
BananaRepublic
post Apr 4 2012, 03:35 AM
Post #8

Admin under the bridge
Posts: 1,197
From: Banana Republic



I want to emphasize that questions like those are very highly dependent on the mechanics of database engine and in some parts, even upon mechanics of the operating system's filesystem and hardware. SQL is just an abstraction that frees us from having to figure out the procedural steps required to retrieve records in most efficient manner. But it's still an abstraction and it leaks. If you're not familiar, I strongly recommend that you read about Spolsky's Law of Leaky Abstraction.
The answer that is correct for one database engine could be wrong for other. For example, I know for a fact that in some database engines, some query may be fulfilled by direct read of the index page without even touching the data page. Then there's also the question of whether a database engine uses pages (Access, SQL Server, and MySQL's InnoDb does. MySQL's MyISAM doesn't.). Then there's question of whether a row size is fixed (if it's fixed, then it's a simple offset of row number * bytes of row size as well column offset). It may be further limited by how hard drive works (e.g. must read a full sector even if it's much bigger than a single record).
There are some database engines specialized for wide tables (e.g. Google's BigTable which is also nonrelational but is very effective in fulfilling a Google search query).
Because there are so many factors and nuances involved with the mechanics, I assert that the best candidate for working out optimal route to the data is database engine's optimizer which "knows" more about the database engine than we would know about it. Therefore, it's typically more important that we write SQL that works to the optimizer's favor than figuring out the actual mechanics involved. Other has correctly pointed out that a well-structured data model also works to its favor. The only time when you would actually about the mechanics is when you're literally pushing the database engine to limits and you've already used up all optimizations possible at the SQL layer. That such time is exceedingly rare. Furthermore, Access on the whole, really does not offer much insights into the optimizers compared to other database engines. You have SHOWPLAN, and you can glean significant gleans from reading the whitepapers (most of them written for Jet 3.0 or so and to my knowledge hasn't significantly changed since).
So in short, I think data modeling and how one writes the SQL matters and how fields are indexed much more than how big a table is.
One more point that hasn't been mentioned about indexing is that cardinality also matters. To give a slightly absurd example - suppose we had a table with a boolean field and 50% of the records had it set to true while other 50% had it false. We add an index. Will the database engine use it? No, not likely because it would decide that reading the whole table (or just the column) is going to be faster than figuring which's which. OTOH, if it had 10% to true, 90%... will it use it? Only if the criteria is where it's true and then it's a maybe. Same thing is true when you have a column where 90% of field value are NULLs - an index with several NULL entries may not work that great but Access does have an index to ignore nulls in the index which can therefore help when you're searching for precious few records that contains non-null values. Similarly, SQL Server has filtered index and PostgreSQL has functional index which allows for more complex indexing schema that allows us to keep the cardinality low and therefore much more profitable for the engine to use the index. I've heard a rough estimate saying that if the most common values are only less than 33% of the whole possible values, then index will be effective, anything more than that, then index will be largely ignored in the optimization but I've not had opportunity to test this assertion.
HTH.
Go to the top of the page
 
+
nuclear_nick
post Apr 4 2012, 05:07 AM
Post #9

UtterAccess Guru
Posts: 582
From: Ohio, USA



Just wanted to add a note of appreciation for all that have contributed to this discussion. One of the main reasons I love what I do (design, program, maintain Access databases for the company I work for) is that there has been many opportunities to keep learning, and I love the "Eureka!" moments when I've been working on how to accomplish what they want accomplished and after spending what seems to be too long on an issue, then the light bulb goes off.
lot of interesting, fun, good information in this discussion for a geek like me. This made my day. Thanks.
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 31st October 2014 - 11:19 AM