February 12, 2005 at 11:58 pm
All SQL server 2000 performance related question.
1. All I need is Boolean data type. Performance is my main issue.
What will be more efficient, bit, tinyint, smallin, or integer? Why?
2. Which is more efficient performance wise?
Should I keep my data in two seperate tables or combine them into a single table with an additional column that identifies urgent/non-urgent ? table0 and table1 has identical columns. Table0 is for urgent entity and table1 is for non-urgent entity, both have identical columns. Which way it will be more efficient - performance wise?
Please explain why?
February 13, 2005 at 3:42 pm
From a performance perspective I think that the fact that BIT fields take up less space means you can more records on a data page and therefore more records can be loaded into memory therefore they should, theoretically, perform better.
However, I read somewhere that Microsoft products handle don't handle integer data types that efficiently and internally they use 32 bit integers for all integer calculations. I could be getting mixed up with this.
If you have a need to query Urgent items separately from non-urgent items on a regular basis then I would use the 2 table approach. The more variation there is in a field the more selective an index can be and boolean values aren't very selective.
If you know in advance that you need urgent items then having them in a separate table means that your records are pre-selected.
If you need to combine the tables then use a UNION query within a view.
February 14, 2005 at 12:03 am
One thing to note is that bit fields are only really useful if there are multiple of them in a row.
From BoL
"If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 through 16 bit columns, they are stored as 2 bytes, and so on"
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 14, 2005 at 12:15 am
I was suspecting that storeage is always in integer multiple of byte. Even so, would it take CPU longer to test a bit field than testing a tinyint? (Performance issue)
February 14, 2005 at 2:10 am
As has been mentioned, if you only have 1 of these in your table, it makes no difference if you use BIT or TINYINT. Both will consume 1 byte. All other datatypes you've mentioned, are ruled out by your performance paradigma. They are simply unnecessary too large, and won't yield you any advantage. Personally I prefer TINYINT, as it gives you more alternatives than 1, 0 and NULL, but that depends on your requirements.
To judge on your single vs. multiple table issue, you need to provide more information what this all is used for. How it is used and so on... There is no general "best practise" on that.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
February 14, 2005 at 3:48 am
Another issue to consider in the bit vs tiny int debate is that tinyint fields can be indexed, bits cannot.
Including a tinyint in an index can give more of a performance gain than is lost by the increase in size.
February 14, 2005 at 3:58 am
That's a myth! You *can* create indexes on BIT columns:
CREATE TABLE IndexBit
(
b1 BIT
)
CREATE CLUSTERED INDEX ixBit ON IndexBit(b1)
The command(s) completed successfully.
the example doesn't make too much sense, however it shows that this is possible. I'm not sure that you can do so in EM.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
February 14, 2005 at 4:07 am
Yes you can index BIT columns but the query optimizer will probably ignore the index because it is likely to have poor selectivity.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply