Dedupe Query

Below, I have some T-SQL to create a table, add some sample records and do a dedupe query. This all works but performance is poor on large data sets and I was wondering if someone had any optimization tips.

Thanks to blindman for helping me develop this version.

Some background: the staging table has undeduped records about users. I want to dedupe and get the record with the most fields. I don't want to mix fields from different records. The staging table is currently unindexed but that can be changed.

CREATE TABLE StagingRecords
(
EmailAddress VARCHAR(75) NULL,
FirstName VARCHAR(255) NULL,
LastName VARCHAR(255) NULL,
StreetAddress VARCHAR(255) NULL,
City VARCHAR(255) NULL,
State VARCHAR(255) NULL,
ZipCode VARCHAR(255) NULL,
RecordID INT IDENTITY NOT NULL
)

INSERT INTO StagingRecords (EmailAddress, FirstName, LastName, StreetAddress, City, State, ZipCode)
VALUES ('usera@.hotmail.com', 'John', 'Doe', NULL, NULL, NULL, NULL)
INSERT INTO StagingRecords (EmailAddress, FirstName, LastName, StreetAddress, City, State, ZipCode)
VALUES ('usera@.hotmail.com', 'Abe', 'Abelman', NULL, NULL, 'MI', NULL)
INSERT INTO StagingRecords (EmailAddress, FirstName, LastName, StreetAddress, City, State, ZipCode)
VALUES ('usera@.hotmail.com', 'Zach', 'Zedcynsky', NULL, NULL, 'TX', NULL)
INSERT INTO StagingRecords (EmailAddress, FirstName, LastName, StreetAddress, City, State, ZipCode)
VALUES ('usera@.hotmail.com', 'Mary', 'Jane', NULL, NULL, NULL, NULL)

INSERT INTO StagingRecords (EmailAddress, FirstName, LastName, StreetAddress, City, State, ZipCode)
VALUES ('zzz@.yahoo.com', 'Cletus', 'Van Damme', NULL, NULL, NULL, NULL)
INSERT INTO StagingRecords (EmailAddress, FirstName, LastName, StreetAddress, City, State, ZipCode)
VALUES ('zzz@.yahoo.com', 'Alfonse', 'Ackbar', NULL, NULL, 'AL', '12345')
INSERT INTO StagingRecords (EmailAddress, FirstName, LastName, StreetAddress, City, State, ZipCode)
VALUES ('zzz@.yahoo.com', 'Zoom', 'Zuckerman', NULL, NULL, 'NJ', '54321')
INSERT INTO StagingRecords (EmailAddress, FirstName, LastName, StreetAddress, City, State, ZipCode)
VALUES ('zzz@.yahoo.com', 'Mary', 'Jane', NULL, 'Springfield', NULL, NULL)

SELECT
NULLCountTable.MinNULLCount, IDTable.TargetRecordID,
StagingRecords.EmailAddress, StagingRecords.FirstName, StagingRecords.LastName, StagingRecords.StreetAddress, StagingRecords.City, StagingRecords.State, StagingRecords.ZipCode
FROM
(SELECT EmailAddress
, MIN(CASE WHEN StagingRecords.FirstName IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.LastName IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.StreetAddress IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.City IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.State IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.ZipCode IS NULL THEN 1 ELSE 0 END) AS MinNULLCount
FROM StagingRecords
GROUP BY EmailAddress) AS NULLCountTable
INNER JOIN
(SELECT Min(RecordID) AS TargetRecordID, EmailAddress
, (CASE WHEN StagingRecords.FirstName IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.LastName IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.StreetAddress IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.City IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.State IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.ZipCode IS NULL THEN 1 ELSE 0 END) AS NULLCount
FROM StagingRecords
GROUP BY EmailAddress, (CASE WHEN StagingRecords.FirstName IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.LastName IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.StreetAddress IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.City IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.State IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.ZipCode IS NULL THEN 1 ELSE 0 END)) AS IDTable
ON (NULLCountTable.EmailAddress = IDTable.EmailAddress AND NULLCountTable.MinNULLCount = IDTable.NULLCount)
INNER JOIN StagingRecords ON (StagingRecords.EmailAddress = IDTable.EmailAddress AND StagingRecords.RecordID = IDTable.TargetRecordID)This has been running on an undeduped table of 170 million records for over 2.5 hours and has yet to output a single row.

Recently, people in this forum said I should use set based solutions such as this over cursors. I should get dramatic/exponential performance gains. And if I wasn't seeing that, then I wasn't doing it right. Well, I must not be doing this right so I'm asking for help.

This seems like it must do extra logic and sorting that the cursor based code doesn't have to do. For example, the cursor code doesn't need unique staging record IDs and never has to join on them or perform a fraction of the joining of this query.

The provided SQL should be say to run and experiment with on a tempdb.

Any help is much appreciated.|||There are probably more efficient solutions, but this approach is about 4 times faster than blindmans more complex query
if you build the view and indexes (which may make the whole thing a wash in the end)

Also, you suggested that your cursor solution appeared faster. It may return some rows faster than blindmans query, but
his suggestion is still fairly efficient. It will read the table 2-3 times but iterating through each row with a cursor,
even if you read each row only once, will still be MUCH SLOWER to complete the entire process.

SET CONCAT_NULL_YIELDS_NULL ON
SET ARITHABORT ON

--Create a view with a calculated colum for count of null records
CREATE VIEW v_StagingRecords WITH SCHEMABINDING AS
SELECT EmailAddress,
CASE WHEN FirstName IS NULL THEN 1 ELSE 0 END
+ CASE WHEN LastName IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StreetAddress IS NULL THEN 1 ELSE 0 END
+ CASE WHEN City IS NULL THEN 1 ELSE 0 END
+ CASE WHEN State IS NULL THEN 1 ELSE 0 END
+ CASE WHEN ZipCode IS NULL THEN 1 ELSE 0 END NullCount, RecordID
FROM dbo.StagingRecords

--3 seconds. 1700 reads
CREATE UNIQUE CLUSTERED INDEX vSR_IDX on v_StagingRecords (RecordID)
CREATE INDEX v_EN_IDX on v_StagingRecords (EmailAddress, NullCount)

--100,000 row table. 0.6 seconds, 800 reads vs 2.8 seconds, 2200 reads for original query.
SELECT * FROM v_StagingRecords WHERE RecordID IN
(
SELECT (SELECT TOP 1 RecordID FROM v_StagingRecords WITH (NOEXPAND)
WHERE EmailAddress = o.EmailAddress Order BY NullCount ASC) RecordID
FROM v_StagingRecords o WITH (NOEXPAND)
GROUP BY EmailAddress
)
ORDER BY EmailAddress|||I loaded 17 million rows into a test table and built the view, indexes, and ran the query. It took 16 minutes in total, 101 seconds for the query itself. I tried blindmans query on the same data it it took 113, seconds so scrap my suggestion. I might play with it a little more and see if I can come up with anything better.

declaring a variable

I am learning T-SQL syntax and I am very familiar with it, however how would I do the following:

We have a table that actually has a column that contains SQL statements. I want to build a SQL statement in Reporting Services that is going to take that column to build a "dynamic" SQL statment and then I will use the exec sp_executesql statement.

Do I need to declare a parameter, or in SQL is there such thing as a variable?

So if I have:

DECLARE @.sql nvarchar(4000)

SELECT AdHocSQL from TheTable

SET @.sql=AdHocSQL

Would this work? Is this syntatically correct? Or should I be doing this some other way?

The report is sort of a summary report that has about 250 different items and each item has different data to get from different tables.

Thanks for the information.

You 'almost' have it down.

Code Snippet

DECLARE @.SQL nvarchar(4000)

SELECT @.SQL = AdHocSQL

FROM MyTable

WHERE {criteria}

EXECUTE sp_executesql @.SQL

|||Thanks for the help. I do appreciate it.

Decision trees, DMX and CONTAINS (T-SQL)

I would appreciate answers to the following doubts I have regarding Decision trees, CONTAINS and using CONTAINS in a DMX query:

1. Does MS decision tree work only off equality/inequality conditions for the nodes? Is it possible to use a predicate as the branch criteria for a node?

2. Can the T-SQL predicate CONTAINS(...) be used in a DMX query? I need to check if a column-value is a substring of another column and create an intermediate column that will enable me to construct a decision tree with the phrase-present/absent branch.

3. Can CONTAINS(...) be used in a select clause? Like -

SELECT CONTAINS(JAT.column1, '"Good day"')

FROM JustAnotherTable;

