insert, delete, update data in database

hi. i'm trying to create a c# application which would insert, update and delete data from a database. could anyone pls point me to the right direction in which i should take? thanks in advance.

You are talking about a Compact Framework application that leverages SQL Mobile for on-device data persistence. To get started with these technologies, start here: http://msdn.microsoft.com/mobility/gettingstarted/default.aspx

You can also download the IBuySpyStore sample application from GotDotNet, which is a complete example of a CF2 application working with SQL Mobile.

Darren

Insert with Index VS Insert without Index

Hi all,
Environment: MSSQL 2000 SP3 on Windows 2000.
In our application, there is a table with 600+ columns and
50+ indexes. As a process we truncate the table and insert
records using INSERT into .. SELECT. While we do a huge
insert(1Million records), we tried two approaches.
A.)Creating indexes on this empty table and then inserted
1M records.
B.)Also we tried inserting 1M records without indexes and
created indexes later.
Approach A took less time than Approach B. The time
difference is more than one hour.
While all RDBMS suggests to drop indexes before doing a
big insert, I would like to know how MSSQL 2000 manages to
do the inserts with indexes with in a resonable time.
Regards,
JP Job
This sounds abnormal. Usually SQL Server recommends dropping index then
recreate, too. I assume there was no concurrent activity in the
server/machine during the runs. I need to get more information in order to
diagnose this further. Can you provide the following information:
1. The plan used in the insert with index case
2. Machine info including # CPU, CPU speed, CPU usage during the two
approaches, physical memory size
3. Is there any ordering of the data inserted? Since the data was selected
from anothet table, did it happen to be sorted on some column? If so, did
that sort order match any index key order?
Thanks.
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"JPJOB" <anonymous@.discussions.microsoft.com> wrote in message
news:11b2201c4420b$a6d6b960$a001280a@.phx.gbl...
> Hi all,
> Environment: MSSQL 2000 SP3 on Windows 2000.
> In our application, there is a table with 600+ columns and
> 50+ indexes. As a process we truncate the table and insert
> records using INSERT into .. SELECT. While we do a huge
> insert(1Million records), we tried two approaches.
> A.)Creating indexes on this empty table and then inserted
> 1M records.
> B.)Also we tried inserting 1M records without indexes and
> created indexes later.
> Approach A took less time than Approach B. The time
> difference is more than one hour.
> While all RDBMS suggests to drop indexes before doing a
> big insert, I would like to know how MSSQL 2000 manages to
> do the inserts with indexes with in a resonable time.
>
> Regards,
> JP Job
>

Insert with Index VS Insert without Index

Hi all,
Environment: MSSQL 2000 SP3 on Windows 2000.
In our application, there is a table with 600+ columns and
50+ indexes. As a process we truncate the table and insert
records using INSERT into .. SELECT. While we do a huge
insert(1Million records), we tried two approaches.
A.)Creating indexes on this empty table and then inserted
1M records.
B.)Also we tried inserting 1M records without indexes and
created indexes later.
Approach A took less time than Approach B. The time
difference is more than one hour.
While all RDBMS suggests to drop indexes before doing a
big insert, I would like to know how MSSQL 2000 manages to
do the inserts with indexes with in a resonable time.
Regards,
JP JobThis sounds abnormal. Usually SQL Server recommends dropping index then
recreate, too. I assume there was no concurrent activity in the
server/machine during the runs. I need to get more information in order to
diagnose this further. Can you provide the following information:
1. The plan used in the insert with index case
2. Machine info including # CPU, CPU speed, CPU usage during the two
approaches, physical memory size
3. Is there any ordering of the data inserted? Since the data was selected
from anothet table, did it happen to be sorted on some column? If so, did
that sort order match any index key order?
Thanks.
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"JPJOB" <anonymous@.discussions.microsoft.com> wrote in message
news:11b2201c4420b$a6d6b960$a001280a@.phx.gbl...
> Hi all,
> Environment: MSSQL 2000 SP3 on Windows 2000.
> In our application, there is a table with 600+ columns and
> 50+ indexes. As a process we truncate the table and insert
> records using INSERT into .. SELECT. While we do a huge
> insert(1Million records), we tried two approaches.
> A.)Creating indexes on this empty table and then inserted
> 1M records.
> B.)Also we tried inserting 1M records without indexes and
> created indexes later.
> Approach A took less time than Approach B. The time
> difference is more than one hour.
> While all RDBMS suggests to drop indexes before doing a
> big insert, I would like to know how MSSQL 2000 manages to
> do the inserts with indexes with in a resonable time.
>
> Regards,
> JP Job
>sql

Insert with Index VS Insert without Index

Hi all,
Environment: MSSQL 2000 SP3 on Windows 2000.
In our application, there is a table with 600+ columns and
50+ indexes. As a process we truncate the table and insert
records using INSERT into .. SELECT. While we do a huge
insert(1Million records), we tried two approaches.
A.)Creating indexes on this empty table and then inserted
1M records.
B.)Also we tried inserting 1M records without indexes and
created indexes later.
Approach A took less time than Approach B. The time
difference is more than one hour.
While all RDBMS suggests to drop indexes before doing a
big insert, I would like to know how MSSQL 2000 manages to
do the inserts with indexes with in a resonable time.
Regards,
JP JobThis sounds abnormal. Usually SQL Server recommends dropping index then
recreate, too. I assume there was no concurrent activity in the
server/machine during the runs. I need to get more information in order to
diagnose this further. Can you provide the following information:
1. The plan used in the insert with index case
2. Machine info including # CPU, CPU speed, CPU usage during the two
approaches, physical memory size
3. Is there any ordering of the data inserted? Since the data was selected
from anothet table, did it happen to be sorted on some column? If so, did
that sort order match any index key order?
Thanks.
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"JPJOB" <anonymous@.discussions.microsoft.com> wrote in message
news:11b2201c4420b$a6d6b960$a001280a@.phx
.gbl...
> Hi all,
> Environment: MSSQL 2000 SP3 on Windows 2000.
> In our application, there is a table with 600+ columns and
> 50+ indexes. As a process we truncate the table and insert
> records using INSERT into .. SELECT. While we do a huge
> insert(1Million records), we tried two approaches.
> A.)Creating indexes on this empty table and then inserted
> 1M records.
> B.)Also we tried inserting 1M records without indexes and
> created indexes later.
> Approach A took less time than Approach B. The time
> difference is more than one hour.
> While all RDBMS suggests to drop indexes before doing a
> big insert, I would like to know how MSSQL 2000 manages to
> do the inserts with indexes with in a resonable time.
>
> Regards,
> JP Job
>

Insert trigger calls .NET application

I have an appication that feeds a SQL Server 2005 database with records. I
have another application that should treat the records inserted by this firs
t
application. I know you can achieve this by incorporating .NET code in SQL
Server 2005.
However is there another possibility that SQL Server tiggers my second
application after inserting records from the first application?
thanks.Hello Guy,

> I have an appication that feeds a SQL Server 2005 database with
> records. I
> have another application that should treat the records inserted by
> this first
> application. I know you can achieve this by incorporating .NET code in
> SQL
> Server 2005.
> However is there another possibility that SQL Server tiggers my second
> application after inserting records from the first application?
There's a couple of ways of doing that:
a. Use service broker to do the inserts and send a copy of the data to a
queue that your second program processes
b. Use a SQLDependency in your second application to watch the table in ques
tion,
get the new records and process them.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/sql

insert then update...

greetings
I am developing an application for the marketing dept at my company.Basically users can build the content of an email to be sent to oursubscriber database.
I am wanting the application to initailly save the content into a database, the update the most recently inserted row.
The save button uses the following SQL command:
Dim SqlMethod As String ="INSERT INTO CZC_email (Offer, SendDate, Destinations, Copy, BannerURL)VALUES ('" & txtCampaignName.Text & "','" &calCampaignDate.SelectedDate.ToString("yy/dd/MM") & "','" &DestinationsSelected & "','" & FreeTextBox2.Text & "', '"& txtBannerPath.Text & "')SELECT @.@.IDENTITY AS 'CZ_ID'"
And my update button has this SQL command:
Dim SqlMethod As String ="UPDATE CZC_email SET SendDate = '" &calCampaignDate.SelectedDate.ToString("yy/dd/MM") & "', Offer = '"& txtCampaignName.Text & "',Destinations = '" &DestinationsSelected & "', BannerURL = '" & txtBannerPath.Text& "' WHERE CZ_ID = @.@.IDENTITY "
but it doesnt seem to be updating. anyone know what I'm doing wrong?
Cheers

(1) Use a stored proc.
(2) Use SCOPE_IDENTITY() instead of @.@.IDENTIY. Check books on line for the differences.
(3) Use Parameterized Queries to prevent SQL Injection atatcks (google for more info on this).|||Save the @.@.IDENTITY values you get from insert command & then in the update command pass the value returned from the insertion instead of @.@.IDENTITY|||I've tried saving the @.@.identity and scope_identity as a value ofvariable varCZ_ID by using the following code (i'm using the MS DAAB)
varCZ_ID = dataReader("SCOPE_IDENTITY")
or

varCZ_ID = dataReader("CZ_ID")

however, this is erroring 'Invalid attempt to read when no data is present.'
Any ideas what I'm doing wrong? this is really doing my head in!
Thanks
|||If you use a stored proc you could save a trip to the server and get back accurate Id.
|||yes I agree, I just wanted to get it working first off.
I managed to fix the problem by saving the @.@.IDENTITY into a session variable.

|||@.@.IDENTITY does not always give you the identity value that just gotgenerated by the insert statement. If multiple calls were made at thesame time it could mix up the Id's. So it is advised to useSCOPE_DENTITY() instead of @.@.IDENTITY.
|||Thats a good point, and something I am aware off.
For this particular application is not a big deal, as it only going to be used by two people - and not at the sametime.
However i will look to improve it soon, and that will be things I will do.
Cheers

Insert text field problem

We have a database consisting of one table that is used to store application
reports. The reports are stored in a text field in the table. The size of the
database is around 17 Gig and is set to auto-grow. The logical drive(part of
a SAN) has 25 Gig free. Last week, we has trouble inserting rows into the
table. We were able to insert reports that were 5K in size, but unable to
insert reports that were 6K. We deleted some of the reports from the table
and now things seem to be fine.
What can cause this?Gary,
Have you recently ran a DBCC CHECKDB on the database containing the table or
a DBCC CHECKTABLE on the individual table itself?
As a side note, you might check out Reporting Services for the future of
report generation.
Reporting Services Home
http://www.microsoft.com/sql/reporting/default.mspx
HTH
Jerry
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:36087812-0920-45AC-955D-E283549391AE@.microsoft.com...
> We have a database consisting of one table that is used to store
> application
> reports. The reports are stored in a text field in the table. The size of
> the
> database is around 17 Gig and is set to auto-grow. The logical drive(part
> of
> a SAN) has 25 Gig free. Last week, we has trouble inserting rows into the
> table. We were able to insert reports that were 5K in size, but unable to
> insert reports that were 6K. We deleted some of the reports from the table
> and now things seem to be fine.
> What can cause this?|||We did run DBCC CHECKDB and everything looked ok. Did not run DBCC
CHECKTABLE. We are moving toward Reporting Services. What should I look for
in the DBCC CHECKTABLE?
"Jerry Spivey" wrote:
> Gary,
> Have you recently ran a DBCC CHECKDB on the database containing the table or
> a DBCC CHECKTABLE on the individual table itself?
> As a side note, you might check out Reporting Services for the future of
> report generation.
> Reporting Services Home
> http://www.microsoft.com/sql/reporting/default.mspx
> HTH
> Jerry
> "Gary" <Gary@.discussions.microsoft.com> wrote in message
> news:36087812-0920-45AC-955D-E283549391AE@.microsoft.com...
> > We have a database consisting of one table that is used to store
> > application
> > reports. The reports are stored in a text field in the table. The size of
> > the
> > database is around 17 Gig and is set to auto-grow. The logical drive(part
> > of
> > a SAN) has 25 Gig free. Last week, we has trouble inserting rows into the
> > table. We were able to insert reports that were 5K in size, but unable to
> > insert reports that were 6K. We deleted some of the reports from the table
> > and now things seem to be fine.
> > What can cause this?
>
>|||Gary,
CHECKDB is a superset of CHECKTABLE so if you're already run CHECKDB there
is no need to run CHECKTABLE.
HTH
Jerry
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:1B00B83D-AC11-46D4-831F-E6A7BC5DEA5F@.microsoft.com...
> We did run DBCC CHECKDB and everything looked ok. Did not run DBCC
> CHECKTABLE. We are moving toward Reporting Services. What should I look
> for
> in the DBCC CHECKTABLE?
> "Jerry Spivey" wrote:
>> Gary,
>> Have you recently ran a DBCC CHECKDB on the database containing the table
>> or
>> a DBCC CHECKTABLE on the individual table itself?
>> As a side note, you might check out Reporting Services for the future of
>> report generation.
>> Reporting Services Home
>> http://www.microsoft.com/sql/reporting/default.mspx
>> HTH
>> Jerry
>> "Gary" <Gary@.discussions.microsoft.com> wrote in message
>> news:36087812-0920-45AC-955D-E283549391AE@.microsoft.com...
>> > We have a database consisting of one table that is used to store
>> > application
>> > reports. The reports are stored in a text field in the table. The size
>> > of
>> > the
>> > database is around 17 Gig and is set to auto-grow. The logical
>> > drive(part
>> > of
>> > a SAN) has 25 Gig free. Last week, we has trouble inserting rows into
>> > the
>> > table. We were able to insert reports that were 5K in size, but unable
>> > to
>> > insert reports that were 6K. We deleted some of the reports from the
>> > table
>> > and now things seem to be fine.
>> > What can cause this?
>>

Insert statment and strange locking behavior

Hi,
We were encountering table locks in the application that is hindering the
concurrency and we found that the insert into table was causing the table
lock and I can easily reproduce this from query analyzer. The statement look
s
like following:
Insert into db1..tab1 select * from db2..tab2
Table tab1 contains about 12M rows and tab2 contains 5000 rows.
Following are the stats and observations:
There is only 1 index on tab1 that is unique clustered and is on sequence
generated columns so we don’t expect lot of page splits since all the new
data will go at the end.
There is no memory pressure on SQL Server since this is the only user
connected.
If we insert only 2999 rows then SQL Sever takes row locks and IX locks on
page and table but when we try to insert >3000 rows SQL Server escalate the
row lock to table X lock so somehow there is some SQL Server algorithm that
causes it to escalate it to table lock
In SQL Server 2005, we are not able to reproduce the issue and it only takes
row locks even for 5000 rows so it seems like the issue is fixed in new
release.
Also >3000 rows insert is not true for all table since on some table SQL
Server escalate to table locks for >2000 rows so there is some other interna
l
SQL Server algorithm.
We are just wondering if someone else sees the same kind of issue and what
the solution was.
This is from Books online
“when a transaction requests rows from a table, SQL Server automatically
acquires locks on those rows affected and places higher-level intent locks o
n
the pages and table, or index, which contain those rows. When the number of
locks held by the transaction exceeds its threshold, SQL Server attempts to
change the intent lock on the table to a stronger lock (for example, an
intent exclusive (IX) would change to an exclusive (X) lock). After acquirin
g
the stronger lock, all page and row level locks held by the transaction on
the table are released, reducing lock overhead”
It is very difficult to believe that threshold is just 5000 row locks for
this type of insert since we had seen SQL Server taking millions of row lock
s
and works fine
Thanks
--HarvinderYou do not say whether you are explicitly defining a locking method on your
query/update
NOLOCK or ROWLOCK
"harvinder" wrote:

> Hi,
> We were encountering table locks in the application that is hindering the
> concurrency and we found that the insert into table was causing the table
> lock and I can easily reproduce this from query analyzer. The statement lo
oks
> like following:
> Insert into db1..tab1 select * from db2..tab2
> Table tab1 contains about 12M rows and tab2 contains 5000 rows.

Insert statment and strange locking behavior

Hi,
We were encountering table locks in the application that is hindering the
concurrency and we found that the insert into table was causing the table
lock and I can easily reproduce this from query analyzer. The statement looks
like following:
Insert into db1..tab1 select * from db2..tab2
Table tab1 contains about 12M rows and tab2 contains 5000 rows.
Following are the stats and observations:
There is only 1 index on tab1 that is unique clustered and is on sequence
generated columns so we donâ't expect lot of page splits since all the new
data will go at the end.
There is no memory pressure on SQL Server since this is the only user
connected.
If we insert only 2999 rows then SQL Sever takes row locks and IX locks on
page and table but when we try to insert >3000 rows SQL Server escalate the
row lock to table X lock so somehow there is some SQL Server algorithm that
causes it to escalate it to table lock
In SQL Server 2005, we are not able to reproduce the issue and it only takes
row locks even for 5000 rows so it seems like the issue is fixed in new
release.
Also >3000 rows insert is not true for all table since on some table SQL
Server escalate to table locks for >2000 rows so there is some other internal
SQL Server algorithm.
We are just wondering if someone else sees the same kind of issue and what
the solution was.
This is from Books online
â'when a transaction requests rows from a table, SQL Server automatically
acquires locks on those rows affected and places higher-level intent locks on
the pages and table, or index, which contain those rows. When the number of
locks held by the transaction exceeds its threshold, SQL Server attempts to
change the intent lock on the table to a stronger lock (for example, an
intent exclusive (IX) would change to an exclusive (X) lock). After acquiring
the stronger lock, all page and row level locks held by the transaction on
the table are released, reducing lock overheadâ'
It is very difficult to believe that threshold is just 5000 row locks for
this type of insert since we had seen SQL Server taking millions of row locks
and works fine
Thanks
--HarvinderYou do not say whether you are explicitly defining a locking method on your
query/update
NOLOCK or ROWLOCK
"harvinder" wrote:
> Hi,
> We were encountering table locks in the application that is hindering the
> concurrency and we found that the insert into table was causing the table
> lock and I can easily reproduce this from query analyzer. The statement looks
> like following:
> Insert into db1..tab1 select * from db2..tab2
> Table tab1 contains about 12M rows and tab2 contains 5000 rows.

insert special characters in database

I am writing a web application and everytime I attempt to insert some special characters in the database I get errors. Is there a way around this?Depends what you mean. If you mean unicode and you are attempting to insert into CHar or VarChar fields then you need to use NVarChar or NChar. BoL has all the gory details. Otherwise you'll need to porovide more details.

HTH|||it seems everytime I include an apostrophe the system gives me an error..not sure why this is happening

my second question is-- when someone types this:

I cant wait
to leave
this place

--It come out like this
I cant wait to leave this place

How can I make the system print all the tab keys and return carriage?

thank you for any responses|||You need to escape apostrophes.

"I can't wait to leave this place"
must be submitted as
"I can''t wait to leave this place"

HTH|||The latter problem is probably a presentation issue - your FE needs to interpret the carriage returns & line feeds.|||hi everybody,

This is Rajen, hope it will be nice sharing ideas with u all. Feeling nice to join u all .:beer:

insert slow operation

hi ,
I have large no. of insert operations in my application .
Can I disable indexes somehow & make it fast . Or Can anyone suggest what
all best possiblities for the same .
Thanks
ARR
Aju
You cannot disable indexes. You can only drop them and re-create after
inserting.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:u0PZcC3OFHA.1396@.TK2MSFTNGP10.phx.gbl...
> hi ,
> I have large no. of insert operations in my application .
> Can I disable indexes somehow & make it fast . Or Can anyone suggest what
> all best possiblities for the same .
>
> Thanks
> ARR
>
|||Hi,
Drop the indexes apart from Clustered index and do the insert. You can
recreate the non clusterd index after the insert. This will speed
up the process. If you have some auditing triggers you can disable them
either.
Thanks
Hari
SQL Server MVP
"Aju" <ajuonline@.yahoo.com> wrote in message
news:u0PZcC3OFHA.1396@.TK2MSFTNGP10.phx.gbl...
> hi ,
> I have large no. of insert operations in my application .
> Can I disable indexes somehow & make it fast . Or Can anyone suggest what
> all best possiblities for the same .
>
> Thanks
> ARR
>
|||For high-volume inserts, consider using a bulk load method such as BULK
INSERT, BCP or DTS. These are much faster than standard Transact-SQL
INSERTs. You can also speed things up by batching multiple INSERT
statements in a single transaction.
Hope this helps.
Dan Guzman
SQL Server MVP
"Aju" <ajuonline@.yahoo.com> wrote in message
news:u0PZcC3OFHA.1396@.TK2MSFTNGP10.phx.gbl...
> hi ,
> I have large no. of insert operations in my application .
> Can I disable indexes somehow & make it fast . Or Can anyone suggest what
> all best possiblities for the same .
>
> Thanks
> ARR
>

insert slow operation

hi ,
I have large no. of insert operations in my application .
Can I disable indexes somehow & make it fast . Or Can anyone suggest what
all best possiblities for the same .
Thanks
ARRAju
You cannot disable indexes. You can only drop them and re-create after
inserting.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:u0PZcC3OFHA.1396@.TK2MSFTNGP10.phx.gbl...
> hi ,
> I have large no. of insert operations in my application .
> Can I disable indexes somehow & make it fast . Or Can anyone suggest what
> all best possiblities for the same .
>
> Thanks
> ARR
>|||Hi,
Drop the indexes apart from Clustered index and do the insert. You can
recreate the non clusterd index after the insert. This will speed
up the process. If you have some auditing triggers you can disable them
either.
Thanks
Hari
SQL Server MVP
"Aju" <ajuonline@.yahoo.com> wrote in message
news:u0PZcC3OFHA.1396@.TK2MSFTNGP10.phx.gbl...
> hi ,
> I have large no. of insert operations in my application .
> Can I disable indexes somehow & make it fast . Or Can anyone suggest what
> all best possiblities for the same .
>
> Thanks
> ARR
>|||For high-volume inserts, consider using a bulk load method such as BULK
INSERT, BCP or DTS. These are much faster than standard Transact-SQL
INSERTs. You can also speed things up by batching multiple INSERT
statements in a single transaction.
Hope this helps.
Dan Guzman
SQL Server MVP
"Aju" <ajuonline@.yahoo.com> wrote in message
news:u0PZcC3OFHA.1396@.TK2MSFTNGP10.phx.gbl...
> hi ,
> I have large no. of insert operations in my application .
> Can I disable indexes somehow & make it fast . Or Can anyone suggest what
> all best possiblities for the same .
>
> Thanks
> ARR
>

insert slow operation

hi ,
I have large no. of insert operations in my application .
Can I disable indexes somehow & make it fast . Or Can anyone suggest what
all best possiblities for the same .
Thanks
ARRAju
You cannot disable indexes. You can only drop them and re-create after
inserting.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:u0PZcC3OFHA.1396@.TK2MSFTNGP10.phx.gbl...
> hi ,
> I have large no. of insert operations in my application .
> Can I disable indexes somehow & make it fast . Or Can anyone suggest what
> all best possiblities for the same .
>
> Thanks
> ARR
>|||Hi,
Drop the indexes apart from Clustered index and do the insert. You can
recreate the non clusterd index after the insert. This will speed
up the process. If you have some auditing triggers you can disable them
either.
Thanks
Hari
SQL Server MVP
"Aju" <ajuonline@.yahoo.com> wrote in message
news:u0PZcC3OFHA.1396@.TK2MSFTNGP10.phx.gbl...
> hi ,
> I have large no. of insert operations in my application .
> Can I disable indexes somehow & make it fast . Or Can anyone suggest what
> all best possiblities for the same .
>
> Thanks
> ARR
>|||For high-volume inserts, consider using a bulk load method such as BULK
INSERT, BCP or DTS. These are much faster than standard Transact-SQL
INSERTs. You can also speed things up by batching multiple INSERT
statements in a single transaction.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Aju" <ajuonline@.yahoo.com> wrote in message
news:u0PZcC3OFHA.1396@.TK2MSFTNGP10.phx.gbl...
> hi ,
> I have large no. of insert operations in my application .
> Can I disable indexes somehow & make it fast . Or Can anyone suggest what
> all best possiblities for the same .
>
> Thanks
> ARR
>

insert query ?

my application will add and delete and update records in db

my problem is when to insert

I have one text box and one dropdownbox one to write the name of db and the dropdownbox to choose the holding server ..

this is the structure of each table >>

servers_tbl : SRV_ID,Server_Name

DB_tbl : DB_ID,DB_Name

srvdb_tbl : DB_ID,SRV_ID(forign keys from the previous tables)

so >>>

I want to add a new db to a server

so I am writing the new db name in the textbox and choose the server from the dropdownbox and press a button to add the db name in the DB_tbl.DB_Name and add the db id in the DB_tbl.DB_ID to the srvdb_tbl.DB_ID and server id in the Servers_tbl.SRV_ID

any one can help me ...

You need a stored procedure along the lines of

CREATE PROCEDURE dbo.AddDbServer ( @.DB_Name VARCHAR(50), @.SRV_ID INT) AS

DECLARE @.DB_ID INT

IF NOT EXISTS(SELECT * FROMDB_tbl WHERE DB_NAME = @.DB_NAME)

BEGIN INSERT INTO DB_tbl (DB_NAME) VALUES (@.DB_Name)

SELECT @.DB_ID = SCOPE_IDENTITY

END

ELSE

SELECT @.DB_ID = SELECT DB_ID FROMDB_tbl WHERE DB_NAME =@.DB_Name

END

INSERT INTOsrvdb_tbl(DB_ID,SRV_ID) VALUES (@.DB_ID , @.SRV_ID)

You will need to test the stored procedure before incorporating it into your program.

Insert performance problem

Hi,
In our application we do bulk insert 20000 rows into staging database
(bulk/logged recovery model) and then insert into production database from
this staging database. It seems like SQL Server is taking 20 sec to do the
insert into prod database. While if we delete records from prod database
table and run insert again it takes 600ms. Cpu time is approx 250ms in both
cases, writes in 1st case are 3500 and 250 in second and reads are
negligible. What can be the possible reason for this behavior that insert
into a tables as select * from 2nd table always runs slow first time as
compare to if we run insert again it works fine(we ran dbcc dropcleanbuffers
and checkpoint to make sure memory is clean for 2nd run)?
Thanks
--Harvinder
Sounds like you have a lot of page splits going on. Do you have a clustered
index on the table you are inserting into? How many nonclustered indexes do
you have? What about RI?
Andrew J. Kelly SQL MVP
"harvinder" <harvinder@.discussions.microsoft.com> wrote in message
news:B5CB822C-6DFD-429D-AEF1-ED0674076E5C@.microsoft.com...
> Hi,
> In our application we do bulk insert 20000 rows into staging database
> (bulk/logged recovery model) and then insert into production database from
> this staging database. It seems like SQL Server is taking 20 sec to do the
> insert into prod database. While if we delete records from prod database
> table and run insert again it takes 600ms. Cpu time is approx 250ms in
> both
> cases, writes in 1st case are 3500 and 250 in second and reads are
> negligible. What can be the possible reason for this behavior that insert
> into a tables as select * from 2nd table always runs slow first time as
> compare to if we run insert again it works fine(we ran dbcc
> dropcleanbuffers
> and checkpoint to make sure memory is clean for 2nd run)?
> Thanks
> --Harvinder
>
|||We remove the clustered index and still the same issue with heap also...
also following is true:
Auto update stats is off ( we do it manually every night)
No trigger
No foreign key to/from this table
No blocking at time of insertion
Autogrow option is off for data and log files
Strange that the same rows insert slowly first time and then if delete from
table and insert again runs very fast...
"Andrew J. Kelly" wrote:

> Sounds like you have a lot of page splits going on. Do you have a clustered
> index on the table you are inserting into? How many nonclustered indexes do
> you have? What about RI?
> --
> Andrew J. Kelly SQL MVP
>
> "harvinder" <harvinder@.discussions.microsoft.com> wrote in message
> news:B5CB822C-6DFD-429D-AEF1-ED0674076E5C@.microsoft.com...
>
>
|||> We remove the clustered index and still the same issue with heap also...
> also following is true:
Just make sure the data being inserted is roughly in the order of the
clustered index otherwise you will get lots of page splits.

> Strange that the same rows insert slowly first time and then if delete
> from
> table and insert again runs very fast...
Well that probably means the data you are reading from in in cache the
second time around.
Andrew J. Kelly SQL MVP
"harvinder" <harvinder@.discussions.microsoft.com> wrote in message
news:5433DEB7-3184-465D-8799-6203DFF09CC4@.microsoft.com...[vbcol=seagreen]
> We remove the clustered index and still the same issue with heap also...
> also following is true:
> Auto update stats is off ( we do it manually every night)
> No trigger
> No foreign key to/from this table
> No blocking at time of insertion
> Autogrow option is off for data and log files
> Strange that the same rows insert slowly first time and then if delete
> from
> table and insert again runs very fast...
>
> "Andrew J. Kelly" wrote:

Insert performance problem

Hi,
In our application we do bulk insert 20000 rows into staging database
(bulk/logged recovery model) and then insert into production database from
this staging database. It seems like SQL Server is taking 20 sec to do the
insert into prod database. While if we delete records from prod database
table and run insert again it takes 600ms. Cpu time is approx 250ms in both
cases, writes in 1st case are 3500 and 250 in second and reads are
negligible. What can be the possible reason for this behavior that insert
into a tables as select * from 2nd table always runs slow first time as
compare to if we run insert again it works fine(we ran dbcc dropcleanbuffers
and checkpoint to make sure memory is clean for 2nd run)?
Thanks
--HarvinderSounds like you have a lot of page splits going on. Do you have a clustered
index on the table you are inserting into? How many nonclustered indexes do
you have? What about RI?
Andrew J. Kelly SQL MVP
"harvinder" <harvinder@.discussions.microsoft.com> wrote in message
news:B5CB822C-6DFD-429D-AEF1-ED0674076E5C@.microsoft.com...
> Hi,
> In our application we do bulk insert 20000 rows into staging database
> (bulk/logged recovery model) and then insert into production database from
> this staging database. It seems like SQL Server is taking 20 sec to do the
> insert into prod database. While if we delete records from prod database
> table and run insert again it takes 600ms. Cpu time is approx 250ms in
> both
> cases, writes in 1st case are 3500 and 250 in second and reads are
> negligible. What can be the possible reason for this behavior that insert
> into a tables as select * from 2nd table always runs slow first time as
> compare to if we run insert again it works fine(we ran dbcc
> dropcleanbuffers
> and checkpoint to make sure memory is clean for 2nd run)?
> Thanks
> --Harvinder
>|||We remove the clustered index and still the same issue with heap also...
also following is true:
Auto update stats is off ( we do it manually every night)
No trigger
No foreign key to/from this table
No blocking at time of insertion
Autogrow option is off for data and log files
Strange that the same rows insert slowly first time and then if delete from
table and insert again runs very fast...
"Andrew J. Kelly" wrote:

> Sounds like you have a lot of page splits going on. Do you have a cluster
ed
> index on the table you are inserting into? How many nonclustered indexes
do
> you have? What about RI?
> --
> Andrew J. Kelly SQL MVP
>
> "harvinder" <harvinder@.discussions.microsoft.com> wrote in message
> news:B5CB822C-6DFD-429D-AEF1-ED0674076E5C@.microsoft.com...
>
>|||> We remove the clustered index and still the same issue with heap also...
> also following is true:
Just make sure the data being inserted is roughly in the order of the
clustered index otherwise you will get lots of page splits.

> Strange that the same rows insert slowly first time and then if delete
> from
> table and insert again runs very fast...
Well that probably means the data you are reading from in in cache the
second time around.
Andrew J. Kelly SQL MVP
"harvinder" <harvinder@.discussions.microsoft.com> wrote in message
news:5433DEB7-3184-465D-8799-6203DFF09CC4@.microsoft.com...[vbcol=seagreen]
> We remove the clustered index and still the same issue with heap also...
> also following is true:
> Auto update stats is off ( we do it manually every night)
> No trigger
> No foreign key to/from this table
> No blocking at time of insertion
> Autogrow option is off for data and log files
> Strange that the same rows insert slowly first time and then if delete
> from
> table and insert again runs very fast...
>
> "Andrew J. Kelly" wrote:
>

Insert performance problem

Hi,
In our application we do bulk insert 20000 rows into staging database
(bulk/logged recovery model) and then insert into production database from
this staging database. It seems like SQL Server is taking 20 sec to do the
insert into prod database. While if we delete records from prod database
table and run insert again it takes 600ms. Cpu time is approx 250ms in both
cases, writes in 1st case are 3500 and 250 in second and reads are
negligible. What can be the possible reason for this behavior that insert
into a tables as select * from 2nd table always runs slow first time as
compare to if we run insert again it works fine(we ran dbcc dropcleanbuffers
and checkpoint to make sure memory is clean for 2nd run)?
Thanks
--HarvinderSounds like you have a lot of page splits going on. Do you have a clustered
index on the table you are inserting into? How many nonclustered indexes do
you have? What about RI?
--
Andrew J. Kelly SQL MVP
"harvinder" <harvinder@.discussions.microsoft.com> wrote in message
news:B5CB822C-6DFD-429D-AEF1-ED0674076E5C@.microsoft.com...
> Hi,
> In our application we do bulk insert 20000 rows into staging database
> (bulk/logged recovery model) and then insert into production database from
> this staging database. It seems like SQL Server is taking 20 sec to do the
> insert into prod database. While if we delete records from prod database
> table and run insert again it takes 600ms. Cpu time is approx 250ms in
> both
> cases, writes in 1st case are 3500 and 250 in second and reads are
> negligible. What can be the possible reason for this behavior that insert
> into a tables as select * from 2nd table always runs slow first time as
> compare to if we run insert again it works fine(we ran dbcc
> dropcleanbuffers
> and checkpoint to make sure memory is clean for 2nd run)?
> Thanks
> --Harvinder
>|||We remove the clustered index and still the same issue with heap also...
also following is true:
Auto update stats is off ( we do it manually every night)
No trigger
No foreign key to/from this table
No blocking at time of insertion
Autogrow option is off for data and log files
Strange that the same rows insert slowly first time and then if delete from
table and insert again runs very fast...
"Andrew J. Kelly" wrote:
> Sounds like you have a lot of page splits going on. Do you have a clustered
> index on the table you are inserting into? How many nonclustered indexes do
> you have? What about RI?
> --
> Andrew J. Kelly SQL MVP
>
> "harvinder" <harvinder@.discussions.microsoft.com> wrote in message
> news:B5CB822C-6DFD-429D-AEF1-ED0674076E5C@.microsoft.com...
> > Hi,
> >
> > In our application we do bulk insert 20000 rows into staging database
> > (bulk/logged recovery model) and then insert into production database from
> > this staging database. It seems like SQL Server is taking 20 sec to do the
> > insert into prod database. While if we delete records from prod database
> > table and run insert again it takes 600ms. Cpu time is approx 250ms in
> > both
> > cases, writes in 1st case are 3500 and 250 in second and reads are
> > negligible. What can be the possible reason for this behavior that insert
> > into a tables as select * from 2nd table always runs slow first time as
> > compare to if we run insert again it works fine(we ran dbcc
> > dropcleanbuffers
> > and checkpoint to make sure memory is clean for 2nd run)?
> >
> > Thanks
> > --Harvinder
> >
>
>|||> We remove the clustered index and still the same issue with heap also...
> also following is true:
Just make sure the data being inserted is roughly in the order of the
clustered index otherwise you will get lots of page splits.
> Strange that the same rows insert slowly first time and then if delete
> from
> table and insert again runs very fast...
Well that probably means the data you are reading from in in cache the
second time around.
Andrew J. Kelly SQL MVP
"harvinder" <harvinder@.discussions.microsoft.com> wrote in message
news:5433DEB7-3184-465D-8799-6203DFF09CC4@.microsoft.com...
> We remove the clustered index and still the same issue with heap also...
> also following is true:
> Auto update stats is off ( we do it manually every night)
> No trigger
> No foreign key to/from this table
> No blocking at time of insertion
> Autogrow option is off for data and log files
> Strange that the same rows insert slowly first time and then if delete
> from
> table and insert again runs very fast...
>
> "Andrew J. Kelly" wrote:
>> Sounds like you have a lot of page splits going on. Do you have a
>> clustered
>> index on the table you are inserting into? How many nonclustered indexes
>> do
>> you have? What about RI?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "harvinder" <harvinder@.discussions.microsoft.com> wrote in message
>> news:B5CB822C-6DFD-429D-AEF1-ED0674076E5C@.microsoft.com...
>> > Hi,
>> >
>> > In our application we do bulk insert 20000 rows into staging database
>> > (bulk/logged recovery model) and then insert into production database
>> > from
>> > this staging database. It seems like SQL Server is taking 20 sec to do
>> > the
>> > insert into prod database. While if we delete records from prod
>> > database
>> > table and run insert again it takes 600ms. Cpu time is approx 250ms in
>> > both
>> > cases, writes in 1st case are 3500 and 250 in second and reads are
>> > negligible. What can be the possible reason for this behavior that
>> > insert
>> > into a tables as select * from 2nd table always runs slow first time as
>> > compare to if we run insert again it works fine(we ran dbcc
>> > dropcleanbuffers
>> > and checkpoint to make sure memory is clean for 2nd run)?
>> >
>> > Thanks
>> > --Harvinder
>> >
>>

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
>
>
>

Insert performance --2nd post

Hi,
In our application we do bulk insert 20000 rows into staging database
(bulk/logged recovery model) and then insert into production database from
this staging database. It seems like SQL Server is taking 20 sec to do the
insert into prod database. While if we delete records from prod database
table and run insert again it takes 600ms. Cpu time is approx 250ms in both
cases, writes in 1st case are 3500 and 250 in second and reads are
negligible. What can be the possible reason for this behavior that insert
into a tables as select * from 2nd table always runs slow first time as
compare to if we run insert again it works fine(we ran dbcc dropcleanbuffers
and checkpoint to make sure memory is clean for 2nd run)?
Also we tried following steps:
We remove the clustered index and still the same issue with heap also...
also following is true:
Auto update stats is off ( we do it manually every night)
No trigger
No foreign key to/from this table
No blocking at time of insertion
Autogrow option is off for data and log files
Strange that the same rows insert slowly first time and then if delete from
table and insert again runs very fast...
Thanks
--HarvinderHi
Look at performance counters for Page Splits and Extent Allocations.
Also, if you have hardware disk cache, the data is in disk cache on the 2nd
run.
Regards
Mike
"harvinder" wrote:
> Hi,
> In our application we do bulk insert 20000 rows into staging database
> (bulk/logged recovery model) and then insert into production database from
> this staging database. It seems like SQL Server is taking 20 sec to do the
> insert into prod database. While if we delete records from prod database
> table and run insert again it takes 600ms. Cpu time is approx 250ms in both
> cases, writes in 1st case are 3500 and 250 in second and reads are
> negligible. What can be the possible reason for this behavior that insert
> into a tables as select * from 2nd table always runs slow first time as
> compare to if we run insert again it works fine(we ran dbcc dropcleanbuffers
> and checkpoint to make sure memory is clean for 2nd run)?
> Also we tried following steps:
> We remove the clustered index and still the same issue with heap also...
> also following is true:
> Auto update stats is off ( we do it manually every night)
> No trigger
> No foreign key to/from this table
> No blocking at time of insertion
> Autogrow option is off for data and log files
> Strange that the same rows insert slowly first time and then if delete from
> table and insert again runs very fast...
>
> Thanks
> --Harvinder
>

Insert performance --2nd post

Hi,
In our application we do bulk insert 20000 rows into staging database
(bulk/logged recovery model) and then insert into production database from
this staging database. It seems like SQL Server is taking 20 sec to do the
insert into prod database. While if we delete records from prod database
table and run insert again it takes 600ms. Cpu time is approx 250ms in both
cases, writes in 1st case are 3500 and 250 in second and reads are
negligible. What can be the possible reason for this behavior that insert
into a tables as select * from 2nd table always runs slow first time as
compare to if we run insert again it works fine(we ran dbcc dropcleanbuffers
and checkpoint to make sure memory is clean for 2nd run)?
Also we tried following steps:
We remove the clustered index and still the same issue with heap also...
also following is true:
Auto update stats is off ( we do it manually every night)
No trigger
No foreign key to/from this table
No blocking at time of insertion
Autogrow option is off for data and log files
Strange that the same rows insert slowly first time and then if delete from
table and insert again runs very fast...
Thanks
--Harvinder
Hi
Look at performance counters for Page Splits and Extent Allocations.
Also, if you have hardware disk cache, the data is in disk cache on the 2nd
run.
Regards
Mike
"harvinder" wrote:

> Hi,
> In our application we do bulk insert 20000 rows into staging database
> (bulk/logged recovery model) and then insert into production database from
> this staging database. It seems like SQL Server is taking 20 sec to do the
> insert into prod database. While if we delete records from prod database
> table and run insert again it takes 600ms. Cpu time is approx 250ms in both
> cases, writes in 1st case are 3500 and 250 in second and reads are
> negligible. What can be the possible reason for this behavior that insert
> into a tables as select * from 2nd table always runs slow first time as
> compare to if we run insert again it works fine(we ran dbcc dropcleanbuffers
> and checkpoint to make sure memory is clean for 2nd run)?
> Also we tried following steps:
> We remove the clustered index and still the same issue with heap also...
> also following is true:
> Auto update stats is off ( we do it manually every night)
> No trigger
> No foreign key to/from this table
> No blocking at time of insertion
> Autogrow option is off for data and log files
> Strange that the same rows insert slowly first time and then if delete from
> table and insert again runs very fast...
>
> Thanks
> --Harvinder
>