Jump to next record insertion.

Hi Fellows,
I am trying to update some records for simplicity my table is as
follow
ProgId PrgOccur UPC
CircD 100 235689748965
EDL 100 526396856971
CircD 100 56985636258
each record is unique means by combination of these 3 fields.
now we need to moce couple of UPCs in EDL 100 to Circ 100. if a UPC
already exists with in that CircD 100 it will simply insert that UPC
into some table and keep on inserting next records.
simply i dont want to stop update process. but during insertiong if
any duplicates found, put them separately and insert others.
Regards,
Bilalbsheikh wrote:
> Hi Fellows,
> I am trying to update some records for simplicity my table is as
> follow
> ProgId PrgOccur UPC
> CircD 100 235689748965
> EDL 100 526396856971
> CircD 100 56985636258
> each record is unique means by combination of these 3 fields.
> now we need to moce couple of UPCs in EDL 100 to Circ 100. if a UPC
> already exists with in that CircD 100 it will simply insert that UPC
> into some table and keep on inserting next records.
> simply i dont want to stop update process. but during insertiong if
> any duplicates found, put them separately and insert others.
> Regards,
> Bilal
Try this:
INSERT INTO tbl (ProgId, PrgOccur, UPC)
SELECT 'Circ', PrgOccur, UPC
FROM tbl AS t
WHERE UPC IN (1234567890,9999999999)
AND ProgId = 'EDL'
AND PrgOccur = 100
AND NOT EXISTS
(SELECT *
FROM tbl
WHERE ProgId = 'Circ'
AND PrgOccur = t.PrgOccur
AND UPC = t.UPC);
INSERT INTO some_other_table (ProgId, PrgOccur, UPC)
SELECT 'Circ', PrgOccur, UPC
FROM tbl AS t
WHERE UPC IN (1234567890,9999999999)
AND ProgId = 'EDL'
AND PrgOccur = 100
AND EXISTS
(SELECT *
FROM tbl
WHERE ProgId = 'Circ'
AND PrgOccur = t.PrgOccur
AND UPC = t.UPC);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Jump to next record insertion.

Hi Fellows,
I am trying to update some records for simplicity my table is as
follow
ProgId PrgOccur UPC
CircD 100 235689748965
EDL 100 526396856971
CircD 100 56985636258
each record is unique means by combination of these 3 fields.
now we need to moce couple of UPCs in EDL 100 to Circ 100. if a UPC
already exists with in that CircD 100 it will simply insert that UPC
into some table and keep on inserting next records.
simply i dont want to stop update process. but during insertiong if
any duplicates found, put them separately and insert others.
Regards,
Bilalbsheikh wrote:
> Hi Fellows,
> I am trying to update some records for simplicity my table is as
> follow
> ProgId PrgOccur UPC
> CircD 100 235689748965
> EDL 100 526396856971
> CircD 100 56985636258
> each record is unique means by combination of these 3 fields.
> now we need to moce couple of UPCs in EDL 100 to Circ 100. if a UPC
> already exists with in that CircD 100 it will simply insert that UPC
> into some table and keep on inserting next records.
> simply i dont want to stop update process. but during insertiong if
> any duplicates found, put them separately and insert others.
> Regards,
> Bilal
Try this:
INSERT INTO tbl (ProgId, PrgOccur, UPC)
SELECT 'Circ', PrgOccur, UPC
FROM tbl AS t
WHERE UPC IN (1234567890,9999999999)
AND ProgId = 'EDL'
AND PrgOccur = 100
AND NOT EXISTS
(SELECT *
FROM tbl
WHERE ProgId = 'Circ'
AND PrgOccur = t.PrgOccur
AND UPC = t.UPC);
INSERT INTO some_other_table (ProgId, PrgOccur, UPC)
SELECT 'Circ', PrgOccur, UPC
FROM tbl AS t
WHERE UPC IN (1234567890,9999999999)
AND ProgId = 'EDL'
AND PrgOccur = 100
AND EXISTS
(SELECT *
FROM tbl
WHERE ProgId = 'Circ'
AND PrgOccur = t.PrgOccur
AND UPC = t.UPC);
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Joins on UPDATE

I need some help understanding what's happening on a join when updating
tables. Assume 2 tables with identical structures:

UPDATE
Table1
SET
Field1 = Table2.Field1,
Field2 = Table2.Field2
FROM
Table2
WHERE
Table1.Field3 = Table2.Field3
AND Table1.Field4 = Table2.Field4

Indexes exist on Field3 and Field4 on both tables. So why does SQL
Server choose a hash join?

