Performance Help: Binary Comparisons vs Full Normalization?

Last Post 18 Feb 2008 01:37 AM by SQLUSA. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
cragi
New Member
New Member

--
17 Feb 2008 11:18 PM
I have a pretty intensive query that I need performance help on. There are ~1 million de-normalized 'adjustment rows' that I am checking about 20 different conditions on, but each of these conditions has multiple possibile entries.

For example, one condition is 'what counties apply' to each row? Now I could cross-join a table listing every county that applies to every row, which would mean 1 million rows X 3,000 potential counties. And for every one of these 20 condition, I'd need to be joining tables for each of these lookups.

Instead, I was told to do a binary comparison of some sort, but I'm not exactly sure of how to do it. This way, I'm not needing to do any joins, but just have a large binary string, with bits representing each county.

Since each query I know the exact county searched, I can see if each row applies (along with each of the other conditions I must check vs the other binary strings).

I accomplished this using:
AND Substring(County, @CountyIndex, 1) = '1'
I have a character string for county, which is painfully slow when running all of these checks.

My hope is if the county in the lookup is 872, I can just scan the table, looking at bit #872 for the county field in each record, rather than joining huge tables for every one of these fixed fields I need to test.

My guess is the fastest way is some sort of binary string comparisons, but I can't find any good resources on the subject. PLEASE HELP!

SQLUSA
New Member
New Member

--
18 Feb 2008 01:37 AM
>There are ~1 million de-normalized 'adjustment rows' that I am checking about 20 different conditions on, but each of these conditions has multiple possibile entries.


Is this a one timer? Periodic?

How about creating clean staging tables from the messy data?

Perform the final update from the staging tables?

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005highperformance/ The Best SQL Server 2005 Training in the World!
tom27
New Member
New Member

--
18 Feb 2008 03:40 AM
MS SQLSERVER is designed to deal with SET operations. Normalize your database so you don't require a SUBTRRING and a CROSS JOIN.



najmh
New Member
New Member

--
21 Feb 2008 10:44 AM
Hi Cragi,
Would it be possible for you to post your schema of tables and actual query and requirements? That will help people to help you in your quest!

SQL Sever allows you to do bit operations. Here is a link:
http://technet.microsoft.com/en-us/...74965.aspx

However, it seems like your database can use some normalization

I hope this helps.
You are not authorized to post a reply.

Acceptable Use Policy