Bulk Inserting Data into a new table with different ID's that need to be changed

Hi

I have a system which Profiles people. However a new profiler has been written which is better, and all the old profiled members have to be moved to the new profiler tables.

I have a Members table with member data in it, it contains the old profile data in the table too, or at least the ID's per member.

In the new profiler things work slightly different.

I'll give an example:

Lets say MemberID 3241 is a smoker, SmokerID = 3 (Moderate Smoker)

And he drinks occasionally, AlcoholID = 2 (Light)

These ID fields are kept in the Members table which link to a table of their own i.e. memSmoking Table, or memAlcohol Table

The new profiler table works different. In the sense that the Alcohol and Smoking are all in the same table, but with different OptionID's and ValuesID's

Here is some sample code that i wrote. Just copy and paste, it will give you a base to work from. I'm trying my best to supply as much information as possible, so if i left anything out then please let me know? And thanks for the help in advance

Code Snippet

--Sample Code:

--Members Table

DECLARE @.Members TABLE (MemberID INT IDENTITY(1,1),

ClientID INT,

Name VARCHAR(50),

Surname VARCHAR(50),

GenderID CHAR(1),

MaritalStatusID INT,

MemSmokingID INT,

MemAlcoholID INT)

INSERT INTO @.Members VALUES (211, 'Carel','Greaves', 'M', 2, 1, 3)

INSERT INTO @.Members VALUES (211, 'Jill', 'Jenkins', 'F', 1, 3, 4)

SELECT * FROM @.Members

--

--Profile Attrubutes

--

DECLARE @.memGender TABLE (GenderID CHAR(1), Description VARCHAR(50))

INSERT INTO @.memGender VALUES ('M', 'Male')

INSERT INTO @.memGender VALUES ('F', 'Female')

SELECT * FROM @.memGender

DECLARE @.memMaritalStatus TABLE (MaritalStatusID INT, Description VARCHAR(50))

INSERT INTO @.memMaritalStatus VALUES (1, 'Married')

INSERT INTO @.memMaritalStatus VALUES (2, 'Single')

INSERT INTO @.memMaritalStatus VALUES (3, 'Devorced')

INSERT INTO @.memMaritalStatus VALUES (4, 'Widowed')

SELECT * FROM @.memMaritalStatus

DECLARE @.memSmoke TABLE (MemSmokingID INT, Description VARCHAR(50))

INSERT INTO @.memSmoke VALUES (1, 'Nil')

INSERT INTO @.memSmoke VALUES (2, 'Light')

INSERT INTO @.memSmoke VALUES (3, 'Moderate')

INSERT INTO @.memSmoke VALUES (4, 'Heavy')

SELECT * FROM @.memSmoke

DECLARE @.memAlcohol TABLE (MemAlcoholID INT, Description VARCHAR(50))

INSERT INTO @.memAlcohol VALUES (1, 'Nil')

INSERT INTO @.memAlcohol VALUES (2, 'Light')

INSERT INTO @.memAlcohol VALUES (3, 'Moderate')

INSERT INTO @.memAlcohol VALUES (4, 'Heavy')

SELECT * FROM @.memAlcohol

--

--New Profile Attributes

--

DECLARE @.MemberOptionAtributes TABLE (ValuesID INT IDENTITY(1,1),

OptionID INT,

DisplayName VARCHAR(50))

INSERT INTO @.MemberOptionAtributes VALUES (2, 'Male')

INSERT INTO @.MemberOptionAtributes VALUES (2, 'Female')

INSERT INTO @.MemberOptionAtributes VALUES (3, 'Married')

INSERT INTO @.MemberOptionAtributes VALUES (3, 'Single')

INSERT INTO @.MemberOptionAtributes VALUES (3, 'Devorced')

INSERT INTO @.MemberOptionAtributes VALUES (3, 'Widowed')

INSERT INTO @.MemberOptionAtributes VALUES (4, 'Nil')

INSERT INTO @.MemberOptionAtributes VALUES (4, 'Light')

INSERT INTO @.MemberOptionAtributes VALUES (4, 'Moderate')

INSERT INTO @.MemberOptionAtributes VALUES (4, 'Heavy')

INSERT INTO @.MemberOptionAtributes VALUES (5, 'Nil')

INSERT INTO @.MemberOptionAtributes VALUES (5, 'Light')

INSERT INTO @.MemberOptionAtributes VALUES (5, 'Moderate')

