Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2004-08-05 : 12:03:46
|
| I've got a table "Users" with 94,000 rows. There's a BIT column named "Deleted". Often, I want to involve Users who are not Deleted (Deleted=0). I recall somewhere in BOL that BIT columns can't be indexed.The execution plan on a query I have shows a Clustered Index Scan with a cost ranging between 70% to 90% of the query. It looks like the execution plan scans the entire User table for "not Deleted" items before any of the joins are performed.I could rewrite the query to do the joins first in a subquery then examine the BIT deleted. Any other ideas on resolving this without rewriting the query?Sam |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-08-05 : 12:15:45
|
| It would be helpful to see the query....Are there other predicates?And why not create a partitioned view?Brett8-) |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-08-05 : 12:18:08
|
| [code]SELECT CenterID, Centername + ' (' + CAST(Total AS VARCHAR) + ')' As Centername FROM ( SELECT C.CenterID, CenterName, SUM(CASE WHEN CA.Assigned = 1 THEN 1 ELSE 0 END) AS Total FROM dbo.Centers C LEFT OUTER JOIN dbo.Users U ON U.CenterID = C.CenterID AND U.Deleted = 0 AND U.ClientID = @ClientID LEFT OUTER JOIN dbo.CourseAssignments CA ON CA.UserID = U.UserID AND CA.CourseID = @CourseID AND CA.Assigned = 1 WHERE C.ClientID = @ClientID AND (C.Inactive = 0 OR C.CenterID = @CenterID) -- Just the Centers in use by users. AND (@AdminCenterID IS NULL OR C.CenterID = @AdminCenterID OR C.CenterID = @CenterID) GROUP BY C.CenterID, CenterName ) A WHERE Total > 0 OR A.CenterID = @CenterID ORDER BY Centername[/code] |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-08-06 : 05:39:19
|
| Umh...changing the datatype to tinyint is not an option...?--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
daidaluus
Yak Posting Veteran
73 Posts |
Posted - 2004-08-06 : 12:51:33
|
| You cannot use BIT columns in primary key constraints, but you can safely use then in clustered and non-clustered indexes. |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-08-06 : 13:20:46
|
| Thanks for that info. Here's another question.Anytime I query table Users, there are 2 columns that are always in the WHERE statement: ClientID (INT) and Deleted (BIT)What's the tradeoff of indexing each column independently or CREATE INDEX IDX_UsersClientIDDeleted ON dbo.Users (ClientID, Deleted)Any reason the latter is better from a performance point of view?Sam |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-07 : 12:25:30
|
| I don't know why people keep saying that bit columns can't be indexed. They can be. They just aren't selective enough to be used. You can "cheat" and use an index hint, which sometimes actually improves performance for some completely insane reason. Technically, the composite index should work better btw, if you're always using it. We have this discussion and tests like this all the time at work. Search the forum for BIT index and see if you come up with any hints. I remember a bunch of us were actually posting test results on here once. It would be interesting to revisit those and see the results.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
|
|
Clustered Index Scan