Antivirus install to db server: table

Showing posts with label table. Show all posts

Friday, March 30, 2012

Question about sproc performance?

whats the difference in performance between these two scenarioes...

Suppose if i build a sproc to insert or update into one table and then take its primary key and insert and update subsequent tables may one or 2 or more take more time in exceuting...

writing indivdual sproc for insert and update for particular table in different sproc and then calling all the sprocs in a master proc using exec sproc command...

Which scenario would better if they are around 230 or 300 or more than records.

Hope some would shed some light into this..

Any help is appreciated...

Regards

Karen

The best advise I can give is to build both of them. You can then look at the individual execution plans of them both and determine which is the better approach for your database. It should also help to highlight if any indexes need to be added to your tables.

|||

I dont think it will be too much of a noticeable difference.. unless you have some very complex logic involving several IF loops which radically changes the WHERE conditions in the query.

|||

Thanks Dinakar...

I just plain inserts and updates...to different tables... and in the above scenario which is best approach to take?

Regards

Karen

|||

If its plain INSERTs/UPDATEs its more of a convenience and easier maintenance that you should decide based on rather than performance. I would create one proc for each table that takes care of INSERT and UPDATE (Depending on whether record exists or not) so you can re-use the procs. If you put multiple tables related INSERTs into one proc and later if you had to do an INSERTt/UPDATE into one of those 2 tables, you'd be re-writing the same logic into another proc again. If you created a separate proc for the table now, you could just call the proc later.

Question about simple query..

How are you ? Please give me an advice :)
It's been bothering me for three days. I'm new SQL user.

I have the following table, which has name, address, city,state,zip
code, and phone number.

kim 3134 n. chatham ellicott city md 21042
410-222-2222
justin 3134 n. chatham rd. ellicott city md 21042
410-222-2222
hong 4343 antonio ln. ellicott city md 21042
555-341-3412
woo 1123 cedar ln. columbiamd21044 410-111-1358

The problem is that the table should not contain the same phone
number.
Phone number 410-222-2222 is duplicated.

How can I erase that extra data, and get the table like this ? :

kim 3134 n. chatham ellicott city md 21042
410-222-2222
*******************the data that has same phone number is
gone*****************
hong 4343 antonio ln. ellicott city md 21042
555-341-3412
woo 1123 cedar ln. columbiamd21044 410-111-1358

First, I used this query, but it turned out all of my data was gone. :
(

delete FROM address WHERE exists (
select * from address where address.phone = address.phone
);

Please help !What is the criteria that you would use to pic which row to keep?
jaehwang wrote:

Quote:

Originally Posted by

How are you ? Please give me an advice :)
It's been bothering me for three days. I'm new SQL user.
>
I have the following table, which has name, address, city,state,zip
code, and phone number.
>
>
kim 3134 n. chatham ellicott city md 21042
410-222-2222
justin 3134 n. chatham rd. ellicott city md 21042
410-222-2222
hong 4343 antonio ln. ellicott city md 21042
555-341-3412
woo 1123 cedar ln. columbiamd21044 410-111-1358
>
>
The problem is that the table should not contain the same phone
number.
Phone number 410-222-2222 is duplicated.
>
How can I erase that extra data, and get the table like this ? :
>
kim 3134 n. chatham ellicott city md 21042
410-222-2222
*******************the data that has same phone number is
gone*****************
hong 4343 antonio ln. ellicott city md 21042
555-341-3412
woo 1123 cedar ln. columbiamd21044 410-111-1358
>
First, I used this query, but it turned out all of my data was gone. :
(
>
delete FROM address WHERE exists (
select * from address where address.phone = address.phone
);
>
Please help !
>

|||You would have to define a criteria based on which a row will be kept or
deleted (that is another column or combination of columns that is unique).
Here is just an example based on your sample data (in this case the row with
the MIN name will be kept, but this assumes no duplicate names with the same
phone):

DELETE FROM Address
WHERE EXISTS (
SELECT *
FROM Address AS A
WHERE A.phone = Address.phone
AND A.name < Address.name)

You can easily reverse the above condition to A.name Address.name to keep
the MAX name.

After you are done you can alter the table and add UNIQUE constraint on the
phone column to prevent duplicate data in the future, something like this:

ALTER TABLE Address ADD CONSTRAINT uphone UNIQUE (phone)

HTH,

Plamen Ratchev
http://www.SQLStudio.comsql

Question about self referencing delete

Let's say we have table as follows.
empid mgrid empname
--
1 null abc
2 1 pqr
empid is primary key for the table
mgrid is self referencing foreign key towards empid
Now on above table, I execute DML as:
DELETE FROM emp_1 WHERE empid in (1, 2)
So SQLSerever, will first attempt to delete record where empid is 1. (Isn't
it?) And then SQLServer is supposed to give an error ; as empid = 1 is being
referred as foreign key in record where empid is 2. But it doesn't happen
so. SQLServer doesn't give an error. So does that mean SQLServer first
deletes record where empid is 2. And then it deletes the record where empid
is 1? (In short, deletes all cascading child records first and then parent
records) Is it SQLServer's normal and expected behavior? Or it is not
guaranteed? Or anything else? Please guide. Thanks.
Regards,
PravinThis is a multi-part message in MIME format.
--=_NextPart_000_0030_01C3745C.97B12C90
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Thanks Tom.
I got the point that because parent and child both are there in delete =statement, SQLServer doesn't throw an error.
However, another point you have specified is "In your case, you did not =specify ON DELETE CASCADE in your foreign key". Does that mean SQLServer =allows setting the option 'on delete cascade' for self referencing keys? =If yes, then please inform me how. If no, then your answer that "In your =DELETE, you specified that you were deleting both the child and the =related parent, so there would be no RI violation." is just the answer =for my question. Right?
Note: I was not able to set this option from enterprize manager. Please =guide, Thanks in Advance.
-- Regards,
Pravin Joshi
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:errsWw9cDHA.828@.TK2MSFTNGP11.phx.gbl...
In your case, you did not specify ON DELETE CASCADE in your foreign =key. If you had, it would have failed, since it is a circular =reference. In your DELETE, you specified that you were deleting both =the child and the related parent, so there would be no RI violation. If =you try the following, it should fail:
DELETE FROM emp_1 WHERE empid =3D 1
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Pravin" <expertco@.vsnl.com> wrote in message =news:OQxXPs9cDHA.456@.TK2MSFTNGP10.phx.gbl...
Let's say we have table as follows.
empid mgrid empname
--
1 null abc
2 1 pqr
empid is primary key for the table
mgrid is self referencing foreign key towards empid
Now on above table, I execute DML as:
DELETE FROM emp_1 WHERE empid in (1, 2)
So SQLSerever, will first attempt to delete record where empid is 1. =(Isn't
it?) And then SQLServer is supposed to give an error ; as empid =3D 1 =is being
referred as foreign key in record where empid is 2. But it doesn't =happen
so. SQLServer doesn't give an error. So does that mean SQLServer first
deletes record where empid is 2. And then it deletes the record where =empid
is 1? (In short, deletes all cascading child records first and then =parent
records) Is it SQLServer's normal and expected behavior? Or it is not
guaranteed? Or anything else? Please guide. Thanks.
Regards,
Pravin
--=_NextPart_000_0030_01C3745C.97B12C90
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Thanks Tom.
I got the point that because parent =and child both are there in delete statement, SQLServer doesn't throw an error.
However, another point you have =specified is "In your case, you did not specify ON DELETE =CASCADE in your foreign key". Does that mean SQLServer allows setting the =option 'on delete cascade' for self referencing keys? If yes, then please inform me =how. If no, then your answer that "In your DELETE, you =specified that you were deleting both the child and the related parent, so there would =be no RI violation." is just the answer for my question. =Right?
Note: I was not able to set this =option from enterprize manager. Please guide, Thanks in Advance.
-- Regards,Pravin Joshi
"Tom Moreau" = wrote in message news:errsWw9cDHA.828@.T=K2MSFTNGP11.phx.gbl...
In your case, you did not specify ON =DELETE CASCADE in your foreign key. If you had, it would have failed, =since it is a circular reference. In your DELETE, you specified that you =were deleting both the child and the related parent, so there would be no =RI violation. If you try the following, it should =fail:

DELETE FROM emp_1 WHERE empid ==3D 1

-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"Pravin" wrote in message news:OQxXPs9cDHA.456@.T=K2MSFTNGP10.phx.gbl...Let's say we have table as follows.empid mgrid =empname--1 &nbs=p; null =abc2 1 pqrempid is primary key =for the tablemgrid is self referencing foreign key towards =empidNow on above table, I execute DML as: DELETE FROM emp_1 =WHERE empid in (1, 2)So SQLSerever, will first attempt to delete =record where empid is 1. (Isn'tit?) And then SQLServer is supposed to =give an error ; as empid =3D 1 is beingreferred as foreign key in record =where empid is 2. But it doesn't happenso. SQLServer doesn't give an error. So =does that mean SQLServer firstdeletes record where empid is 2. And then =it deletes the record where empidis 1? (In short, deletes all =cascading child records first and then parentrecords) Is it SQLServer's normal and = expected behavior? Or it is notguaranteed? Or anything else? =Please guide. =Thanks.Regards,Pravin

