Is there a better way of doing an INSERT or UPDATE

I have several different situations where I want to
Update the record if it exists
or
add a new record if it does not exist.
An example might be
----
-- Has this table already got a record for this entry...
select @.rowcnt=count(*) from mytable where myField = '123'
if ( @.rowcnt = 0 )
begin ... NO so create the record with an initial cnt of 1
insert into mytable (myField, cnt) values ( 'text123', 1 )
end
else
begin -- ... YES so increment the cnt
Update mytable set cnt=cnt+1 where myField = 'text123'
end
---
The select count(*) can take a long time on a large table.
Table Structure something like
--
id PK
myField varchar(32)
cnt int
--
Thanks
BillTry:
If Exists (select * from mytable where myField = '123')
update ...
else
insert ...
The Exists will perform much better than the Count because it will stop as
soon as it hits a match.
"Bill" <wje@.blueyonder.co.uk> wrote in message
news:33ff429ctg88ethir0ej3g4plhfbr19sqo@.
4ax.com...
>I have several different situations where I want to
> Update the record if it exists
> or
> add a new record if it does not exist.
> An example might be
> ----
> -- Has this table already got a record for this entry...
> select @.rowcnt=count(*) from mytable where myField = '123'
> if ( @.rowcnt = 0 )
> begin ... NO so create the record with an initial cnt of 1
> insert into mytable (myField, cnt) values ( 'text123', 1 )
> end
> else
> begin -- ... YES so increment the cnt
> Update mytable set cnt=cnt+1 where myField = 'text123'
> end
> ---
> The select count(*) can take a long time on a large table.
> Table Structure something like
> --
> id PK
> myField varchar(32)
> cnt int
> --
> Thanks
> Bill|||The count(*) of your select will perform a scan on every record (either
indexed or otherwise) to find the number of instances. The higher the
rowcount, the longer it will take. Since it looks like (from your
example) that you are always looking to update exactly 1 row each time
use EXISTS instead. This will stop the reads once the record has been
located. Additionally, take a look at the indexes . . .:
if ( NOT EXISTS( select * from myTable where myField = '123') )
begin ... NO so create the record with an initial cnt of 1
insert into mytable (myField, cnt) values ( 'text123', 1 )
end
else
begin -- ... YES so increment the cnt
Update mytable set cnt=cnt+1 where myField = 'text123'
end
hope that helps . . .|||> The select count(*) can take a long time on a large table.
do you have a unique index on myField?|||Thanks very much.
Those replies were extremely useful.
Bill
On Thu, 20 Apr 2006 16:59:48 GMT, Bill <wje@.blueyonder.co.uk> wrote:

>I have several different situations where I want to
>Update the record if it exists
>or
>add a new record if it does not exist.
>An example might be
>----
>-- Has this table already got a record for this entry...
>select @.rowcnt=count(*) from mytable where myField = '123'
>if ( @.rowcnt = 0 )
> begin ... NO so create the record with an initial cnt of 1
> insert into mytable (myField, cnt) values ( 'text123', 1 )
> end
>else
> begin -- ... YES so increment the cnt
> Update mytable set cnt=cnt+1 where myField = 'text123'
> end
>---
>The select count(*) can take a long time on a large table.
>Table Structure something like
>--
>id PK
>myField varchar(32)
>cnt int
>--
>Thanks
>Bill|||As others have already pointed out, you can use exists instead of count(*),
but there's another problem with using the logic below in a concurrent
environment.
Here's what you need:
BEGIN TRAN
IF EXISTS (
SELECT *
FROM mytable WITH(UPDLOCK, HOLDLOCK)
WHERE myField = '123'
)
UPDATE...
ELSE
INSERT...
IF @.@.ERROR != 0 OR @.@.ROWCOUNT = 0
ROLLBACK
ELSE
COMMIT
What's most important here is the explicit transaction and WITH(UPDLOCK,
HOLDLOCK). Without these, you'll get inconsistent results in a concurrent
environment. Without the transaction, it's possible for another transaction
to delete the row between the time that the shared lock from the SELECT is
released and the time that the database engine tries to obtain an exclusive
lock for the UPDATE. Without both the transaction and HOLDLOCK, it's
possible for another transaction to insert a row where myField = '123'
between the time that EXISTS is evaluated and the time that the INSERT
starts executing. Without UPDLOCK, it's possible for two concurrent
instances to obtain and hold shared locks on the row where myField = '123'
such that neither can obtain an exclusive lock in order to do the UPDATE,
causing a deadlock. Now, that can happen only if the transaction isolation
level is stricter than READ COMMITTED, that is, REPEATABLE READ or
SERIALIZABLE. If the isolation level is READ COMMITTED (the default), then
other anomalies can occur, such as updates being lost, or primary key
constraint violations.
Thus, any time you issue a SELECT before an UPDATE or INSERT (even within an
EXISTS clause), you should wrap the whole thing in a transaction and
decorate the SELECT with the WITH(UPDLOCK, HOLDLOCK) clause. I prefer to
explicitly specify HOLDLOCK, even if the isolation level is SERIALIZABLE,
because that way if the isolation level is later changed (to improve
performance or scalability, for example), the above problems won't reappear.
"Bill" <wje@.blueyonder.co.uk> wrote in message
news:33ff429ctg88ethir0ej3g4plhfbr19sqo@.
4ax.com...
>I have several different situations where I want to
> Update the record if it exists
> or
> add a new record if it does not exist.
> An example might be
> ----
> -- Has this table already got a record for this entry...
> select @.rowcnt=count(*) from mytable where myField = '123'
> if ( @.rowcnt = 0 )
> begin ... NO so create the record with an initial cnt of 1
> insert into mytable (myField, cnt) values ( 'text123', 1 )
> end
> else
> begin -- ... YES so increment the cnt
> Update mytable set cnt=cnt+1 where myField = 'text123'
> end
> ---
> The select count(*) can take a long time on a large table.
> Table Structure something like
> --
> id PK
> myField varchar(32)
> cnt int
> --
> Thanks
> Bill|||One comment: Using NOT EXISTS will also force a table or index scan. The
query processor has to scan all records to determine if one is missing.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"epperly" <epperlys@.gmail.com> wrote in message
news:1145553039.015639.244770@.i39g2000cwa.googlegroups.com...
> The count(*) of your select will perform a scan on every record (either
> indexed or otherwise) to find the number of instances. The higher the
> rowcount, the longer it will take. Since it looks like (from your
> example) that you are always looking to update exactly 1 row each time
> use EXISTS instead. This will stop the reads once the record has been
> located. Additionally, take a look at the indexes . . .:
> if ( NOT EXISTS( select * from myTable where myField = '123') )
> begin ... NO so create the record with an initial cnt of 1
> insert into mytable (myField, cnt) values ( 'text123', 1 )
> end
> else
> begin -- ... YES so increment the cnt
> Update mytable set cnt=cnt+1 where myField = 'text123'
> end
> hope that helps . . .
>|||On Thu, 20 Apr 2006 22:01:01 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:

>One comment: Using NOT EXISTS will also force a table or index scan. The
>query processor has to scan all records to determine if one is missing.
First of all, if there is an index to support the subquery it will not
need to perform a scan.
Second, EXISTS performs exactly the same as NOT EXISTS. It has to, as
answering either question answers both questions.
Roy Harvey
Beacon Falls, CT

Is that possible from live website sql record to " intranet " database ?

hi.

I would wish to know whether it is possible for me to program the code from html or aspx ( from web hosting server ) to our office intranet database which is window server 2005?

I had tried to google for more info but there are too complicated thing for me to understand.

Actually i am doing the web form feedback form from the website for the public to fill in and submit. i am using the mysql from web server to store the records from the feedback form.

We have another database in our server, ms server 2005 to store records in the intranet office only. i need to get the records from mysql to intranet database. i don`t think there is a automated scripting which allow u to auto update the intranet database from mysql( web server )

So i thought of one thing . program html or aspx to insert records directly to our database intranet . but how ?

I appreciated that.

Regards

newbie on aspnet.

You can do it by

Add a web service to your remote server to expose your table.|||Add a web service to your remote server to expose your table.Lock the web service site to only respond to the expternal IP address of your of your office.Write an application to consume the web service and update the internal table.|||

>But i think it is better to program aspx to insert records to intranetdatabase directly. or any alternate ? All database software are alreadyfixed, no change.

For the web application to be able to write directly to the local database will require opening up your firewall - this is not a good idea as it opens up the possibility of your intranet database being hacked. The solution I suggested requires only the HTTP and HTTPS ports to be opened. As the data is being dragged in rather than being pushed in, it is much more secure.

>All database software are already fixed, no change.
If the table is a pure log (records are only added), then the required information may already be be present. You would need to provide your table creation scripts for me to comment further.

Is SP command quicker than recordset?

Hi,
I have a web site that uses ASP. I want to know if a record exists for
a specific date. I will be doing this 30 times for each refresh of the
page.
What I've setup is a simple SP that takes a date as an input and
returns a count.
CREATE PROCEDURE spGetNumber
(
@.Date smalldatetime
)
AS
SET NOCOUNT ON
DECLARE @.Count tinyint
SELECT @.Count = COUNT(ID) FROM Race WHERE Date=@.Date
RETURN @.Count
--
In my ASP, I'm calling a command object.
It seems pretty fast, but I only have 100 records in the table, 1 user
at a time. In the future I expect 100's of users and 10,000's of
records.
Is this the best method?
thx,
Bodihi bodi
u can continue using the SP. it is advisible to contact the database only
once from the ASP page instead of contacting 30 times
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"BodiKlamph@.gmail.com" wrote:

> Hi,
> I have a web site that uses ASP. I want to know if a record exists for
> a specific date. I will be doing this 30 times for each refresh of the
> page.
> What I've setup is a simple SP that takes a date as an input and
> returns a count.
> --
> CREATE PROCEDURE spGetNumber
> (
> @.Date smalldatetime
> )
> AS
> SET NOCOUNT ON
> DECLARE @.Count tinyint
> SELECT @.Count = COUNT(ID) FROM Race WHERE Date=@.Date
> RETURN @.Count
> --
> In my ASP, I'm calling a command object.
> It seems pretty fast, but I only have 100 records in the table, 1 user
> at a time. In the future I expect 100's of users and 10,000's of
> records.
> Is this the best method?
> thx,
> Bodi
>|||On 7 Aug 2005 11:18:46 -0700, BodiKlamph@.gmail.com wrote:

>Hi,
>I have a web site that uses ASP. I want to know if a record exists for
>a specific date. I will be doing this 30 times for each refresh of the
>page.
Hi Bodi,
Issuing 30 consecutive calls is not a good idea. If the sole purpose is
to find the dates for which no row exists, rewrite the stored procedure
to take two parameters (starting and ending date) and return a resultset
of all dates for which no rows exist.

>What I've setup is a simple SP that takes a date as an input and
>returns a count.
Another thing: if you only need to verify existence, use EXISTS, not
COUNT. A query with COUNT will always continue until all matching rows
are found. A query with EXISTS will stop after the first matching row.

>It seems pretty fast, but I only have 100 records in the table, 1 user
>at a time. In the future I expect 100's of users and 10,000's of
>records.
10,000's of rows is not very much for SQL Server, but still - why incur
extra overhead that can easily be avoided.

>Is this the best method?
The best method involves a calendar table. If you don't have one yet in
your database, go ahead and create it now. Instructions for making a
calendar table and many examples of how you can use one are at this
site: http://www.aspfaq.com/show.asp?id=2519.
Remember that you only need to create the calendar table once! Just
don't forget to add some new rows every year or two.
Once you have a calendar table, you can find the dates without row in
your Race table with the following stored procedure:
CREATE PROC FindDatesWithoutRowInRace
(@.StartDate datetime,
@.EndDate datetime)
AS
SELECT c.dt
FROM dbo.Calendar AS c
WHERE c.dt >= @.StartDate
AND c.dt <= @.EndDate
AND NOT EXISTS
(SELECT *
FROM Race AS r
WHERE e.[Date] = c.dt)
go
The final step would be to change the calling code to issue one call
with two parameters, then fetch and process the rows returned by the
stored procedure.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||A couple things in addition to Chandra's and Hugo's posts...
The return value from an SP is intended to be a success/failure indicator,
like a windows app - returning non-zero indicates failure, so when you get a
count > 0 and return it you're indicating that the SP failed.
If you want a count of matching records you should use an output parameter;
if you simply want to test for existance you should use EXISTS like Hugo
said, and set the output BIT to 1...
Another note, with COUNT - generally you want to use COUNT(*) not
COUNT([field_name]) because then SQL can optimize to use the best index
rather than have to use an index or constraint (assuming one exists) with th
e
specified field.
I'm going to go out on a limb and guess that you're trying to display a
calendar, hence the 30 calls per request. For this scenario you should retur
n
records that fall in a specific date range:
SELECT DISTINCT [date]
FROM [table_owner].Race
WHERE [date] BETWEEN @.lo_date AND @.hi_date
Then populate the calendar from the recordset in your ASP. Rememebr that
datetime includes time, so if you want records thru the end of the last date
specified then you'll have to be sure either all dates in the table have
00:00:00.000 for the time portion or @.lo_date has time 00:00:00.000 and
@.hi_date has time 23:59:59.997.
Be sure your command objkect's type is set to stored procedure, not text
query, for fastest execution.
Good luck, - KH
"BodiKlamph@.gmail.com" wrote:

> Hi,
> I have a web site that uses ASP. I want to know if a record exists for
> a specific date. I will be doing this 30 times for each refresh of the
> page.
> What I've setup is a simple SP that takes a date as an input and
> returns a count.
> --
> CREATE PROCEDURE spGetNumber
> (
> @.Date smalldatetime
> )
> AS
> SET NOCOUNT ON
> DECLARE @.Count tinyint
> SELECT @.Count = COUNT(ID) FROM Race WHERE Date=@.Date
> RETURN @.Count
> --
> In my ASP, I'm calling a command object.
> It seems pretty fast, but I only have 100 records in the table, 1 user
> at a time. In the future I expect 100's of users and 10,000's of
> records.
> Is this the best method?
> thx,
> Bodi
>|||As above...
But think in terms of SETS of data.
What is the ideal SET of data that the web application could get - KH has
already suggested "the set of dates where there are no races". This may not
be ideal for your real needs, but is definitely a substantial improvement on
making multiple calls that s out many times singular Yes / No answers -
which aren't sets at all, they are simple questions.
- Tim
<BodiKlamph@.gmail.com> wrote in message
news:1123438726.697862.102120@.g43g2000cwa.googlegroups.com...
> Hi,
> I have a web site that uses ASP. I want to know if a record exists for
> a specific date. I will be doing this 30 times for each refresh of the
> page.
> What I've setup is a simple SP that takes a date as an input and
> returns a count.
> --
> CREATE PROCEDURE spGetNumber
> (
> @.Date smalldatetime
> )
> AS
> SET NOCOUNT ON
> DECLARE @.Count tinyint
> SELECT @.Count = COUNT(ID) FROM Race WHERE Date=@.Date
> RETURN @.Count
> --
> In my ASP, I'm calling a command object.
> It seems pretty fast, but I only have 100 records in the table, 1 user
> at a time. In the future I expect 100's of users and 10,000's of
> records.
> Is this the best method?
> thx,
> Bodi
>|||In addition to the already mentioned ideas:
If you are always making exactly 30 calls, do the different values of date
have a relationship to one another?
For example, using a resultset instead of the return output:
SELECT sum (case when date = @.date then 1 else 0 end) as first,
sum(case when date = dateadd(minute,-1,@.date) then 1 else 0 end)
as second
...
sum(case when date = dateadd(minute,N-1,@.date) then 1 else 0 end)
as Nth
FROM race
WHERE date between @.date and dateadd(minute,N-1,@.date)
or something along these lines and it will probably be faster. Even doing
thirty parms and then putting the thirty sums would likely be better because
of networkIO type stuff.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
<BodiKlamph@.gmail.com> wrote in message
news:1123438726.697862.102120@.g43g2000cwa.googlegroups.com...
> Hi,
> I have a web site that uses ASP. I want to know if a record exists for
> a specific date. I will be doing this 30 times for each refresh of the
> page.
> What I've setup is a simple SP that takes a date as an input and
> returns a count.
> --
> CREATE PROCEDURE spGetNumber
> (
> @.Date smalldatetime
> )
> AS
> SET NOCOUNT ON
> DECLARE @.Count tinyint
> SELECT @.Count = COUNT(ID) FROM Race WHERE Date=@.Date
> RETURN @.Count
> --
> In my ASP, I'm calling a command object.
> It seems pretty fast, but I only have 100 records in the table, 1 user
> at a time. In the future I expect 100's of users and 10,000's of
> records.
> Is this the best method?
> thx,
> Bodi
>|||thx Hugo, your answer seems to best suite my needs.
Yes, 30 calls is quite bad. Returning a recordset with just hte valid
dates is a much better idea. then i can loop in my asp code and add
only the valid calendar days.
question? wut's up with the calendar table you mentioned; why would I
need that?
Can't I just do:
SELECT DISTINCT [Date] FROM myTable WHERE [Date] >= startDate and
[Date] <= endDate
that would return only the valid dates|||K, I finished making the changes.
2 questions.
I have a loop that writes out my calendar days (part of an ASP calendar
class). For each day, i want to see if there is a corresponding record
in the resultset I do this by an inefficeint (tha'ts why i'm here)
recordset.find method. I have to search from record 1 each time tho
This is probaly the wrong group now, since it's more ADO than SQL, but
I'll give it a host since you're already familiar with my question.
I was using set rs = cmd.execute, but it compalined that my recordset
was forward only. So I cahnged it form a SP to a simple recordset.open
(using dbopendynamic so i could use the .find method).
I was thinking it may be easier to use GetRows() then loop through the
much less bulky array. Not sure how I'd do this yet, but I can think
of something...unless there is already a set way of doing this?
thx,
Bodi|||lol
at this point I'm just talking to myself.
I used the GetString method, with a row deliminator of comma
then, instead of .find, I did:
if instr(rsDays, "," & DAY(DateString) & ",")
works like a charm, and i imagine it's way faster to do a string check
then it is a .find
thx everybody|||On 8 Aug 2005 21:13:48 -0700, BodiKlamph@.gmail.com wrote:

>thx Hugo, your answer seems to best suite my needs.
>Yes, 30 calls is quite bad. Returning a recordset with just hte valid
>dates is a much better idea. then i can loop in my asp code and add
>only the valid calendar days.
>question? wut's up with the calendar table you mentioned; why would I
>need that?
>Can't I just do:
>SELECT DISTINCT [Date] FROM myTable WHERE [Date] >= startDate and
>[Date] <= endDate
>that would return only the valid dates
Hi Bodi,
I must have misunderstood you earlier. I thought you wanted to find the
dates that are not yet in your table. The query above will do the
reverse: find the dates that are (at least once) in the table.
But do remember that a datetime column includes a time part as well. If
you don't ensure that the time part is the same for all entries, the
query above might return the same date several times, since the DISTINCT
will look at the time part as well.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

is record exist ?

hi,
I would like asked an opinion about the system of my coding if is ok? I have one table here to modify. My point here is to count the record to avoid a duplicate record .
rs.open "Select count (field) as name from table Where field='001'",cn
If rs!name > 0 then
Update sqlstatement
Else
Insert sqlstatement
Endif

Any suggestion pls..Be certain that your query can use an index, since that will make more difference than anything else you can do.

Consider making a single query to both test for the existance of the row and actually do the insert/update. This is a balancing act, since this can either save or waste time depending on how you construct it. A stored procedure is probably your best bet to get the best performance.

If you want to push the performance limit, you can use something like:CREATE PROCEDURE foo
@.piFooID INT
, @.pcFooStuff NVARCHAR(20)
AS

UPDATE foo
SET fooStuff = @.pcFooStuff
WHERE fooID = @.piFooID

IF 0 = @.@.rowcount
INSERT foo (
fooID, fooStuff
) VALUES (
@.piFooID, @.pcFooStuff
)

RETURN-PatP|||Thanks Pat,

Pat I Would Like To Asked You What Is The Best Website That Discuss About Tsql / Stored Proc?

Popskie,|||Unfortunately that question is subjective... The "best" website is the one that addresses your needs, and no two people have exactly the same needs. Heck, over any significant time span, even one person will have different needs!

The short answer boils down to whatever site helps you the most is the best for you. This will change over time, as both you and the available web sites change. Todays answer will mean practically nothing a year from now.

-PatP