March 18, 2009 at 2:26 pm
Hey Guys... don't hijack the thread. We're in SQL 7,2000 here.
Please take further discussion over to an SQL2k5 thread. It's a good topic.
However, Fox is moving in the right direction and we don't want to lose sight of the objective.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 18, 2009 at 2:39 pm
Bob Hovious (3/18/2009)
Hey Guys... don't hijack the thread. We're in SQL 7,2000 here.Please take further discussion over to an SQL2k5 thread. It's a good topic.
However, Fox is moving in the right direction and we don't want to lose sight of the objective.
I do have to agree with Bob on this one. we need to keep the noise level down and not introduce version differences between SQL Server 2000 and SQL Server 2005/2008. Let's take this all in small steps.
Thank you.
March 18, 2009 at 4:06 pm
Bob Hovious (3/18/2009)
Hey Fox. Don't be overwhelmed by the rush of answers and questions after each post. There are a lot of people watching and we aren't coordinating our efforts. I'm typing at the same time as Lynn and J.
I am on vacation. Starting tomorrow, I will no longer be able to do this in real time.
March 18, 2009 at 6:11 pm
foxjazz (3/18/2009)
Lets now say that we have 2 records to insert at seq=2 and 3so would something like this work?
update tbl set seq = tbl.seq + (select count(*) from insertdata) where seq > 2
insert into tbl (partnumber,seq) select partnumber, rowcount() + 1 from insertdata order by seq
does this make sense to anyone?
Yes, the UPDATE would work, but the INSERT wouldn't. There's no rowcount() function (you need SELECT COUNT(*)) and the ORDER BY seq means ORDER BY insertdata.seq, which doesn't achieve anything.
The following is a corrected version with annotated results.
create table tbl (partnumber varchar(5), seq int)
create table insertdata (partnumber varchar(5))
--
insert tbl
select '7712',1 union all
select '7714',2 union all
select '7783',3 union all
select '7809',4 union all
select '7888',5
--
insert insertdata
select '7766' union all
select '7777'
--
select * from tbl
--
select * from insertdata
--
update tbl set seq=seq+(select count(*) from insertdata) where seq >= 2
--
select * from tbl
--
--insert into tbl (partnumber,seq) select partnumber,rowcount()+1 from insertdata order by seq
--
insert into tbl
select a.partnumber,n.seq+1
from insertdata a
join (
select count(*) as 'seq', x.partnumber
from insertdata x, insertdata y
where x.partnumber >= y.partnumber
group by x.partnumber
) n
on a.partnumber=n.partnumber
--
select * from tbl order by seq
--
drop table tbl
drop table insertdata
/*------------------------
-- create table tbl... insert...
(5 row(s) affected)
-- create table insertdata... insert...
(2 row(s) affected)
-- select * from tbl
partnumber seq
---------- -----------
7712 1
7714 2
7783 3
7809 4
7888 5
(5 row(s) affected)
-- select * from insertdata
partnumber
----------
7766
7777
(2 row(s) affected)
-- update tbl set seq = ...
(4 row(s) affected)
-- select * from tbl
partnumber seq
---------- -----------
7712 1
7714 4
7783 5
7809 6
7888 7
(5 row(s) affected)
-- insert into tbl ...
(2 row(s) affected)
-- select * from tbl order by seq
partnumber seq
---------- -----------
7712 1
7766 2
7777 3
7714 4
7783 5
7809 6
7888 7
(7 row(s) affected)
------------------------*/
Derek
March 18, 2009 at 7:06 pm
Lynn Pettis (3/18/2009)
Chris Morris (3/18/2009)
foxjazz (3/18/2009)
{} is much better than BEGIN BEGIN BEGIN BEGIN END END END ENDHeh quite possibly - but the count of BEGIN - END pairs goes right down once people realise that they're waaaaaay overused.
The BEGIN and END are not needed here because the IF is followed by a single statement:
IF OBJECT_ID('tempdb..#Temp') IS NOT NULLBEGIN
DROP TABLE #Temp
END
Many people wrap their entire stored procedure within a BEGIN - END block - why?
The main culprit of course is loops. Once you start thinking set-based, the count of BEGIN - END pairs in your code will go right down and you will wonder what all the fuss was about.
I wrap my entire store procedures in a BEGIN END block, why? It clearly delineates the stored procedure for one. Also, somewhere in another thread, I think Jeff Moden actually found a slight performance gain be doing it as he never did it either. This is something that probably should be investigated in more depth in a different thread.
If I recall correctly, it was a slight performance wane...
--Jeff Moden
March 18, 2009 at 7:14 pm
foxjazz (3/18/2009)
J, my dba wife pointed it out to me last night that set based solutions are not always superior to cursors.
She's confusing things like the use of triangular joins with true set based code. Triangular joins look set based, but are actually thousands of times worse than cursors. It's a common mistake for DBA's to say that set based solutions are not always superior to cursors.
--Jeff Moden
March 18, 2009 at 7:16 pm
J (3/18/2009)
I am on vacation.
It's not exactly what they name "on vacation". 😉
"On vacation" means "gone fishing", "being on tour", "hiking over incredible range", getting pissed end in the end.
But doing what you do on job just not being paid for this - it's not exactly the idea behind the concept of vacations.
:hehe:
_____________
Code for TallyGenerator
March 18, 2009 at 7:33 pm
Jeff, I warned Fox he would hear about triangular joins one day.
Let us get the man weaned off cursors first !! 😉
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 18, 2009 at 8:59 pm
JacekO (3/18/2009)
I would keep it simple. Deal with one record at a time. If your Stored Procedure is designed to work fine with one record you can call it sequentially with all the other records. The user interface's job should be to feed you the data the way you want it. If you design you database interface to accept one or two or more orders at the same time you are creating more problems than you probably are willing to solve.(And then you will have a reason to rant about SQL...)
Although not simpler and only if you need to insert more than just a couple of rows, a sequence table with an increment as a parameter in the feed would probably work better. In SQL Server 2000, it would also require the use of a temp table, but it's much faster than RBAR methods. It also prevents hot-spotting, duplicated numbering, and deadlocks if the proc that does the work is written correctly.
--Jeff Moden
March 18, 2009 at 9:41 pm
Bob Hovious (3/18/2009)
Jeff, I warned Fox he would hear about triangular joins one day.Let us get the man weaned off cursors first !! 😉
Guys, I'm impressed with your performance, but you are displaying best techniques in SQL to a guy who cannot get
"WHY THE HECK SQL???"
He picked somewhere some SQL syntax but he has no idea why his beloved C# cannot be used to work with data.
He's never got his head around multiuser processing, locking strategy, transaction isolation.
He's complaining about DECLARE - SET syntax being not able to understand that everything is already declared with CREATE TABLE statement and set with INSERT/UPDATE statements.
He's concerned about BEGIN-END syntax not having a clue that processing data in 2 or more steps (that's only case where BEGIN-END is needed, right?) involves significant risk of breaking of data integrity and should be avoided by any means. And if it's not avoidable then wrapping it into transaction, not just "code brackets", should be considered.
He's talking about making SQL acceptable by a compiler, when SQL is designed as interpreter and CANNOT BE a compiler. Because compiled code depends on statistics, objects existence, server or connection settings, etc., and some procedures may be recompiled several times during execution.
Probably this is what he needs to study first?
Then it would be easier for him to understand that his beloved C#, as well as other procedural programming languages, is useless in database environment.
And why opening-closing transactions when looping creates such a overhead.
Then he’s know why no loops and no cursors are allowed in data processing.
And he would be asking different questions.
_____________
Code for TallyGenerator
March 18, 2009 at 11:07 pm
Sergiy (3/18/2009)
J (3/18/2009)
I am on vacation.It's not exactly what they name "on vacation". 😉
"On vacation" means "gone fishing", "being on tour", "hiking over incredible range", getting pissed end in the end.
But doing what you do on job just not being paid for this - it's not exactly the idea behind the concept of vacations.
:hehe:
J isn't on vacation now, but will be going on vacation starting tomorrow.
March 18, 2009 at 11:16 pm
And talking about syntax inprovements in SQL 2k5 and 2k8 I'd say it's not good at all.
By introducing comfort of procedural programming in stored procedures MS invites developers to move more procedural code to DB server from Application servers and user workstations.
It does not add anything to system performance but concentrates all computing activities distributed over many application servers on single DB server.
And considering code quality of majority of those programs it's safe to say that SQL 2008 is being turned to MS Access - easy programmed DB tool for small and not really scalable applications.
_____________
Code for TallyGenerator
March 18, 2009 at 11:17 pm
Jeff Moden (3/18/2009)
Lynn Pettis (3/18/2009)
Chris Morris (3/18/2009)
foxjazz (3/18/2009)
{} is much better than BEGIN BEGIN BEGIN BEGIN END END END ENDHeh quite possibly - but the count of BEGIN - END pairs goes right down once people realise that they're waaaaaay overused.
The BEGIN and END are not needed here because the IF is followed by a single statement:
IF OBJECT_ID('tempdb..#Temp') IS NOT NULLBEGIN
DROP TABLE #Temp
END
Many people wrap their entire stored procedure within a BEGIN - END block - why?
The main culprit of course is loops. Once you start thinking set-based, the count of BEGIN - END pairs in your code will go right down and you will wonder what all the fuss was about.
I wrap my entire store procedures in a BEGIN END block, why? It clearly delineates the stored procedure for one. Also, somewhere in another thread, I think Jeff Moden actually found a slight performance gain be doing it as he never did it either. This is something that probably should be investigated in more depth in a different thread.
If I recall correctly, it was a slight performance wane...
You know Jeff, it was so many posts ago, I can't even remember. That's why I also said it was something that should be investigated further in a different thread. If we are making progress here, we don't want a lot of noise interfering with the work in progress. The fact that foxjazz's tone as changed indicates a possible change in attitude and a willingness to at least listen to reasonable arguments. We are waiting for him to do some homework that Bob asked him to complete.
March 18, 2009 at 11:24 pm
Sergiy (3/18/2009)
Bob Hovious (3/18/2009)
Jeff, I warned Fox he would hear about triangular joins one day.Let us get the man weaned off cursors first !! 😉
Guys, I'm impressed with your performance, but you are displaying best techniques in SQL to a guy who cannot get
"WHY THE HECK SQL???"
He picked somewhere some SQL syntax but he has no idea why his beloved C# cannot be used to work with data.
He's never got his head around multiuser processing, locking strategy, transaction isolation.
He's complaining about DECLARE - SET syntax being not able to understand that everything is already declared with CREATE TABLE statement and set with INSERT/UPDATE statements.
He's concerned about BEGIN-END syntax not having a clue that processing data in 2 or more steps (that's only case where BEGIN-END is needed, right?) involves significant risk of breaking of data integrity and should be avoided by any means. And if it's not avoidable then wrapping it into transaction, not just "code brackets", should be considered.
He's talking about making SQL acceptable by a compiler, when SQL is designed as interpreter and CANNOT BE a compiler. Because compiled code depends on statistics, objects existence, server or connection settings, etc., and some procedures may be recompiled several times during execution.
Probably this is what he needs to study first?
Then it would be easier for him to understand that his beloved C#, as well as other procedural programming languages, is useless in database environment.
And why opening-closing transactions when looping creates such a overhead.
Then he’s know why no loops and no cursors are allowed in data processing.
And he would be asking different questions.
And if we continue to work in the direction we are, we could finally see his epiphany moment. Once that happens then he should start asking the questions that he needs to so that he can begin to use SQL Server to its fullest potential. He'll still have things to learn, but should realize that he isn't alone and has a great resource here at SSC.
March 18, 2009 at 11:31 pm
foxjazz (3/18/2009)
Um I don't quite understand and it's my fault for not getting my last req accross.Forget producttype..
lets say we just have partnumber, seq
and we want to insert a part at a certain seq location.
something like:
select partnumber from tbl order by seq where seq > 2
update tbl set seq = tbl.seq + 1 where seq > 2
insert into tbl (partnumber,seq) values ('newpn', 2)
ok we can see that we can insert 1 record this way.
Lets now say that we have 2 records to insert at seq=2 and 3
so would something like this work?
update tbl set seq = tbl.seq + (select count(*) from insertdata) where seq > 2
insert into tbl (partnumber,seq) select partnumber, rowcount() + 1 from insertdata order by seq
does this make sense to anyone?
If you remember, I asked you for a business reason for the user to sequence the parts. Can you provide that for us?
Viewing 15 posts - 271 through 285 (of 465 total)
You must be logged in to reply to this topic. Login to reply