INSERT INTO @.MemberOptionAtributes VALUES (5, 'Heavy')

SELECT * FROM @.MemberOptionAtributes

--MemberProfileLookupValues Table

DECLARE @.MemberProfileLookupValues TABLE (EntryID INT IDENTITY(1,1),

MemberID INT,

OptionID INT,

ValueID INT)

--This is how the data should be inserted, but i don't know how to do it in bulk for all the members in the members table (There are over 1000000)!

INSERT INTO @.MemberProfileLookupValues VALUES (1, 1, 1)

INSERT INTO @.MemberProfileLookupValues VALUES (1, 3, 5)

INSERT INTO @.MemberProfileLookupValues VALUES (1, 4, 7)

INSERT INTO @.MemberProfileLookupValues VALUES (1, 5, 13)

INSERT INTO @.MemberProfileLookupValues VALUES (2, 1, 2)

INSERT INTO @.MemberProfileLookupValues VALUES (2, 3, 3)

INSERT INTO @.MemberProfileLookupValues VALUES (2, 4, 9)

INSERT INTO @.MemberProfileLookupValues VALUES (2, 5, 14)

SELECT * FROM @.MemberProfileLookupValues

--Here is a piece of actual code that i tried that i thought would work but it inserted all the fields for all options for all members (WHOOPS!)

/*SET NOCOUNT ON

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

SELECT M.MemberID, '6', CASE M.MaritalStatusID WHEN 1 THEN '7'

WHEN 2 THEN '8'

WHEN 3 THEN '9'

WHEN 4 THEN '10'

END

FROM Members M

INNER JOIN _MemberProfileLookupValues ML ON M.MemberID = ML.MemberID

WHERE M.Active = 1

AND ML.OptionID <> 6

GO

*/

Carel:

What is the objective here; I am afraid I missed it.

|||

Old Profiler uses these tables

@.Members

@.memSmoking

@.memAlcohol

@.memMaritalStatus

New Profiler uses these Tables

@.MemberOptionAtributes

@.MemberProfileLookupValues

So the memberID From the @.members table has to be inserted into the @.memberProfileLookupValues table, where the OptionID matched the type of attribute i.e. Smoking, Alcohol etc

Smoking = OptionID (4 in this case) i get the OptionID values from the @.MemberOptionAtributes table

Alcohol = OptionID (5 in this case) i also get the OptionID values from the @.MemberOptionAttributes table

When it comes to the memory tables i created, just look at the INSERT STATEMENT for the @.MemberProfileLookupValues table at the bottom and compare to the fields in the @.Members table.

OptionID's Come from the @.MemberOptionAtributes table. The valuesID's just have to be set to the right places.

for example:

If you look at the memSmoking Table

it has 4 ID's

1

2

3

4

In the @.MemberOptionAtributes table the OptionID for Smoking is 4

as far as the values are concerned for smoking in the MemberOptionAtributes table

@.memSmoking .memSmokingID(1) = @.MemberOptionAtributes .ValueID(7)

@.memSmoking .memSmokingID(2) = @.MemberOptionAtributes .ValueID(8)

@.memSmoking .memSmokingID(3) = @.MemberOptionAtributes .ValueID(9)

@.memSmoking .memSmokingID(4) = @.MemberOptionAtributes .ValueID(10)

I hope this helps, i'll keep trying to give as much info as possible.

Kind Regards

Carel Greaves

=== Edited by Carel Greaves @. 23 Jun 2007 9:32 PM UTC===

This was the porst that i posted yesterday, i sat thinking about the problem again today, and came up with something that might help me a little more, and maybe unconfuse the situation.

I want to insert all the generated memberID's in the members table into the @.MemberProfileLookupValues table with the AlcoholID's and MemSmokingID's

Hovever the New Profiler accepts the MemberID, OptionID, and ValuesID
Where the old way it was done was basically a pivotted way of doing it, i'm basically just un-pivvotting the table in the new table.

--Real Code that i actually used.
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
SELECT m.MemberID, '12', CASE MC.HealthInterestID
WHEN 1 THEN '71'
WHEN 2 THEN '72'
WHEN 3 THEN '73'
WHEN 4 THEN '74'
WHEN 5 THEN '75'
WHEN 6 THEN '76'
WHEN 7 THEN '77'
WHEN 8 THEN '78'
WHEN 9 THEN '79'
WHEN 10 THEN '80'
WHEN 11 THEN '81'
WHEN 12 THEN '82'
WHEN 13 THEN '83'
WHEN 14 THEN '84'
END
FROM Members m, _MemberProfileLookupValues ml, memHealthInterests mc
WHERE m.memberID = ml.MemberID
AND m.MemberID = mc.MemberID
AND m.Active = 1
AND ml.OptionID &lt;&gt; 12
GO

