<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 14 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
{font-family:"MS Mincho";
panose-1:2 2 6 9 4 2 5 8 3 4;}
@font-face
{font-family:"MS Mincho";
panose-1:2 2 6 9 4 2 5 8 3 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:"\@MS Mincho";
panose-1:2 2 6 9 4 2 5 8 3 4;}
@font-face
{font-family:Webdings;
panose-1:5 3 1 2 1 5 9 6 7 3;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
{mso-style-priority:34;
margin-top:0cm;
margin-right:0cm;
margin-bottom:0cm;
margin-left:36.0pt;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
span.EmailStyle17
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri","sans-serif";}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
/* List Definitions */
@list l0
{mso-list-id:807818082;
mso-list-type:hybrid;
mso-list-template-ids:941804862 -724420464 67698691 67698693 67698689 67698691 67698693 67698689 67698691 67698693;}
@list l0:level1
{mso-level-start-at:0;
mso-level-number-format:bullet;
mso-level-text:-;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:20.5pt;
text-indent:-18.0pt;
font-family:"Calibri","sans-serif";
mso-fareast-font-family:"MS Mincho";
mso-bidi-font-family:"Times New Roman";}
@list l0:level2
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:56.5pt;
text-indent:-18.0pt;
font-family:"Courier New";}
@list l0:level3
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:92.5pt;
text-indent:-18.0pt;
font-family:Wingdings;}
@list l0:level4
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:128.5pt;
text-indent:-18.0pt;
font-family:Symbol;}
@list l0:level5
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:164.5pt;
text-indent:-18.0pt;
font-family:"Courier New";}
@list l0:level6
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:200.5pt;
text-indent:-18.0pt;
font-family:Wingdings;}
@list l0:level7
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:236.5pt;
text-indent:-18.0pt;
font-family:Symbol;}
@list l0:level8
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:272.5pt;
text-indent:-18.0pt;
font-family:"Courier New";}
@list l0:level9
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:308.5pt;
text-indent:-18.0pt;
font-family:Wingdings;}
@list l1
{mso-list-id:1668707026;
mso-list-type:hybrid;
mso-list-template-ids:2145701412 -52678658 67698691 67698693 67698689 67698691 67698693 67698689 67698691 67698693;}
@list l1:level1
{mso-level-start-at:0;
mso-level-number-format:bullet;
mso-level-text:-;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:"Calibri","sans-serif";
mso-fareast-font-family:"MS Mincho";
mso-bidi-font-family:"Times New Roman";}
@list l1:level2
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:"Courier New";}
@list l1:level3
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Wingdings;}
@list l1:level4
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Symbol;}
@list l1:level5
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:"Courier New";}
@list l1:level6
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Wingdings;}
@list l1:level7
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Symbol;}
@list l1:level8
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:"Courier New";}
@list l1:level9
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Wingdings;}
ol
{margin-bottom:0cm;}
ul
{margin-bottom:0cm;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Jolyon already mentioned a lot of good things to look at (how often you update the data, the cardinality/distribution of the data you index, etc)<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>I am more of an Oracle guy and never had any experience with FireBird/InterBase … but I guess those two characteristics will apply to any RDBMS system:<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>1.) Maintaining indexes takes time. Any update/insert/delete on the data means indexes also have to be touched. So make sure you get the balance between write/read right and not overindex things. <br><br><o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>2.) scanning indexes and then looking up the datarow for it costs time too. For Oracle the golden rule of thumb is to forget about indexes if you access more than 15% of the table - a full table scan will be faster than an index. That’s just a guide, hardcore performance tuning specialists look at length of data stored in a row and storage block size and a few other things and depending on those numbers it can be as low as 5% or as high as 30% (for tables with very small rows). I am assuming that Firebird probably isn’t doing as much optimization for caching data as Oracle does, in which case those %-numbers might even be lower for Firebird than with Oracle.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";color:black'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";color:black'><br><br></span><span style='font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D'><o:p></o:p></span></p><p class=MsoNormal style='margin-bottom:12.0pt'><span style='font-size:10.0pt;font-family:"Arial","sans-serif";color:black'>Kind regards,<br><br></span><span style='font-size:10.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p></o:p></span></p><p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Arial","sans-serif";color:black'>Stefan Müller</span></b><span style='font-size:10.0pt;font-family:"Arial","sans-serif";color:black'>,<br>R&D Manager</span><span style='font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D'><br><br></span><b><span style='font-size:10.0pt;font-family:"Arial","sans-serif";color:white;background:red'>ORCL</span></b><span style='font-size:10.0pt;font-family:"Calibri","sans-serif";color:#1F497D'> </span><b><span style='font-size:10.0pt;font-family:"Arial","sans-serif";color:black'>Toolbox Ltd.</span></b><span style='font-size:10.0pt;font-family:"Calibri","sans-serif";color:#1F497D'> <br></span><span style='font-size:10.0pt;font-family:"Arial","sans-serif";color:black'>Auckland, New Zealand</span><span style='font-size:10.0pt;font-family:"Calibri","sans-serif";color:#1F497D'> <o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:9.0pt;font-family:Webdings;color:#777777'><br>P</span><span style='font-size:9.0pt;font-family:"Arial","sans-serif";color:#777777'> Please consider the environment before printing this email<br><br>This message is intended for the adresse named above and may contain privileged or confidential information.<br>If you are not the intended recipient of this message you must not use, copy, distribute or disclose it to anyone.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'> delphi-bounces@listserver.123.net.nz [mailto:delphi-bounces@listserver.123.net.nz] <b>On Behalf Of </b>Jolyon Smith<br><b>Sent:</b> Monday, 31 March 2014 8:56 a.m.<br><b>To:</b> NZ Borland Developers Group - Delphi List<br><b>Subject:</b> Re: [DUG] Using Boolean (Char(1)) in Firebird<o:p></o:p></span></p><p class=MsoNormal><o:p> </o:p></p><div><p class=MsoNormal style='margin-bottom:12.0pt'>I don't think you can adopt a general rule for all boolean type conditions in data. In the two example fields you cite, for example, I can see that there is a potential difference in the nature of the booleans involved.<o:p></o:p></p><p class=MsoNormal>ActiveRecord - looks like something that could change over time. A record that was active may become inactive and I further speculate that there will over time be far more inactive records than active ones.<br><br>AccountTransactionType - looks like something that is fixed. The type of a transaction seems unlikely to change once that transaction has been recorded. You might call this a "static" boolean, as opposed to the more "dynamic" nature of the previous example.<o:p></o:p></p><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Of course, more specific domain knowledge may reveal these assumptions to be invalid, but you get the general idea.... the characteristics of a particular datum go beyond it's simple data type and those characteristics in turn determine the most appropriate implementation (which in turn will depend on whether the dominant context is OLTP or OLAP - i.e. efficiency of creating/modifying data vs efficiency of queries).<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>In the case of "static" booleans for example, you might consider creating separate tables for records of different values in this field. For convenience of querying all records you can of course project a view which unions the two (or more) tables involved, with a derived, virtual column containing the discriminating field value. This also opens up the possibility that the most efficient indexes for rows of a certain type (i.e. now table) may well be different than those for the other. i.e. the way you work with Income transactions might benefit from different indexes than Expense transactions.<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>On the other hand, the way you work with income and expense transactions may mean that you are better off having indexes operating over ALL transactions, regardless of Income/Expense type.<br><br>See what I mean about "the best way" being dependent on far more than just the data type ?<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>And there's still more to it than that...<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>w.r.t index selectivity, I am not convinced that the 1 / # of distinct values metric is a particularly reliable measure. It surely assumes an even distribution of distinct values across the data set ?<o:p></o:p></p></div><div><p class=MsoNormal style='margin-bottom:12.0pt'><br>i.e. if you have 100,000 records and they have a column where 50,000 rows have one value and 50,000 have another, then yes, the efficiency and thus the utility of any index on that value is going to be negligible (but then, no better than having no index isn't actually *worse*, is it ? Although there will be some overhead introduced in maintaining the index, though I doubt this will itself be hugely significant).<br><br>On the other hand, if only 1,000 of those 100,000 records have one value and the remaining 99,000 have another, AND if your application most often queries that table to select those in the smaller subset (the 1,000) then whilst an index may not be of any benefit for querying the 99,000, it surely will provide benefit for those queries that select the 1,000 (or from among them), a benefit which *might* be worth the overhead of maintaining that index even though it provides little/no benefit for the handful/minority of queries that work with the 99,000 records ?<br><br><o:p></o:p></p></div><div><div><p class=MsoNormal>The bottom line is, there is no shortcut for properly understanding your data and the way your application(s) work(s) with that data for correctly tuning your database structure and metadata for optimal performance.<o:p></o:p></p></div></div><div><p class=MsoNormal><br>:)<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p><div><p class=MsoNormal>On 30 March 2014 15:19, Steve Peacocke <<a href="mailto:steve@peacocke.net" target="_blank">steve@peacocke.net</a>> wrote:<o:p></o:p></p><div><p class=MsoNormal>Hi all,<o:p></o:p></p><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>I'm playing around with a Firebird database and wanted to know from you DB experts out there how you handle booleans in a table.<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>These could be as simple as<o:p></o:p></p></div><div><p class=MsoNormal> ActiveRecord (Y/N)<o:p></o:p></p></div><div><p class=MsoNormal> AccountTransactionType (I/E) - (Income or Expense)<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>That last I would normally think would be "Income (Y/N)" so that would be a boolean too.<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>My understanding is that this will never be indexed, even if you specifically add an index to it. So how do you handle it. There may be several boolean fields in a table definition.<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>As these tables c an contain several hundred thousand records, this could potentially slow down any query to say total all records last 3 years where Active and Income - as the only index would then be on the date field, there is a possibility that this could potentially be a very slow query.<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>I've heard of others creating another table to create, say, non-Avtive record ID's, but this one table could have several booleans, therefore creating several new tables (combining then into a single table with the field name would cause the same problem).<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Any thoughts?<span style='color:#888888'><br clear=all><o:p></o:p></span></p><div><p class=MsoNormal><span style='color:#888888'><br>Steve Peacocke<br>Mobile: +64 220 612-611<o:p></o:p></span></p><div><div><p class=MsoNormal><span style='color:#888888'><a href="http://nz.linkedin.com/pub/steve-peacocke/1/a06/489" target="_blank">Linkedin Professional Profile</a><o:p></o:p></span></p></div></div></div></div></div><p class=MsoNormal><br>_______________________________________________<br>NZ Borland Developers Group - Delphi mailing list<br>Post: <a href="mailto:delphi@listserver.123.net.nz">delphi@listserver.123.net.nz</a><br>Admin: <a href="http://delphi.org.nz/mailman/listinfo/delphi" target="_blank">http://delphi.org.nz/mailman/listinfo/delphi</a><br>Unsubscribe: send an email to <a href="mailto:delphi-request@listserver.123.net.nz">delphi-request@listserver.123.net.nz</a> with Subject: unsubscribe<o:p></o:p></p></div><p class=MsoNormal><o:p> </o:p></p></div></div></div></body></html>