4. Does CONTAINS(...) support both arguments to be column references? Or, is it mandatory that the pattern (argument #2) has to be a literal string or a variable? E.g.: I need to know the validity of the following expression -

SELECT * FROM JustAnotherTable JAT

WHERE CONTAINS(JAT.column1, JAT.column3);

The decision tree split conditions are based on equality/inequality conditions for categorical attributes and numeric/range comparisons for continuous values - we don't do arbitrary predicates.

CONTAINS is not supported in DMX.

Decimal Data Type losing scale?

I'm trying to update a table that has decimal values. They are defined at
precision of 15 and scale of 2.
When I use a T-Sql update query, I'm sending a value "with pennies", but the
table is only reflecting the integer portion. I've even tried
UPDATE (myTableName)
SET myCost = CAST(@.Cost AS decimal(15,2))
without success.
When I step through the code of my VB.NET program and view the value of item
I've added to the parameters collection of my update query command object, I
DO SEE the pennies. When the query has executed, they aren't in the table.
I CAN TYPE the pennies into the record in the table with Enterprise Mgr. An
d
I can retrieve them with my program. But I can't send new values with
pennies and get them respected in the new table values.
I have seen in Books on Line that we are supposed to explicitly CAST our
decimal values. But shouldn't this take care of it?Hello Q,
When you run profiler what do you see the values being sent as? If you have
your precision and scale not matching exactly in your VB.NET application
it can cause it to send it incorrectly to the database.
Aaron Weiker
http://aaronweiker.com/
http://sqlprogrammer.org/

> I'm trying to update a table that has decimal values. They are
> defined at precision of 15 and scale of 2.
> When I use a T-Sql update query, I'm sending a value "with pennies",
> but the
> table is only reflecting the integer portion. I've even tried
> UPDATE (myTableName)
> SET myCost = CAST(@.Cost AS decimal(15,2))
> without success.
> When I step through the code of my VB.NET program and view the value
> of item I've added to the parameters collection of my update query
> command object, I DO SEE the pennies. When the query has executed,
> they aren't in the table. I CAN TYPE the pennies into the record in
> the table with Enterprise Mgr. And I can retrieve them with my
> program. But I can't send new values with pennies and get them
> respected in the new table values.
> I have seen in Books on Line that we are supposed to explicitly CAST
> our decimal values. But shouldn't this take care of it?
>|||Is it possible that you've failed to specify precision and scale
for your decimal parameter? The default precision and scale for
a decimal parameter is precision 18, scale 0. If this doesn't seem
to help, could you post the relevant VB.NET code dealing with
the parameter?
Steve Kass
Drew University
Q Johnson wrote:

>I'm trying to update a table that has decimal values. They are defined at
>precision of 15 and scale of 2.
>When I use a T-Sql update query, I'm sending a value "with pennies", but th
e
>table is only reflecting the integer portion. I've even tried
> UPDATE (myTableName)
> SET myCost = CAST(@.Cost AS decimal(15,2))
>without success.
>When I step through the code of my VB.NET program and view the value of ite
m
>I've added to the parameters collection of my update query command object,
I
>DO SEE the pennies. When the query has executed, they aren't in the table.
>I CAN TYPE the pennies into the record in the table with Enterprise Mgr. A
nd
>I can retrieve them with my program. But I can't send new values with
>pennies and get them respected in the new table values.
>I have seen in Books on Line that we are supposed to explicitly CAST our
>decimal values. But shouldn't this take care of it?
>
>

Debugging T-SQL Codes

Hi,
How can I use query analyzer to debug my codes and stored procedures. I need
something like debug tools of VB(watching line by line execution). Are such
tools available for SQL server?
Thanks,
AminYes, for stored procedures., In Query Analyzer, Object Browser, right-click
the procedure and you'll find the debugger.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Amin Sobati" <amins@.morva.net> wrote in message
news:%23gTTEiECEHA.3788@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How can I use query analyzer to debug my codes and stored procedures. I
need
> something like debug tools of VB(watching line by line execution). Are
such
> tools available for SQL server?
> Thanks,
> Amin
>|||In Query Analyzer, open the object browser. In the object
browser, go down to the stored procedures in a database,
select a stored procedure, right click and select debug.
-Sue
On Fri, 12 Mar 2004 19:12:08 +0430, "Amin Sobati"
<amins@.morva.net> wrote:

>Hi,
>How can I use query analyzer to debug my codes and stored procedures. I nee
d
>something like debug tools of VB(watching line by line execution). Are such
>tools available for SQL server?
>Thanks,
>Amin
>

Debugging stored procedures in SQL2005

I am getting following error message while trying to step into stored procedue:

"Unable to start T-SQL Debugging. Could not connect to computer 'xxx'. The referenced account is currently locked out and may not be logged on to"

I use VS 2005 Professional edition. SQL Server Authentication is used to create data connection in Server Explored. SQL/CLR debugging is enabled for the connection. I followed all steps from this article:

Setting Up SQL Debugging http://msdn2.microsoft.com/en-us/library/s4sszxst(VS.80).aspx

Any help?

Thanks

Slava

It sounds like your password has expired. You can change your password using SQL Server management studio, or you can ask a system administrator to do it for you.

Debugging SQL Server 2005 Stored Proc with Visual Studio 2005

Hi all,

I have a big ol' stored proc (about 6,000 lines), written in T-SQL. I want to debug this stored proc using something other than a bunch of PRINT statements. I tried using the Visual Studio debugger (Server Explorer - Database - Stored Proc - "Step Into Stored Procedure"), but this is behaving erratically.
The "Current statement" yellow cursor is rarely on the line that is currently being executed. It is usually between 2 and 100 lines above the actual current statement. It seems that the further down the code I get, the bigger the distance (in lines) between the yellow cursor and the actual current statement. Maybe something related to comments or multi-line statements?
I have heard of a similar problem with older versions of Visual C++ related to line feeds and return carriages (CR, LF vs. CRLF) but this doesn't seem to be the problem.
Has anyone had similar problems?

Thank you,

Vince

Might be experiencing something similar. I am used to working in SQL Server. I create my stored procedures in the query editor in SQL Server 2005. Sometimes I want to debug the procedure, so I have to go into VS, open up the object explorer, make sure that the debugging setting it on, find the stored procedure and step into it.

Well... I am seeiing erradic behavior as well; about 75% of the time, the yellow cursor disappears. Like right now... It thinks it is still debugging but the cursor is gone and the only option available for debuuging is to stop debugging. oh, and the task bar says its running... I can't seem to find out why this is happening, since I am seeing no other reports of this happening to people. Your post is the closest so far. Did you resolve your problem?

|||

While I dont have an exact remedy for you, this is not that uncommon. I have used the tools (both VS and SQL) for about two years now since the first public beta whenever that was :)...

