March 18, 2009 at 10:24 am
Selfless plug -- One of the articles now referenced in my signature block is actually mine, though it is based off Jeff's work.
March 18, 2009 at 10:25 am
Fox, I can't speak to Oracle. I'm totally ignorant there, although I'm told they code totally differently. I will say that, in SQL Server, solid set-based solutions almost always run faster than cursors and while loops. I say almost because it is possible to have BAD set based solutions that grind slow. One day you may hear about triangular joins and RBAR, but trust me you don't want to go there yet.
There comes a point where performance gains come from examining the execution plans of queries, the table structure constraints and indices, the locking that's going on in the system, and probably a lot of other stuff I can't think of right now. The answers aren't always to be found in 20 lines code, but as you convert over from cursors, your code on SQL Server is going to run much faster.
__________________________________________________
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 10:28 am
let's say we have the table with
partnumber, producttype, sequence
After inserting part in the table we need to resequence the sequence number which depends on the partnumber.
so select partnumber from tbl order by partnumber -- assumming 10 records the sequence should be
1 to 10.
When inserting a part (lets say we insert many parts, with differing producttypes. We would then have to resequence each producttype set after the inserts (or deletes) have been done.
so if we say select * from partnumber, producttype orderby producttype, partnumber
the sequence would begin at 1 and end at the end for each producttype in the table.
Now you know I am going to ask how would you resequence the parts (without updating the already sequenced records or correctly sequenced records)
Meaning if we have 1,2,3,4,5,6 and we enter a record at pos 4, we don't need to update the records that contain the sequence of 1,2,3.
March 18, 2009 at 10:32 am
foxjazz (3/18/2009)
{} is much better than BEGIN BEGIN BEGIN BEGIN END END END END
For you, perhaps. I find BEGIN END just fine. Remember I did come from a development environment where we actually used COBOL. You think T-SQL is wordy?
Any way, I'd never write either {{{{}}}} or BEGIN BEGIN BEGIN BEGIN END END END END. it would look like this:
BEGIN
BEGIN
BEGIN
BEGIN
END
END
END
END
or, using {}:
{
{
{
{
}
{
{
{
I like white space. It makes thing more readable.
March 18, 2009 at 10:33 am
foxjazz (3/18/2009)
{} is much better than BEGIN BEGIN BEGIN BEGIN END END END END
Heh 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 NULL
BEGIN
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.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 18, 2009 at 10:37 am
foxjazz (3/18/2009)
let's say we have the table withpartnumber, producttype, sequence
After inserting part in the table we need to resequence the sequence number which depends on the partnumber.
so select partnumber from tbl order by partnumber -- assumming 10 records the sequence should be
1 to 10.
When inserting a part (lets say we insert many parts, with differing producttypes. We would then have to resequence each producttype set after the inserts (or deletes) have been done.
so if we say select * from partnumber, producttype orderby producttype, partnumber
the sequence would begin at 1 and end at the end for each producttype in the table.
Now you know I am going to ask how would you resequence the parts (without updating the already sequenced records or correctly sequenced records)
Meaning if we have 1,2,3,4,5,6 and we enter a record at pos 4, we don't need to update the records that contain the sequence of 1,2,3.
I'll be honest, for me trying to work just off your word description of a problem is difficult. It would help if you provided an actual example of what you are trying to accomplish here. One of my favorite sayings is "Pretend I'm from Missouri, and show me what you want". By the way, Missouri is also known as the Show Me State. I am much more of a visual person, which is why I DON'T do telephone technical support.
March 18, 2009 at 10:41 am
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.
March 18, 2009 at 10:43 am
Lynn Pettis (3/18/2009)
foxjazz (3/18/2009)
{} is much better than BEGIN BEGIN BEGIN BEGIN END END END ENDFor you, perhaps. I find BEGIN END just fine. Remember I did come from a development environment where we actually used COBOL. You think T-SQL is wordy?
Any way, I'd never write either {{{{}}}} or BEGIN BEGIN BEGIN BEGIN END END END END. it would look like this:
BEGIN
BEGIN
BEGIN
BEGIN
END
END
END
END
or, using {}:
{
{
{
{
}
{
{
{
I like white space. It makes thing more readable.
Lynn,
What I found usefull and now we have it in our coding standards si to use comments with each BEGIN and END to make it easier to read so the code would look like this. I think code readability is a very important factor too often overlooked.
BEGIN --MailChecks
BEGIN --BuyMilk
BEGIN --RakeTheGarden
BEGIN --PaintTheHouse
END --PaintTheHouse
END --RakeTheGarden
END --BuyMilk
END --MailChecks
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 18, 2009 at 10:45 am
After inserting part in the table we need to resequence the sequence number which depends on the partnumber
'
Fox, I will write a little bit of code to do it. But first I'm going to say that part of relational db thinking is that a sequence number that depends on the part number is totally unnecessary for what we do. We can sequence based on the part numbers themselves, we may even build a totally different index to support that. If we want to add a row, we just add it and use ORDER BY on the column itself, not on a sequence number.
Look at the results from the two queries at the bottom. They are identical. Since I know I don't have to do a loop to "read a row, increment a sequence number by 1, then read another row", I can disregard the sequence number entirely. It adds no functionality I didn't already have. This is important, because I'm trying to get you to see how we THINK about problems differently. It's good practice to always make sure each row has a unique key, but we order by whatever is convenient for the report or application at hand.
create table #parts (partnumber varchar(10), productType varchar(10), sequence int)
insert into #parts
select 'A23455','Carburetor', 1 union all
select 'A23456','Carburetor', 2 union all
select 'B23454','Carburetor', 3
select partnumber,productType from #parts
order by PARTNUMBER
select partnumber,productType from #parts
order by SEQUENCE
drop table #parts
Also, please observe how I've set up the problem. I give you (and everyone else) the code to create a table, and some data to illustrate. Anyone can cut and paste this, to test it, or to change it. If you say you are looking for certain results, we can be sure our solution delivers before we post.
I know you are experiencing technical difficulties with your hardware, but when they are resolved you will find that taking ten minutes to set up the problem gets you solid answers MUCH quicker.... and you may even realize what the problem is yourself while setting up the question.
__________________________________________________
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 10:48 am
let me give some data
partnumber, producttype, sequence
7401,belt,1
7432,belt,2
8401,hose,1
8422,hose,2
This is the table.
inert a record (8403,hose,?)
after a resequence you would have
8401,hose,1
8402,hose,2
8422,hose,3
March 18, 2009 at 10:55 am
Now you know I am going to ask how would you resequence the parts (without updating the already sequenced records or correctly sequenced records)
Well, I have seen a case where the prefix of a part number was used to identify the supplier. Since that part number was communicated to repeat customers, the scheme began to fall apart when a better supplier was found. You just could not change a part number like that.
In the same vein, the UPC code was abused to enter the product part number - it just limited you the first 6 digits, and only if you had no letters in the part number. Since you could not change a released UPC code either, this resulted in gaps in the sequence numbers.
In your case, I do not understand why you would want to "resequence" the table. Its primary key is only intended to allow the server to distinguish between identical records yes, I know, this violates 3NF - and all the children will go home in time for supper - right!). And if you want to change a primary key, then this screws up the foreign keys in the detail tables.
The actual sequence of records in a database is not relevant. If you are concerned with ordering, you will be better served with the ORDER BY clause.
March 18, 2009 at 10:55 am
OK DAMN
I don't have it all wrong, but here is the issue.
The user defines the sequence, it isn't alphabetical. So when inserting a record the user would give 2 as the sequence, and the rest of the records have to be re-sequenced.
March 18, 2009 at 10:57 am
foxjazz (3/18/2009)
let me give some datapartnumber, producttype, sequence
7401,belt,1
7432,belt,2
8401,hose,1
8422,hose,2
This is the table.
inert a record (8403,hose,?)
after a resequence you would have
8401,hose,1
8402,hose,2
8422,hose,3
Before I write any code, plus I have work to do here so it will have to wait for lunch time, it appears that the actual sequence is the Part Number, correct?
With that, here is something else you should start to understand. There is no order to how data is stored in a Relational Database. Not totally true, as if you have a clustere index defined, the data is stored in the order of the clustered index. Order in a relational database is dtermined by the ORDER BY clause when selecting data.
March 18, 2009 at 10:57 am
Continuing my previous example with your data. I get the results desired by simply ignoring the sequence number column. If I'm going to do this a lot, I will be sure to have one or more indexes that are primarily based on partnumber.
If I don't insist that there MUST be a sequence number, there is no problem to begin with.
create table #parts (partnumber varchar(10), productType varchar(10), sequence int)
insert into #parts
select 7401,'belt',1 union all
select 7432,'belt',2 union all
select 8401,'hose',1 union all
select 8422,'hose',2
select partnumber,productType from #parts
order by PARTNUMBER
insert into #parts
select 8403,'hose',0
select partnumber,productType from #parts
order by PARTNUMBER
drop table #parts
__________________________________________________
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 11:00 am
foxjazz (3/18/2009)
It seems that if I write a query like this:declare @i int
select top 10 @i = @i + 1 , partnumber from prd
I shouldn't get an error. But hell what do I know.
The problem is that you are mixing 2 uses of SELECT.
SELECT @i=@i+1
SELECT @x=col FROM tbl
Both these are assigning values. In the second case, if multiple rows are returned, then the 'last' value gets assign, so if you haven't used ORDER BY then anything may happen. The form SELECT @x=@x+col FROM tbl is only really used for concatenation. To produce a total SUM(col) would be more efficient
SELECT col FROM tbl
This actual returns a table which may be used by an outer query of returned to a calling application. In the case where the calling application is Query Analyzer or Management Studio, then application simply displays the table.
I assume that your query
select top 5 @i=@i+1, partnumber from prd
is trying to get a list of partnumber with a sequence.
Since tables are (theoretically) unordered, this is meaningless unless you apply an ordering.
I've already shown one way to do this
-- you can omit the 'top 5' from either select to get the same result
create table #i (i int identity(1,1), partnumber int)
insert #i (partnumber)
select top 5 partnumber from prd
select top 5 i,partnumber from #i
drop table #i
An alternative, not using temp tables or IDENTITY is to use a self join.
select top 5 count(*) as 'i', a.partnumber from prd a, prd b
where a.partnumber >= b.partnumber
group by a.partnumber
order by a.partnumber
These won't actually produce the same result, since the first will return the partnumbers in whatever order they happen to be in tabel prd, while the second returns them in partnumber order.
There are probably more efficient ways to do this. Since I use SS2K5, I'd probably just use row_number(), so I'm having to think a bit about what SS2K can do! 🙂
Derek
Viewing 15 posts - 241 through 255 (of 465 total)
You must be logged in to reply to this topic. Login to reply