itzik ben-gan

I am using the following query which I found from a fragmanet of code
by itzik ben-gan to assign a common group id for group of records in my
case which have similar SSN and first Name and Last Name. if the SSN is
the same it should also check the first name and last name of the
record. Becuase records have more than three AKA names, I need to check
all the possibilities of first name last name combination to verify the
records are the same.
This code works fine and can assign group numbers for all the rows.
I am trying this code on a database of 65,000 rows. It's taking around
20 minute to complete. but I'll have to run the same code on
800,000,000 rows.
It will take years to finish.
even if the query is optimized to run in 1 second for 65,000 rows, it
will take more than 4 hours to run on the 800,000,000 row. This where I
realized I am in the "wrong jungle".
1. is there any other feasible and faster way to do this? very
important issue.
2. while assigning group number, it doesn't give sequential numbers. It
skips some of the numbers( group Number 1,2 5,9...) just curiouse(
not very important)
SELECT c1.fname, c1.lname, c1.ssn , c3.tu_id,
(SELECT 1 + count(*)
FROM distFLS AS c2
WHERE c2.ssn < c1.ssn
or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
substring(c1.fname,1,1) or substring(c2.lname,1,1) =
substring(c1.lname,1,1)
or substring(c2.fname,1,1) =
substring(c1.lname,1,1) or substring(c2.lname,1,1) =
substring(c1.fname,1,1))
)) AS grp_num
into tmp_FLS
FROM distFLS AS c1
JOIN tu_people_data AS c3
ON (c1.ssn = c3.ssn and
c1.fname = c3.fname and
c1.lname= c3.lname)
GO
distinct firstname, lastname and SSN table from the tu_people_data.
I created this table to increase the query performance.
CREATE TABLE [distFLS] (
[fname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[lname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[ssn] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [TU_People_Data] (
[tu_id] [bigint] NOT NULL ,
[count_id] [int] NOT NULL ,
[fname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[lname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[ssn] [int] NULL ,
CONSTRAINT [PK_tu_bulk_people] PRIMARY KEY CLUSTERED
(
[tu_id],
[count_id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
sample data
there is a column count_id after the tu_id and before fname(tu_id and
count_id are primary keys)
tu_id fname lname SSN
156078480 KRISINA WALSH 999999000
156078480 KRISTINA GIERER 999999000
156078480 KRISTINA WALSH 999999000
151257883 J SOTO 999999111
151257883 JOSE LARIOS 999999111
151257883 JOSE SOTO 999999111
151257883 L SOTO 999999111
136312525 ELADIO GARCIA 999999222
136312525 ELADIO NAVA 999999222
136312525 ELADIO NAVAGARCIA 999999222
136312525 GARCIA NAVA 999999222
149180940 DARREN SAUERWINE 999999333
149180940 DARREN SUAERWIN 999999333First, examine the 65,000 T-SQL in Query Analyzer using the Show Execution
Plan feature and confirm that the select portion of the query is using
efficient index ss.
http://msdn.microsoft.com/library/d... />
1_5pde.asp
Also, when inserting, updating, or deleting a massive amount of data (ex > 1
million rows), you start running into series issues with the CPU, I/O, and
disk storage consumed by transaction logging. I assure you that attempting
to update 800 million rows in a single batch will take much longer than 4
hours, regardless of your server configuration. Do a goole search of the
*sqlserver* newsgroups using the keywords "transaction log" "million" and
"hours". There are techniques for minimizing transaction logging and
performing the updates or inserts in batches using a looping method. You
will also need to coordinate with the network admin and allocate the storage
space on the SAN ahead of time.
http://groups.google.com/groups? as...n
um=100
Once done, there are also the issues of logical index fragmentation and
physical extent fragmentation, etc.
Don't fire this up on a Friday evening and expect it to run over the
wend; it could be a w long project at best.
<jacob.dba@.gmail.com> wrote in message
news:1141687843.303092.247700@.i39g2000cwa.googlegroups.com...
>I am using the following query which I found from a fragmanet of code
> by itzik ben-gan to assign a common group id for group of records in my
> case which have similar SSN and first Name and Last Name. if the SSN is
> the same it should also check the first name and last name of the
> record. Becuase records have more than three AKA names, I need to check
> all the possibilities of first name last name combination to verify the
> records are the same.
> This code works fine and can assign group numbers for all the rows.
> I am trying this code on a database of 65,000 rows. It's taking around
> 20 minute to complete. but I'll have to run the same code on
> 800,000,000 rows.
> It will take years to finish.
> even if the query is optimized to run in 1 second for 65,000 rows, it
> will take more than 4 hours to run on the 800,000,000 row. This where I
> realized I am in the "wrong jungle".
> 1. is there any other feasible and faster way to do this? very
> important issue.
> 2. while assigning group number, it doesn't give sequential numbers. It
> skips some of the numbers( group Number 1,2 5,9...) just curiouse(
> not very important)
> SELECT c1.fname, c1.lname, c1.ssn , c3.tu_id,
> (SELECT 1 + count(*)
> FROM distFLS AS c2
> WHERE c2.ssn < c1.ssn
> or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
> substring(c1.fname,1,1) or substring(c2.lname,1,1) =
> substring(c1.lname,1,1)
> or substring(c2.fname,1,1) =
> substring(c1.lname,1,1) or substring(c2.lname,1,1) =
> substring(c1.fname,1,1))
> )) AS grp_num
> into tmp_FLS
> FROM distFLS AS c1
> JOIN tu_people_data AS c3
> ON (c1.ssn = c3.ssn and
> c1.fname = c3.fname and
> c1.lname= c3.lname)
> GO
> distinct firstname, lastname and SSN table from the tu_people_data.
> I created this table to increase the query performance.
> CREATE TABLE [distFLS] (
> [fname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
> [lname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
> [ssn] [int] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [TU_People_Data] (
> [tu_id] [bigint] NOT NULL ,
> [count_id] [int] NOT NULL ,
> [fname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
> [lname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
> [ssn] [int] NULL ,
> CONSTRAINT [PK_tu_bulk_people] PRIMARY KEY CLUSTERED
> (
> [tu_id],
> [count_id]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> sample data
> there is a column count_id after the tu_id and before fname(tu_id and
> count_id are primary keys)
> tu_id fname lname SSN
> 156078480 KRISINA WALSH 999999000
> 156078480 KRISTINA GIERER 999999000
> 156078480 KRISTINA WALSH 999999000
> 151257883 J SOTO 999999111
> 151257883 JOSE LARIOS 999999111
> 151257883 JOSE SOTO 999999111
> 151257883 L SOTO 999999111
> 136312525 ELADIO GARCIA 999999222
> 136312525 ELADIO NAVA 999999222
> 136312525 ELADIO NAVAGARCIA 999999222
> 136312525 GARCIA NAVA 999999222
> 149180940 DARREN SAUERWINE 999999333
> 149180940 DARREN SUAERWIN 999999333
>

itzik ben-gan

I am using the following query which I found from a fragmanet of code
by itzik ben-gan to assign a common group id for group of records in my

case which have similar SSN and first Name and Last Name. if the SSN is

the same it should also check the first name and last name of the
record. Becuase records have more than three AKA names, I need to check

all the possibilities of first name last name combination to verify the

records are the same.
This code works fine and can assign group numbers for all the rows.
I am trying this code on a database of 65,000 rows. It's taking around
20 minute to complete. but I'll have to run the same code on
800,000,000 rows.
It will take years to finish.
even if the query is optimized to run in 1 second for 65,000 rows, it
will take more than 4 hours to run on the 800,000,000 row. This where I

realized I am in the "wrong jungle".
1. is there any other feasible and faster way to do this? very
important issue.
2. while assigning group number, it doesn't give sequential numbers. It

skips some of the numbers( group Number 1,2 5,9...) just curiouse(
not very important)

SELECT c1.fname, c1.lname, c1.ssn , c3.tu_id,
(SELECT 1 + count(*)
FROM distFLS AS c2
WHERE c2.ssn < c1.ssn
or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
substring(c1.fname,1,1) or substring(c2.lname,1,1) =
substring(c1.lname,1,1)
or substring(c2.fname,1,1) =
substring(c1.lname,1,1) or substring(c2.lname,1,1) =
substring(c1.fname,1,1))
)) AS grp_num
into tmp_FLS
FROM distFLS AS c1
JOIN tu_people_data AS c3
ON (c1.ssn = c3.ssn and
c1.fname = c3.fname and
c1.lname= c3.lname)
GO

distinct firstname, lastname and SSN table from the tu_people_data.
I created this table to increase the query performance.

CREATE TABLE [distFLS] (
[fname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[lname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[ssn] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [TU_People_Data] (
[tu_id] [bigint] NOT NULL ,
[count_id] [int] NOT NULL ,
[fname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[lname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[ssn] [int] NULL ,
CONSTRAINT [PK_tu_bulk_people] PRIMARY KEY CLUSTERED
(
[tu_id],
[count_id]
) ON [PRIMARY]
) ON [PRIMARY]
GO

sample data
there is a column count_id after the tu_id and before fname(tu_id and
count_id are primary keys)
tu_id fname lname SSN
156078480 KRISINA WALSH 999999000
156078480 KRISTINA GIERER 999999000
156078480 KRISTINA WALSH 999999000
151257883 J SOTO 999999111
151257883 JOSE LARIOS 999999111
151257883 JOSE SOTO 999999111
151257883 L SOTO 999999111
136312525 ELADIO GARCIA 999999222
136312525 ELADIO NAVA 999999222
136312525 ELADIO NAVAGARCIA 999999222
136312525 GARCIA NAVA 999999222
149180940 DARREN SAUERWINE 999999333
149180940 DARREN SUAERWIN 999999333Back to basics. The most commn errors in numeric codes liek SSN are
1) missing digit
2) extra digit
3) one wrong digit
4) Pairwise transpose

For names, use Metaphone and write it in a better 3GL than T-SQL.|||i'm not a huge fan of the method of group number assignment. seems
pretty inefficient to do a row count every time.

Try this;
select distinct left(c1.fname,1) as fname, left(c1.lname,1) as lname,
c1.ssn into tmpgroups from tu_people_data

Using SQL, add a new column to tmpgroups which is an identity column
named tu_id

I'm a little puzzled on the business logic. It looks like the records
are identical if the ssn is the same, and the first letter of first
name OR first letter of last name is same. Is this what you want?|||you can add the identity in one shot
select distinct identity(int,1,1) as tu_id,left(c1.fname,1) as fname,
left(c1.lname,1) as lname,
c1.ssn into tmpgroups from tu_people_data

http://sqlservercode.blogspot.com/|||(jacob.dba@.gmail.com) writes:

> I am using the following query which I found from a fragmanet of code
> by itzik ben-gan to assign a common group id for group of records in my
> case which have similar SSN and first Name and Last Name. if the SSN is
> the same it should also check the first name and last name of the
> record. Becuase records have more than three AKA names, I need to check
> all the possibilities of first name last name combination to verify the
> records are the same.
> This code works fine and can assign group numbers for all the rows.
> I am trying this code on a database of 65,000 rows. It's taking around
> 20 minute to complete. but I'll have to run the same code on
> 800,000,000 rows.
> It will take years to finish.
> even if the query is optimized to run in 1 second for 65,000 rows, it
> will take more than 4 hours to run on the 800,000,000 row. This where I
> realized I am in the "wrong jungle".

I hope that the query is not going to be run in a regular fashion on
those 800 million rows, but once you are there, it will be a one-off.

For the problem as given it sounds like a nightmare to process 800
million rows.

> SELECT c1.fname, c1.lname, c1.ssn , c3.tu_id,
> (SELECT 1 + count(*)
> FROM distFLS AS c2
> WHERE c2.ssn < c1.ssn
> or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
> substring(c1.fname,1,1) or substring(c2.lname,1,1) =
> substring(c1.lname,1,1)
> or substring(c2.fname,1,1) =
> substring(c1.lname,1,1) or substring(c2.lname,1,1) =
> substring(c1.fname,1,1))
> )) AS grp_num
> into tmp_FLS
> FROM distFLS AS c1
> JOIN tu_people_data AS c3
> ON (c1.ssn = c3.ssn and
> c1.fname = c3.fname and
> c1.lname= c3.lname)
> GO

Your definition of distFLS does not have an index, at least you did not
post one. A clustered index on ssn would be a good start. In the same
vein, add an index on (ssn, fnmae, lname) on TU_PeopleData.

That may at least speed up your test case on 65000 rows. Although, you
probably need more tweaks to do the 800 million.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I have a table with first name, last name, SSN and other columns.
I want to assign group number according to this business logic.
1. Records with equal SSN and (similar first name or last name) belong
to the same group.
John Smith 1234
Smith John 1234
S John 1234
J Smith 1234
John Smith and Smith John falls in the same group Number as long as
they have similar SSN.
This is because I have a record of equal SSN but the first name and
last name is switched because of people who make error inserting last
name as first name and vice versa. John Smith and Smith John will have
equal group Name if they have equal SSN.
2. There are records with equal SSN but different first name and last
name. These belong to different group numbers.
Equal SSN doesn't guarantee equal group number, at least one of the
first name or last name should be the same. John Smith and Dan Brown
with equal SSN=1234 shouldn't fall in the same group number.

Sample data:
Id Fname lname SSN grpNum
1 John Smith 1234 1
2 Smith John 1234 1
3 S John 1234 1
4 J Smith 1234 1
5 J S 1234 1
6 Dan Brown 1234 2
7 John Smith 1111 3

I have tried this code for 65,000 rows. It took 20 minute. I have to
run it for 21 million row data. It will take years.

INSERT into temp_FnLnSSN_grp
SELECT c1.fname, c1.lname, c1.ssn AS ssn, c3.tu_id,
(SELECT 1 + count(*)
FROM distFLS AS c2
WHERE c2.ssn < c1.ssn
or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
substring(c1.fname,1,1) or substring(c2.lname,1,1) =
substring(c1.lname,1,1)
or substring(c2.fname,1,1) =
substring(c1.lname,1,1) or substring(c2.lname,1,1) =
substring(c1.fname,1,1))
)) AS group_number
FROM distFLS AS c1
JOIN tu_people_data AS c3
ON (c1.ssn = c3.ssn and
c1.fname = c3.fname and
c1.lname= c3.lname)

dist FLS is distinct First Name, last Name and SSN table from the
people table.

Doug wrote:
> i'm not a huge fan of the method of group number assignment. seems
> pretty inefficient to do a row count every time.
> Try this;
> select distinct left(c1.fname,1) as fname, left(c1.lname,1) as lname,
> c1.ssn into tmpgroups from tu_people_data
> Using SQL, add a new column to tmpgroups which is an identity column
> named tu_id
> I'm a little puzzled on the business logic. It looks like the records
> are identical if the ssn is the same, and the first letter of first
> name OR first letter of last name is same. Is this what you want?|||I have a table with first name, last name, SSN and other columns.
I want to assign group number according to this business logic.
1. Records with equal SSN and (similar first name or last name) belong
to the same group.
John Smith 1234
Smith John 1234
S John 1234
J Smith 1234
John Smith and Smith John falls in the same group Number as long as
they have similar SSN.
This is because I have a record of equal SSN but the first name and
last name is switched because of people who make error inserting last
name as first name and vice versa. John Smith and Smith John will have
equal group Name if they have equal SSN.
2. There are records with equal SSN but different first name and last
name. These belong to different group numbers.
Equal SSN doesn't guarantee equal group number, at least one of the
first name or last name should be the same. John Smith and Dan Brown
with equal SSN=1234 shouldn't fall in the same group number.

Sample data:
Id Fname lname SSN grpNum
1 John Smith 1234 1
2 Smith John 1234 1
3 S John 1234 1
4 J Smith 1234 1
5 J S 1234 1
6 Dan Brown 1234 2
7 John Smith 1111 3

I have tried this code for 65,000 rows. It took 20 minute. I have to
run it for 21 million row data. It will take years.

INSERT into temp_FnLnSSN_grp
SELECT c1.fname, c1.lname, c1.ssn AS ssn, c3.tu_id,
(SELECT 1 + count(*)
FROM distFLS AS c2
WHERE c2.ssn < c1.ssn
or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
substring(c1.fname,1,1) or substring(c2.lname,1,1) =
substring(c1.lname,1,1)
or substring(c2.fname,1,1) =
substring(c1.lname,1,1) or substring(c2.lname,1,1) =
substring(c1.fname,1,1))
)) AS group_number
FROM distFLS AS c1
JOIN tu_people_data AS c3
ON (c1.ssn = c3.ssn and
c1.fname = c3.fname and
c1.lname= c3.lname)

dist FLS is distinct First Name, last Name and SSN table from the
people table.

Doug wrote:
> i'm not a huge fan of the method of group number assignment. seems
> pretty inefficient to do a row count every time.
> Try this;
> select distinct left(c1.fname,1) as fname, left(c1.lname,1) as lname,
> c1.ssn into tmpgroups from tu_people_data
> Using SQL, add a new column to tmpgroups which is an identity column
> named tu_id
> I'm a little puzzled on the business logic. It looks like the records
> are identical if the ssn is the same, and the first letter of first
> name OR first letter of last name is same. Is this what you want?

Item counts...

I have a group w/ two fields, such as:

INCIDENT TYPE; NUMBER OF EVENTS
Burglary; 1
Burglary; 1
Burglary; 1
Arrest; 1
Citation Issued; 1

However, I need it to do the following:

INCIDENT TYPE; NUMBER OF EVENTS
Burglary; 3
Arrest; 1
Citation Issued; 1

I have set the INCIDENT TYPE fields to 'suppress on duplications'; however, how do I get the field in the NUMBER OF EVENTS field to successfully "count" the same type of events in the INCIDENT TYPE fields if I do this?

Sorry, I haven't used Crystal Reports (now using version 11) since my VB6.0 days (been awhile).I'm no guru myself, but try this:

Suppress the detail line and make a group footer with two fields, field1 (incident type from your example), and a formula field that is Sum(field2, field1). If you didn't have a group on field1, make one.

Good Luck,
Larry

Issues setting up users to authenticate via NT group into MSSQL

I have a NT Group of users that need to connect to MSSQL db using integrated auth and run a stored proc. I have got this concept to work for individual windows users but not a group.

Does anyone have any ideas?

(Win XP, MSSQL 2000 SP3, Windows Group = "SQLUsers", member is "User2")

-- TSQL --

USE master
GO
sp_grantlogin 'pc\SQLUsers'
GO
sp_defaultdb 'pc\SQLUsers', 'theDB'
GO
USE theDB
GO
sp_grantdbaccess 'pc\SQLUsers', 'SQLUsers'
GO
GRANT EXECUTE ON [dbo].[uspThing] TO SQLUsers
GO

-- Trying to connect with OSQL.EXE:

> osql -d theDB -E
Login failed for user 'pc\User2'.

BUT....

runing it for the individual user:

--TSQL

USE master
GO
sp_grantlogin 'pc\User2'
GO
sp_defaultdb 'pc\User2', 'theDB'
GO
USE theDB
GO
sp_grantdbaccess 'pc\User2', 'SQLUsers'
GO
GRANT EXECUTE ON [dbo].[uspThing] TO SQLUsers
GO

-- now trying to connect with OSQL.EXE as User2:

>osql -d theDB -E
1> exec uspThing
2> go
3> data...

all is fine.....

I can't use the user method - to much admin... any ideas!?

PKOk, get this... because I was using "runas /user:User2 cmd.exe" sessions for testing the different users integrated security (osql -E -d dbName) etc the group changes I was making were not reflected until I logged out of the command session.

I have been officially driven mad by this one...

Issueing a CREATE TABLE statement within a transaction

Sorry new to the newsgroups so please point me to where I need to be if this is the wrong group. Below is a chunck of code that I have written to play around with transactions in Visual Studion 2005. I want to be able to create a table, the columns in it, and add rows of data and if any of those statements fail I woudl like to rollback the entire transaction. My testing has shown that when the CREATE TABLE sql is issued it automatcially commits. In my example below the VER_INFO table is immediately created when the ExecuteNonQuery() statement is invoked. The next 2 insert statements are held correctly in the transaction, however when the second table, WADES_TEST is created that causes the 2 rows to be inserted and the second table is immediately created. So my commit statement is useless. Is this normal? Is there a way to force the system to keep the CREATE TABLE statement in the transaction so I can roll it back if needed?

OracleCommand cmd = new OracleCommand();

OracleConnection wConnection = new OracleConnection(wConnString);

wConnection.Open();

OracleTransaction trans = wConnection.BeginTransaction();

string wQry = "CREATE TABLE VER_INFO (VER NVARCHAR2(25))";

cmd.CommandText = wQry;

cmd.Connection = wConnection;

cmd.Transaction = trans;

try

{

cmd.ExecuteNonQuery();

wQry = "INSERT INTO VER_INFO VALUES ('INITIALIZED')";

OracleCommand cmd4 = new OracleCommand(wQry, wConnection, trans);

cmd4.ExecuteNonQuery();

wQry = "INSERT INTO VER_INFO VALUES ('testss')";

OracleCommand cmd2 = new OracleCommand(wQry, wConnection, trans);

cmd2.ExecuteNonQuery();

wQry = "CREATE TABLE WADES_TEST (WADE NVARCHAR2(25))";

OracleCommand cmd3 = new OracleCommand(wQry, wConnection, trans);

cmd3.ExecuteNonQuery();

trans.Commit();

wConnection.Dispose();

}

catch

{

trans.Rollback();

wConnection.Close();

}

Thanks in advance,

Wade Sharp

This might be more appropriate to be posted to an Oracle forum or newsgroup. Or to an ADO.NET forum.

Issue with Page break

Hi,

I have developed a simple report containing a table which has one group. The group has property 'Repeat Group Header '.

I can see the report when it returns data which spans more than one page. Other wise I see a blank page when seen from the web application. The report is seen properly in Designer.

I found out that if I had a 'Page Break at start' = True for the table, I can see the reports containing single page. But this page break is not acceptable.

Has anyone faced similar issue? Whats the solution to it?

Thanks,

Tanmaya

The issue was not with the report. I am using awreportviewer and it was placed in html table within <tr><td> tags.

I removed the viewer control from table & it started working fine.

Issue with inserting chart into table's cell

Hello, look at this one

I've formed table with grouping

+ Group 1 | Sum(field1) | Chart
Elem1 | field1 | "Cannot place a chart at this location in the table"

Why I cannot insert chart at this place?

Thanks

It sounds like you are trying to place the chart into the table detail area. I.e. for every detail data row, you would one chart instance - so every chart instance has just one data point.

Instead, put the chart into the group header or group footer.

-- Robert

|||I get a similar error when trying to paste a table in a detail cell of another table. "Cannot place a Table at this location in the Table" Yet I can insert a new table into that table cell from the toolbox. The table I want to paste is based on a single data point from the parent table. I can recreate it, but it would be a lot easier to paste it from another report.