I have seen this behavior over the months several times, but not as of late. It usually had to do with referring to old versions of code. Make sure you are referencing the correct Server/DB/Proc and refresh then debug. And check you DB connection properties.

Hope this helps,

Derek

|||

I do think it does have something to do with saving the procedure using Visual Studio 2005. It *seems* that if I save it to a project and try debugging it I have less problems...

This leads me to ask about best practices for managing T-SQL and databases. I'm sure my circumstances are not uncommon; I have a single, rather large database that is utilized by multiple applications (many of which can be in a beta development stage requireing frequent changes to the database). I was to implement source control practices for myself and my teams and I favor subversion for source control. When I create a new application, or upgrade an application to VS 2005, how can I best set up my database for change management?

I have the impression that Microsoft wants me to change the way that I work to handle my database, but it is very unclear HOW they want me to work with their products. Sometimes I write stored procedures that are not part of a single application, but that are used by multiple applications - both web and desktop. So, in this case would it be best to create some kind of database project that is seperate from my applications? And if so, how could I organize it to manage change and quality? Ugh.

|||

Hi Ryan,

I think you will find SQL Server source control integration a good first step towards acommplishing change management in the database. There have been several 3rd party attempts at the problem of managing change in the database, but those I have used were fair at best.

In general, your database can and should be a seperate entity for all but the most simplest projects aka "mom and pop websites/apps". My dev. team uses VSS (not used Team System so you may want to check into that as well) and each project is self-contained in the repository. We do not mix and match projects of different types. So maybe very simplistically speaking your VSS repository structure...

FRONTEND

-Web

-Desktop

MIDDLETIER

-Classes

-Web Services

DATABASE

-Database X

--Stored Procs

--Functions

--Triggers

DML

DDL

-Database Y

-Database Z

etc...you may enjoy a recent post on my blog regarding the usage of mangement studio's VSS integration...

http://derekcomingore.iuplog.com/default.asp?item=167168

Derek

Debugging "Stored procedure in T-SQL Debugger"

For 2 days already Stored procedures on SQL Server don`t work.
When debugging:
"ODBC: Msg 0, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot load the DLL
mssdi98.dll, or one of the DLLs it references. Reason: 126(The specified
module could not be found.)."
Other object in database (tables,views) work OK.
What is going on?
Vladimir
Did you read the troubleshooting section in Books Online? I searched for below string and found the section:
"Cannot load the DLL"
Make sure you use the updated Books Online:
http://www.microsoft.com/SQL/techinf...00/default.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Majstor" <majstorv@.hotmail-removethis-.com> wrote in message news:O5q2UF0FEHA.1012@.TK2MSFTNGP11.phx.gbl...
> For 2 days already Stored procedures on SQL Server don`t work.
> When debugging:
> "ODBC: Msg 0, Level 16, State 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot load the DLL
> mssdi98.dll, or one of the DLLs it references. Reason: 126(The specified
> module could not be found.)."
> Other object in database (tables,views) work OK.
> What is going on?
> Vladimir
>