Thanks in advance.(andrewbb@.gmail.com) writes:
> I need some help understanding what's happening on a join when updating
> tables. Assume 2 tables with identical structures:
> UPDATE
> Table1
> SET
> Field1 = Table2.Field1,
> Field2 = Table2.Field2
> FROM
> Table2
> WHERE
> Table1.Field3 = Table2.Field3
> AND Table1.Field4 = Table2.Field4
>
> Indexes exist on Field3 and Field4 on both tables. So why does SQL
> Server choose a hash join?

Are those indexes on (Field3, Field4) or indexes on the individual
fields?

In any case, one of the tables will have to be scanned. Say that would
be Table2. Now for each row, we should look for a matching row in Table1.
Now, assume that only a few rows match. In this case, using a nested loop
and look up the row in Table1 is a good idea.

But what if all rows match? In this case, the pages in Table1 would be
accessed many times, and that would be expensive. Better then to scan
Table1 once. If there is a clustered index on (Field3, Field4), SQL
Server should be able to do a merge join, and scan both tables in
parallel. But if the index is non-clustered, then it's not of much
use, so instead SQL Server builds the hash table.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Fields 3 and 4 are individual indexes (not clustered) and the unique
key for the record.

There is exactly a one to one relationship between the two tables, so
how should I structure this to update quickly?|||(andrewbb@.gmail.com) writes:
> Fields 3 and 4 are individual indexes (not clustered) and the unique
> key for the record.
> There is exactly a one to one relationship between the two tables, so
> how should I structure this to update quickly?

You should have a clustered index on (field3, field4).

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Joining two fields in a query

I am trying to join two fields in a query in SQL 2000. For example.

Update myTable SET field_1 = @.field_1_value , field_2 = @.field_2_value, field_3 = @.field_1_value + ' x ' + field_2_value

Is this even possible.

I want the user to input values for fields 1 and 2, then in the background combine the two and insert that value in field 3.

Thanks in advance,

Scotty_C

the ' x ' should also be inserted between the values.

|||that'll work! there's just a typo for the @.field_2_value

Update myTable SET field_1 = @.field_1_value , field_2 = @.field_2_value, field_3 = @.field_1_value + ' x ' + @.field_2_value|||

I agree that there was a typo, however, the given SQL Statement was just fabricated for the forum as an example.

Thank you for you input.

When I attempt to execute the Statement I get the Error Message:

"Syntax Error Converting the varChar value ' x ' to a column of datatype int."

The actual SQL Statement being used is this:

UPDATE SheetSizes
SET Width = @.Width, Length = @.Length, Standard = @.Standard, Label = @.Width + ' x ' + @.Length
WHERE (SheetSizeID = @.SheetSizeID)

The datatype for the column "Length" is varChar(50)

|||excuse me, it has been a long day, the datatype for the column "Label" is varChar(50) and the dataype for the columns "Length" and "Width" is int.|||

Scotty_C wrote:

I agree that there was a typo, however, the given SQL Statement was just fabricated for the forum as an example.

Thank you for you input.

When I attempt to execute the Statement I get the Error Message:

"Syntax Error Converting the varChar value ' x ' to a column of datatype int."

The actual SQL Statement being used is this:

UPDATE SheetSizes
SET Width = @.Width, Length = @.Length, Standard = @.Standard, Label = @.Width + ' x ' + @.Length
WHERE (SheetSizeID = @.SheetSizeID)

The datatype for the column "Length" is varChar(50)

you have to use CAST or CONVERT before concatenating your values...
UPDATE SheetSizes
SET Width = @.Width, Length = @.Length, Standard = @.Standard, Label = CAST(@.Width AS varchar(10)) + ' x ' + CAST(@.Length AS varchar(10))
WHERE (SheetSizeID = @.SheetSizeID)

HTH,|||

Yes CryptoKnight,

That works very well, thank you!

Thanks,

Scotty_C

Joining to large tables to perfrom update

I have 2 large tables that are over 11 million records each. I need to join
them on 1 field and then update 4 fields. So my script is this
update a
set a.field1= b.field1,
a.field2= b.field2,
a.field3 = b.field3,
a.field4 = bfield4
from a inner join b
on a.field5= b.field5
This query is taking a long time to run and I am wondering if there are any
join hints or lock hints that I can put in there to make it more efficient.
Any help is appreciated.an index on b(field5, field1, field2, field3, field4) might help with
this particular update.
Considering the performance of the whole system, it might or might not
be worth keeping, depending on your priorities.|||You can use
update a
set a.field1= b.field1,
a.field2= b.field2,
a.field3 = b.field3,
a.field4 = bfield4
from a inner join b with (nolock)
on a.field5= b.field5
however, for 11 million rows, it will still take a lot of time.
I would create script that executes the update in batches (Example: 1
million per batch based on field5). In other words, I would create a
"control" table where I can store the field5, the bacth number and when was
updated. This way even if any of the batch updates do not complete (for any
reason), you can start where you left off rather than start all over again.
"Andy" wrote:

> I have 2 large tables that are over 11 million records each. I need to jo
in
> them on 1 field and then update 4 fields. So my script is this
> update a
> set a.field1= b.field1,
> a.field2= b.field2,
> a.field3 = b.field3,
> a.field4 = bfield4
> from a inner join b
> on a.field5= b.field5
> This query is taking a long time to run and I am wondering if there are an
y
> join hints or lock hints that I can put in there to make it more efficient
.
> Any help is appreciated.

JOINing on derived tables?

I have an UPDATE query that sets a "quantity" field in a table based
on the sum of events in another table. Those events are "basketed"
into different accounts through a four-way tupple, (acctId, portcode,
mgrgpcode, invpgm).
UPDATE requires the use of a derived table when using aggregates, fair
enough. The problem is that the interior derived table query is very
expensive, yet only a few rows of the returned recordset match in the
outer table. Without artificial limits, the query takes on the order
of 30 seconds, when the entire query batch otherwise takes about 5 to
10.
Here is the query in question (tpPNL means "temporary profit 'n
loss") . tpHPL already contains a number of rows for various accounts,
ONE of these rows is in an account that needs the complex calculation
of the inner query. Yet when the query runs, it does so for every
record in tblTrades, which has 2 million+ rows. I have artificially
introduced a WHERE constraint to limit this down for testing purposes,
but this is far from ideal. What should happen is that the inner query
will return one row for every (acctId, portcode, mgrgpcode, invpgm)
tupple in the outer table (tpHPL).
I realize I can do another sub-select on tpHPL and return a list of
which of those tupples is being used, but this strikes me as yet
another performance hit. Is there some easy way to have the inner
JOINed on the outer so this "just happens"?
UPDATE tpPNL SET
openingMVLocalCcy = s.openingMV,
closingMVLocalCcy = s.closingMV,
openingMVAcctCcy = s.openingMV * h.openingFX,
closingMVAcctCcy = s.closingMV * h.closingFX
FROM tpPNL h JOIN
(SELECT acctId, portcode, mgrgpcode, invpgm,
SUM(
CASE
WHEN TranDate>@.startDate THEN 0
ELSE amount
END) as openingMV,
SUM(
CASE
WHEN TranDate>@.endDate THEN 0
ELSE amount
END) as closingMV
FROM tblTrades
WHERE deleted=0
AND portcode=400
GROUP BY acctId, portcode, mgrgpcode, invpgm) as s
ON s.acctId=h.accountId AND s.portcode=h.portfolioId AND
s.mgrgpcode=h.groupIdI would write an EXISTS test in the subquery that checks for matches
in tpHPL. If there are really as few matches as you say it should pay
off. Alternately, it might be possible to simply JOIN tblTrades and
tpHPL in the subquery, though that would only work if the set of join
columns constitutes the full key to tpHPL.
Not that it sounds like you need me to tell you how to do that, just
saying that is what I would do.
Roy Harvey
Beacon Falls, CT
On Wed, 30 Apr 2008 08:02:13 -0700 (PDT), Maury Markowitz
<maury.markowitz@.gmail.com> wrote:
>I have an UPDATE query that sets a "quantity" field in a table based
>on the sum of events in another table. Those events are "basketed"
>into different accounts through a four-way tupple, (acctId, portcode,
>mgrgpcode, invpgm).
>UPDATE requires the use of a derived table when using aggregates, fair
>enough. The problem is that the interior derived table query is very
>expensive, yet only a few rows of the returned recordset match in the
>outer table. Without artificial limits, the query takes on the order
>of 30 seconds, when the entire query batch otherwise takes about 5 to
>10.
>Here is the query in question (tpPNL means "temporary profit 'n
>loss") . tpHPL already contains a number of rows for various accounts,
>ONE of these rows is in an account that needs the complex calculation
>of the inner query. Yet when the query runs, it does so for every
>record in tblTrades, which has 2 million+ rows. I have artificially
>introduced a WHERE constraint to limit this down for testing purposes,
>but this is far from ideal. What should happen is that the inner query
>will return one row for every (acctId, portcode, mgrgpcode, invpgm)
>tupple in the outer table (tpHPL).
>I realize I can do another sub-select on tpHPL and return a list of
>which of those tupples is being used, but this strikes me as yet
>another performance hit. Is there some easy way to have the inner
>JOINed on the outer so this "just happens"?
>UPDATE tpPNL SET
> openingMVLocalCcy = s.openingMV,
> closingMVLocalCcy = s.closingMV,
> openingMVAcctCcy = s.openingMV * h.openingFX,
> closingMVAcctCcy = s.closingMV * h.closingFX
>FROM tpPNL h JOIN
>(SELECT acctId, portcode, mgrgpcode, invpgm,
> SUM(
> CASE
> WHEN TranDate>@.startDate THEN 0
> ELSE amount
> END) as openingMV,
>SUM(
> CASE
> WHEN TranDate>@.endDate THEN 0
> ELSE amount
> END) as closingMV
> FROM tblTrades
> WHERE deleted=0
> AND portcode=400
>GROUP BY acctId, portcode, mgrgpcode, invpgm) as s
> ON s.acctId=h.accountId AND s.portcode=h.portfolioId AND
>s.mgrgpcode=h.groupId|||On Apr 30, 11:41=A0am, "Roy Harvey (SQL Server MVP)"
<roy_har...@.snet.net> wrote:
> off. =A0Alternately, it might be possible to simply JOIN tblTrades and
> tpHPL in the subquery, though that would only work if the set of join
> columns constitutes the full key to tpHPL.
Can you give me a simple example of this? I was thinking of something
like...
WHERE acctId IN (select distinct acctId from tpHPL)
AND portcode IN (select distinct portfolio from tpHPL)
but that seems expensive!
Maury|||Your approach of using two independent IN clauses is incorrect.
Imagine that we had two rows of data in tpHPL:
acctId portcode
ABC XYZ
BCD MNO
Using two independent IN clauses that would match any of four
combinations:
ABC XYZ
ABC MNO
BCD MNO
BCD XYZ
What I suggest instead is to use an EXISTS test in the subquery.
UPDATE tpPNL
SET openingMVLocalCcy = s.openingMV,
closingMVLocalCcy = s.closingMV,
openingMVAcctCcy = s.openingMV * h.openingFX,
closingMVAcctCcy = s.closingMV * h.closingFX
FROM tpPNL h
JOIN (SELECT acctId, portcode, mgrgpcode, invpgm,
SUM(CASE WHEN TranDate > @.startDate
THEN 0
ELSE amount
END) as openingMV,
SUM(CASE WHEN TranDate > @.endDate
THEN 0
ELSE amount
END) as closingMV
FROM tblTrades
WHERE deleted = 0
AND portcode = 400
AND EXISTS
(SELECT * FROM tpPNL as X
WHERE tblTrades.acctId = X.accountId
AND tblTrades.portcode = X.portfolioId
AND tblTrades.mgrgpcode = X.groupId)
GROUP BY acctId, portcode, mgrgpcode, invpgm) as s
ON s.acctId = h.accountId
AND s.portcode = h.portfolioId
AND s.mgrgpcode = h.groupId
Roy Harvey
Beacon Falls, CT
On Wed, 30 Apr 2008 11:28:02 -0700 (PDT), Maury Markowitz
<maury.markowitz@.gmail.com> wrote:
>On Apr 30, 11:41 am, "Roy Harvey (SQL Server MVP)"
><roy_har...@.snet.net> wrote:
>> off. Alternately, it might be possible to simply JOIN tblTrades and
>> tpHPL in the subquery, though that would only work if the set of join
>> columns constitutes the full key to tpHPL.
>Can you give me a simple example of this? I was thinking of something
>like...
>WHERE acctId IN (select distinct acctId from tpHPL)
> AND portcode IN (select distinct portfolio from tpHPL)
>but that seems expensive!
>Maury

Join problem

Hi - I have a problem with an update (with a join) that I'm attempting to run on a table with 53 million records in.

Basically I set this query off for the first time and it chugged away for 96 hours before I killed it - something was awry.
I've restored the database to another server (just in case we have an issue with disks, compress, memory etc) and run some tests on samples of the 53 million and for low samples, the update runs in a reasonable time which increases in time directly in line with the increase in sample size:

Top # sample from database tests:
1,000,000 - 36 seconds
2,000,000 - 71 seconds
3,000,000 - 122 seconds
but once I try 4,000,000 it runs and runs - got up to 36 minutes before I cancelled it.

Can anyone see any reason for this? I don't think the query size is going up exponentially - because if you graph the sample size & time from 1-3 million, the line is linear.

I'm currently adding a record ID to the table so I can select the bottom 4,000,000 so I can be sure that there's not some weird data somewhere between 3-4mill that is making the query go whoopsie.

Here are the tables & query I am attemping to run:

UPDATE MailingHistory_Sample
SET MailingHistory_Sample.ERIValue = ListCategoryHierarchy2.[ERI Value]
FROM ListCategoryHierarchy2
WHERE MailingHistory_Sample.[SourceCode] = ListCategoryHierarchy2.[SourceCode ID]

Each table is clustered on SourceCode ID/Sourcecode

(varchars are COLLATE Latin1_General_CI_AS)

CREATE TABLE [ListCategoryHierarchy2] (
[Campaign ID] [varchar] (255) NULL,
[Media ID] [varchar] (255) NULL,
[Media Description] [varchar] (255) NULL ,
[Media Selections] [varchar] (255) NULL ,
[SourceCode ID] [varchar] (7) NULL ,
[List ID] [varchar] (255) NULL ,
[Mailing Date] [smalldatetime] NULL ,
[List Category] [varchar] (255) NULL ,
[Source Code Offer] [varchar] (255) NULL ,
[ERI Value] [float] NULL
) ON [PRIMARY]
(9,923 records)

CREATE TABLE [MailingHistory_sample] (
[MatchKey] [binary] (20) NULL ,
[SourceCode] [varchar] (6) NULL ,
[ListID] [varchar] (7) NULL ,
[StationeryCode] [varchar] (5) NULL ,
[PDYear] [varchar] (2) NULL ,
[NaadID] [varchar] (10) NULL ,
[OrderNumber] [char] (9) NOT NULL ,
[CustomerNumber] [binary] (8) NULL ,
[CampaignCode] [varchar] (4) NULL ,
[ProductCode] [varchar] (4) NULL ,
[ResponseType] [varchar] (1) NULL ,
[HouseholdNumber] [bigint] NULL ,
[IndividualNumber] [bigint] NULL ,
[DataType] [varchar] (1) NULL ,
[AddressNumber] [bigint] NULL ,
[DateStamp] [char] (8) NULL ,
[CountColumn] [int] NULL ,
[MailingInstance] [int] NULL ,
[PCPrizm] [char] (5) NULL ,
[Postcode] [char] (7) NULL ,
[PostalArea] [varchar] (2) NULL ,
[TVRegion] [varchar] (3) NULL ,
[ERIRange] [int] NULL ,
[ERIValue] [float] NULL ,
[MediaID] [varchar] (10) NULL
) ON [PRIMARY]

SQL 2000

Any thoughts? Could there be some critical mass of temp table size etc that I am hitting?
thx
wAh, just got the (PRIMARY' filegroup is full) message after trying to insert an ID into the large table - could this be the bigger problem?|||Well, I'm not too fond of your syntax. You should link your tables in a join rather than the WHERE clause, though I don't know that this would be impacting your execution time. It might.

UPDATE MailingHistory_Sample
SET MailingHistory_Sample.ERIValue = ListCategoryHierarchy2.[ERI Value]
FROM MailingHistory_Sample
inner join ListCategoryHierarchy2 on MailingHistory_Sample.[SourceCode] = ListCategoryHierarchy2.[SourceCode ID]

Also, drop any indexes on MailingHistory_Sample except one on [SourceCode]. This column should be indexed in both tables.|||Ah, just got the (PRIMARY' filegroup is full) message after trying to insert an ID into the large table - could this be the bigger problem?

Well that's one problem...

Where are the Indexes for these tables?|||As I've mentioned, each table is index (clustered) on SourceCode ID/Sourcecode.

We've actually managed to update all the 54 million rows in this table by adding an identity, and running this update in batches of 3million each - this took only a few hours.

Its definitely a volume issue which I hit sometime after 3million where the query just runs for days and doesn't (as far as I can see) complete.

Any ideas what this might be?|||As I've mentioned, each table is index (clustered) on SourceCode ID/Sourcecode.

We've actually managed to update all the 54 million rows in this table by adding an identity, and running this update in batches of 3million each - this took only a few hours.

Its definitely a volume issue which I hit sometime after 3million where the query just runs for days and doesn't (as far as I can see) complete.

Any ideas what this might be?

If your database is running in Full Recovery mode, a complete before and after image of the update must be stored in the transaction log. If the log file is situated on the same physical drive as the primary data store, has too small an auto-grow value, or is badly physically fragmented, the overall update time can become very, very long.

Options include: issuing an ALTER DATABASE command and backup to take the database into Simple Recovery before the update; relocate the transaction log to a dedicated physical drive; set the transaction log to very, very big and do not truncate its space to filing system when backing up.|||Ah thanks - that makes sense. I only learnt this morning about putting data files and transaction logs on different physical disks. That is the case for this database so we are going to slap in a new drive and split the two.

thanks
w