December 21, 2010 at 3:49 pm
Much of what you want to do depends on the distribution of the data. If only a small percentage of the rows in the table have one or more of those columns not null, then adding a boolean type column that is maintained by a trigger could pay off. You could then add an index on that column with a WHERE clause (since this is 2008) and avoid a table scan.
It has to be a very small percentage of the table that would match the criteria in order to be useful because key lookups are very expensive.
Todd Fifield
December 21, 2010 at 4:27 pm
tfifield (12/21/2010)
Much of what you want to do depends on the distribution of the data. If only a small percentage of the rows in the table have one or more of those columns not null, then adding a boolean type column that is maintained by a trigger could pay off. You could then add an index on that column with a WHERE clause (since this is 2008) and avoid a table scan.It has to be a very small percentage of the table that would match the criteria in order to be useful because key lookups are very expensive.
Todd Fifield
Other than the size of the column being tested, how would this be different from a filtered index on the column itself? To avoid lookups he has to have a true covering index or indexed view.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 24, 2010 at 10:56 am
tfifield (12/21/2010)
Much of what you want to do depends on the distribution of the data. If only a small percentage of the rows in the table have one or more of those columns not null, then adding a boolean type column that is maintained by a trigger could pay off. You could then add an index on that column with a WHERE clause (since this is 2008) and avoid a table scan.It has to be a very small percentage of the table that would match the criteria in order to be useful because key lookups are very expensive.
Todd Fifield
Unfortunately, no, it's between 40,000 - 60,000 out of an 80,000 record table. I guess a bit more thought on my part might have prevented this entire thread: since I'm joining so many of the records anyway, trying to speed the initial selection just isn't going to help much. This whole business is a port from JET with a much smaller recordset, and apparently needs some more analysis. Shoveling this many records around (and then sorting them, to boot) simply isn't a very good way to proceed. It's inefficient and fairly slow, even when it's SQL Server and a hotrod machine doing it.
Thanks for the insights, I think it's time to bail this entire function and start working on a better mousetrap.
December 24, 2010 at 10:59 am
The Dixie Flatline (12/21/2010)
tfifield (12/21/2010)
Much of what you want to do depends on the distribution of the data. If only a small percentage of the rows in the table have one or more of those columns not null, then adding a boolean type column that is maintained by a trigger could pay off. You could then add an index on that column with a WHERE clause (since this is 2008) and avoid a table scan.It has to be a very small percentage of the table that would match the criteria in order to be useful because key lookups are very expensive.
Todd Fifield
Other than the size of the column being tested, how would this be different from a filtered index on the column itself? To avoid lookups he has to have a true covering index or indexed view.
What I was trying to do was test one (de-normalized) column instead of many individual columns, but several posts here and some tests on my part have shown me that the selection is a very small part of the overall cost, and that this is not a good avenue to pursue.
December 28, 2010 at 1:45 pm
I understand. You are showing wisdom by rethinking the process while you can. Best of luck to you in the new year.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply