SQL Server Performance Forum – Threads Archive
bit to bool conversion
I wonder whether there is a quicker way to check the value of a bit than comparing it to 0 or 1.<br /><br />Example of what I do now:<br /><br />CREATE TABLE tForUpdate<br />(<br /> ID1 int NOT NULL,<br /> ID2 int NOT NULL,<br /> Switch bit NOT NULL<br />)<br /><br />CREATE TABLE tHistoricalValues<br />(<br /> ID1 int NOT NULL,<br /> ID2 int NOT NULL <br />)<br /><br />I’ve only used select so far, not update (which I want to – eventually).<br /><br />SELECT FU.ID2, HV.ID1<br />FROM<br />tForUpdate FU<br />INNER JOIN tHistoricalValues HV ON HV.ID2=FU.ID2<br />WHERE<br />FU.Switch=0<br /><br />I’ve used QA to get an estimated query plan and then selecting the values from<br />tForUpdate takes 95% of the time. Part of this seems to be converting the bit to an int<br />before comparing it.<br /><br />Running the query without the WHERE-statement is much faster, but I suspect that<br />will not be a good idea once I switch to UPDATE and the table contents continue to grow.<br /><br />The real tForUpdate contains something like 8 000 000 rows<br />The real tHistoricalValues contains something like 200 000 rows<br /><br />I will run this query once a week and both tForUpdate and tHistoricalValues <br />will grow with 200 000 rows every week.<br /><br />Any brilliant ideas? <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ /><br /><br />/HL<br />
The JOIN is on the second column only? You could add a separate index on ID2, or put ID2 before ID1 in what I assume is the PK on the table.
Yes, the join is only on the second column. Assuming the execution plan is correct the join only takes
5% of the execution time, so I don’t worry about the join. TY anyway, /HL
What is the idea behind the Switch column?
The Switch column tells whether that row has been handled or not.
I want to update it with a batch ID (ID1) to say in what context. /HL
The BatchId may offer a slightly better reponse time for filtering on NULLs, as compared to 1 or 0. Since you can filter on the ID1 column, the Switch column is redundant …
Unfortunately I don’t have ID1, before the update it is set to a default value of -1.<br />Otherwise I agree – it is redundant.<br /><br />This system will be completely replaced in 4 months or so, which means that a total<br />redesign of the database is not in the cards (except for the new version of course).<br />Otherwise that would be my prefered solution… <img src=’/community/emoticons/emotion-5.gif’ alt=’
‘ /><br /><br />Right now I’m only looking for the best/fastest way to construct my, gum & wire, patch.<br /><br /><img src=’/community/emoticons/emotion-1.gif’ alt=’‘ /><br /><br />/HL
]]>