insert or replace: ado

Showing posts with label ado. Show all posts

Monday, March 19, 2012

Insert syntax error

I'm not quite sure if this is the right forum. I am trying to execute a insert statement. Using an ADO connection with Jet 4 (MS Access and VBA).

"INSERT INTO tblLocking (Table, Record, User) VALUES ('tblDiscrepancy','70','mnewheiser')"

I am getting a syntax error on the above statement. All fields that i am inserting to (Table, Record and User) are all text types, and the table does exist.

There is a fourth column in the table (LockID) which is a Access [Auto-Number] type(why i'm not sure on the forum) do i need to declare this in the statement.

Any help would be much appreciated as its is beginning to drive me mad.

CheersOk, so it appears the solutions wasn't as complicated as i thought it would be.

Although 'Table' and 'User' where valid columns in the table, they are also reserved words (or something like that). So in the statement they needs [ and ] around them (i.e. [Table])

Don't have to slit my wrists now. :)

Monday, March 12, 2012

INSERT statement conflicted with COLUMN CHECK constraint.

Hi,
I'm attempting to insert a new row into an SQL table using ADO written
with c# and stored procedures.
The ADO code is running OK, and i know it should work as i have used
equivilent code succeffully for other tables. However i am getting the
following error:
{"INSERT statement conflicted with COLUMN CHECK constraint 'CK
tblPatient pntStage'. The conflict occurred in database 'YLCdbSQL',
table 'tblPatient', column 'pntStage'.\r\nThe statement has been
terminated." }
pntStage has data type NVarChar, and maximum length 8. The values i am
attempting to input do not violate these criteria. I have deleted the
complete row and added it again incase there was some hidden input
mask, this has not solved the problem.
Any ideas what the problem might be? Here's my stored procedure if
taht's any help.
CREATE PROCEDURE proc_InsertPatient
(@.patientNo int output,
@.pntUnitID nvarchar(15),
@.pntTitle nvarchar(4),
@.pntFName nvarchar(20),
@.pntLName nvarchar(30),
@.pntDOB nvarchar(8),
@.pntSex nvarchar(1),
@.pntAddress1 nvarchar(150),
@.pntAddress2 nvarchar(150),
@.pntAddress3 nvarchar(150),
@.pntCountryNo int output,
@.pntPostcode nvarchar (10),
@.pntHPhone nvarchar (14),
@.pntWPhone nvarchar (14),
@.pntMobPhone nvarchar (14),
@.pntEmail nvarchar (50),
@.pntStage nvarchar (8),
@.pntT tinyint,
@.pntN tinyint,
@.pntM tinyint,
@.pntPreviousTreatments char (1000),
@.pntFurtherNotes char (1000)
)
AS
INSERT INTO tblPatient (pntUnitID, pntTitle, pntFName, pntLName,
pntDOB, pntSex, pntAddress1,
pntAddress2, pntAddress3, pntCountryNo, pntPostcode, pntHPhone,
pntWPhone,
pntMobPhone, pntEmail, pntStage, pntT, pntN, pntM,
pntPreviousTreatments, pntFurtherNotes)
VALUES
(@.pntUnitID, @.pntTitle, @.pntFName, @.pntLName, @.pntDOB, @.pntSex,
@.pntAddress1,
@.pntAddress2, @.pntAddress3, @.pntCountryNo, @.pntPostcode, @.pntHPhone,
@.pntWPhone,
@.pntMobPhone, @.pntEmail, @.pntStage, @.pntT, @.pntN, @.pntM,
@.pntPreviousTreatments, @.pntFurtherNotes)
SELECT @.patientNo=@.@.IDENTITY
GO
Thanks.Hi
pntStage might have a check constraint that specifics that the values can
only be in a certain range or of a certain patters. Look at the Column's
check constrains though EM to see what has been setup.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Assimalyst" <c_oxtoby@.hotmail.com> wrote in message
news:1122817569.583719.19100@.g43g2000cwa.googlegroups.com...
> Hi,
> I'm attempting to insert a new row into an SQL table using ADO written
> with c# and stored procedures.
> The ADO code is running OK, and i know it should work as i have used
> equivilent code succeffully for other tables. However i am getting the
> following error:
> {"INSERT statement conflicted with COLUMN CHECK constraint 'CK
> tblPatient pntStage'. The conflict occurred in database 'YLCdbSQL',
> table 'tblPatient', column 'pntStage'.\r\nThe statement has been
> terminated." }
> pntStage has data type NVarChar, and maximum length 8. The values i am
> attempting to input do not violate these criteria. I have deleted the
> complete row and added it again incase there was some hidden input
> mask, this has not solved the problem.
> Any ideas what the problem might be? Here's my stored procedure if
> taht's any help.
> CREATE PROCEDURE proc_InsertPatient
> (@.patientNo int output,
> @.pntUnitID nvarchar(15),
> @.pntTitle nvarchar(4),
> @.pntFName nvarchar(20),
> @.pntLName nvarchar(30),
> @.pntDOB nvarchar(8),
> @.pntSex nvarchar(1),
> @.pntAddress1 nvarchar(150),
> @.pntAddress2 nvarchar(150),
> @.pntAddress3 nvarchar(150),
> @.pntCountryNo int output,
> @.pntPostcode nvarchar (10),
> @.pntHPhone nvarchar (14),
> @.pntWPhone nvarchar (14),
> @.pntMobPhone nvarchar (14),
> @.pntEmail nvarchar (50),
> @.pntStage nvarchar (8),
> @.pntT tinyint,
> @.pntN tinyint,
> @.pntM tinyint,
> @.pntPreviousTreatments char (1000),
> @.pntFurtherNotes char (1000)
> )
> AS
> INSERT INTO tblPatient (pntUnitID, pntTitle, pntFName, pntLName,
> pntDOB, pntSex, pntAddress1,
> pntAddress2, pntAddress3, pntCountryNo, pntPostcode, pntHPhone,
> pntWPhone,
> pntMobPhone, pntEmail, pntStage, pntT, pntN, pntM,
> pntPreviousTreatments, pntFurtherNotes)
> VALUES
> (@.pntUnitID, @.pntTitle, @.pntFName, @.pntLName, @.pntDOB, @.pntSex,
> @.pntAddress1,
> @.pntAddress2, @.pntAddress3, @.pntCountryNo, @.pntPostcode, @.pntHPhone,
> @.pntWPhone,
> @.pntMobPhone, @.pntEmail, @.pntStage, @.pntT, @.pntN, @.pntM,
> @.pntPreviousTreatments, @.pntFurtherNotes)
> SELECT @.patientNo=@.@.IDENTITY
> GO
>
> Thanks.
>|||Please post the table DDL (including the CHECK constraint) and the @.pntStage
value you are trying to insert. This will help us identify the cause of
your problem.

> I have deleted the
> complete row and added it again incase there was some hidden input
> mask, this has not solved the problem.
I'm not sure I understand what you mean by 'deleted the complete row'.
Since the insert failed, I wouldn't expect you would find the row in
tblPatient.
Hope this helps.
Dan Guzman
SQL Server MVP
"Assimalyst" <c_oxtoby@.hotmail.com> wrote in message
news:1122817569.583719.19100@.g43g2000cwa.googlegroups.com...
> Hi,
> I'm attempting to insert a new row into an SQL table using ADO written
> with c# and stored procedures.
> The ADO code is running OK, and i know it should work as i have used
> equivilent code succeffully for other tables. However i am getting the
> following error:
> {"INSERT statement conflicted with COLUMN CHECK constraint 'CK
> tblPatient pntStage'. The conflict occurred in database 'YLCdbSQL',
> table 'tblPatient', column 'pntStage'.\r\nThe statement has been
> terminated." }
> pntStage has data type NVarChar, and maximum length 8. The values i am
> attempting to input do not violate these criteria. I have deleted the
> complete row and added it again incase there was some hidden input
> mask, this has not solved the problem.
> Any ideas what the problem might be? Here's my stored procedure if
> taht's any help.
> CREATE PROCEDURE proc_InsertPatient
> (@.patientNo int output,
> @.pntUnitID nvarchar(15),
> @.pntTitle nvarchar(4),
> @.pntFName nvarchar(20),
> @.pntLName nvarchar(30),
> @.pntDOB nvarchar(8),
> @.pntSex nvarchar(1),
> @.pntAddress1 nvarchar(150),
> @.pntAddress2 nvarchar(150),
> @.pntAddress3 nvarchar(150),
> @.pntCountryNo int output,
> @.pntPostcode nvarchar (10),
> @.pntHPhone nvarchar (14),
> @.pntWPhone nvarchar (14),
> @.pntMobPhone nvarchar (14),
> @.pntEmail nvarchar (50),
> @.pntStage nvarchar (8),
> @.pntT tinyint,
> @.pntN tinyint,
> @.pntM tinyint,
> @.pntPreviousTreatments char (1000),
> @.pntFurtherNotes char (1000)
> )
> AS
> INSERT INTO tblPatient (pntUnitID, pntTitle, pntFName, pntLName,
> pntDOB, pntSex, pntAddress1,
> pntAddress2, pntAddress3, pntCountryNo, pntPostcode, pntHPhone,
> pntWPhone,
> pntMobPhone, pntEmail, pntStage, pntT, pntN, pntM,
> pntPreviousTreatments, pntFurtherNotes)
> VALUES
> (@.pntUnitID, @.pntTitle, @.pntFName, @.pntLName, @.pntDOB, @.pntSex,
> @.pntAddress1,
> @.pntAddress2, @.pntAddress3, @.pntCountryNo, @.pntPostcode, @.pntHPhone,
> @.pntWPhone,
> @.pntMobPhone, @.pntEmail, @.pntStage, @.pntT, @.pntN, @.pntM,
> @.pntPreviousTreatments, @.pntFurtherNotes)
> SELECT @.patientNo=@.@.IDENTITY
> GO
>
> Thanks.
>|||Mike,
Thanks for the quick reply.
I've looked in EM at the tblPatient table. Within it's properties i can
only see row Name (pntStage), Data Type (nvarchar), Size (8), Nulls
(not allowed), Default (blank).
Is this what you mean? Is there another way to check more detailed
constraints?
Thanks again.|||Hi
In EM, when you are in the Table Edit screen, top left next to the save
button is the Properties button. On the check constraints tab, you can see
what columns have constraints and what they are.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Assimalyst" <c_oxtoby@.hotmail.com> wrote in message
news:1122818859.232265.305760@.g14g2000cwa.googlegroups.com...
> Mike,
> Thanks for the quick reply.
> I've looked in EM at the tblPatient table. Within it's properties i can
> only see row Name (pntStage), Data Type (nvarchar), Size (8), Nulls
> (not allowed), Default (blank).
> Is this what you mean? Is there another way to check more detailed
> constraints?
> Thanks again.
>|||Dan,
Excuse my ignorance, but where do i find the table DDL?
As regards the input value, i have tried a few "Unknown", "I", "II",
"Ia", to name a few.
By the deleting row comment, i was just meaning that it didn't work, so
i completely removed that particular row from the table, then recreated
it. I thought perhaps i might have put some sort of input mask
constraint or something on it that i had forgotten about. By doing this
it would remove that possibility.
Thanks.|||Mike,
I've just done that, there were some constraints on it. not sure how
they got there, but i've altered them, problem solved! :)
Thank you very much!|||One method to generate the table DDL is to navigate to the table using the
Query Analyzer Object Browser and then right-click on the table and select
script to clipboard as create. You can then paste into your post.
Hope this helps.
Dan Guzman
SQL Server MVP
"Assimalyst" <c_oxtoby@.hotmail.com> wrote in message
news:1122819272.357946.52430@.f14g2000cwb.googlegroups.com...
> Dan,
> Excuse my ignorance, but where do i find the table DDL?
> As regards the input value, i have tried a few "Unknown", "I", "II",
> "Ia", to name a few.
> By the deleting row comment, i was just meaning that it didn't work, so
> i completely removed that particular row from the table, then recreated
> it. I thought perhaps i might have put some sort of input mask
> constraint or something on it that i had forgotten about. By doing this
> it would remove that possibility.
> Thanks.
>|||You might want to find out WHY someone put constraints on the data.
Having a default of a blank on what should be a code is a sign that
someone did not do much design work. Of course we know that when we
saw the "tbl;" and "pnt-" prefixes that violation basic rules for
naming data elements. The "tbl-" prefix is silly in a language with
one data structure; the "pnt-" tells us the location f one occurence of
a data element, not what it is. When you wrote "pntSex" did you mean
"sex_code", "sex_frequency", "sex_preference", "sex_total"? Again,
name it for what it is, never for where it is.
And the use of NVARCHAR(n) in codes is usually a sign the nobody
designed the encodings; we prefer CHAR(n) so can add constraints and do
validation. Do you really use a lot of Chinese characters? If you
allow it, it will come.

Friday, February 24, 2012

Insert produces error

Hi,

Using SQL Server 2000 with Windows 2000 Adv Server
&
Microsoft Access linked table (running stored procedure using ADO as
follows:

************************************************** ********
Private Sub cboAddrType_NotInList(NewData As String, Response As Integer)

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim msg As String

On Error GoTo Err_AddrType_NotInList
'Exit the procedure if the combo box was cleared
If Trim(NewData) = "" Then Exit Sub

'Confirm that the user wants to add AddrType
msg = "'" & Trim(NewData) & "' is not in the list." & vbCr & vbCr
msg = msg & "Do you want to add it?"
If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
'If the user chose not to add AddrType, set the response
'argument to supress an error message and undo changes.
Response = acDataErrContinue
MsgBox "No record added.", vbOKOnly, "Action Cancelled"
Else
'If the user chose to add AddrType, open a recordset
'using the AddrType table

Set cmd = New ADODB.Command
Set cnn = New ADODB.Connection
cnn.Open "Provider=SQLOLEDB;Data Source=penland01;Initial
Catalog=groomery;Integrated Security=SSPI;"

cmd.ActiveConnection = cnn
cmd.CommandText = "spInsertAddrType"
cmd.CommandType = adCmdStoredProc

Set prm = cmd.CreateParameter("AddrType", adVarChar,
adParamInput, , Trim(NewData))
cmd.Execute Parameters:=prm
'Set Response argument to indicate that new data is being added
Response = acDataErrAdded

cnn.Close
Set cnn = Nothing
End If

Exit_AddrType_NotInList:
Exit Sub

Err_AddrType_NotInList:
MsgBox Err.Description
Response = acDataErrContinue
************************************************** ********

"NewData" is a text string - in this case "Test"

The stored procedure referenced in the code is:

************************************
CREATE PROCEDURE [spInsertAddrType]
(@.AddrType [nvarchar](50))

AS
INSERT INTO [groomery].[dbo].[tblAddrTypes]
([fldAddrType])

VALUES
(@.AddrType)
GO
*************************************

When I execute this code, I receive the following error

"Cannot update identity column 'fldAddrTypeID'."

fldAddrTypeID is configured as follows:

***************************
Data Type = int
Identity = Yes
Identity Seed = 1
Identity Increment = 1
***************************

The documentation I've found online concerning this error says that it is
produced when you try to supply a value for an identity field without SET
IDENTITY_INSERT on. Obviously I am NOT specifying a value, so I can't
figure why I'm getting this error.

Thanks for any help you can offer.

ToddHi,

Found the answer elsewhere but thought I'd share it here in case someone
else has this problem.

Access's upsizing wizard created a trigger on tblAddrTypes which (evidently)
was meant to emulate Access's autonumber functionality. Once I deleted that
trigger, everything worked fine.

Todd
"Todd" <infoNOSPAM@.MAPSONgroomery.biz> wrote in message
news:T1j5e.11405$FN4.303@.newssvr21.news.prodigy.co m...
> Hi,
> Using SQL Server 2000 with Windows 2000 Adv Server
> &
> Microsoft Access linked table (running stored procedure using ADO as
> follows:
> ************************************************** ********
> Private Sub cboAddrType_NotInList(NewData As String, Response As Integer)
> Dim cnn As ADODB.Connection
> Dim cmd As ADODB.Command
> Dim prm As ADODB.Parameter
> Dim msg As String
> On Error GoTo Err_AddrType_NotInList
> 'Exit the procedure if the combo box was cleared
> If Trim(NewData) = "" Then Exit Sub
> 'Confirm that the user wants to add AddrType
> msg = "'" & Trim(NewData) & "' is not in the list." & vbCr & vbCr
> msg = msg & "Do you want to add it?"
> If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
> 'If the user chose not to add AddrType, set the response
> 'argument to supress an error message and undo changes.
> Response = acDataErrContinue
> MsgBox "No record added.", vbOKOnly, "Action Cancelled"
> Else
> 'If the user chose to add AddrType, open a recordset
> 'using the AddrType table
>
> Set cmd = New ADODB.Command
> Set cnn = New ADODB.Connection
> cnn.Open "Provider=SQLOLEDB;Data Source=penland01;Initial
> Catalog=groomery;Integrated Security=SSPI;"
> cmd.ActiveConnection = cnn
> cmd.CommandText = "spInsertAddrType"
> cmd.CommandType = adCmdStoredProc
> Set prm = cmd.CreateParameter("AddrType", adVarChar,
> adParamInput, , Trim(NewData))
> cmd.Execute Parameters:=prm
> 'Set Response argument to indicate that new data is being added
> Response = acDataErrAdded
> cnn.Close
> Set cnn = Nothing
> End If
> Exit_AddrType_NotInList:
> Exit Sub
> Err_AddrType_NotInList:
> MsgBox Err.Description
> Response = acDataErrContinue
> ************************************************** ********
> "NewData" is a text string - in this case "Test"
> The stored procedure referenced in the code is:
> ************************************
> CREATE PROCEDURE [spInsertAddrType]
> (@.AddrType [nvarchar](50))
> AS
> INSERT INTO [groomery].[dbo].[tblAddrTypes]
> ([fldAddrType])
> VALUES
> (@.AddrType)
> GO
> *************************************
> When I execute this code, I receive the following error
> "Cannot update identity column 'fldAddrTypeID'."
> fldAddrTypeID is configured as follows:
> ***************************
> Data Type = int
> Identity = Yes
> Identity Seed = 1
> Identity Increment = 1
> ***************************
> The documentation I've found online concerning this error says that it is
> produced when you try to supply a value for an identity field without SET
> IDENTITY_INSERT on. Obviously I am NOT specifying a value, so I can't
> figure why I'm getting this error.
> Thanks for any help you can offer.
> Todd

Insert performance/nvarchar

hello,
i wrote a performance test for sequential inserts with ado.net on a P4 2GHz
512 Meg Ram machine
and got the following scores:
insert 10000 ints 1:30 mins
insert 10000 reals 1:20 mins
inserting 10000 nvarchars
first 10000: 1:30
second 10000: 4:11
third 10000: 6:50
fourth 10000: 9:30
fifth 10000: 12:12
sixth 10000: 15:00
seventh 10000 18:20
so the times gets worse and worse.
i would expect, that the convergate but they don't
Is this normal?
If yes we will have problems, because we expect a couple of millions entries
in this
table where this strings are stored.
all tables for the performance test have the same stucture and indices
except of
the datatype which is tested, which is
id
value
Can you give me a hint how to speed this?
thanks mikeDo you have your databases auto-growing during these tests, or did you set
the files to a large enough size before the tests to ensure that they
wouldn't grow?
Do you have the columns indexed? Are the inserts causing page splits?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
news:E8610339-B39C-4A69-BD95-7EA882D0D228@.microsoft.com...
> hello,
> i wrote a performance test for sequential inserts with ado.net on a P4
2GHz
> 512 Meg Ram machine
> and got the following scores:
> insert 10000 ints 1:30 mins
> insert 10000 reals 1:20 mins
> inserting 10000 nvarchars
> first 10000: 1:30
> second 10000: 4:11
> third 10000: 6:50
> fourth 10000: 9:30
> fifth 10000: 12:12
> sixth 10000: 15:00
> seventh 10000 18:20
> so the times gets worse and worse.
> i would expect, that the convergate but they don't
> Is this normal?
> If yes we will have problems, because we expect a couple of millions
entries
> in this
> table where this strings are stored.
> all tables for the performance test have the same stucture and indices
> except of
> the datatype which is tested, which is
> id
> value
> Can you give me a hint how to speed this?
> thanks mike|||Hello Adam,
yes its auto-growing
yes columns are indexed
most inserts are NOT causing page splits
thanks mike
"Adam Machanic" wrote:
> Do you have your databases auto-growing during these tests, or did you set
> the files to a large enough size before the tests to ensure that they
> wouldn't grow?
> Do you have the columns indexed? Are the inserts causing page splits?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> news:E8610339-B39C-4A69-BD95-7EA882D0D228@.microsoft.com...
> > hello,
> >
> > i wrote a performance test for sequential inserts with ado.net on a P4
> 2GHz
> >
> > 512 Meg Ram machine
> >
> > and got the following scores:
> >
> > insert 10000 ints 1:30 mins
> > insert 10000 reals 1:20 mins
> >
> > inserting 10000 nvarchars
> > first 10000: 1:30
> > second 10000: 4:11
> > third 10000: 6:50
> > fourth 10000: 9:30
> > fifth 10000: 12:12
> > sixth 10000: 15:00
> > seventh 10000 18:20
> >
> > so the times gets worse and worse.
> > i would expect, that the convergate but they don't
> > Is this normal?
> >
> > If yes we will have problems, because we expect a couple of millions
> entries
> > in this
> > table where this strings are stored.
> >
> > all tables for the performance test have the same stucture and indices
> > except of
> > the datatype which is tested, which is
> > id
> > value
> >
> > Can you give me a hint how to speed this?
> >
> > thanks mike
>
>|||You'll get more consistent results if you grow the file first...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
news:857ED961-21D6-4B62-B2DF-07B3FB24F3FA@.microsoft.com...
> Hello Adam,
> yes its auto-growing
> yes columns are indexed
> most inserts are NOT causing page splits
> thanks mike
>
> "Adam Machanic" wrote:
> > Do you have your databases auto-growing during these tests, or did you
set
> > the files to a large enough size before the tests to ensure that they
> > wouldn't grow?
> >
> > Do you have the columns indexed? Are the inserts causing page splits?
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> > news:E8610339-B39C-4A69-BD95-7EA882D0D228@.microsoft.com...
> > > hello,
> > >
> > > i wrote a performance test for sequential inserts with ado.net on a P4
> > 2GHz
> > >
> > > 512 Meg Ram machine
> > >
> > > and got the following scores:
> > >
> > > insert 10000 ints 1:30 mins
> > > insert 10000 reals 1:20 mins
> > >
> > > inserting 10000 nvarchars
> > > first 10000: 1:30
> > > second 10000: 4:11
> > > third 10000: 6:50
> > > fourth 10000: 9:30
> > > fifth 10000: 12:12
> > > sixth 10000: 15:00
> > > seventh 10000 18:20
> > >
> > > so the times gets worse and worse.
> > > i would expect, that the convergate but they don't
> > > Is this normal?
> > >
> > > If yes we will have problems, because we expect a couple of millions
> > entries
> > > in this
> > > table where this strings are stored.
> > >
> > > all tables for the performance test have the same stucture and indices
> > > except of
> > > the datatype which is tested, which is
> > > id
> > > value
> > >
> > > Can you give me a hint how to speed this?
> > >
> > > thanks mike
> >
> >
> >|||hello adam,
nope,
i dropped the old database, created a new one with fixed size
600 meg (for data and tranlog).
The behaviour is still the same.
The insert times are growing endless.
greetings mike
"Adam Machanic" wrote:
> You'll get more consistent results if you grow the file first...
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> news:857ED961-21D6-4B62-B2DF-07B3FB24F3FA@.microsoft.com...
> > Hello Adam,
> >
> > yes its auto-growing
> > yes columns are indexed
> >
> > most inserts are NOT causing page splits
> >
> > thanks mike
> >
> >
> >
> > "Adam Machanic" wrote:
> >
> > > Do you have your databases auto-growing during these tests, or did you
> set
> > > the files to a large enough size before the tests to ensure that they
> > > wouldn't grow?
> > >
> > > Do you have the columns indexed? Are the inserts causing page splits?
> > >
> > >
> > > --
> > > Adam Machanic
> > > SQL Server MVP
> > > http://www.sqljunkies.com/weblog/amachanic
> > > --
> > >
> > >
> > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> > > news:E8610339-B39C-4A69-BD95-7EA882D0D228@.microsoft.com...
> > > > hello,
> > > >
> > > > i wrote a performance test for sequential inserts with ado.net on a P4
> > > 2GHz
> > > >
> > > > 512 Meg Ram machine
> > > >
> > > > and got the following scores:
> > > >
> > > > insert 10000 ints 1:30 mins
> > > > insert 10000 reals 1:20 mins
> > > >
> > > > inserting 10000 nvarchars
> > > > first 10000: 1:30
> > > > second 10000: 4:11
> > > > third 10000: 6:50
> > > > fourth 10000: 9:30
> > > > fifth 10000: 12:12
> > > > sixth 10000: 15:00
> > > > seventh 10000 18:20
> > > >
> > > > so the times gets worse and worse.
> > > > i would expect, that the convergate but they don't
> > > > Is this normal?
> > > >
> > > > If yes we will have problems, because we expect a couple of millions
> > > entries
> > > > in this
> > > > table where this strings are stored.
> > > >
> > > > all tables for the performance test have the same stucture and indices
> > > > except of
> > > > the datatype which is tested, which is
> > > > id
> > > > value
> > > >
> > > > Can you give me a hint how to speed this?
> > > >
> > > > thanks mike
> > >
> > >
> > >
>
>|||Can you post the table definitions, including constraints and indexes?
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
news:021A9342-82D0-48B5-8746-9F6E013AC032@.microsoft.com...
> hello adam,
> nope,
> i dropped the old database, created a new one with fixed size
> 600 meg (for data and tranlog).
> The behaviour is still the same.
> The insert times are growing endless.
> greetings mike
>
> "Adam Machanic" wrote:
> > You'll get more consistent results if you grow the file first...
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> > news:857ED961-21D6-4B62-B2DF-07B3FB24F3FA@.microsoft.com...
> > > Hello Adam,
> > >
> > > yes its auto-growing
> > > yes columns are indexed
> > >
> > > most inserts are NOT causing page splits
> > >
> > > thanks mike
> > >
> > >
> > >
> > > "Adam Machanic" wrote:
> > >
> > > > Do you have your databases auto-growing during these tests, or did
you
> > set
> > > > the files to a large enough size before the tests to ensure that
they
> > > > wouldn't grow?
> > > >
> > > > Do you have the columns indexed? Are the inserts causing page
splits?
> > > >
> > > >
> > > > --
> > > > Adam Machanic
> > > > SQL Server MVP
> > > > http://www.sqljunkies.com/weblog/amachanic
> > > > --
> > > >
> > > >
> > > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in
message
> > > > news:E8610339-B39C-4A69-BD95-7EA882D0D228@.microsoft.com...
> > > > > hello,
> > > > >
> > > > > i wrote a performance test for sequential inserts with ado.net on
a P4
> > > > 2GHz
> > > > >
> > > > > 512 Meg Ram machine
> > > > >
> > > > > and got the following scores:
> > > > >
> > > > > insert 10000 ints 1:30 mins
> > > > > insert 10000 reals 1:20 mins
> > > > >
> > > > > inserting 10000 nvarchars
> > > > > first 10000: 1:30
> > > > > second 10000: 4:11
> > > > > third 10000: 6:50
> > > > > fourth 10000: 9:30
> > > > > fifth 10000: 12:12
> > > > > sixth 10000: 15:00
> > > > > seventh 10000 18:20
> > > > >
> > > > > so the times gets worse and worse.
> > > > > i would expect, that the convergate but they don't
> > > > > Is this normal?
> > > > >
> > > > > If yes we will have problems, because we expect a couple of
millions
> > > > entries
> > > > > in this
> > > > > table where this strings are stored.
> > > > >
> > > > > all tables for the performance test have the same stucture and
indices
> > > > > except of
> > > > > the datatype which is tested, which is
> > > > > id
> > > > > value
> > > > >
> > > > > Can you give me a hint how to speed this?
> > > > >
> > > > > thanks mike
> > > >
> > > >
> > > >
> >
> >
> >|||Hello Adam,
here comes the table
create table LogStringTable
(
ID int identity (1,1) not null,
stringValue nvarchar(400) not null,
attributeTypeId int not null,
logItemId int not null
constraint FKATIhasStringValues
foreign key ( attributeTypeId )
references LogAttributeType (Id),
constraint FKItemHasStringvalues
foreign key (LogItemId )
references LogItem ( Id )
) on primary
there are four indices
primary key index on Id (clustered)
and an the other columns (not unique)
thank you mike
"Adam Machanic" wrote:
> Can you post the table definitions, including constraints and indexes?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> news:021A9342-82D0-48B5-8746-9F6E013AC032@.microsoft.com...
> > hello adam,
> >
> > nope,
> >
> > i dropped the old database, created a new one with fixed size
> > 600 meg (for data and tranlog).
> >
> > The behaviour is still the same.
> >
> > The insert times are growing endless.
> >
> > greetings mike
> >
> >
> >
> > "Adam Machanic" wrote:
> >
> > > You'll get more consistent results if you grow the file first...
> > >
> > >
> > > --
> > > Adam Machanic
> > > SQL Server MVP
> > > http://www.sqljunkies.com/weblog/amachanic
> > > --
> > >
> > >
> > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> > > news:857ED961-21D6-4B62-B2DF-07B3FB24F3FA@.microsoft.com...
> > > > Hello Adam,
> > > >
> > > > yes its auto-growing
> > > > yes columns are indexed
> > > >
> > > > most inserts are NOT causing page splits
> > > >
> > > > thanks mike
> > > >
> > > >
> > > >
> > > > "Adam Machanic" wrote:
> > > >
> > > > > Do you have your databases auto-growing during these tests, or did
> you
> > > set
> > > > > the files to a large enough size before the tests to ensure that
> they
> > > > > wouldn't grow?
> > > > >
> > > > > Do you have the columns indexed? Are the inserts causing page
> splits?
> > > > >
> > > > >
> > > > > --
> > > > > Adam Machanic
> > > > > SQL Server MVP
> > > > > http://www.sqljunkies.com/weblog/amachanic
> > > > > --
> > > > >
> > > > >
> > > > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in
> message
> > > > > news:E8610339-B39C-4A69-BD95-7EA882D0D228@.microsoft.com...
> > > > > > hello,
> > > > > >
> > > > > > i wrote a performance test for sequential inserts with ado.net on
> a P4
> > > > > 2GHz
> > > > > >
> > > > > > 512 Meg Ram machine
> > > > > >
> > > > > > and got the following scores:
> > > > > >
> > > > > > insert 10000 ints 1:30 mins
> > > > > > insert 10000 reals 1:20 mins
> > > > > >
> > > > > > inserting 10000 nvarchars
> > > > > > first 10000: 1:30
> > > > > > second 10000: 4:11
> > > > > > third 10000: 6:50
> > > > > > fourth 10000: 9:30
> > > > > > fifth 10000: 12:12
> > > > > > sixth 10000: 15:00
> > > > > > seventh 10000 18:20
> > > > > >
> > > > > > so the times gets worse and worse.
> > > > > > i would expect, that the convergate but they don't
> > > > > > Is this normal?
> > > > > >
> > > > > > If yes we will have problems, because we expect a couple of
> millions
> > > > > entries
> > > > > > in this
> > > > > > table where this strings are stored.
> > > > > >
> > > > > > all tables for the performance test have the same stucture and
> indices
> > > > > > except of
> > > > > > the datatype which is tested, which is
> > > > > > id
> > > > > > value
> > > > > >
> > > > > > Can you give me a hint how to speed this?
> > > > > >
> > > > > > thanks mike
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||Hello Adam,
I dropped all indices and tried again
-> nothing principaly changed.
The times are shorter, but they are still growing endless,
with each 10000 insert.
When I have 100.000 entries in that table than the performance is reduce to
about
15 inserts/second compared with 100 inserts/second when starting with a
blank table.
And the performance goes down and down.
Greets mike
"Adam Machanic" wrote:
> Can you post the table definitions, including constraints and indexes?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> news:021A9342-82D0-48B5-8746-9F6E013AC032@.microsoft.com...
> > hello adam,
> >
> > nope,
> >
> > i dropped the old database, created a new one with fixed size
> > 600 meg (for data and tranlog).
> >
> > The behaviour is still the same.
> >
> > The insert times are growing endless.
> >
> > greetings mike
> >
> >
> >
> > "Adam Machanic" wrote:
> >
> > > You'll get more consistent results if you grow the file first...
> > >
> > >
> > > --
> > > Adam Machanic
> > > SQL Server MVP
> > > http://www.sqljunkies.com/weblog/amachanic
> > > --
> > >
> > >
> > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> > > news:857ED961-21D6-4B62-B2DF-07B3FB24F3FA@.microsoft.com...
> > > > Hello Adam,
> > > >
> > > > yes its auto-growing
> > > > yes columns are indexed
> > > >
> > > > most inserts are NOT causing page splits
> > > >
> > > > thanks mike
> > > >
> > > >
> > > >
> > > > "Adam Machanic" wrote:
> > > >
> > > > > Do you have your databases auto-growing during these tests, or did
> you
> > > set
> > > > > the files to a large enough size before the tests to ensure that
> they
> > > > > wouldn't grow?
> > > > >
> > > > > Do you have the columns indexed? Are the inserts causing page
> splits?
> > > > >
> > > > >
> > > > > --
> > > > > Adam Machanic
> > > > > SQL Server MVP
> > > > > http://www.sqljunkies.com/weblog/amachanic
> > > > > --
> > > > >
> > > > >
> > > > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in
> message
> > > > > news:E8610339-B39C-4A69-BD95-7EA882D0D228@.microsoft.com...
> > > > > > hello,
> > > > > >
> > > > > > i wrote a performance test for sequential inserts with ado.net on
> a P4
> > > > > 2GHz
> > > > > >
> > > > > > 512 Meg Ram machine
> > > > > >
> > > > > > and got the following scores:
> > > > > >
> > > > > > insert 10000 ints 1:30 mins
> > > > > > insert 10000 reals 1:20 mins
> > > > > >
> > > > > > inserting 10000 nvarchars
> > > > > > first 10000: 1:30
> > > > > > second 10000: 4:11
> > > > > > third 10000: 6:50
> > > > > > fourth 10000: 9:30
> > > > > > fifth 10000: 12:12
> > > > > > sixth 10000: 15:00
> > > > > > seventh 10000 18:20
> > > > > >
> > > > > > so the times gets worse and worse.
> > > > > > i would expect, that the convergate but they don't
> > > > > > Is this normal?
> > > > > >
> > > > > > If yes we will have problems, because we expect a couple of
> millions
> > > > > entries
> > > > > > in this
> > > > > > table where this strings are stored.
> > > > > >
> > > > > > all tables for the performance test have the same stucture and
> indices
> > > > > > except of
> > > > > > the datatype which is tested, which is
> > > > > > id
> > > > > > value
> > > > > >
> > > > > > Can you give me a hint how to speed this?
> > > > > >
> > > > > > thanks mike
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||hello adam,
i know now, that it is not a problem if the sql server.
it has to do with ado.net.
currently i don't know what it is, but now I inserted 10.000 nvarchars with
the
query analyzer and it lasts about 5 seconds
regardless how many records are in the table.
so i have to look into the ado.net stuff.
thank you for your help
I'll let you know what is is, when i know it.
thank you very much
greets michael
"Adam Machanic" wrote:
> Can you post the table definitions, including constraints and indexes?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> news:021A9342-82D0-48B5-8746-9F6E013AC032@.microsoft.com...
> > hello adam,
> >
> > nope,
> >
> > i dropped the old database, created a new one with fixed size
> > 600 meg (for data and tranlog).
> >
> > The behaviour is still the same.
> >
> > The insert times are growing endless.
> >
> > greetings mike
> >
> >
> >
> > "Adam Machanic" wrote:
> >
> > > You'll get more consistent results if you grow the file first...
> > >
> > >
> > > --
> > > Adam Machanic
> > > SQL Server MVP
> > > http://www.sqljunkies.com/weblog/amachanic
> > > --
> > >
> > >
> > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> > > news:857ED961-21D6-4B62-B2DF-07B3FB24F3FA@.microsoft.com...
> > > > Hello Adam,
> > > >
> > > > yes its auto-growing
> > > > yes columns are indexed
> > > >
> > > > most inserts are NOT causing page splits
> > > >
> > > > thanks mike
> > > >
> > > >
> > > >
> > > > "Adam Machanic" wrote:
> > > >
> > > > > Do you have your databases auto-growing during these tests, or did
> you
> > > set
> > > > > the files to a large enough size before the tests to ensure that
> they
> > > > > wouldn't grow?
> > > > >
> > > > > Do you have the columns indexed? Are the inserts causing page
> splits?
> > > > >
> > > > >
> > > > > --
> > > > > Adam Machanic
> > > > > SQL Server MVP
> > > > > http://www.sqljunkies.com/weblog/amachanic
> > > > > --
> > > > >
> > > > >
> > > > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in
> message
> > > > > news:E8610339-B39C-4A69-BD95-7EA882D0D228@.microsoft.com...
> > > > > > hello,
> > > > > >
> > > > > > i wrote a performance test for sequential inserts with ado.net on
> a P4
> > > > > 2GHz
> > > > > >
> > > > > > 512 Meg Ram machine
> > > > > >
> > > > > > and got the following scores:
> > > > > >
> > > > > > insert 10000 ints 1:30 mins
> > > > > > insert 10000 reals 1:20 mins
> > > > > >
> > > > > > inserting 10000 nvarchars
> > > > > > first 10000: 1:30
> > > > > > second 10000: 4:11
> > > > > > third 10000: 6:50
> > > > > > fourth 10000: 9:30
> > > > > > fifth 10000: 12:12
> > > > > > sixth 10000: 15:00
> > > > > > seventh 10000 18:20
> > > > > >
> > > > > > so the times gets worse and worse.
> > > > > > i would expect, that the convergate but they don't
> > > > > > Is this normal?
> > > > > >
> > > > > > If yes we will have problems, because we expect a couple of
> millions
> > > > > entries
> > > > > > in this
> > > > > > table where this strings are stored.
> > > > > >
> > > > > > all tables for the performance test have the same stucture and
> indices
> > > > > > except of
> > > > > > the datatype which is tested, which is
> > > > > > id
> > > > > > value
> > > > > >
> > > > > > Can you give me a hint how to speed this?
> > > > > >
> > > > > > thanks mike
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||Hello Adam,
I got it.
There is an option at the data adapter called
refresh the dataset
This was set to true.
I set it to false and now my world is perpendicular again.
The insert times for 10.000 stings are now about 5-7 seconds.
Unfortunatly, I even didn't use a dataset, so this option is useless even
when set to true.
I think this is worthy a microsoft call.
thank you for your help.
mike.
"Adam Machanic" wrote:
> Can you post the table definitions, including constraints and indexes?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> news:021A9342-82D0-48B5-8746-9F6E013AC032@.microsoft.com...
> > hello adam,
> >
> > nope,
> >
> > i dropped the old database, created a new one with fixed size
> > 600 meg (for data and tranlog).
> >
> > The behaviour is still the same.
> >
> > The insert times are growing endless.
> >
> > greetings mike
> >
> >
> >
> > "Adam Machanic" wrote:
> >
> > > You'll get more consistent results if you grow the file first...
> > >
> > >
> > > --
> > > Adam Machanic
> > > SQL Server MVP
> > > http://www.sqljunkies.com/weblog/amachanic
> > > --
> > >
> > >
> > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> > > news:857ED961-21D6-4B62-B2DF-07B3FB24F3FA@.microsoft.com...
> > > > Hello Adam,
> > > >
> > > > yes its auto-growing
> > > > yes columns are indexed
> > > >
> > > > most inserts are NOT causing page splits
> > > >
> > > > thanks mike
> > > >
> > > >
> > > >
> > > > "Adam Machanic" wrote:
> > > >
> > > > > Do you have your databases auto-growing during these tests, or did
> you
> > > set
> > > > > the files to a large enough size before the tests to ensure that
> they
> > > > > wouldn't grow?
> > > > >
> > > > > Do you have the columns indexed? Are the inserts causing page
> splits?
> > > > >
> > > > >
> > > > > --
> > > > > Adam Machanic
> > > > > SQL Server MVP
> > > > > http://www.sqljunkies.com/weblog/amachanic
> > > > > --
> > > > >
> > > > >
> > > > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in
> message
> > > > > news:E8610339-B39C-4A69-BD95-7EA882D0D228@.microsoft.com...
> > > > > > hello,
> > > > > >
> > > > > > i wrote a performance test for sequential inserts with ado.net on
> a P4
> > > > > 2GHz
> > > > > >
> > > > > > 512 Meg Ram machine
> > > > > >
> > > > > > and got the following scores:
> > > > > >
> > > > > > insert 10000 ints 1:30 mins
> > > > > > insert 10000 reals 1:20 mins
> > > > > >
> > > > > > inserting 10000 nvarchars
> > > > > > first 10000: 1:30
> > > > > > second 10000: 4:11
> > > > > > third 10000: 6:50
> > > > > > fourth 10000: 9:30
> > > > > > fifth 10000: 12:12
> > > > > > sixth 10000: 15:00
> > > > > > seventh 10000 18:20
> > > > > >
> > > > > > so the times gets worse and worse.
> > > > > > i would expect, that the convergate but they don't
> > > > > > Is this normal?
> > > > > >
> > > > > > If yes we will have problems, because we expect a couple of
> millions
> > > > > entries
> > > > > > in this
> > > > > > table where this strings are stored.
> > > > > >
> > > > > > all tables for the performance test have the same stucture and
> indices
> > > > > > except of
> > > > > > the datatype which is tested, which is
> > > > > > id
> > > > > > value
> > > > > >
> > > > > > Can you give me a hint how to speed this?
> > > > > >
> > > > > > thanks mike
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||Thanks for all of the updates; this is good to know! Can you post an
abbreviated version of the code you were using? How does a dataset option
affect you when you're not using a dataset? And how did you turn it off
without using a dataset?
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
news:EBAADD96-9F27-4A71-AC21-B8487352237C@.microsoft.com...
> Hello Adam,
> I got it.
> There is an option at the data adapter called
> refresh the dataset
> This was set to true.
> I set it to false and now my world is perpendicular again.
> The insert times for 10.000 stings are now about 5-7 seconds.
> Unfortunatly, I even didn't use a dataset, so this option is useless even
> when set to true.
> I think this is worthy a microsoft call.
> thank you for your help.
> mike.
>
> "Adam Machanic" wrote:
> > Can you post the table definitions, including constraints and indexes?
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in message
> > news:021A9342-82D0-48B5-8746-9F6E013AC032@.microsoft.com...
> > > hello adam,
> > >
> > > nope,
> > >
> > > i dropped the old database, created a new one with fixed size
> > > 600 meg (for data and tranlog).
> > >
> > > The behaviour is still the same.
> > >
> > > The insert times are growing endless.
> > >
> > > greetings mike
> > >
> > >
> > >
> > > "Adam Machanic" wrote:
> > >
> > > > You'll get more consistent results if you grow the file first...
> > > >
> > > >
> > > > --
> > > > Adam Machanic
> > > > SQL Server MVP
> > > > http://www.sqljunkies.com/weblog/amachanic
> > > > --
> > > >
> > > >
> > > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in
message
> > > > news:857ED961-21D6-4B62-B2DF-07B3FB24F3FA@.microsoft.com...
> > > > > Hello Adam,
> > > > >
> > > > > yes its auto-growing
> > > > > yes columns are indexed
> > > > >
> > > > > most inserts are NOT causing page splits
> > > > >
> > > > > thanks mike
> > > > >
> > > > >
> > > > >
> > > > > "Adam Machanic" wrote:
> > > > >
> > > > > > Do you have your databases auto-growing during these tests, or
did
> > you
> > > > set
> > > > > > the files to a large enough size before the tests to ensure that
> > they
> > > > > > wouldn't grow?
> > > > > >
> > > > > > Do you have the columns indexed? Are the inserts causing page
> > splits?
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Adam Machanic
> > > > > > SQL Server MVP
> > > > > > http://www.sqljunkies.com/weblog/amachanic
> > > > > > --
> > > > > >
> > > > > >
> > > > > > "Michael Zdarsky" <zdarsky@.zac-it.com.(nospamplease)> wrote in
> > message
> > > > > > news:E8610339-B39C-4A69-BD95-7EA882D0D228@.microsoft.com...
> > > > > > > hello,
> > > > > > >
> > > > > > > i wrote a performance test for sequential inserts with ado.net
on
> > a P4
> > > > > > 2GHz
> > > > > > >
> > > > > > > 512 Meg Ram machine
> > > > > > >
> > > > > > > and got the following scores:
> > > > > > >
> > > > > > > insert 10000 ints 1:30 mins
> > > > > > > insert 10000 reals 1:20 mins
> > > > > > >
> > > > > > > inserting 10000 nvarchars
> > > > > > > first 10000: 1:30
> > > > > > > second 10000: 4:11
> > > > > > > third 10000: 6:50
> > > > > > > fourth 10000: 9:30
> > > > > > > fifth 10000: 12:12
> > > > > > > sixth 10000: 15:00
> > > > > > > seventh 10000 18:20
> > > > > > >
> > > > > > > so the times gets worse and worse.
> > > > > > > i would expect, that the convergate but they don't
> > > > > > > Is this normal?
> > > > > > >
> > > > > > > If yes we will have problems, because we expect a couple of
> > millions
> > > > > > entries
> > > > > > > in this
> > > > > > > table where this strings are stored.
> > > > > > >
> > > > > > > all tables for the performance test have the same stucture and
> > indices
> > > > > > > except of
> > > > > > > the datatype which is tested, which is
> > > > > > > id
> > > > > > > value
> > > > > > >
> > > > > > > Can you give me a hint how to speed this?
> > > > > > >
> > > > > > > thanks mike
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Insert Performance Degradation

I am working with SQL Server 7 on win2k server. There was a vb6 ado ODBC
application written accessing this SQL Server 7 db. Unfortunately, through
a
series of workstation upgrades, the source code was lost.
Recently, the performance of table inserts degraded significantly. I am
trying figure out as to what is causing it.
I am guessing there is some sort of locking problem going on.
Is there a utility which allows you to trace ODBC activity?
If anyone knows of any other utility or method to diagnose the problem, I
would greatly appreciate help.
Thanks in advanceUse Profiler to trace the statements sent to sql server. Start by using the
duration template and filter for duration greater than, let us say, 1000
milliseconds or whatever amount you decide. See BOL for more information
about "Profiler".
AMB
"RG" wrote:

> I am working with SQL Server 7 on win2k server. There was a vb6 ado ODBC
> application written accessing this SQL Server 7 db. Unfortunately, throug
h a
> series of workstation upgrades, the source code was lost.
> Recently, the performance of table inserts degraded significantly. I am
> trying figure out as to what is causing it.
> I am guessing there is some sort of locking problem going on.
> Is there a utility which allows you to trace ODBC activity?
> If anyone knows of any other utility or method to diagnose the problem, I
> would greatly appreciate help.
> Thanks in advance|||Thanks for your help.
It appears the following statement is a problem
sp_cursorfetch 422977628, 2, 0, 1.
How can I find the query behind this cursor?
Thanks
"Alejandro Mesa" wrote:
> Use Profiler to trace the statements sent to sql server. Start by using th
e
> duration template and filter for duration greater than, let us say, 1000
> milliseconds or whatever amount you decide. See BOL for more information
> about "Profiler".
>
> AMB
> "RG" wrote:
>|||See if this helps:
Server Side Cursors and ADO Cursor Types
http://www.sqlteam.com/item.asp?ItemID=11842
AMB
"RG" wrote:
> Thanks for your help.
> It appears the following statement is a problem
> sp_cursorfetch 422977628, 2, 0, 1.
> How can I find the query behind this cursor?
> Thanks
> "Alejandro Mesa" wrote:
>|||It looks like this is it. It also appears that if you don't have the source
,
there is not much you could do on the database side to improve this
performance.
"Alejandro Mesa" wrote:
> See if this helps:
> Server Side Cursors and ADO Cursor Types
> http://www.sqlteam.com/item.asp?ItemID=11842
>
> AMB
> "RG" wrote:
>

Insert Performance Degradation

I am working with SQL Server 7 on win2k server. There was a vb6 ado ODBC
application written accessing this SQL Server 7 db. Unfortunately, through a
series of workstation upgrades, the source code was lost.
Recently, the performance of table inserts degraded significantly. I am
trying figure out as to what is causing it.
I am guessing there is some sort of locking problem going on.
Is there a utility which allows you to trace ODBC activity?
If anyone knows of any other utility or method to diagnose the problem, I
would greatly appreciate help.
Thanks in advance
my 1st guess would be index fragmentation.
1. do your tables have a clustered index (They should have in most cases)
2. do your indexes (if present) have a fill factor of Less Than 100% and
Greater than 0% (they should if you are inserting and updating a lot AND if
the clustered index is NOT an identity or some other monotomically
incrementing value)
3. are your indexes (if Present) fragmented ?
(Check by using DBCC ShowContig (TableName)
Then look at the "Scan Density" Number. It should be >=80% or
performance will start to degrade
Cheers,
Greg Jackson
PDX, Oregon
|||Thanks for your help.
It appears the following statement is a problem
sp_cursorfetch 422977628, 2, 0, 1.
It takes over a second to execute it.
How can I find the query behind this cursor?
Thanks
"pdxJaxon" wrote:

> my 1st guess would be index fragmentation.
> 1. do your tables have a clustered index (They should have in most cases)
> 2. do your indexes (if present) have a fill factor of Less Than 100% and
> Greater than 0% (they should if you are inserting and updating a lot AND if
> the clustered index is NOT an identity or some other monotomically
> incrementing value)
> 3. are your indexes (if Present) fragmented ?
> (Check by using DBCC ShowContig (TableName)
> Then look at the "Scan Density" Number. It should be >=80% or
> performance will start to degrade
>
>
> Cheers,
> Greg Jackson
> PDX, Oregon
>
>
>

Insert Performance Degradation

I am working with SQL Server 7 on win2k server. There was a vb6 ado ODBC
application written accessing this SQL Server 7 db. Unfortunately, through
a
series of workstation upgrades, the source code was lost.
Recently, the performance of table inserts degraded significantly. I am
trying figure out as to what is causing it.
I am guessing there is some sort of locking problem going on.
Is there a utility which allows you to trace ODBC activity?
If anyone knows of any other utility or method to diagnose the problem, I
would greatly appreciate help.
Thanks in advancemy 1st guess would be index fragmentation.
1. do your tables have a clustered index (They should have in most cases)
2. do your indexes (if present) have a fill factor of Less Than 100% and
Greater than 0% (they should if you are inserting and updating a lot AND if
the clustered index is NOT an identity or some other monotomically
incrementing value)
3. are your indexes (if Present) fragmented ?
(Check by using DBCC ShowContig (TableName)
Then look at the "Scan Density" Number. It should be >=80% or
performance will start to degrade
Cheers,
Greg Jackson
PDX, Oregon|||Thanks for your help.
It appears the following statement is a problem
sp_cursorfetch 422977628, 2, 0, 1.
It takes over a second to execute it.
How can I find the query behind this cursor?
Thanks
"pdxJaxon" wrote:

> my 1st guess would be index fragmentation.
> 1. do your tables have a clustered index (They should have in most cases)
> 2. do your indexes (if present) have a fill factor of Less Than 100% and
> Greater than 0% (they should if you are inserting and updating a lot AND i
f
> the clustered index is NOT an identity or some other monotomically
> incrementing value)
> 3. are your indexes (if Present) fragmented ?
> (Check by using DBCC ShowContig (TableName)
> Then look at the "Scan Density" Number. It should be >=80% or
> performance will start to degrade
>
>
> Cheers,
> Greg Jackson
> PDX, Oregon
>
>
>

Insert Performance Degradation

I am working with SQL Server 7 on win2k server. There was a vb6 ado ODBC
application written accessing this SQL Server 7 db. Unfortunately, through a
series of workstation upgrades, the source code was lost.
Recently, the performance of table inserts degraded significantly. I am
trying figure out as to what is causing it.
I am guessing there is some sort of locking problem going on.
Is there a utility which allows you to trace ODBC activity?
If anyone knows of any other utility or method to diagnose the problem, I
would greatly appreciate help.
Thanks in advancemy 1st guess would be index fragmentation.
1. do your tables have a clustered index (They should have in most cases)
2. do your indexes (if present) have a fill factor of Less Than 100% and
Greater than 0% (they should if you are inserting and updating a lot AND if
the clustered index is NOT an identity or some other monotomically
incrementing value)
3. are your indexes (if Present) fragmented ?
(Check by using DBCC ShowContig (TableName)
Then look at the "Scan Density" Number. It should be >=80% or
performance will start to degrade
Cheers,
Greg Jackson
PDX, Oregon|||Thanks for your help.
It appears the following statement is a problem
sp_cursorfetch 422977628, 2, 0, 1.
It takes over a second to execute it.
How can I find the query behind this cursor?
Thanks
"pdxJaxon" wrote:
> my 1st guess would be index fragmentation.
> 1. do your tables have a clustered index (They should have in most cases)
> 2. do your indexes (if present) have a fill factor of Less Than 100% and
> Greater than 0% (they should if you are inserting and updating a lot AND if
> the clustered index is NOT an identity or some other monotomically
> incrementing value)
> 3. are your indexes (if Present) fragmented ?
> (Check by using DBCC ShowContig (TableName)
> Then look at the "Scan Density" Number. It should be >=80% or
> performance will start to degrade
>
>
> Cheers,
> Greg Jackson
> PDX, Oregon
>
>
>

Monday, March 19, 2012

Insert syntax error

Monday, March 12, 2012

INSERT statement conflicted with COLUMN CHECK constraint.

Friday, February 24, 2012

Insert produces error

Insert performance/nvarchar

Insert performance/nvarchar

Insert performance/nvarchar

Insert Performance Degradation

Insert Performance Degradation

Insert Performance Degradation

Insert Performance Degradation

Insert Performance Degradation

Insert Performance Degradation

insert or replace

Blog Archive

About Me