--=_NextPart_000_0030_01C3745C.97B12C90--|||This is a multi-part message in MIME format.
--=_NextPart_000_000F_01C37454.B43BD200
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
The point I was making was that you cannot use ON DELETE CASCADE in a =self-referencing situation. You should avoid using EM for creating =tables. I stick with scripting it out and then saving the scripts under =version control.
-- Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Pravin" <expertco@.vsnl.com> wrote in message =news:usF6m5CdDHA.560@.TK2MSFTNGP11.phx.gbl...
Thanks Tom.
I got the point that because parent and child both are there in delete =statement, SQLServer doesn't throw an error.
However, another point you have specified is "In your case, you did not =specify ON DELETE CASCADE in your foreign key". Does that mean SQLServer =allows setting the option 'on delete cascade' for self referencing keys? =If yes, then please inform me how. If no, then your answer that "In your =DELETE, you specified that you were deleting both the child and the =related parent, so there would be no RI violation." is just the answer =for my question. Right?
Note: I was not able to set this option from enterprize manager. Please =guide, Thanks in Advance.
-- Regards,
Pravin Joshi
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:errsWw9cDHA.828@.TK2MSFTNGP11.phx.gbl...
In your case, you did not specify ON DELETE CASCADE in your foreign =key. If you had, it would have failed, since it is a circular =reference. In your DELETE, you specified that you were deleting both =the child and the related parent, so there would be no RI violation. If =you try the following, it should fail:
DELETE FROM emp_1 WHERE empid =3D 1
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Pravin" <expertco@.vsnl.com> wrote in message =news:OQxXPs9cDHA.456@.TK2MSFTNGP10.phx.gbl...
Let's say we have table as follows.
empid mgrid empname
--
1 null abc
2 1 pqr
empid is primary key for the table
mgrid is self referencing foreign key towards empid
Now on above table, I execute DML as:
DELETE FROM emp_1 WHERE empid in (1, 2)
So SQLSerever, will first attempt to delete record where empid is 1. =(Isn't
it?) And then SQLServer is supposed to give an error ; as empid =3D 1 =is being
referred as foreign key in record where empid is 2. But it doesn't =happen
so. SQLServer doesn't give an error. So does that mean SQLServer first
deletes record where empid is 2. And then it deletes the record where =empid
is 1? (In short, deletes all cascading child records first and then =parent
records) Is it SQLServer's normal and expected behavior? Or it is not
guaranteed? Or anything else? Please guide. Thanks.
Regards,
Pravin
--=_NextPart_000_000F_01C37454.B43BD200
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

The point I was making was that you =cannot use ON DELETE CASCADE in a self-referencing situation. You should avoid =using EM for creating tables. I stick with scripting it out and then saving =the scripts under version control.
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Pravin" wrote in message news:usF6m5CdDHA.560@.T=K2MSFTNGP11.phx.gbl...
Thanks Tom.
I got the point that because parent =and child both are there in delete statement, SQLServer doesn't throw an error.
However, another point you have =specified is "In your case, you did not specify ON DELETE =CASCADE in your foreign key". Does that mean SQLServer allows setting the =option 'on delete cascade' for self referencing keys? If yes, then please inform me =how. If no, then your answer that "In your DELETE, you =specified that you were deleting both the child and the related parent, so there would =be no RI violation." is just the answer for my question. =Right?
Note: I was not able to set this =option from enterprize manager. Please guide, Thanks in Advance.
-- Regards,Pravin Joshi
"Tom Moreau" = wrote in message news:errsWw9cDHA.828@.T=K2MSFTNGP11.phx.gbl...
In your case, you did not specify ON =DELETE CASCADE in your foreign key. If you had, it would have failed, =since it is a circular reference. In your DELETE, you specified that you =were deleting both the child and the related parent, so there would be no =RI violation. If you try the following, it should =fail:

DELETE FROM emp_1 WHERE empid ==3D 1

-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"Pravin" wrote in message news:OQxXPs9cDHA.456@.T=K2MSFTNGP10.phx.gbl...Let's say we have table as follows.empid mgrid =empname--1 &nbs=p; null =abc2 1 pqrempid is primary key =for the tablemgrid is self referencing foreign key towards =empidNow on above table, I execute DML as: DELETE FROM emp_1 =WHERE empid in (1, 2)So SQLSerever, will first attempt to delete =record where empid is 1. (Isn'tit?) And then SQLServer is supposed to =give an error ; as empid =3D 1 is beingreferred as foreign key in record =where empid is 2. But it doesn't happenso. SQLServer doesn't give an error. So =does that mean SQLServer firstdeletes record where empid is 2. And then =it deletes the record where empidis 1? (In short, deletes all =cascading child records first and then parentrecords) Is it SQLServer's normal and = expected behavior? Or it is notguaranteed? Or anything else? =Please guide. =Thanks.Regards,Pravin

--=_NextPart_000_000F_01C37454.B43BD200--|||This is a multi-part message in MIME format.
--=_NextPart_000_000D_01C374C4.4266C7F0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Ok Tom. That clears the doubt. Thanks.
-- Regards,
Pravin
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:#aD7FZHdDHA.4020@.tk2msftngp13.phx.gbl...
The point I was making was that you cannot use ON DELETE CASCADE in a =self-referencing situation. You should avoid using EM for creating =tables. I stick with scripting it out and then saving the scripts under =version control.
-- Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Pravin" <expertco@.vsnl.com> wrote in message =news:usF6m5CdDHA.560@.TK2MSFTNGP11.phx.gbl...
Thanks Tom.
I got the point that because parent and child both are there in delete =statement, SQLServer doesn't throw an error.
However, another point you have specified is "In your case, you did =not specify ON DELETE CASCADE in your foreign key". Does that mean =SQLServer allows setting the option 'on delete cascade' for self =referencing keys? If yes, then please inform me how. If no, then your =answer that "In your DELETE, you specified that you were deleting both =the child and the related parent, so there would be no RI violation." is =just the answer for my question. Right?
Note: I was not able to set this option from enterprize manager. =Please guide, Thanks in Advance.
-- Regards,
Pravin Joshi
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:errsWw9cDHA.828@.TK2MSFTNGP11.phx.gbl...
In your case, you did not specify ON DELETE CASCADE in your foreign =key. If you had, it would have failed, since it is a circular =reference. In your DELETE, you specified that you were deleting both =the child and the related parent, so there would be no RI violation. If =you try the following, it should fail:
DELETE FROM emp_1 WHERE empid =3D 1
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Pravin" <expertco@.vsnl.com> wrote in message =news:OQxXPs9cDHA.456@.TK2MSFTNGP10.phx.gbl...
Let's say we have table as follows.
empid mgrid empname
--
1 null abc
2 1 pqr
empid is primary key for the table
mgrid is self referencing foreign key towards empid
Now on above table, I execute DML as:
DELETE FROM emp_1 WHERE empid in (1, 2)
So SQLSerever, will first attempt to delete record where empid is 1. =(Isn't
it?) And then SQLServer is supposed to give an error ; as empid =3D =1 is being
referred as foreign key in record where empid is 2. But it doesn't =happen
so. SQLServer doesn't give an error. So does that mean SQLServer =first
deletes record where empid is 2. And then it deletes the record =where empid
is 1? (In short, deletes all cascading child records first and then =parent
records) Is it SQLServer's normal and expected behavior? Or it is =not
guaranteed? Or anything else? Please guide. Thanks.
Regards,
Pravin
--=_NextPart_000_000D_01C374C4.4266C7F0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Ok Tom. That clears the doubt. Thanks.
-- Regards,Pravin
"Tom Moreau" = wrote in message news:#aD7FZHdDHA.4020=@.tk2msftngp13.phx.gbl...
The point I was making was that you =cannot use ON DELETE CASCADE in a self-referencing situation. You should =avoid using EM for creating tables. I stick with scripting it out and =then saving the scripts under version control.
-- Tom

----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Pravin" wrote in message news:usF6m5CdDHA.560@.T=K2MSFTNGP11.phx.gbl...
Thanks Tom.

I got the point that because parent =and child both are there in delete statement, SQLServer doesn't throw an error.

However, another point you have =specified is "In your case, you did not specify ON DELETE =CASCADE in your foreign key". Does that mean SQLServer allows setting =the option 'on delete cascade' for self referencing keys? If yes, then =please inform me how. If no, then your answer that "In =your DELETE, you specified that you were deleting both the child and the related =parent, so there would be no RI violation." is just the answer for my =question. Right?

Note: I was not able to set =this option from enterprize manager. Please guide, Thanks in Advance.
-- Regards,Pravin Joshi
"Tom Moreau" = wrote in message news:errsWw9cDHA.828@.T=K2MSFTNGP11.phx.gbl...
In your case, you did not specify =ON DELETE CASCADE in your foreign key. If you had, it would have failed, =since it is a circular reference. In your DELETE, you specified that =you were deleting both the child and the related parent, so there would =be no RI violation. If you try the following, it should =fail:

DELETE FROM emp_1 WHERE =empid =3D 1

-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"Pravin" wrote in =message news:OQxXPs9cDHA.456@.T=K2MSFTNGP10.phx.gbl...Let's say we have table as follows.empid mgrid =empname--1 &nbs=p; null =abc2 1 pqrempid is primary =key for the tablemgrid is self referencing foreign key towards =empidNow on above table, I execute DML as: DELETE FROM =emp_1 WHERE empid in (1, 2)So SQLSerever, will first attempt to =delete record where empid is 1. (Isn'tit?) And then SQLServer is =supposed to give an error ; as empid =3D 1 is beingreferred as foreign key =in record where empid is 2. But it doesn't happenso. SQLServer doesn't =give an error. So does that mean SQLServer firstdeletes record where =empid is 2. And then it deletes the record where empidis 1? (In short, =deletes all cascading child records first and then parentrecords) Is it =SQLServer's normal and expected behavior? Or it is notguaranteed? Or =anything else? Please guide. Thanks.Regards,Pravin

--=_NextPart_000_000D_01C374C4.4266C7F0--sql

Monday, March 26, 2012

question about process order

Hi all,
I have a trigger for update on a table. It does a whole bunch of things,
then calls a SP. I want to then check something afet the SP is completed. To
check this out I put a print statement after the line that called the SP
exec calcdeductables @.claimid, 1
print 'Check SIP'
The SP, by nature update multiple rows in child tables, as A result I would
expect the following:
(1 row(s) affected)
(1 row(s) affected)
(4 row(s) affected)
But with the print statement I am gettingh
(1 row(s) affected)
(1 row(s) affected)
Check SIP
(4 row(s) affected)
Should'nt the Check sip statement come last
Thanks
RobertRobert Bravery (me@.u.com) writes:
> I have a trigger for update on a table. It does a whole bunch of things,
> then calls a SP. I want to then check something afet the SP is
> completed. To check this out I put a print statement after the line that
> called the SP
> exec calcdeductables @.claimid, 1
> print 'Check SIP'
> The SP, by nature update multiple rows in child tables, as A result I
> would expect the following:
> (1 row(s) affected)
> (1 row(s) affected)
> (4 row(s) affected)
> But with the print statement I am gettingh
> (1 row(s) affected)
> (1 row(s) affected)
> Check SIP
> (4 row(s) affected)
> Should'nt the Check sip statement come last
A more interesting question is from where you got that @.claimid. If you
do
SELECT @.claimid = claimid FROM inserted
then you need to rewrite your trigger. A trigger fires once per statement,
and thus "inserted" can include more than more than one row.
If you believe that you know that your table will ever be subject to
single-row inserts and updates, add this to your trigger:
IF @.@.rowcount > 1
BEGIN
RAISERROR('Multi-row operations not supported!, 16, 1)
ROLLBACK TRANSACTION
RETURN
END
If you need to support multi-row operations, you can run a cursor over
"inserted" (and maybe this is what you do already). However, since a
trigger runs in the context of a transaction defined by the statement
that defined it, it is not a good idea to start looping in a trigger, as
this could cause contention issues.
The remedy would be to perform the updates directly the in trigger, in a
set-based fashion rather than running one by one from your procedure. If
you do that your question about output may be become a moot point.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Does the procedure end with that PRINT statement?
ML
http://milambda.blogspot.com/|||HI Erland
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97729620C5F6DYazorman@.127.0.0.1...
> Robert Bravery (me@.u.com) writes:
> A more interesting question is from where you got that @.claimid. If you
> do
> SELECT @.claimid = claimid FROM inserted
> then you need to rewrite your trigger. A trigger fires once per statement,
> and thus "inserted" can include more than more than one row.
> If you believe that you know that your table will ever be subject to
> single-row inserts and updates, add this to your trigger:
Yes I do get @.claimid from the inserted table. But it will alwaus be only
one record that is update. Just the nature of the operation.
Ok so then I need to qualify some things in my mind.
If a update trigger is ona table, and that table will always only have one
row updated at a time, the update trigger would fire, there is now an
inserted table, with the values on the update row in it. Now that trigger
then updates another table.
Do you no get a different inserted table, or is the same inserted table the
one created by the original insert, used.
Thanks for the single-row inserts little guidence you gave there
What I basically whant to do is that the update trigger on claims, updates
another table, which has say 4 rowse to update. Those four rows are related
to claim table. After that related table and its four rows are updates, I
want to perform and agregate check. All this will do is to notify the user
of an above or below threshold.
Thanks
Robert|||Robert Bravery (me@.u.com) writes:
> Yes I do get @.claimid from the inserted table. But it will alwaus be only
> one record that is update. Just the nature of the operation.
That's alright. But add an assertion to state that this is the case, to
prevent accidents.

> Ok so then I need to qualify some things in my mind.
> If a update trigger is ona table, and that table will always only have one
> row updated at a time, the update trigger would fire, there is now an
> inserted table, with the values on the update row in it. Now that trigger
> then updates another table.
> Do you no get a different inserted table, or is the same inserted table
> the one created by the original insert, used.
That's a different one. "inserted" and "deleted" are virtual tables, and
they are only visible within the trigger, nowhere else. So there is no
risk for confusion.

> What I basically whant to do is that the update trigger on claims,
> updates another table, which has say 4 rowse to update. Those four rows
> are related to claim table. After that related table and its four rows
> are updates, I want to perform and agregate check. All this will do is
> to notify the user of an above or below threshold.
I'm not sure that I understand exactly. I assumed that the PRINT was part
of some debug output. Are you now saying that the output is intended for
user consumption?
Generally, triggers are not supposed to generate any output (save for
error messages from integrity checks). It's perfectly legal to so, but
generally client code usually see INSERT, UPDATE, DELETE as operations
that do not delete data. Of course, if you code your application
accordingly, you can have output from the trigger. Still, I would consider
a different solution, if this is the case.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||HI Erland,
> That's a different one. "inserted" and "deleted" are virtual tables, and
> they are only visible within the trigger, nowhere else. So there is no
> risk for confusion.
Ok thanks, that makes it easier to understand

> I'm not sure that I understand exactly. I assumed that the PRINT was part
> of some debug output. Are you now saying that the output is intended for
> user consumption?
I did put it there for degug purposes, because I could not get what I wanted
correctly

> Generally, triggers are not supposed to generate any output (save for
> error messages from integrity checks). It's perfectly legal to so, but
> generally client code usually see INSERT, UPDATE, DELETE as operations
> that do not delete data. Of course, if you code your application
> accordingly, you can have output from the trigger. Still, I would consider
> a different solution, if this is the case.
Basically the output would only be a type of informational message
ok in psudo code
the SP would update a table called liability, based on amounts entered into
the claim table.
After the SP has update the needed rows, I would like, to perhaps call
another SP, so
declare @.liab numeric(12,2), @.ded numeric(12,2)
select @.liab=sum(ammount) from claimliability where claimid = 10
select @.ded = ammount from ClaimDeductables where claimid = 10 and clmdedid=
70
if @.liab<@.ded
raiserror('Claim does not partake in SIP', 10,1)
Is this feasable, or am I going about it the wrong way
Thanks
Robert|||HI,
No the Print statement, debug for the time being, is in the trigger and is
after the exec of the SO
Thanks
RObert
"ML" <ML@.discussions.microsoft.com> wrote in message
news:0E2F2CE4-C6DD-496C-8200-73F3BDDFC489@.microsoft.com...
> Does the procedure end with that PRINT statement?
>
> ML
> --
> http://milambda.blogspot.com/|||Since I can't see the entire code used in the process you're debugging, try
putting a PRINT statement at the end of each procedure - e.g. "print
'<procedure name> done."
Are there more triggers?
ML
http://milambda.blogspot.com/|||>The SP, by nature update multiple rows in child tables, as A result I would
>expect the following:
>(1 row(s) affected)
>(1 row(s) affected)
>(4 row(s) affected)
>But with the print statement I am gettingh
>(1 row(s) affected)
>(1 row(s) affected)
>Check SIP
>(4 row(s) affected)
>Should'nt the Check sip statement come last
I have the ouput of PRINT appear out of order relative to the retults
from SELECT (or UPDATE or INSERT) all the time. They apparently are
buffered independently. This is annoying, and I have never seen it
documented, but it is expected and nothing to worry about.
Roy|||HI Roy,
Thanks. THats reassuring
Robert
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:sirpv1duj8up6ssb9as694g5fpntjhq9li@.
4ax.com...
would
> I have the ouput of PRINT appear out of order relative to the retults
> from SELECT (or UPDATE or INSERT) all the time. They apparently are
> buffered independently. This is annoying, and I have never seen it
> documented, but it is expected and nothing to worry about.
> Roy

Question about Primary key

Hi there

Ok here is my problem.
Lets say i have 20 rows in my table and i delete 19 and add 1.
The primary key's will be 1 and 21, my question is how do i get it to say 1 and 2 ?

looks like your table primary key is an identity column.

Why do you want to do this ? What if you delete a column and add another again ? Do you want to keep repeating this ?

|||Looks like you need:

DBCC

CHECKIDENT(tableName, RESEED, new_identity_value)

Make sure to set the new identity value to the max of the current identity fields. i.e. run something with logic like:

var maxIdentity = SELECT MAX(ID) FROM tableName

DBCC CHECKIDENT(tableName, RESEED, maxIdentity)

If your last identity was 1 after the delete, the next row entered will be 2.

question about primary key

how can I set my primary key to star again from 1' for example, if I had a
table with some rows in it and I deleted all that rows, next time when I
insert a new row in that table, primary key starts from last primary key of
previous rows, but I want it to start again from 1.
is that possible'
thanks!Bernard,
presumably you mean that your primary key has an identity attribute? In this
case if you truncate the table the seed is reset, or you can use DBCC
CHECKIDENT :
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED)
GO
to get the original seed value (usually 1)
or
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 30)
GO
to set the initial value to be 30.
HTH,
Paul Ibisonsql

question about performance of partition tables

1. When I partition a table ( 90 million rows ) into
five partitions. (create view as select * from table1
union select * from table 2 union ...)
I get a 2 x times query time increase, but by the time
#partitions = 10, the query time is same as the large
table. when #partitions = 15 query time > large table.
Ideally I would want to partition it into 50 states.
Why is it that parallel query execution does not speedup
the process.
The indexes are virtually the same ( most cases covering
non-clustered indexes.)
Is it because of merging the results and sorting them
( because of the group by/order by clauses )? however
the query plan says those are 0%.
You can ask me any question about the query statement,
table format and query plan execution.
I thought about this for a week and don't have an answer.There will always come a point where the resources will be overwhelmed byt
he requests. The more queries you attempt to do simultaneously the less
chances are they will each perform as well as running by them selves. At
some point the cpu or disk queues will start playing a factor. You also
have more overhead when trying to put all the results together and have more
chance of tempdb being a factor.
Andrew J. Kelly
SQL Server MVP
"Ramesh" <anonymous@.discussions.microsoft.com> wrote in message
news:119d201c3f56c$fba82640$a001280a@.phx
.gbl...
> 1. When I partition a table ( 90 million rows ) into
> five partitions. (create view as select * from table1
> union select * from table 2 union ...)
> I get a 2 x times query time increase, but by the time
> #partitions = 10, the query time is same as the large
> table. when #partitions = 15 query time > large table.
> Ideally I would want to partition it into 50 states.
> Why is it that parallel query execution does not speedup
> the process.
> The indexes are virtually the same ( most cases covering
> non-clustered indexes.)
> Is it because of merging the results and sorting them
> ( because of the group by/order by clauses )? however
> the query plan says those are 0%.
> You can ask me any question about the query statement,
> table format and query plan execution.
> I thought about this for a week and don't have an answer.|||cpu utilization seems low : < 40 %
disk I/O queues < 15 on RAID-5 containing tables
(6 disks) and < 3 on RAID-10 containing indexes
(6 disks)
tempdb and log on seperate RAID-10 ( disk queues < 2)
(4 disks)
It is true that queries which only use indexes (covering)
work well upto 10 partitions
queries which would need to use leaf keys and then use
clustering keys work well upto 5 partitions.
Is there something I should know on creating the
clustered/non-clustered indexes different on the
partitioned tables?
Is there some material I can read on?
Is the Windows 2000 server be part of the bottleneck?
Is there any specific parameters I should target and
resolve. ( I am aiming for 60 partitions : each with
1-2 million rows each to reduce skewness). I thought this
would speed up the queries 2 orders of magnitude. Sadly
this does not work the way I calculated.

>--Original Message--
>There will always come a point where the resources will
be overwhelmed byt
>he requests. The more queries you attempt to do
simultaneously the less
>chances are they will each perform as well as running by
them selves. At
>some point the cpu or disk queues will start playing a
factor. You also
>have more overhead when trying to put all the results
together and have more
>chance of tempdb being a factor.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Ramesh" <anonymous@.discussions.microsoft.com> wrote in
message
> news:119d201c3f56c$fba82640$a001280a@.phx
.gbl...
time
speedup
covering
answer.
>
>.
>|||Disk queues of 15 or so on a 6 disk Raid 5 are still high enough to warrant
paying attention to them. That means you are in fact waiting on disk I/O.
You might get better results from combining all 12 disks from the indexes
and data into one Raid 10. As for the partitioning that is tough to say.
Most partitioning requires a lot of testing under your specific conditions
to determine which is best for you. Are you sure you need to partition that
table at all? 50 partitions is a lot and you only have 90 million rows.
What are the typical queries like against this table? Maybe just adjusting
the clustered index will do.
Andrew J. Kelly
SQL Server MVP
"Ramesh Krishnan" <anonymous@.discussions.microsoft.com> wrote in message
news:11d6201c3f62b$ea334100$a301280a@.phx
.gbl...
> cpu utilization seems low : < 40 %
> disk I/O queues < 15 on RAID-5 containing tables
> (6 disks) and < 3 on RAID-10 containing indexes
> (6 disks)
> tempdb and log on seperate RAID-10 ( disk queues < 2)
> (4 disks)
> It is true that queries which only use indexes (covering)
> work well upto 10 partitions
> queries which would need to use leaf keys and then use
> clustering keys work well upto 5 partitions.
> Is there something I should know on creating the
> clustered/non-clustered indexes different on the
> partitioned tables?
> Is there some material I can read on?
> Is the Windows 2000 server be part of the bottleneck?
> Is there any specific parameters I should target and
> resolve. ( I am aiming for 60 partitions : each with
> 1-2 million rows each to reduce skewness). I thought this
> would speed up the queries 2 orders of magnitude. Sadly
> this does not work the way I calculated.
>
> be overwhelmed byt
> simultaneously the less
> them selves. At
> factor. You also
> together and have more
> message
> time
> speedup
> covering
> answer.

question about performance of partition tables

1. When I partition a table ( 90 million rows ) into
five partitions. (create view as select * from table1
union select * from table 2 union ...)
I get a 2 x times query time increase, but by the time
#partitions = 10, the query time is same as the large
table. when #partitions = 15 query time > large table.
Ideally I would want to partition it into 50 states.
Why is it that parallel query execution does not speedup
the process.
The indexes are virtually the same ( most cases covering
non-clustered indexes.)
Is it because of merging the results and sorting them
( because of the group by/order by clauses )? however
the query plan says those are 0%.
You can ask me any question about the query statement,
table format and query plan execution.
I thought about this for a week and don't have an answer.There will always come a point where the resources will be overwhelmed byt
he requests. The more queries you attempt to do simultaneously the less
chances are they will each perform as well as running by them selves. At
some point the cpu or disk queues will start playing a factor. You also
have more overhead when trying to put all the results together and have more
chance of tempdb being a factor.
--
Andrew J. Kelly
SQL Server MVP
"Ramesh" <anonymous@.discussions.microsoft.com> wrote in message
news:119d201c3f56c$fba82640$a001280a@.phx.gbl...
> 1. When I partition a table ( 90 million rows ) into
> five partitions. (create view as select * from table1
> union select * from table 2 union ...)
> I get a 2 x times query time increase, but by the time
> #partitions = 10, the query time is same as the large
> table. when #partitions = 15 query time > large table.
> Ideally I would want to partition it into 50 states.
> Why is it that parallel query execution does not speedup
> the process.
> The indexes are virtually the same ( most cases covering
> non-clustered indexes.)
> Is it because of merging the results and sorting them
> ( because of the group by/order by clauses )? however
> the query plan says those are 0%.
> You can ask me any question about the query statement,
> table format and query plan execution.
> I thought about this for a week and don't have an answer.|||cpu utilization seems low : < 40 %
disk I/O queues < 15 on RAID-5 containing tables
(6 disks) and < 3 on RAID-10 containing indexes
(6 disks)
tempdb and log on seperate RAID-10 ( disk queues < 2)
(4 disks)
It is true that queries which only use indexes (covering)
work well upto 10 partitions
queries which would need to use leaf keys and then use
clustering keys work well upto 5 partitions.
Is there something I should know on creating the
clustered/non-clustered indexes different on the
partitioned tables?
Is there some material I can read on?
Is the windows 2000 server be part of the bottleneck?
Is there any specific parameters I should target and
resolve. ( I am aiming for 60 partitions : each with
1-2 million rows each to reduce skewness). I thought this
would speed up the queries 2 orders of magnitude. Sadly
this does not work the way I calculated.
>--Original Message--
>There will always come a point where the resources will
be overwhelmed byt
>he requests. The more queries you attempt to do
simultaneously the less
>chances are they will each perform as well as running by
them selves. At
>some point the cpu or disk queues will start playing a
factor. You also
>have more overhead when trying to put all the results
together and have more
>chance of tempdb being a factor.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Ramesh" <anonymous@.discussions.microsoft.com> wrote in
message
>news:119d201c3f56c$fba82640$a001280a@.phx.gbl...
>> 1. When I partition a table ( 90 million rows ) into
>> five partitions. (create view as select * from table1
>> union select * from table 2 union ...)
>> I get a 2 x times query time increase, but by the
time
>> #partitions = 10, the query time is same as the large
>> table. when #partitions = 15 query time > large table.
>> Ideally I would want to partition it into 50 states.
>> Why is it that parallel query execution does not
speedup
>> the process.
>> The indexes are virtually the same ( most cases
covering
>> non-clustered indexes.)
>> Is it because of merging the results and sorting them
>> ( because of the group by/order by clauses )? however
>> the query plan says those are 0%.
>> You can ask me any question about the query statement,
>> table format and query plan execution.
>> I thought about this for a week and don't have an
answer.
>
>.
>|||Disk queues of 15 or so on a 6 disk Raid 5 are still high enough to warrant
paying attention to them. That means you are in fact waiting on disk I/O.
You might get better results from combining all 12 disks from the indexes
and data into one Raid 10. As for the partitioning that is tough to say.
Most partitioning requires a lot of testing under your specific conditions
to determine which is best for you. Are you sure you need to partition that
table at all? 50 partitions is a lot and you only have 90 million rows.
What are the typical queries like against this table? Maybe just adjusting
the clustered index will do.
--
Andrew J. Kelly
SQL Server MVP
"Ramesh Krishnan" <anonymous@.discussions.microsoft.com> wrote in message
news:11d6201c3f62b$ea334100$a301280a@.phx.gbl...
> cpu utilization seems low : < 40 %
> disk I/O queues < 15 on RAID-5 containing tables
> (6 disks) and < 3 on RAID-10 containing indexes
> (6 disks)
> tempdb and log on seperate RAID-10 ( disk queues < 2)
> (4 disks)
> It is true that queries which only use indexes (covering)
> work well upto 10 partitions
> queries which would need to use leaf keys and then use
> clustering keys work well upto 5 partitions.
> Is there something I should know on creating the
> clustered/non-clustered indexes different on the
> partitioned tables?
> Is there some material I can read on?
> Is the windows 2000 server be part of the bottleneck?
> Is there any specific parameters I should target and
> resolve. ( I am aiming for 60 partitions : each with
> 1-2 million rows each to reduce skewness). I thought this
> would speed up the queries 2 orders of magnitude. Sadly
> this does not work the way I calculated.
> >--Original Message--
> >There will always come a point where the resources will
> be overwhelmed byt
> >he requests. The more queries you attempt to do
> simultaneously the less
> >chances are they will each perform as well as running by
> them selves. At
> >some point the cpu or disk queues will start playing a
> factor. You also
> >have more overhead when trying to put all the results
> together and have more
> >chance of tempdb being a factor.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Ramesh" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:119d201c3f56c$fba82640$a001280a@.phx.gbl...
> >> 1. When I partition a table ( 90 million rows ) into
> >> five partitions. (create view as select * from table1
> >> union select * from table 2 union ...)
> >> I get a 2 x times query time increase, but by the
> time
> >> #partitions = 10, the query time is same as the large
> >> table. when #partitions = 15 query time > large table.
> >>
> >> Ideally I would want to partition it into 50 states.
> >> Why is it that parallel query execution does not
> speedup
> >> the process.
> >>
> >> The indexes are virtually the same ( most cases
> covering
> >> non-clustered indexes.)
> >> Is it because of merging the results and sorting them
> >> ( because of the group by/order by clauses )? however
> >> the query plan says those are 0%.
> >>
> >> You can ask me any question about the query statement,
> >> table format and query plan execution.
> >>
> >> I thought about this for a week and don't have an
> answer.
> >
> >
> >.
> >sql

Question about performance

I'm using a tableadapter for my update operations.

The application i'm using typically creates 2500 dirty rows for a single table and then updates using the tableadapter.Update(). The tableadapter.update() method takes about 50 seconds to complete, which is way too long.

This is the only query running agaist the sql server 2000 database ( as this is a test server only), so i'm not sure if it is a problem with sql server or a problem with the tableadapter. Can anyone recomment how to improve performance for updating with the tableadapter and sql 2000?

Thanks in advance.

How big is each row? Was the CPU fully utilized? Is the client remote or local? For performance related issue, if you can describe your configuration and share out the code, it will be helpful.|||

The cpu isn't fully being utilzed on the sql server machine.

Here is the exact process:

There are 3 tables for this process. I basically create the information inside the dataset, and then postback to the server with all changes. Some of those changes are creating new rows and retrieving identities as well.

Example schema:

Table1 ( ID1 (identity, pk), 3 misc rows)

Table2 ( ID2 (Identity, pk,), ID1 (ForiegnKey)

Table 3 ( ID2 (ForigneKey) )

So, Table1 is parent to Table2 and Table3 is parent to table 2.

The user will create a row in Table1. The ID of Table1 is set to 0 as a temp Primary Key until it can resolve the identity when it hits the db. The user will then create on average 300 rows in table 2. Table2's ID is set to 0 as a temp Primary Key until it can resolve the identity when it hits the db. Next, the user creates 6 rows (on average) for every ID in Table2 ( 6 * 300).

So, then when you update this dataset, you're basically udpating Table1, getting the ID's from the database and cascading them down to Table2. Table2 will update and recieve it's true identities and then pass it down to table 3. Or something like that. So, it takes about a minute for this to update... Is my methodology fuzzy?

Please let me know if you need anymore detail!

|||

I still haven't solved this problem and maybe there is no solution. But I'll try one more time to clarify.

http://www.condoresorts.com/sample.jpg

Ok, so here is the process:

1. A user will create a time span that will be entered into the RoomInfo table. The start date is stored in Arrival column and end date stored in Departure column.

2. The time span which is stored in RoomInfo (Departure date - Arrival Date) will get expanded for each day and will be stored in Room_days.

3. Then the individual days for a timespan can then be assigned one to many people in the RoomDay_Customer table.

* RoomInfo and Room_Days primary key is an identity.

The problem is if you enter a timespan that spans more then 6 months, the update is really slow using the tableadapter.Update method.

A six month time span creates the following data:

1 entry in RoomInfo

180 entries in Room_Days ( on average)

540 entries in RoomDay_Customer ( for 3 people for example)

The only bottleneck I can think of is the identities. Is there a more efficenet way to update a typed dataset aside from calling its update method?

Question about performance

I'm using a tableadapter for my update operations.

Thanks in advance.

How big is each row? Was the CPU fully utilized? Is the client remote or local? For performance related issue, if you can describe your configuration and share out the code, it will be helpful.|||

The cpu isn't fully being utilzed on the sql server machine.

Here is the exact process:

Example schema:

Table1 ( ID1 (identity, pk), 3 misc rows)

Table2 ( ID2 (Identity, pk,), ID1 (ForiegnKey)

Table 3 ( ID2 (ForigneKey) )

So, Table1 is parent to Table2 and Table3 is parent to table 2.

Please let me know if you need anymore detail!

|||

I still haven't solved this problem and maybe there is no solution. But I'll try one more time to clarify.

http://www.condoresorts.com/sample.jpg

Ok, so here is the process:

1. A user will create a time span that will be entered into the RoomInfo table. The start date is stored in Arrival column and end date stored in Departure column.

2. The time span which is stored in RoomInfo (Departure date - Arrival Date) will get expanded for each day and will be stored in Room_days.

3. Then the individual days for a timespan can then be assigned one to many people in the RoomDay_Customer table.

* RoomInfo and Room_Days primary key is an identity.

The problem is if you enter a timespan that spans more then 6 months, the update is really slow using the tableadapter.Update method.

A six month time span creates the following data:

1 entry in RoomInfo

180 entries in Room_Days ( on average)

540 entries in RoomDay_Customer ( for 3 people for example)

The only bottleneck I can think of is the identities. Is there a more efficenet way to update a typed dataset aside from calling its update method?

Question about OUTPUT clause

In the past when inserting a record into a table with an identity column, to return the new identity value I used one of SCOPE_IDENTITY, IDENT_CURRENT, and @.@.IDENTITY.

Question: will this sql 2005 approach also provide the newly added identity value?

insert into TestTable -- ID column of testtable is an Identity field

output inserted.id, inserted.col1 into @.insertedRecords

values('row 20')

select ID from @.insertedRecords

TIA,

Barkingdog

Sure can. Did your attempts not work? Here is a script to demo:

create table testTable
(
testTableId int identity primary key,
value varchar(10) unique
)
go

declare @.insertedRows table (
testTableId int primary key,
value varchar(10) unique
)

insert into testTable (value) -- ID column of testtable is an Identity field
output inserted.testTableId, inserted.value into @.insertedRows
values('row 20')

select scope_identity()
select * from @.insertedRows

declare @.insertedRows table (
testTableId int primary key,
value varchar(10) unique
)

insert into testTable (value) -- ID column of testtable is an Identity field
output inserted.testTableId, inserted.value into @.insertedRows
select 'row 21'
union all
select 'row 22'

select * from @.insertedRows

|||

I haven't encoutnered any problems but I was just wondering if my original thought was correct.

Thanks.

Barkingdog

sql

Friday, March 23, 2012

Question about Neural Networks

I created a test table (name - "Nset") with the columns:
id (int), n1 (float), n2 (float), n3 (float) and c1 (varchar).
Then filled a table the followings information:
id n1 n2 n3 c1

1 0,1 0,1 0,6 one

2 0,2 0,1 0,5 one

3 0,7 0,5 0,1 two

4 0,4 0,9 0,3 two

5 0,5 0,1 0,5 three

And created a neural network with tuning by default. "id"-field is the key. n1, n2 and n3 are inputs. c1 - predict.

Then i tryed predict query, like:

SELECT

PREDICT([Nset].[c1])

FROM

[Nset]

NATURAL PREDICTION JOIN

(SELECT 0,5 AS [n1], 0,1 AS [n2], 0,5 AS [n3]) AS t

The result is "three". This is correct. And some other tests appeared correct.

But, when I filled the column c1 with numerical values (one = 1, two=2, three=3) and changed type to int, a predict query left off to work correctly.

Previous query return 4.

And other tests showed that a value returned large on unit.

Is this correct?

Thanks.

Hello

It can be explained. The algorithm actually works correctly, the models are different.

Here is what happens:

first case: "one", "two", "three" are strings, so the mining structure / model creation wizard decides that they should be treated as discrete. The net is trained to fit particular states of a distinct attribute (c1). State "three" is only described by one training point (the last row) so the weights of the network are optimized (actually, over-trained) to predict "three" for the combination of inputs that appears in the last row. This is why the prediction result is "three"

second case: 1, 2 and 3 are this time numbers. The wizard decides that, as numeric values, they should be treated as Continuous. You can change this behavior (the wizard only makes a suggestion) in one of the wizard pages. As the target is now a continuous variable, the network is not optimized independently for the distinct state '3'. It actually is optimized for the whole surface defined by the training points.

You can fix it immediately by changing, in the wizard, the content type for the target column, c1, from Continuous to Discrete.

The differences between results should reduce as more training cases are presented to the network

Hope this clarifies the issue

|||Thanks! Now working properly!|||

And how it is better to work with variable neurons number?

It is similar to the previous case, but the table in a following kind:

case_id

input_name

input_weight

output_val

0,1

0,6

0,2

0,1

0,5

0,7

0,5

0,1

0,4

0,9

0,3

0,5

0,1

0,5

I can't use PIVOT of SQL Server, because i don't know neurons count...

I suppose, that i need to build the cube. And after that to build Neural Network model.

It is correct? There Is any other way?

|||

You could model like below:

case_id n1 n2 n3 output

1 0.1 0.1 0.6 1

2 ...

Alternatively, you could use the nested table feature and create a model like below. This would work if you do not know the number of inputs before modeling

(
Case_ID long key,
Output long continuous predict,
inputs Table
(
neuron text key,
weight double continuous
)
)

Assuming that you have a data source view containing the table above, you can train the mining structure/model self-joining the table with itself (using Case_ID as Key and then as Foreign Key).

In the wizard, you can specify the same table as both Case and Nested as long as you define a one-to-many reflexive relationship from case_id to case_id.

|||

Thank you for help again!

All works except for a Neural Network Viewer.

The NN Viewer gives out a message:

"Execution of the managed stored procedure GetAttributeScores failed with the following error: Exception has been thrown by the target of an invocation.Input string was not in a correct format..."

But it is not important. Prediction query works correct.

Once again thanks!

|||

Glad to hear it works

> All works except for a Neural Network Viewer

Some viewers problems were fixed in SP2 of SQL Server 2005, which should be available publicly soon

Question about Neural Networks

I created a test table (name - "Nset") with the columns:
id (int), n1 (float), n2 (float), n3 (float) and c1 (varchar).
Then filled a table the followings information:
id n1 n2 n3 c1

1 0,1 0,1 0,6 one

2 0,2 0,1 0,5 one

3 0,7 0,5 0,1 two

4 0,4 0,9 0,3 two

5 0,5 0,1 0,5 three

And created a neural network with tuning by default. "id"-field is the key. n1, n2 and n3 are inputs. c1 - predict.

Then i tryed predict query, like:

SELECT

PREDICT([Nset].[c1])

FROM

[Nset]

NATURAL PREDICTION JOIN

(SELECT 0,5 AS [n1], 0,1 AS [n2], 0,5 AS [n3]) AS t

The result is "three". This is correct. And some other tests appeared correct.

But, when I filled the column c1 with numerical values (one = 1, two=2, three=3) and changed type to int, a predict query left off to work correctly.

Previous query return 4.

And other tests showed that a value returned large on unit.

Is this correct?

Thanks.

Hello

It can be explained. The algorithm actually works correctly, the models are different.

Here is what happens:

You can fix it immediately by changing, in the wizard, the content type for the target column, c1, from Continuous to Discrete.

The differences between results should reduce as more training cases are presented to the network

Hope this clarifies the issue

|||Thanks! Now working properly!|||

And how it is better to work with variable neurons number?

It is similar to the previous case, but the table in a following kind:

case_id

input_name

input_weight

output_val

0,1

0,6

0,2

0,1

0,5

0,7

0,5

0,1

0,4

0,9

0,3

0,5

0,1

0,5

I can't use PIVOT of SQL Server, because i don't know neurons count...

I suppose, that i need to build the cube. And after that to build Neural Network model.

It is correct? There Is any other way?

|||

You could model like below:

case_id n1 n2 n3 output

1 0.1 0.1 0.6 1

2 ...

Alternatively, you could use the nested table feature and create a model like below. This would work if you do not know the number of inputs before modeling

(
Case_ID long key,
Output long continuous predict,
inputs Table
(
neuron text key,
weight double continuous
)
)

Assuming that you have a data source view containing the table above, you can train the mining structure/model self-joining the table with itself (using Case_ID as Key and then as Foreign Key).

In the wizard, you can specify the same table as both Case and Nested as long as you define a one-to-many reflexive relationship from case_id to case_id.

|||

Thank you for help again!

All works except for a Neural Network Viewer.

The NN Viewer gives out a message:

"Execution of the managed stored procedure GetAttributeScores failed with the following error: Exception has been thrown by the target of an invocation.Input string was not in a correct format..."

But it is not important. Prediction query works correct.

Once again thanks!

|||

Glad to hear it works

> All works except for a Neural Network Viewer

Some viewers problems were fixed in SP2 of SQL Server 2005, which should be available publicly soon

question about Msg 20068

Is there any work arounds to replicate a table that has more then 255
columns?
TIA,
Joe,
the most common suggestion is to partition the table and replicate it as 2
articles. Although far from ideal, this can be made transparent to the front
end by using views to amalgamate the data.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks for the tip. I have a follow up question, if I partition the
table, will each partition need a primary key assigment?
thanks,
Joe
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uCzhYskWFHA.2664@.TK2MSFTNGP15.phx.gbl...
> Joe,
> the most common suggestion is to partition the table and replicate it as 2
> articles. Although far from ideal, this can be made transparent to the
front
> end by using views to amalgamate the data.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Joe,
this is correct, although the relationship will be 1:1. You can use a view
to amalgamate the data and an instead-of trigger to insert the data to the 2
tables.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Question about move large amount of data from database to database

guys,

I have a project need to move more than 100,000 records from one
database table to another database table every week. Currently, users
input date range from web UI, my store procedure will take those date
ranges to INSERT records to a table in another database, then delete
the records, but it will take really long time to finish this action
(up to 1 or 2 hours).

My question is if there is some other way I should do to speed up the
action, I am thinking about use bcp to copy those records to datafile
and then use bcp to insert it into SQL Server table. Is this the right
way to do it or should I consider other solution (then, what is the
solution.)

Thanks a lot!On Apr 23, 2:23 pm, Lee <lee.jenkins...@.gmail.comwrote:

Quote:

Originally Posted by

guys,
>
I have a project need to move more than 100,000 records from one
database table to another database table every week. Currently, users
input date range from web UI, my store procedure will take those date
ranges to INSERT records to a table in another database, then delete
the records, but it will take really long time to finish this action
(up to 1 or 2 hours).
>
My question is if there is some other way I should do to speed up the
action, I am thinking about use bcp to copy those records to datafile
and then use bcp to insert it into SQL Server table. Is this the right
way to do it or should I consider other solution (then, what is the
solution.)
>
Thanks a lot!

Use a Select Into statement and make sure the destination db is set to
a simple recovery model.|||Yes, BCP will be a good option for fast data transfer. All of the BULK
operations (BULK INSERT, SELECT INTO, BCP) are minimally logged when a non
FULL recovery model is set.

Another issue could be the purging of the archived records from your main
table. If you have it as a single DELETE and it takes long time to complete,
then you can break it into smaller DELETE chunks.

If you have SQL Server 2005 Enterprise Edition, an interesting alternative
is to use partitioned tables. Specifically range partitions based on date
ranges (in your case could be weekly) can help with archiving. Take a look
at the following article (in particular the section about Range Partitions):
http://msdn2.microsoft.com/en-us/library/ms345146.aspx
HTH,

Plamen Ratchev
http://www.SQLStudio.com|||Lee (lee.jenkins.ca@.gmail.com) writes:

Quote:

Originally Posted by

It shouldn't take 1-2 hours to move 100.000 rows. It sounds like the
process is not well implemented, or that there are indexes missing. Yes,
you can gain speed by using BCP, but you also add complexity to the
solution that I can't really see should be needed with the volumes you
indicate?

Would it be possible for you to post the definition of the tables, including
indexes and the stored procedure?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Plamen, Thanks a lot. I will try it and let you know the result.
Thanks again!

On Apr 23, 2:08 pm, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

Yes, BCP will be a good option for fast data transfer. All of the BULK
operations (BULK INSERT, SELECT INTO, BCP) are minimally logged when a non
FULL recovery model is set.
>
Another issue could be the purging of the archived records from your main
table. If you have it as a single DELETE and it takes long time to complete,
then you can break it into smaller DELETE chunks.
>
If you have SQL Server 2005 Enterprise Edition, an interesting alternative
is to use partitioned tables. Specifically range partitions based on date
ranges (in your case could be weekly) can help with archiving. Take a look
at the following article (in particular the section about Range Partitions):http://msdn2.microsoft.com/en-us/library/ms345146.aspx
>
HTH,
>
Plamen Ratchevhttp://www.SQLStudio.com

|||Erland, Thanks a lot for the reply, also forgot to say thanks to Brad,
Here is the table:

CREATE TABLE [dbo].[tbl_record](
[record_id] [int] IDENTITY(1,1) NOT NULL,
[record_CC_id] [int] NOT NULL,
[record_content] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL CONSTRAINT [DF_tbl_record_record_content] DEFAULT (''),
[record_date] [datetime] NOT NULL CONSTRAINT
[DF_tbl_record_record_date] DEFAULT (getdate()),
[record_ip] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF_tbl_record_record_ip] DEFAULT (''),
[record_active] [bit] NOT NULL CONSTRAINT
[DF_tbl_record_record_archive] DEFAULT (1),
CONSTRAINT [PK_tbl_record] PRIMARY KEY CLUSTERED
(
[record_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON
[PRIMARY]
) ON [PRIMARY]

And The stored procedure is here:

ALTER PROCEDURE [dbo].[ArchiveRecords]
(
@.ddate datetime
)
AS
BEGIN TRAN
SET IDENTITY_INSERT record_archive.dbo.tbl_record_archive ON;
INSERT INTO record_archive.dbo.tbl_record_archive
(
record_id,
record_CC_id,
record_content,
record_date,
record_ip,
record_active
)
SELECT
record_id,
record_CC_id,
record_content,
record_date,
record_ip,
record_active
FROM tbl_record WHERE record_date <= @.ddate;
DELETE FROM tbl_record WHERE record_date <= @.ddate;
SET IDENTITY_INSERT record_archive.dbo.tbl_record_archive OFF;
IF @.@.ERROR = 0 BEGIN COMMIT TRAN END ELSE BEGIN ROLLBACK TRAN END

On Apr 23, 2:31 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Lee (lee.jenkins...@.gmail.com) writes:

Quote:

Originally Posted by

>
It shouldn't take 1-2 hours to move 100.000 rows. It sounds like the
process is not well implemented, or that there are indexes missing. Yes,
you can gain speed by using BCP, but you also add complexity to the
solution that I can't really see should be needed with the volumes you
indicate?
>
Would it be possible for you to post the definition of the tables, including
indexes and the stored procedure?
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

|||Should I remove the clusterd index on the record_id field and create
nonclustered index on this field and create a clustered index on
record_date field since in my query, I always select a range of data
by date.

On Apr 23, 3:16 pm, Lee <lee.jenkins...@.gmail.comwrote:

Quote:

Originally Posted by

Erland, Thanks a lot for the reply, also forgot to say thanks to Brad,
Here is the table:
>
CREATE TABLE [dbo].[tbl_record](
[record_id] [int] IDENTITY(1,1) NOT NULL,
[record_CC_id] [int] NOT NULL,
[record_content] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL CONSTRAINT [DF_tbl_record_record_content] DEFAULT (''),
[record_date] [datetime] NOT NULL CONSTRAINT
[DF_tbl_record_record_date] DEFAULT (getdate()),
[record_ip] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF_tbl_record_record_ip] DEFAULT (''),
[record_active] [bit] NOT NULL CONSTRAINT
[DF_tbl_record_record_archive] DEFAULT (1),
CONSTRAINT [PK_tbl_record] PRIMARY KEY CLUSTERED
(
[record_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON
[PRIMARY]
) ON [PRIMARY]
>
And The stored procedure is here:
>
ALTER PROCEDURE [dbo].[ArchiveRecords]
(
@.ddate datetime
)
AS
BEGIN TRAN
SET IDENTITY_INSERT record_archive.dbo.tbl_record_archive ON;
INSERT INTO record_archive.dbo.tbl_record_archive
(
record_id,
record_CC_id,
record_content,
record_date,
record_ip,
record_active
)
SELECT
record_id,
record_CC_id,
record_content,
record_date,
record_ip,
record_active
FROM tbl_record WHERE record_date <= @.ddate;
DELETE FROM tbl_record WHERE record_date <= @.ddate;
SET IDENTITY_INSERT record_archive.dbo.tbl_record_archive OFF;
IF @.@.ERROR = 0 BEGIN COMMIT TRAN END ELSE BEGIN ROLLBACK TRAN END
>
On Apr 23, 2:31 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:
>
>
>

Quote:

Originally Posted by

Lee (lee.jenkins...@.gmail.com) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

Would it be possible for you to post the definition of the tables, including
indexes and the stored procedure?

Quote:

Originally Posted by

--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se

Quote:

Originally Posted by

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -

>
- Show quoted text -

|||Brad, Thanks for the reply, my situation is the target table already
have lots of records and I will just append the data to that table.

On Apr 23, 1:58 pm, Brad <Brad.Marsh...@.Teksouth.comwrote:

Quote:

Originally Posted by

On Apr 23, 2:23 pm, Lee <lee.jenkins...@.gmail.comwrote:
>
>
>
>
>

Quote:

Originally Posted by

guys,

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

Thanks a lot!

>
Use a Select Into statement and make sure the destination db is set to
a simple recovery model.- Hide quoted text -
>
- Show quoted text -

|||Lee (lee.jenkins.ca@.gmail.com) writes:

Quote:

Originally Posted by

Should I remove the clusterd index on the record_id field and create
nonclustered index on this field and create a clustered index on
record_date field since in my query, I always select a range of data
by date.

Yes, that was precisely my reaction when I saw the table. Make the primary
key on record_id non-clustered, and add a clustered index on the date
column. I would guess you should do this on the archive table as well.

Also, I don't see the point with having the IDENTITY property on the
archive table. Just make it a normal column, and you don't need that
SET IDENTITY_INSERT. Not that it affects performance, but it looks cleaner.
However to change this, you would need rename the existing table, create
it a new and copy over. There is no ALTER syntax for changing the
IDENTITY property.

Question about MAX()

I have a table which has case numbers and version numbers. The same case number has different version numbers. I was wondering if there was a way to select all the case numbers with the highest version numbers. Pretty much what I am looking for is as follows:

TABLECASES:

CaseNumber VersionNum

1234 1

1234 2

1234 3

3567 1

3567 2

Upon running my query..I want something as follows:

CaseNumber VersionNum

1234 3

3567 2

Can sombody point me how to construct the query..thanks

SELECT CASENUMBER, MAX(VERSIONNUM)
FROM TABLECASES
GROUP BY CASENUMBER

It should be that simple,

Roberto Hernández-Pou
http://community.rhpconsulting.net

|||

Thanks a lot for this prompt response. Now suppose I have additional information in this table that differs from version to version..something as follows:

TABLECASES:

CaseNumber VersionNum VersionInfo

1234 1 finapp

1234 2 reopened

1234 3 closed

3567 1 finapp

3567 2 reopened

Now if I use the query that you suggested which is

SELECT CASENUMBER, MAX(VERSIONNUM)
FROM TABLECASES
GROUP BY CASENUMBER

I get the following:

CaseNumber VersionNum

1234 3

3567 2

Now..what I want instead is something like:

CaseNumber VersionInfo

1234 closed

3567 reopened

The version info is contingent upon the version number..is there a way to modify the sql to do that...thanks

|||

SELECT a.CASENUMBER, a.VersionInfo FROM TABLECASES a INNER JOIN (SELECT CASENUMBER, MAX(VERSIONNUM) as maxVersionNum FROM TABLECASES GROUP BY CASENUMBER) b ON a.CaseNumber =b.CaseNumber AND VERSIONNUM=b.maxVERSIONNUM

|||If you use SQL Server 2005, you can also do this:

SELECT a.CASENUMBER, a.VersionInfo FROM

(SELECT CASENUMBER, VersionInfo , ROW_Number() OVER(partition by CASENUMBER ORDER BY VERSIONNUM DESC) as rankNum

FROM TABLECASES) AS a

WHERE a.rankNum=1

Question about Max function

I have table name datagraph
dat price
08/30/2004 23
09/1/2004 100
09/1/2004 21
09/1/2004 12
09/1/2004 32

I want to write Sql that show the highest price of today. I tried

sql="select max(price)from datagraph where dat like '%"&date()&"%')"

It does not work !!Which database server?|||To echo gannet, which database engine? It would also help if you could post the DDL for the table, since it looks like your dat column might be text of some kind instead of a date column.

-PatP|||it's access, and it's a text column

see http://www.dbforums.com/t1009057.html

gop373, use a DATE/TIME column

question about log file size when alter Huge table

I have the next question, and i would like to hear what do you think
about, and if is there a better solution for "my problem"

here is the question, I have a huge table with 60GB of data (image
files). The problem happen always when i try to ALTER the structure of
the table. For example I change a field char(3) to char(4)...the
sqlserver then performs the "alter table" command...that must be
something similar than "insert into the new table + drop the actual
table" and for that I need about 60GB o space for my LOG file, and
takes hours to complete the operation.

Is this the only way to alter a single field in my table??

I would like to heard you opinions...Thanks..

ALberto"Gordowey" <albertoiriarte@.gmail.com> wrote in message
news:1130717670.681491.136070@.g47g2000cwa.googlegr oups.com...
> I have the next question, and i would like to hear what do you think
> about, and if is there a better solution for "my problem"
> here is the question, I have a huge table with 60GB of data (image
> files). The problem happen always when i try to ALTER the structure of
> the table. For example I change a field char(3) to char(4)...the
> sqlserver then performs the "alter table" command...that must be
> something similar than "insert into the new table + drop the actual
> table" and for that I need about 60GB o space for my LOG file, and
> takes hours to complete the operation.

Sounds about right.

> Is this the only way to alter a single field in my table??

Well, you might be able to ADD a new column to your table, that might work
better. (especially if it has no default).

Or, it might be easier to BCP the data out, truncate the table, BCP the data
in.

(in this case I don't think native format would work so you'd have to
experiment.)

> I would like to heard you opinions...Thanks..
> ALberto|||Gordowey (albertoiriarte@.gmail.com) writes:
> I have the next question, and i would like to hear what do you think
> about, and if is there a better solution for "my problem"
> here is the question, I have a huge table with 60GB of data (image
> files). The problem happen always when i try to ALTER the structure of
> the table. For example I change a field char(3) to char(4)...the
> sqlserver then performs the "alter table" command...that must be
> something similar than "insert into the new table + drop the actual
> table" and for that I need about 60GB o space for my LOG file, and
> takes hours to complete the operation.

Yup, that's it.

> Is this the only way to alter a single field in my table??

Rather than having ALTER TABLE to all that under the covers, you could do
it your self. The twist is that then you can do the insert in batches,
and truncate the transaction log between the turns. (Simplest is to run
in simple recovery mode if you can.) When you compose the batches, use
the clustered index for the table, else selection of the batches may be
horribly slow.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Question about locks

What do you mean by 'doing a table lock'? Do you mean using a lock hint?
Which hint? What is the statement that is affecting the data?
Have you started an explicit transaction with BEGIN TRAN?
We need to see exactly what you are doing to lock the table to be able to
answer your questions, but in general, it is better to let SQL Server's lock
manager handle all the necessary locking automatically.
Why are you updating only one row at a time? If you are interested in
performance, you might consider figuring out a way to process all the rows
at once. That's what a relational database is good at.
HTH
Kalen Delaney, SQL Server MVP
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:5C5D6009-F702-4100-BCE1-F194E3695DAC@.microsoft.com...
> In short, we are sending several statements to the database from one
> source
> (my application). When this application is running nobody else will be
> accessing the database.
> If I do a table lock isn't it still releasing that lock after each
> statement?
> I ask because each update I send to the database will be effecting only
> one
> row at a time so I don't think I would get performance gains because I
> lock
> the table for one record update then lock it again etc etc. The manager
> itself could most likely do that faster anyway.
>In short, we are sending several statements to the database from one source
(my application). When this application is running nobody else will be
accessing the database.
If I do a table lock isn’t it still releasing that lock after each stateme
nt?
I ask because each update I send to the database will be effecting only one
row at a time so I don’t think I would get performance gains because I loc
k
the table for one record update then lock it again etc etc. The manager
itself could most likely do that faster anyway.|||What do you mean by 'doing a table lock'? Do you mean using a lock hint?
Which hint? What is the statement that is affecting the data?
Have you started an explicit transaction with BEGIN TRAN?
We need to see exactly what you are doing to lock the table to be able to
answer your questions, but in general, it is better to let SQL Server's lock
manager handle all the necessary locking automatically.
Why are you updating only one row at a time? If you are interested in
performance, you might consider figuring out a way to process all the rows
at once. That's what a relational database is good at.
HTH
Kalen Delaney, SQL Server MVP
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:5C5D6009-F702-4100-BCE1-F194E3695DAC@.microsoft.com...
> In short, we are sending several statements to the database from one
> source
> (my application). When this application is running nobody else will be
> accessing the database.
> If I do a table lock isn't it still releasing that lock after each
> statement?
> I ask because each update I send to the database will be effecting only
> one
> row at a time so I don't think I would get performance gains because I
> lock
> the table for one record update then lock it again etc etc. The manager
> itself could most likely do that faster anyway.
>

Friday, March 30, 2012

Question about sproc performance?

Question about simple query..

Question about self referencing delete

Monday, March 26, 2012

question about process order

Question about Primary key

question about primary key

question about performance of partition tables

question about performance of partition tables

Question about performance

Question about performance

Question about OUTPUT clause

Friday, March 23, 2012

Question about Neural Networks

Question about Neural Networks

question about Msg 20068

Question about move large amount of data from database to database

Question about MAX()

Question about Max function

question about log file size when alter Huge table

Question about locks

Antivirus install to db server

Blog Archive

About Me