Debug SQL User Functions

Does anyone know if there's a way to debug T-SQL user functions using sql
server 2000 sp3 on Windows Small Business Server 2003 from a client computer
running an Access 2003 mdb front-end on Windows XP SP2?
JayNot directly. You could debug the code if you create it as a procedure inste
ad.
ML|||ML,
I was afraid that would be the answer. The reason I wrote it as a function
is so that I could put it in a server-side query as in: "SELECT
MyFunc(SomeField, @.SomeInputVariable) AS SomeLabel FROM dbo.SomeTable;". Is
there a way to do the same thing with a procedure?
Jay
"ML" wrote:

> Not directly. You could debug the code if you create it as a procedure ins
tead.
>
> ML|||All you need in such a case is a sample of input parameters - for instance i
n
a table variable - and then you execute the code you intend to use in the
function with each set of parameters in a loop.
Or maybe you can post your DDL and get a 'second opinion'.
ML|||Try this out:
declare @.output2 varchar(30)
exec testbysandeep 1,2,@.output1=@.output2 OUTPUT
select @.output2
output1 is the field which is the output of the function. You assign this
value to the output2 variable.Then by doing a select statement you display
the value.
Hope this helps.
jains
"jay" wrote:
> ML,
> I was afraid that would be the answer. The reason I wrote it as a functi
on
> is so that I could put it in a server-side query as in: "SELECT
> MyFunc(SomeField, @.SomeInputVariable) AS SomeLabel FROM dbo.SomeTable;". I
s
> there a way to do the same thing with a procedure?
> Jay
> "ML" wrote:
>|||Hi Jay,
You can debug a UDF from QA by making a small SP that calls the UDF. The QA
debugger steps into the UDF with F11 the same as VS.
Cheers
Doug Forster
"jay" <jay@.discussions.microsoft.com> wrote in message
news:E2A1224F-03FA-4422-BD86-215675844400@.microsoft.com...
> ML,
> I was afraid that would be the answer. The reason I wrote it as a
> function
> is so that I could put it in a server-side query as in: "SELECT
> MyFunc(SomeField, @.SomeInputVariable) AS SomeLabel FROM dbo.SomeTable;".
> Is
> there a way to do the same thing with a procedure?
> Jay
> "ML" wrote:
>|||Hi Doug Forster,
Is there any kind of configuration setting involed at server side ?
With warm regards
Jatinder|||Well I do this ON the server with admin rights and it just works. Maybe
someone else knows if it is possible to debug from another box, though I
notice the docs caution against debugging on a production server.
Cheers
Doug Forster
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1122878941.648594.17860@.g47g2000cwa.googlegroups.com...
> Hi Doug Forster,
> Is there any kind of configuration setting involed at server side ?
> With warm regards
> Jatinder
>|||Hi Forster ,
The problem is that it is not working (debuggin) even on Server .
It says that you are logged as 'Local Account' . Do I have to Logon the
service as administrator woul that effect other clients?
With warm regards
Jatinder Singh
Doug Forster wrote:
> Well I do this ON the server with admin rights and it just works. Maybe
> someone else knows if it is possible to debug from another box, though I
> notice the docs caution against debugging on a production server.
> Cheers
> Doug Forster
> "jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
> news:1122878941.648594.17860@.g47g2000cwa.googlegroups.com...|||Hi I just want to add what I am facing when trying to debug a procedure
(created on master DB using sa login)
when I start debugging on this procedure in query analyzer, I recieve a
msg box which says:
SP debugging may not work properly if you log on as 'Local System
account'
while SQL Server is configured to run as a service.
You can open Event Viwer to see details.
Do you want to continue?
When I continue with this and execute this procedure by providing
parameter values, I get the print outputs, but procedure execution
doesn't break on break points!!
I am puzzled now how to work around with this? How can I break
execution on break points?
Thanks

Debug SQL in SQL Server 2005

Does anyone know how to debug T-SQL in SQL Server 2005? I could not find the
same functionality as in SQL 2000.
Thanks,
LijunThe T-SQL debugger has been removed in SQL Server 2005. To debug T-SQL you
have to use Visual Studio 2005 or later. See the following articles for more
details:
http://blogs.msdn.com/sqlprogrammability/archive/2006/04/29/586495.aspx
http://msdn2.microsoft.com/en-us/library/s0fk6z6e(VS.80).aspx
SQL Server 2008 will add back the T-SQL debugger.
HTH,
Plamen Ratchev
http://www.SQLStudio.com