Performance Issue: Binary Comparisons Instead? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Performance Issue: Binary Comparisons Instead?

Author

Topic

cragi
Starting Member

14 Posts

Posted - 2008-02-18 : 02:52:52

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!

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-02-18 : 11:29:28

Do you mean CHECKSUM ?

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-18 : 11:51:54

Can you give us a table definition and some information about the lookups you need to preform? For your example, what is the def for the base table and the county table and how do you know if the county applies?

cragi
Starting Member

14 Posts

Posted - 2008-02-18 : 13:28:27

USE [Prime]
GO
/****** Object: Table [dbo].[PrimeAdjustMaster] Script Date: 02/18/2008 12:24:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PrimeAdjustMaster](
[AdjustMasterID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[AdjustName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[HideName] [bit] NOT NULL CONSTRAINT [DF_PrimeAdjustMaster_HdeName] DEFAULT ((0)),
[AdjustGroupName] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CustomerID] [bigint] NOT NULL,
[ProgramTypeString] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LoanTypeString] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TermString] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PurposeString] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[OccupancyString] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EscrowString] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PropertyString] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_PrimeAdjustMaster_PropertyString] DEFAULT ('1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111'),
[StateString] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CountyString] [char](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DocString] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AusString] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SubFinancing] [bit] NOT NULL CONSTRAINT [DF_PrimeAdjustMaster_MiOnly] DEFAULT ((0)),
[LpmiOnly] [bit] NOT NULL CONSTRAINT [DF_PrimeAdjustMaster_IoOnly2] DEFAULT ((0)),
[TprOnly] [bit] NOT NULL CONSTRAINT [DF_PrimeAdjustMaster_IoOnly1] DEFAULT ((0)),
[IoOnly] [bit] NOT NULL CONSTRAINT [DF_PrimeAdjustMaster_IoOnly] DEFAULT ((0)),
[NonLpmi] [bit] NOT NULL CONSTRAINT [DF_PrimeAdjustMaster_LpmiOnly1] DEFAULT ((0)),
[NonTPR] [bit] NOT NULL CONSTRAINT [DF_PrimeAdjustMaster_TprOnly1] DEFAULT ((0)),
[NonIO] [bit] NOT NULL CONSTRAINT [DF_PrimeAdjustMaster_IoOnly1_1] DEFAULT ((0)),
[Okay] [bit] NOT NULL CONSTRAINT [DF_PrimeAdjustMaster_Okay] DEFAULT ((0)),
[NameOkay] [bit] NOT NULL CONSTRAINT [DF_PrimeAdjustMaster_NameOkay] DEFAULT ((0)),
[Verified] [bit] NOT NULL CONSTRAINT [DF_PrimeAdjustMaster_Verified] DEFAULT ((0)),
[BuydownOkay] [bit] NOT NULL CONSTRAINT [DF_PrimeAdjustMaster_BuydownOkay] DEFAULT ((0)),
[AdjustShared] [bit] NOT NULL CONSTRAINT [DF_PrimeAdjustMaster_AdjustShared] DEFAULT ((0)),
[LpmiTprIoOkay] [bit] NOT NULL CONSTRAINT [DF_PrimeAdjustMaster_LpmiTprIoOkay] DEFAULT ((0)),
[AdminID] [bigint] NULL,
[create_date] [smalldatetime] NULL CONSTRAINT [DF_PrimeAdjustMaster_create_date] DEFAULT (getdate()),
[modify_date] [smalldatetime] NULL CONSTRAINT [DF_PrimeAdjustMaster_modify_date] DEFAULT (getdate()),
CONSTRAINT [PK_PrimeAdjustMaster] PRIMARY KEY CLUSTERED
(
[AdjustMasterID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

SELECT
PrimeAdjustMaster.AdjustMasterID, PrimeAdjustMaster.AdjustName, PrimeAdjustMaster.HideName, PrimeAdjustDetail.AdjustDetailID,
ISNULL(PrimeSelectLender.LenderID, 0) AS LenderID,
ISNULL(PrimeSelectProgram.ProgramID, 0) AS ProgramID,
ISNULL(PrimeSelectCustomerLocation.CustomerLocationID, 0) AS CustomerLocationID,
ISNULL(PrimeSelectRateSheet.RateSheetID, 0) AS RateSheetID,
PrimeAdjustDetail.LtvA, PrimeAdjustDetail.LtvB, PrimeAdjustDetail.CltvA,PrimeAdjustDetail.CltvB,PrimeAdjustDetail.LoanAmountA,
PrimeAdjustDetail.LoanAmountB, PrimeAdjustDetail.DateA,PrimeAdjustDetail.DateB,PrimeAdjustDetail.DtiA,
PrimeAdjustDetail.DtiB,PrimeAdjustDetail.FicoA, PrimeAdjustDetail.FicoB,PrimeAdjustDetail.LockDaysA,PrimeAdjustDetail.LockDaysB,
PrimeAdjustDetail.PrepayA, PrimeAdjustDetail.PrepayB,PrimeAdjustDetail.PriceAdjustment,PrimeAdjustDetail.Description,
PrimeAdjustDetail.RateAdjustment,PrimeAdjustDetail.Disallow,PrimeAdjustDetail.HiddenMargin, PrimeAdjustDetail.UWFees,
PrimeAdjustMaster.AusString
FROM PrimeAdjustDetail
INNER JOIN PrimeAdjustMaster ON PrimeAdjustDetail.AdjustMasterID = PrimeAdjustMaster.AdjustMasterID
LEFT OUTER JOIN PrimeSelectLender ON PrimeAdjustMaster.AdjustMasterID = PrimeSelectLender.AdjustMasterID
LEFT OUTER JOIN PrimeSelectProgram ON PrimeAdjustMaster.AdjustMasterID = PrimeSelectProgram.AdjustMasterID
LEFT OUTER JOIN PrimeSelectRateSheet ON PrimeAdjustMaster.AdjustMasterID = PrimeSelectRateSheet.AdjustMasterID
LEFT OUTER JOIN PrimeSelectCustomerLocation ON PrimeAdjustMaster.AdjustMasterID = PrimeSelectCustomerLocation.AdjustMasterID
WHERE
(@LTV between LtvA AND ltvB)
AND ((@CLTV between cltvA AND cltvB) AND ((CLTVA =-99999999 AND CLTVB =99999999) OR @CLTV > @LTV))
AND (@LoanAmount between LoanAmountA AND LoanAmountB)
AND (@DTI between DTIA AND DTIB)
AND (@FICO between FicoA AND FicoB)
AND (@LockDays between LockDaysA AND LockDaysB)
AND (CURRENT_TIMESTAMP between isnull(DateA,CURRENT_TIMESTAMP) AND isnull(DateB,CURRENT_TIMESTAMP))
AND (@PrePay between PrePayA AND PrePayB) AND
substring(TermString, @TermType, 1) = '1' AND
substring(ProgramTypeString, @ProgramType, 1) = '1' AND
substring(OccupancyString, @OccupancyType, 1) = '1' AND
substring(LoanTypeString, @LoanType, 1) = '1' AND
substring(PropertyString, @PropertyType, 1) = '1' AND
substring(StateString, @StateInt, 1) = '1' AND
substring(CountyString, @CountyInt-1, 1) = '1' AND
substring(DocString, @DocType, 1) = '1' AND
substring(EscrowString, @EscrowType, 1) = '1' AND
substring(PurposeString, @PurposeType, 1) = '1'
AND iSNULL(PrimeAdjustMaster.CustomerID,0) in (@CustomerId, 0)
AND (LPMIOnly = @OptionLPMI or LPMIOnly ='false')
AND (TPROnly = @OptionTPR or TPROnly ='false')
AND (IOOnly = @OptionIO or IOOnly ='false')
AND (NonIO <> @OptionIO or NonIO = 'false')
AND (NonLPMI <> @OptionLPMI or NonLPMI = 'false')
AND (NonTPR <> @OptionTPR or NonTPR = 'false')
AND (NoSubFinanceOnly = 0 OR (PrimeAdjustDetail.NoSubFinanceOnly = 1 AND @LTV = @CLTV))

Here is an example of how it was implemented. Basically I want to
do a lookup on each row, to see if the input county is valid for
that row (along with checking the other conditions). I want the fastest way to check this, so I thought by keeping it flattened in
the same table would be the most efficient rather than looking up another table for every condition.

For exampl, the user selects that he is Broward County, and the administrator has checked off every applicable county for that row.
By scanning just the 'X county bit' of every row, we can determine which of the 1 million conditions apply.

hrishi_des
Yak Posting Veteran

76 Posts

Posted - 2008-02-19 : 04:31:26

Hi,

CHECKSUM has lot of collisions, If you are using SQL server 2005 use hashbytes.

When solution is simple, God is answering….

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources