Bulk Updates taking a long time

We have a huge table with around 25 Million records. We want to reset two int Columns of all records to 0. Currently its taking around 1.5 hours... What are the best practises we can follow to reduce the total update time.

Initially we used - Update <TableName> set <Column1>=0, <Column2>=0.
Later we modified the query to include a WHERE clause and did the update in batch mode such as

DECLARE @.maxCount Int
DECLARE @.iCount Int
SELECT @.MaxCount = Max(ID) FROM organizationsource
SET @.iCount = 0
WHILE (@.iCount<@.MaxCount)
BEGIN
UPDATE <tableName> set <Column1> = 0, <Column2>=0
WHERE ID between @.iCount and @.iCount+1000000
SET @.iCount = @.iCount + 1000000
END

Can you please suggest some tips to improve the update performance.
Can we do something at the SQL Server level / are there any settings at the database level for performing faster updates.

Thanks,
Loonysan

Hard to determine based on the information you've provided so far:

Is the ID field the primary index?|||

Thanks for your interest.

To Answer your Questions

1) Yes - ID field is the primary key in my table.
2) This table will not be accessed by other applications during the update process.
3) Yeah - I have the Data and Log files in different drives. (Should I keep them in different disks for better performance)
4) We are using SQL Server 2005 :)
5) The code is resides in a Stored Procedure

Thanks,
Loonysan

|||

Ok.

Are the columns you are updating indexed also?

This can slow down updates. If so, drop the index and recreate after the update has happened.

Also - make sure the following is turned off to improve performance:

auto create statistics

Bulk Load - In a Panic Need Help... PLEASE!

Please help I am new to XML and Schema will someone look this over and tell
me what I am doing wrong?
Ok, I receive inventory updates once a week from my vendor in the following
format: (Obviously trimmed down)
<?xml version="1.0" encoding="UTF-8"?>
<AvailableBatch>
<Date>08/14/2005</Date>
<Available>
<Sku>40</Sku>
<Part>10 </Part>
<Location>20</Location>
<Qty>0</Qty>
<Time>07:20:09</Time>
<Detail>
<Desc>TIGHTS, MENS</Desc>
<Price>14.90</Price>
</Detail>
</Available>
<Available>
<Sku>REPEAT OVER 10000 TIMES</Sku>
<Part>1REPEAT OVER 10000 TIMES</Part>
<Location>REPEAT OVER 10000 TIMES</Location>
<Qty>REPEAT OVER 10000 TIMES</Qty>
<Time>REPEAT OVER 10000 TIMES</Time>
<Detail>
<Desc>REPEAT OVER 10000 TIMES</Desc>
<Price>REPEAT OVER 10000 TIMES</Price>
</Detail>
</Available>
</AvailableBatch>
tonight I picked through tutorials to try to setup my schema file... I know
this is wrong... Im just at a loss as to why? Now please keep in mind that
the above xml file only has the date once and then repeats the "<Available>"
section thousands of times:
Schema File:
<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="XMLSchema"
targetNamespace="http://tempuri.org/XMLSchema.xsd"
elementFormDefault="qualified"
xmlns="http://tempuri.org/XMLSchema.xsd"
xmlns:mstns="http://tempuri.org/XMLSchema.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="AvailableBatch">
<xs:complexType>
<xs:sequence>
<xs:element name ="Date">
<xs:complexType>
<xs:sequence id="Available">
<xs:element name ="Sku" type ="xs:string" maxOccurs
="unbounded"/>
<xs:element name ="Part" type ="xs:string" maxOccurs
="unbounded"/>
<xs:element name ="Location" type ="xs:string" maxOccurs
="unbounded"/>
<xs:element name ="Qty" type ="xs:string" maxOccurs
="unbounded"/>
<xs:element name ="Time" type ="xs:string" maxOccurs
="unbounded"/>
<xs:sequence id ="Detail">
<xs:element name ="Description" type ="xs:string" maxOccurs
="unbounded"/>
<xs:element name ="Price" type ="xs:string" maxOccurs
="unbounded"/>
</xs:sequence>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
I have a tuesday deadline to get this data into my data base... can someone
please give me a hand?
I really appreciate whoever can take their time to help me... You have no
idea how much I appreciate it!
Aaron
I did find a schema generator: Would this be correct or is this wrong?
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
elementFormDefault="qualified">
<xs:element name="AvailableBatch">
<xs:complexType>
<xs:sequence>
<xs:element ref="Date"/>
<xs:element maxOccurs="unbounded" ref="Available"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Date" type="xs:string"/>
<xs:element name="Available">
<xs:complexType>
<xs:sequence>
<xs:element ref="Sku"/>
<xs:element ref="Part"/>
<xs:element ref="Location"/>
<xs:element ref="Qty"/>
<xs:element ref="Time"/>
<xs:element ref="Detail"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Sku" type="xs:integer"/>
<xs:element name="Part" type="xs:string"/>
<xs:element name="Location" type="xs:integer"/>
<xs:element name="Qty" type="xs:integer"/>
<xs:element name="Time" type="xs:NMTOKEN"/>
<xs:element name="Detail">
<xs:complexType>
<xs:sequence>
<xs:element ref="Desc"/>
<xs:element minOccurs="0" ref="Weight"/>
<xs:element minOccurs="0" ref="Price"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Desc" type="xs:string"/>
<xs:element name="Weight" type="xs:decimal"/>
<xs:element name="Price" type="xs:decimal"/>
</xs:schema>
Aaron C wrote:
>Please help I am new to XML and Schema will someone look this over and tell
>me what I am doing wrong?
>Ok, I receive inventory updates once a week from my vendor in the following
>format: (Obviously trimmed down)
><?xml version="1.0" encoding="UTF-8"?>
><AvailableBatch>
> <Date>08/14/2005</Date>
> <Available>
> <Sku>40</Sku>
> <Part>10 </Part>
> <Location>20</Location>
> <Qty>0</Qty>
> <Time>07:20:09</Time>
> <Detail>
> <Desc>TIGHTS, MENS</Desc>
> <Price>14.90</Price>
> </Detail>
> </Available>
><Available>
> <Sku>REPEAT OVER 10000 TIMES</Sku>
> <Part>1REPEAT OVER 10000 TIMES</Part>
> <Location>REPEAT OVER 10000 TIMES</Location>
> <Qty>REPEAT OVER 10000 TIMES</Qty>
> <Time>REPEAT OVER 10000 TIMES</Time>
> <Detail>
> <Desc>REPEAT OVER 10000 TIMES</Desc>
> <Price>REPEAT OVER 10000 TIMES</Price>
> </Detail>
> </Available>
></AvailableBatch>
>tonight I picked through tutorials to try to setup my schema file... I know
>this is wrong... Im just at a loss as to why? Now please keep in mind that
>the above xml file only has the date once and then repeats the "<Available>"
>section thousands of times:
>Schema File:
><?xml version="1.0" encoding="utf-8" ?>
><xs:schema id="XMLSchema"
> targetNamespace="http://tempuri.org/XMLSchema.xsd"
> elementFormDefault="qualified"
> xmlns="http://tempuri.org/XMLSchema.xsd"
> xmlns:mstns="http://tempuri.org/XMLSchema.xsd"
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:element name="AvailableBatch">
> <xs:complexType>
> <xs:sequence>
> <xs:element name ="Date">
> <xs:complexType>
> <xs:sequence id="Available">
> <xs:element name ="Sku" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:element name ="Part" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:element name ="Location" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:element name ="Qty" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:element name ="Time" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:sequence id ="Detail">
> <xs:element name ="Description" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:element name ="Price" type ="xs:string" maxOccurs
>="unbounded"/>
> </xs:sequence>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
></xs:schema>
>I have a tuesday deadline to get this data into my data base... can someone
>please give me a hand?
>I really appreciate whoever can take their time to help me... You have no
>idea how much I appreciate it!
>Aaron
|||Now that I have my schema and my xml file: Where do I go from here to
bulkload my Sql Dataase?
In over my head, lol
Aaron
Aaron C wrote:[vbcol=seagreen]
>I did find a schema generator: Would this be correct or is this wrong?
><?xml version="1.0" encoding="UTF-8"?>
><xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
>elementFormDefault="qualified">
> <xs:element name="AvailableBatch">
> <xs:complexType>
> <xs:sequence>
> <xs:element ref="Date"/>
> <xs:element maxOccurs="unbounded" ref="Available"/>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <xs:element name="Date" type="xs:string"/>
> <xs:element name="Available">
> <xs:complexType>
> <xs:sequence>
> <xs:element ref="Sku"/>
> <xs:element ref="Part"/>
> <xs:element ref="Location"/>
> <xs:element ref="Qty"/>
> <xs:element ref="Time"/>
> <xs:element ref="Detail"/>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <xs:element name="Sku" type="xs:integer"/>
> <xs:element name="Part" type="xs:string"/>
> <xs:element name="Location" type="xs:integer"/>
> <xs:element name="Qty" type="xs:integer"/>
> <xs:element name="Time" type="xs:NMTOKEN"/>
> <xs:element name="Detail">
> <xs:complexType>
> <xs:sequence>
> <xs:element ref="Desc"/>
> <xs:element minOccurs="0" ref="Weight"/>
> <xs:element minOccurs="0" ref="Price"/>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <xs:element name="Desc" type="xs:string"/>
> <xs:element name="Weight" type="xs:decimal"/>
> <xs:element name="Price" type="xs:decimal"/>
></xs:schema>
>[quoted text clipped - 80 lines]
Message posted via http://www.droptable.com
|||You have to specify the annotations to describe the mapping between your Xml
and relatinal structures.
Check out this link:
http://msdn.microsoft.com/library/de...tions_0gqb.asp
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aaron C via droptable.com" <forum@.droptable.com> wrote in message
news:52DF026D67AC8@.droptable.com...
> Now that I have my schema and my xml file: Where do I go from here to
> bulkload my Sql Dataase?
> In over my head, lol
> Aaron
>
> Aaron C wrote:
>
> --
> Message posted via http://www.droptable.com

Bulk Load - In a Panic Need Help... PLEASE!

Please help I am new to XML and Schema will someone look this over and tell
me what I am doing wrong'
Ok, I receive inventory updates once a w from my vendor in the following
format: (Obviously trimmed down)
<?xml version="1.0" encoding="UTF-8"?>
<AvailableBatch>
<Date>08/14/2005</Date>
<Available>
<Sku>40</Sku>
<Part>10 </Part>
<Location>20</Location>
<Qty>0</Qty>
<Time>07:20:09</Time>
<Detail>
<Desc>TIGHTS, MENS</Desc>
<Price>14.90</Price>
</Detail>
</Available>
<Available>
<Sku>REPEAT OVER 10000 TIMES</Sku>
<Part>1REPEAT OVER 10000 TIMES</Part>
<Location>REPEAT OVER 10000 TIMES</Location>
<Qty>REPEAT OVER 10000 TIMES</Qty>
<Time>REPEAT OVER 10000 TIMES</Time>
<Detail>
<Desc>REPEAT OVER 10000 TIMES</Desc>
<Price>REPEAT OVER 10000 TIMES</Price>
</Detail>
</Available>
</AvailableBatch>
tonight I picked through tutorials to try to setup my schema file... I know
this is wrong... Im just at a loss as to why? Now please keep in mind that
the above xml file only has the date once and then repeats the "<Available>"
section thousands of times:
Schema File:
<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="XMLSchema"
targetNamespace="http://tempuri.org/XMLSchema.xsd"
elementFormDefault="qualified"
xmlns="http://tempuri.org/XMLSchema.xsd"
xmlns:mstns="http://tempuri.org/XMLSchema.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="AvailableBatch">
<xs:complexType>
<xs:sequence>
<xs:element name ="Date">
<xs:complexType>
<xs:sequence id="Available">
<xs:element name ="Sku" type ="xs:string" maxOccurs
="unbounded"/>
<xs:element name ="Part" type ="xs:string" maxOccurs
="unbounded"/>
<xs:element name ="Location" type ="xs:string" maxOccurs
="unbounded"/>
<xs:element name ="Qty" type ="xs:string" maxOccurs
="unbounded"/>
<xs:element name ="Time" type ="xs:string" maxOccurs
="unbounded"/>
<xs:sequence id ="Detail">
<xs:element name ="Description" type ="xs:string" maxOccurs
="unbounded"/>
<xs:element name ="Price" type ="xs:string" maxOccurs
="unbounded"/>
</xs:sequence>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
I have a tuesday deadline to get this data into my data base... can someone
please give me a hand?
I really appreciate whoever can take their time to help me... You have no
idea how much I appreciate it!
AaronI did find a schema generator: Would this be correct or is this wrong?
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
elementFormDefault="qualified">
<xs:element name="AvailableBatch">
<xs:complexType>
<xs:sequence>
<xs:element ref="Date"/>
<xs:element maxOccurs="unbounded" ref="Available"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Date" type="xs:string"/>
<xs:element name="Available">
<xs:complexType>
<xs:sequence>
<xs:element ref="Sku"/>
<xs:element ref="Part"/>
<xs:element ref="Location"/>
<xs:element ref="Qty"/>
<xs:element ref="Time"/>
<xs:element ref="Detail"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Sku" type="xs:integer"/>
<xs:element name="Part" type="xs:string"/>
<xs:element name="Location" type="xs:integer"/>
<xs:element name="Qty" type="xs:integer"/>
<xs:element name="Time" type="xs:NMTOKEN"/>
<xs:element name="Detail">
<xs:complexType>
<xs:sequence>
<xs:element ref="Desc"/>
<xs:element minOccurs="0" ref="Weight"/>
<xs:element minOccurs="0" ref="Price"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Desc" type="xs:string"/>
<xs:element name="Weight" type="xs:decimal"/>
<xs:element name="Price" type="xs:decimal"/>
</xs:schema>
Aaron C wrote:
>Please help I am new to XML and Schema will someone look this over and tell
>me what I am doing wrong'
>Ok, I receive inventory updates once a w from my vendor in the following
>format: (Obviously trimmed down)
><?xml version="1.0" encoding="UTF-8"?>
><AvailableBatch>
> <Date>08/14/2005</Date>
> <Available>
> <Sku>40</Sku>
> <Part>10 </Part>
> <Location>20</Location>
> <Qty>0</Qty>
> <Time>07:20:09</Time>
> <Detail>
> <Desc>TIGHTS, MENS</Desc>
> <Price>14.90</Price>
> </Detail>
> </Available>
><Available>
> <Sku>REPEAT OVER 10000 TIMES</Sku>
> <Part>1REPEAT OVER 10000 TIMES</Part>
> <Location>REPEAT OVER 10000 TIMES</Location>
> <Qty>REPEAT OVER 10000 TIMES</Qty>
> <Time>REPEAT OVER 10000 TIMES</Time>
> <Detail>
> <Desc>REPEAT OVER 10000 TIMES</Desc>
> <Price>REPEAT OVER 10000 TIMES</Price>
> </Detail>
> </Available>
></AvailableBatch>
>tonight I picked through tutorials to try to setup my schema file... I kno
w
>this is wrong... Im just at a loss as to why? Now please keep in mind tha
t
>the above xml file only has the date once and then repeats the "<Available>
"
>section thousands of times:
>Schema File:
><?xml version="1.0" encoding="utf-8" ?>
><xs:schema id="XMLSchema"
> targetNamespace="http://tempuri.org/XMLSchema.xsd"
> elementFormDefault="qualified"
> xmlns="http://tempuri.org/XMLSchema.xsd"
> xmlns:mstns="http://tempuri.org/XMLSchema.xsd"
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:element name="AvailableBatch">
> <xs:complexType>
> <xs:sequence>
> <xs:element name ="Date">
> <xs:complexType>
> <xs:sequence id="Available">
> <xs:element name ="Sku" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:element name ="Part" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:element name ="Location" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:element name ="Qty" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:element name ="Time" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:sequence id ="Detail">
> <xs:element name ="Description" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:element name ="Price" type ="xs:string" maxOccurs
>="unbounded"/>
> </xs:sequence>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
></xs:schema>
>I have a tuesday deadline to get this data into my data base... can someon
e
>please give me a hand?
>I really appreciate whoever can take their time to help me... You have no
>idea how much I appreciate it!
>Aaron|||Now that I have my schema and my xml file: Where do I go from here to
bulkload my Sql Dataase?
In over my head, lol
Aaron
Aaron C wrote:
>I did find a schema generator: Would this be correct or is this wrong?
><?xml version="1.0" encoding="UTF-8"?>
><xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
>elementFormDefault="qualified">
> <xs:element name="AvailableBatch">
> <xs:complexType>
> <xs:sequence>
> <xs:element ref="Date"/>
> <xs:element maxOccurs="unbounded" ref="Available"/>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <xs:element name="Date" type="xs:string"/>
> <xs:element name="Available">
> <xs:complexType>
> <xs:sequence>
> <xs:element ref="Sku"/>
> <xs:element ref="Part"/>
> <xs:element ref="Location"/>
> <xs:element ref="Qty"/>
> <xs:element ref="Time"/>
> <xs:element ref="Detail"/>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <xs:element name="Sku" type="xs:integer"/>
> <xs:element name="Part" type="xs:string"/>
> <xs:element name="Location" type="xs:integer"/>
> <xs:element name="Qty" type="xs:integer"/>
> <xs:element name="Time" type="xs:NMTOKEN"/>
> <xs:element name="Detail">
> <xs:complexType>
> <xs:sequence>
> <xs:element ref="Desc"/>
> <xs:element minOccurs="0" ref="Weight"/>
> <xs:element minOccurs="0" ref="Price"/>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <xs:element name="Desc" type="xs:string"/>
> <xs:element name="Weight" type="xs:decimal"/>
> <xs:element name="Price" type="xs:decimal"/>
></xs:schema>
>
>[quoted text clipped - 80 lines]
Message posted via http://www.webservertalk.com|||You have to specify the annotations to describe the mapping between your Xml
and relatinal structures.
Check out this link:
http://msdn.microsoft.com/library/d...ations_0gqb.asp
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aaron C via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:52DF026D67AC8@.webservertalk.com...
> Now that I have my schema and my xml file: Where do I go from here to
> bulkload my Sql Dataase?
> In over my head, lol
> Aaron
>
> Aaron C wrote:
>
> --
> Message posted via http://www.webservertalk.com

BULK INSERT maybe ??

I have a directory with files:

X.dbf
X.mdx
Y.dbf
Y.mdx
...

These files contain updates for my DB (I don't know their structure). How I can insert them in temporary tables on the SQL server ?

Note: I don't want to use Import/Export Tool, cause I will need this insert as scripts...I'd use DTS.

-PatP|||I'd use DTS.

-PatP

You would?

I'd create a sproc...

USing xp-Cmdshell, I would interogate the directory and load the file names to a table.

I would process the files 1 by 1

I would the bcp the data in to a single column varchar table

I would then process the data.

Bu if you don't know the structure of the data, what would you propose you'd do?|||Last time I did something like this, the .dbf extension files came from dBase V. DTS has an interface for that datafile type. As for .mdx ... ?|||It isn't hard to determine the schema of a DBF file, as Tom pointed out they are just dBase files which are effectively a single table with the schema tucked into the file header. You can relatively easily import arbitrary dbf files into a database from within a DTS package... It is more complex than dealing with a static structure, but not rocket science by any means.

The MDX files are just queries written as Multi-dimensional Expressions. Those can simply be stuffed into a TEXT column somewhere, probably the best organization would be to simply track what file they came from (X.MDX) and the text in a single table, maybe with some decorative columns to track when the file was timestamped, when it was imported into the table, etc.

-PatP|||Multi-dimensional Expressions? I may be way off, but I think I remember MDX to be primary indexes and NDX secondary (?)...Or maybe it's FoxPro? It's been awhile.|||I should just make it part of my sig...

"It really helps if you read these things"|||...and your point is...?