The problem that i had with this statement is that it Inserted the memberID with all of these field per memberID, instead of looking for where the values is = lets say 9 and insering the values 79 (According to the CASE)

|||

This was the post is started two days ago, wouold someone please be able to help me out.

Just look at my @.Members table and the @.MemberProfileLookupValues

This was the porst that i posted yesterday, i sat thinking about the problem again today, and came up with something that might help me a little more, and maybe unconfuse the situation.

I want to insert all the generated memberID's in the members table into the @.MemberProfileLookupValues table with the AlcoholID's and MemSmokingID's

Hovever the New Profiler accepts the MemberID, OptionID, and ValuesID
Where the old way it was done was basically a pivotted way of doing it, i'm basically just un-pivvotting the table in the new table.

Code Snippet

--Real Code that i actually used.
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
SELECT m.MemberID, '12', CASE MC.HealthInterestID
WHEN 1 THEN '71'
WHEN 2 THEN '72'
WHEN 3 THEN '73'
WHEN 4 THEN '74'
WHEN 5 THEN '75'
WHEN 6 THEN '76'
WHEN 7 THEN '77'
WHEN 8 THEN '78'
WHEN 9 THEN '79'
WHEN 10 THEN '80'
WHEN 11 THEN '81'
WHEN 12 THEN '82'
WHEN 13 THEN '83'
WHEN 14 THEN '84'
END
FROM Members m, _MemberProfileLookupValues ml, memHealthInterests mc
WHERE m.memberID = ml.MemberID
AND m.MemberID = mc.MemberID
AND m.Active = 1
AND ml.OptionID &lt;&gt; 12
GO

The problem that i had with this statement is that it Inserted the memberID with all of these field per memberID, instead of looking for where the values is = lets say 9 and insering the values 79 (According to the CASE)

|||

Carel,

I've been hoping (obviously, against hope) that you would see the mistakes inherrent in attempting to shove a EAV data model into a relationation data engine. Here, I'm going to allow someone else to attempt to explain it again. (From: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61024&whichpage=2, Michael Jones:

I think that the query you posted is a perfect illustration of the biggest disadvantage of the Entity/Attribute model, that it saves a little work up front in data modeling by allowing “open ended” insertion of new attributes at the cost of having to program the true data structure into each query. Of course, there are other annoying little problems, like enforcing not null, DRI, domain integrity, default values, check constraints, creating useful indexes, transactional integrity, etc. Basically, it takes all the most useful features of a relational data model, and throws them away.

I have to revise my other non-PC comment: "Encoded in binary, then base64 encoded into text? That’s one of the stupidest thing I've ever seen, but it looks like a stoke of genius compared to using an Entity/Attribute data model!!! What brain-dead morons came up with that!? I know it had to be a committee, because no one could be that stupid all on their own!"

The 'Original Members' table is the 'best' way to store this data. Both attempts of using some form of EAV is a bastardization of the relational database, and will always cause you more grief than it is worth. The task at hand is but one more example.

Using the code you provided (and thank you for the DDL and sample data!!!), it seems that you may be over-complicating the process. I think that it will work for you like this:

Code Snippet

INSERT INTO @.MemberProfileLookupValues
( MemberID,
OptionID,
ValueID
)
SELECT
m.MemberID,
'6',
CASE m.MaritalStatusID
WHEN 1 THEN '7'
WHEN 2 THEN '8'
WHEN 3 THEN '9'
WHEN 4 THEN '10'
ELSE NULL
END
FROM @.Members m

EntryID MemberID OptionID ValueID
-- -- -- --
1 1 6 8
2 2 6 7

Please do yourself a favor and do some research on the issues, problems, and pitfalls related to EAV data and relational databases.

(There are EAV databases available -I don't know how they evaluate...)

|||

Hi Arnie

Thanks for the help with that thread. I read what went on there and i agree with what the guys are talking about. Unfortunately i have been given the task of converting from the old model (Which is right) to the new model i.e. (EAV).

I don't have much say in the matter as the company outsourced the EAV system which was created and actually paid money for it.

When i ran my query to insert the memberID's into the EAV model then it would run through the whole case statement and insert each value per member. for example.

Instead of looking for where the value is 1 and then assigning it value 7.

It would insert values (7, 8, 9, and 10) per memberID for all memberID's. That was my problem. (And it would do it Multiple Times)

Maybe something is wrong, but it looks right to me and it makes sense. (i'm not too concerned about the type of model at present moment) i just want the data to go into the tables right.

Code Snippet

--INSERT memSmokingID Data of Members from Members Table

SET NOCOUNT ON

INSERTINTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

SELECT m.MemberID,'9',CASE M.MemSmokingID

WHEN 1 THEN'59'

WHEN 2 THEN'60'

WHEN 3 THEN'61'

WHEN 4 THEN'62'

END

FROM Members M

INNERJOIN _MemberProfileLookupValues ML ON M.MemberID = ML.MemberID

WHERE M.Active = 1

AND ml.OptionID <> 9

GO

|||

Why do you JOIN to [_MemberProfileLookupValues]?

That JOIN produces a row in the resultset for each row in [_MemberProfileLookupValues] -and I think that you only want one row per [Members] row.

Unless I don't have a complete picture of the data, you would be better off, as in my previous example, leaving the JOIN and WHERE clause out of the query. (I suspose you could still have the [m.Active = 1] filter though...)

|||

As i said in my previous post, stupidity resides EVERYWHERE

Thanks Arnie (AGAIN!!!) he he

makes sense now to me why it would keep on inserting more and more duplicates.

Bulk insert and Double Quotes

Hi,

i have been struggling with this problem for a few weeks so i am really hoping that some one can help me.

i have a system to in importing large amounts of data from text files in the a SQL 2005 DB. work fine 90% of the time but there are 2 files that are causign me really problems.

the delimiter in the fil is "," and there are commas with out quotes in some fo the fields so i can not ignor the quotes.

this command will import the files fine

Bulk Insert [RawCallData] From '\\Gci-linc-srv06\Billing\SQL Filters\GCI\Test.csv'

WITH

(FIELDTERMINATOR = '","')

but as not all the fields in [Raw Call Data] are used all of the time i need to use format files.

so my question is simple who do i do this with a format file ?

Code Snippet

Bulk Insert [RawCallData] From '\\Gci-linc-srv06\Billing\SQL Filters\GCI\Test.csv'

WITH

(FIELDTERMINATOR = '","'

, FORMATFILE = 'format_file_path')

|||

that will work, but i have to use a format file as there are 50 fields in raw call data and i am only use 19 for this file, other files that are imported use all 50.

I have found the solution my self by accident

you can use the following format file to do it

9.0
19
1 SQLCHAR 0 255 "\",\"" 1 Field1 Latin1_General_CI_AS
2 SQLCHAR 0 255 "\",\"" 2 Field2 Latin1_General_CI_AS
3 SQLCHAR 0 255 "\",\"" 3 Field3 Latin1_General_CI_AS
4 SQLCHAR 0 255 "\",\"" 4 Field4 Latin1_General_CI_AS
5 SQLCHAR 0 255 "\",\"" 5 Field5 Latin1_General_CI_AS
6 SQLCHAR 0 255 "\",\"" 6 Field6 Latin1_General_CI_AS
7 SQLCHAR 0 255 "\",\"" 7 Field7 Latin1_General_CI_AS
8 SQLCHAR 0 255 "\",\"" 8 Field8 Latin1_General_CI_AS
9 SQLCHAR 0 255 "\",\"" 9 Field9 Latin1_General_CI_AS
10 SQLCHAR 0 255 "\",\"" 10 Field10 Latin1_General_CI_AS
11 SQLCHAR 0 255 "\",\"" 11 Field11 Latin1_General_CI_AS
12 SQLCHAR 0 255 "\",\"" 12 Field12 Latin1_General_CI_AS
13 SQLCHAR 0 255 "\",\"" 13 Field13 Latin1_General_CI_AS
14 SQLCHAR 0 255 "\",\"" 14 Field14 Latin1_General_CI_AS
15 SQLCHAR 0 255 "\",\"" 15 Field15 Latin1_General_CI_AS
16 SQLCHAR 0 255 "\",\"" 16 Field16 Latin1_General_CI_AS
17 SQLCHAR 0 255 "," 17 Field17 Latin1_General_CI_AS
18 SQLCHAR 0 255 "," 18 Field18 Latin1_General_CI_AS
19 SQLCHAR 0 255 "\r\n" 19 Field19 Latin1_General_CI_AS

Bulk Insert and Data Aggregation

I manage a legacy system that dumps it's data into a number of different
databases (same schema) on a nightly basis using bulk insert. I need to
formulate a strategy for efficiently aggregating that data into a single
database right after these nightly extractions complete. Here is my current
stategy:
1. Duplicate the legacy system's database schema and add an identifier column
to specify which database the data loaded from.
2. Each night, delete all records in the table.
3. Each night, for each database:
3a. Set each table's default value to a value that references the current
database being loaded.
3b. Use the legacy system's flat files and format files to bulk insert into
the database.
3c. Clear the default value.
What other steps would faciliate performance? Dropping and recreating the
indexes? Does anyone forsee faults in this strategy?
Thanks,
Matt
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200801/1Hi
You could add a view with your extra "column" or use a query to export from
each database and then you don't have to worry about the defaults as the
value is in the flat files. This would mean that you could load in parrallel
although having separate tables for each database may allow you to reduce
contention when doing this. If you had separate tables then you could use a
partitioned view to "join" them all, this view could identify the source if
necessary.
John
"lotek via SQLMonster.com" <u16539@.uwe> wrote in message
news:7f0a31b9b1f67@.uwe...
>I manage a legacy system that dumps it's data into a number of different
> databases (same schema) on a nightly basis using bulk insert. I need to
> formulate a strategy for efficiently aggregating that data into a single
> database right after these nightly extractions complete. Here is my
> current
> stategy:
> 1. Duplicate the legacy system's database schema and add an identifier
> column
> to specify which database the data loaded from.
> 2. Each night, delete all records in the table.
> 3. Each night, for each database:
> 3a. Set each table's default value to a value that references the current
> database being loaded.
> 3b. Use the legacy system's flat files and format files to bulk insert
> into
> the database.
> 3c. Clear the default value.
>
> What other steps would faciliate performance? Dropping and recreating the
> indexes? Does anyone forsee faults in this strategy?
> Thanks,
> Matt
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200801/1
>

Bulk insert - to log or not to log

Dear all
I have a web application which has many customers. Each customer has their
own system, which means they have their own database. Each customer has
many users.
Out of office hours, I allow the customer to run very large data updates
against their systems. These are bulk inserts which use minimal logging. I
do this to ensure that they take as little time as possible.
However, some customers demand that they can do "small" data updates during
the day. If the data updates fall below a particular size threshold, then
they run during office hours. If one runs, there's minimal performance loss
to the other users on the server. However, if more than one runs at the
same time, then the server basically grinds to a halt, giving interactive
users extremely poor performance.
It was recommended to me that during office hours I should do bulk inserts
that were fully logged instead of minimally logged. The reasoning was that
the logging operation takes time, ensuring that the bulk insert can't hog
the processor, giving the interactive user far better performance. The
upload may take up to 10 times longer though.
This makes sense to me, but unfortunately I can't test it without doing it
on the live server. Any thoughts on this?
Thanks in advance
GriffGriff
>It was recommended to me that during office hours I >should do bulk inserts
>that were fully logged instead of minimally logged.
It depends as far as I know on recovery model of the database. Only if you
have full recovery model the bulk operation is fully logged. if the
transaction is failed you are able to restore the log file at point of time.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>|||
> It depends as far as I know on recovery model of the database. Only if you
> have full recovery model the bulk operation is fully logged. if the
> transaction is failed you are able to restore the log file at point of
time.
Hi Uri
True, but presumably the recovery option can be changed so that it's FULL
during office hours and BULK-LOGGED at other times. I'm not sure though
whether it's sensible to keep changing the recovery model...my guess is
not.
Griff|||Full recovery model will certainly cause a big increase in IO activity in
your log file during th day. Writing those log records also cnosume a lot of
CPU. It is doubtful that other database users can see better performance
while this is going on. I can not say for sure that you will see a
performance degradation, but the chance is pretty high.
I would start with checking the following things first:
Do you have blocking and deadlock problem when you introduce more than one
update workload (bulk insert)?
Is there lock escalation?
Is it 100% CPU? -- check performance monitor
Or Is it very long disk queue length? -- check performance monitor
A starting point is to read this article:
sql7perftune.asp" target="_blank">http://msdn.microsoft.com/library/d...ql7perftune.asp
and you will find some really great SQL Server performance tuning articles
on the web.
I recommend that you have a test system to experiment with various settings.
If you are MSDN subscriber, you may also want to check out SQL Server 2005
beta 2. It has a feature called "read committed snapshot" that may increase
throughput if you have a blocking and deadlock problem.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>

Bulk insert - to log or not to log

Dear all
I have a web application which has many customers. Each customer has their
own system, which means they have their own database. Each customer has
many users.
Out of office hours, I allow the customer to run very large data updates
against their systems. These are bulk inserts which use minimal logging. I
do this to ensure that they take as little time as possible.
However, some customers demand that they can do "small" data updates during
the day. If the data updates fall below a particular size threshold, then
they run during office hours. If one runs, there's minimal performance loss
to the other users on the server. However, if more than one runs at the
same time, then the server basically grinds to a halt, giving interactive
users extremely poor performance.
It was recommended to me that during office hours I should do bulk inserts
that were fully logged instead of minimally logged. The reasoning was that
the logging operation takes time, ensuring that the bulk insert can't hog
the processor, giving the interactive user far better performance. The
upload may take up to 10 times longer though.
This makes sense to me, but unfortunately I can't test it without doing it
on the live server. Any thoughts on this?
Thanks in advance
GriffGriff
>It was recommended to me that during office hours I >should do bulk inserts
>that were fully logged instead of minimally logged.
It depends as far as I know on recovery model of the database. Only if you
have full recovery model the bulk operation is fully logged. if the
transaction is failed you are able to restore the log file at point of time.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>|||> It depends as far as I know on recovery model of the database. Only if you
> have full recovery model the bulk operation is fully logged. if the
> transaction is failed you are able to restore the log file at point of
time.
Hi Uri
True, but presumably the recovery option can be changed so that it's FULL
during office hours and BULK-LOGGED at other times. I'm not sure though
whether it's sensible to keep changing the recovery model...my guess is
not.
Griff|||Full recovery model will certainly cause a big increase in IO activity in
your log file during th day. Writing those log records also cnosume a lot of
CPU. It is doubtful that other database users can see better performance
while this is going on. I can not say for sure that you will see a
performance degradation, but the chance is pretty high.
I would start with checking the following things first:
Do you have blocking and deadlock problem when you introduce more than one
update workload (bulk insert)?
Is there lock escalation?
Is it 100% CPU? -- check performance monitor
Or Is it very long disk queue length? -- check performance monitor
A starting point is to read this article:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/msdn_sql7perftune.asp
and you will find some really great SQL Server performance tuning articles
on the web.
I recommend that you have a test system to experiment with various settings.
If you are MSDN subscriber, you may also want to check out SQL Server 2005
beta 2. It has a feature called "read committed snapshot" that may increase
throughput if you have a blocking and deadlock problem.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>

Bulk insert - to log or not to log

Dear all
I have a web application which has many customers. Each customer has their
own system, which means they have their own database. Each customer has
many users.
Out of office hours, I allow the customer to run very large data updates
against their systems. These are bulk inserts which use minimal logging. I
do this to ensure that they take as little time as possible.
However, some customers demand that they can do "small" data updates during
the day. If the data updates fall below a particular size threshold, then
they run during office hours. If one runs, there's minimal performance loss
to the other users on the server. However, if more than one runs at the
same time, then the server basically grinds to a halt, giving interactive
users extremely poor performance.
It was recommended to me that during office hours I should do bulk inserts
that were fully logged instead of minimally logged. The reasoning was that
the logging operation takes time, ensuring that the bulk insert can't hog
the processor, giving the interactive user far better performance. The
upload may take up to 10 times longer though.
This makes sense to me, but unfortunately I can't test it without doing it
on the live server. Any thoughts on this?
Thanks in advance
Griff
Griff
>It was recommended to me that during office hours I >should do bulk inserts
>that were fully logged instead of minimally logged.
It depends as far as I know on recovery model of the database. Only if you
have full recovery model the bulk operation is fully logged. if the
transaction is failed you are able to restore the log file at point of time.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>
|||
> It depends as far as I know on recovery model of the database. Only if you
> have full recovery model the bulk operation is fully logged. if the
> transaction is failed you are able to restore the log file at point of
time.
Hi Uri
True, but presumably the recovery option can be changed so that it's FULL
during office hours and BULK-LOGGED at other times. I'm not sure though
whether it's sensible to keep changing the recovery model...my guess is
not.
Griff
|||Full recovery model will certainly cause a big increase in IO activity in
your log file during th day. Writing those log records also cnosume a lot of
CPU. It is doubtful that other database users can see better performance
while this is going on. I can not say for sure that you will see a
performance degradation, but the chance is pretty high.
I would start with checking the following things first:
Do you have blocking and deadlock problem when you introduce more than one
update workload (bulk insert)?
Is there lock escalation?
Is it 100% CPU? -- check performance monitor
Or Is it very long disk queue length? -- check performance monitor
A starting point is to read this article:
http://msdn.microsoft.com/library/de...l7perftune.asp
and you will find some really great SQL Server performance tuning articles
on the web.
I recommend that you have a test system to experiment with various settings.
If you are MSDN subscriber, you may also want to check out SQL Server 2005
beta 2. It has a feature called "read committed snapshot" that may increase
throughput if you have a blocking and deadlock problem.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>

Bulk import images to binary object in SQL 2000

I have a web application that I am rebuilding. I have many picture files that want to take off the file system and move into SQL as a blob. I will create an index of uids against the file names but need a good way to bulk add the files to the database... any hints on code or tools would be a great help.

Thanks

Bill

You can use a utility 'textcopy' in 'Command Prompt' under the SQL instance directory:

C:\Program Files\Microsoft SQL Server\MSSQL$InstanceName\Binn

FYI, you can use it as following, make sure the command in a single line:

====Copy data from file to table====

textcopy /S labsh96223\iori2000 /I /F c:\xml\iori.bmp /D northwind /T testImage /C img /U sa /P /W "where idx=2"
====Copy data from table to file====

textcopy /S labsh96223\iori2000 /O /F c:\xml\iori.jpg /D northwind /T testImage /C img /U sa /P /W "where idx=2"

You can also use some programming code (written in VC++,C# etc.) to manipulte the binary data of BLOBs between files and database.

Buisnes Managment System

i am desighning a database that looks after all expences of a buisnes. please any ideas would be welcome with regards to the user interface and requirements that you would need inorder for the product to function for your buisnes or needs

Hope to here from you soon

Thank you in advance
Simon Burgess

Dmop123@.hotmail.com
Email and msnThe obvious question is why don't you just use one of the dozens of off-the-shelf accounting packages? You will definitely be reinventing the wheel here.|||all If that is the case then how has the wheel transformed form wooden spocked cart wheels to the qulity of pirelli (whitch would you put on your car).

Within every thing there is always room for inprovement as nothing is perfect, but things can only get beter if constructive critisism is given.

chears|||One person may carve a wagon wheel but it takes a team of experts in many fields to create a Pirelli, plus one more to improve on it.|||thank you.

BUILTIN\Administrators vs System Administrator

Hello all,

does someone know where to find information regarding what is accesible to a BUILTIN\Administrator which is not accesible to a System Administrator?

Somewhere I have read that a System Administrator can not see the "All users' folders (i.e. the collection of "My folder"s for all users of the Report Server) but I have also experienced a behaviour which has surprised me: as a System Administrator with all permissions I cann't even see folders that a BUILTIN\Administrator can.

Does it mean that a System Administrator can not really manage all the resources of a Report Server?

Any ideas will be welcomed. Many thanks.

Firstly I would like to ask from where you have read the information, rather than replying you to with generic answers in this topic.

As one of the best practices is to remove the Builtin\Administrators as a part of security breakdown involving the sysadmin role. By default (from installation), the NT group BUILTIN\Administrators is granted sysadmin rights to SQL Server because it is made a member of the syadmin role. The BUILTIN\Administrators group corresponds to the local Administrators group for the server.

BUILTIN\Administrators

Is it customary/good practice to remove the
BUILTIN\Administrators group from the system databases?
Thanks,
FergieI usually create some other group for the windows accounts who are to be
sysadmins and remove this one.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Fergie" <anonymous@.discussions.microsoft.com> wrote in message
news:e91b01c3f086$ed5684a0$a501280a@.phx.gbl...
> Is it customary/good practice to remove the
> BUILTIN\Administrators group from the system databases?
> Thanks,
> Fergie|||In my experience it is common to do this. Many places I've
worked have domain admins in the local admins group of
each server and there have been too many people on the
network who are administrators and don't know SQL. However
if you do remove it, first of all be sure to create a
login in the sysadmin role which is an 'invariant' login.
Also, there are implications as far as full-text indexing
is concerned - you'll need to add NT Authority\System as a
sysadmin - see the article on this by Brian Kelley
(http://www.sqlservercentral.com/columnists/bkelley/sqlserv
ersecuritysecurityadmins.asp).
Regards,
Paul Ibison
>--Original Message--
>Is it customary/good practice to remove the
>BUILTIN\Administrators group from the system databases?
>Thanks,
>Fergie
>.
>|||Be very careful if you try to do this to a clustered SQL server. I think
there is a kb article about it.
Christian Smith
"Fergie" <anonymous@.discussions.microsoft.com> wrote in message
news:e91b01c3f086$ed5684a0$a501280a@.phx.gbl...
> Is it customary/good practice to remove the
> BUILTIN\Administrators group from the system databases?
> Thanks,
> Fergie

BUILTIN\Administrators

Is it customary/good practice to remove the
BUILTIN\Administrators group from the system databases?
Thanks,
FergieI usually create some other group for the windows accounts who are to be
sysadmins and remove this one.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Fergie" <anonymous@.discussions.microsoft.com> wrote in message
news:e91b01c3f086$ed5684a0$a501280a@.phx.gbl...
> Is it customary/good practice to remove the
> BUILTIN\Administrators group from the system databases?
> Thanks,
> Fergie|||Be very careful if you try to do this to a clustered SQL server. I think
there is a kb article about it.
Christian Smith
"Fergie" <anonymous@.discussions.microsoft.com> wrote in message
news:e91b01c3f086$ed5684a0$a501280a@.phx.gbl...
> Is it customary/good practice to remove the
> BUILTIN\Administrators group from the system databases?
> Thanks,
> Fergie

Building an application on multiple data sources

ASP.NET on SQL Server

I've been asked to quote for developing a system to expose data on a
web application. Most of the data will come from SQL Server DBs
located on a single box. However, some of the data will be sourced
from ORACLE which is located on a different box. It may be necessary
to create VIEWS and Stored Procedures joining these DBs

Does anyone have any pointers, clues, hints, tips or pitfalls that I
might consider while making my proposal? What sort of extra
contingency should I allow for the connection to ORACLE? Should I do
all the data retrieval on the DB server, or should I do it on the Web
server?

Any thoughts would be appreciated.

Thanks

William BalmerWilliam wrote:

Quote:

Originally Posted by

ASP.NET on SQL Server
>
I've been asked to quote for developing a system to expose data on a
web application. Most of the data will come from SQL Server DBs
located on a single box. However, some of the data will be sourced
from ORACLE which is located on a different box. It may be necessary
to create VIEWS and Stored Procedures joining these DBs
>
Does anyone have any pointers, clues, hints, tips or pitfalls that I
might consider while making my proposal? What sort of extra
contingency should I allow for the connection to ORACLE? Should I do
all the data retrieval on the DB server, or should I do it on the Web
server?

Look into the Linked Server features of SQL server. You can link to the
Oracle db & run queries against the Oracle db thru SQL server. This may
reduce the maintenance - you'll only be writing in SQL Server syntax, or
calling SQL Server stored procedures that query the Oracle db.
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)|||"Linked Server" can be one way to do this. However general network
latency and throughput restrictions due to bandwidth limitations or
network traffic, etc. can completely kill joins across the wire.

Also, be prepared to write views on the Oracle box to convert Oracle
data types to be more compatible with Sql Servers needs even using
linked servers.

Another option to consider it to "replicate" the data from the Oracle
box onto a Sql Server box, avoiding the need for joins across the wire.
This is the option I had to take to get acceptable performance for one
job I did.

William wrote:

Quote:

Originally Posted by

ASP.NET on SQL Server
>
I've been asked to quote for developing a system to expose data on a
web application. Most of the data will come from SQL Server DBs
located on a single box. However, some of the data will be sourced
from ORACLE which is located on a different box. It may be necessary
to create VIEWS and Stored Procedures joining these DBs
>
Does anyone have any pointers, clues, hints, tips or pitfalls that I
might consider while making my proposal? What sort of extra
contingency should I allow for the connection to ORACLE? Should I do
all the data retrieval on the DB server, or should I do it on the Web
server?
>
Any thoughts would be appreciated.
>
Thanks
>
William Balmer