PROGRESS to T-SQL .. Making the shift.

[murphys]

Greetings everyone.

I'm writing this article in hopes of helping you PROGRESS gurus out there make the shift over to Microsoft's SQL Server and using T-SQL as your primary database programming language.

For the purpose of this article, we're going to "forget" that PROGRESS has a SQL-92 implementation, as I'm safe in saying that almost all of you used PROGRESS for what it is/was - a procedural (some say "imperative") 4GL.

Procedural. That's a key word. It's something that PROGRESS IS, and something that T-SQL IS NOT. T-SQL is a "declarative" langauge, and I'll explain the difference right now.

When you give a friend directions to your house, you might explain to them: "Go 2 miles south on 82nd Ave, turn right on Oak St, go 1 mile, then turn right on 64th Ave. My house # is 123, and it's on the left-side. Park on the street in front if the driveway is full."

What you've done here is procedural. You have some definitive control over the steps to accomplish the end goal of helping your friend arrive safely and efficiently to your home.

The declarative side of things can be best paralleled by when you order something off the 'net. You "declare" your shipping address, and the product just arrives. You don't need to give the FedEx driver specific instructions on finding your house - FedEx has their own internal processes and logic for figuring this stuff out. You simply issue a high-level command that is met with a result. End of story.

This is exactly how T-SQL works in comparison to PROGRESS. T-SQL produces sets of results. You work with data set-by-set instead of in PROGRESS where you may work with it record-by-record.

Now - T-SQL provides some procedural structures that may confuse you, and will have you tempted to use them. Things such as a CURSOR, or a DO...WHILE loop. When it comes to using these things, simply: DON'T. Unless it's absolutely necessary. Even then, 99.9% of the time, there is a declarative equivalent to the procedural path you're about to embark on. The reason we avoid these kinds of things in T-SQL is that they are SLOW. They will make your grandmother's driving seem on par with Richard Petty.

In T-SQL - you have to ditch your notions of a "CurrentRow" or "Looping through a set". There is nothing like this in T-SQL. You issue and command and get a set of results. If you can back yourself out of procedural thinking for awhile, you'll find the equivalant T-SQL code is usually shorter, and always faster.

Let's do a comparison of a common task. To paint the scenario, let's say our customer has implemented a new warehouse naming strategy, and as a result - needs to convert his old whse names into to new monikers. We have created a table with two fields: one to store the old whse value, and one to hold the new value. Our goal is to use this table as a "look-up" when sorting through standard Syteline tables to update the old values to new ones. In our example below, we'll focus on using this table to update the itemwhse table.

In PROGRESS, we'd do this:

for each conv_table no-lock:
  find first itemwhse where itemwhse.whse = conv_table.old_value.

  do while available(itemwhse):
    itemwhse.whse = conv_table.new_value.
    find first itemwhse where itemwhse.whse = conv_table.old_value.
  end.
end.

Basically - we loop through the conv_table, and for each record - we search through the itemwhse table for the old value - and wherever we find it, we replace it with the new value.

Many of you, in your shift to T-SQL may try to implement the above code in a simliar, procedural fashion such as:

DECLARE @cOldVal varchar(50), @cNewVal varchar(50)
DECLARE cOldNew CURSOR FOR SELECT Old_Value, New_Value FROM conv_table
OPEN cOldNew
FETCH NEXT FROM cOldNew INTO @cOldVal, @cNewVal

WHILE @@FETCH_STATUS = 0
BEGIN
  UPDATE itemwhse SET itemwhse.whse = @cNewVal WHERE itemwhse.whse = @cOldVal
  FETCH NEXT FROM cOldNew INTO @cOldVal, @cNewVal
END

CLOSE cOldNew
DEALLOCATE cOldNew

Phew! What a MESS!

If we can step-back, and abandon the notion of a current-row context, the proper, declarative way to write this is as follows:

UPDATE itemwhse SET itemwhse.whse = conv_table.new_value 
  FROM itemwhse 
  JOIN conv_table ON itemwhse.whse = conv_table.old_value

Much more simple, and produces the exact same results in substantially less time.

So - to sum up, the things you PROGRESS gurus need to keep in mind when shifting to T-SQL:

...T-SQL is NOT procedural. Stop thinking about code-flow, control structures, loops, etc. These things are procedural.
...Cursors and Looping in T-SQL are evil and slow.
...Stop thinking in the context of a "current record" and think more on a "complete result-set" level.

I hope this helps you out. Just FYI - my background is primarily SQL/VB. I have about 4 years of PROGRESS experience mixed in, but I've been typing SQL for the last 10 years.

If anyone has any questions, or needs a hand through anything - please feel free to ask!

[murphys]

Also - remember this:

Progress vs. T-SQL.

It's NOT a competition about which is the "better" language. As far as I'm concerned - debates of this nature are simply religious discussions with little to no bearing in practical reality, and belong in the bit-bin with discussions such as RISC vs. CISC (or Mac vs. x86 PC for you non-geeks).

Remember - it's all about using the correct tool for the job at-hand.

[Walter]

Very nice. I read about fetch being slow and to stay away from it.

How would you do equivalent of Progress FIND in a table you don't loop through?

for each itemwhse:

find job where job.item = itemwhse.item and ...

if available job then do.
end

else do something else

Thanks, Walter.

[Derik]

It depends on what you want to 'DO' at the end...

Here is the code to join up the job to itemwhse...
select job.item
from job
join itemwhse
on job.item = itemwhse.item

but, in the end, it will depend on what action you want to take - and how it relates to the set of data you want to return...

If you are trying to see where there is no job tied to the item for the item whse, then you can use outer joins, etc...to demonstrate that those columns would be NULL in the result set, etc...

Hope that explains a little - keep the questions coming...

Derik

[murphys]

Originally Posted by Walter

Very nice. I read about fetch being slow and to stay away from it.

How would you do equivalent of Progress FIND in a table you don't loop through?

for each itemwhse:

find job where job.item = itemwhse.item and ...

if available job then do.
end

else do something else

Thanks, Walter.

Exactly what Derik said --

With T-SQL you have to get out of the record-by-record mindset...so it's rare that you'll actually be searcing for a single, specific record. You'll be searching for a group/set of records that conform to a specific criteria.

In your code above, take it conceptually up to the 50,000 ft overview level .. what you're trying to do is:

"Give me a list of jobs that have an equivalent item in the itemwhse table..."

... the SQL to produce such a result-set will contain an inner-join:

SELECT * FROM job JOIN itemwhse ON job.item = itemwhse.item

Originally Posted by Derik

If you are trying to see where there is no job tied to the item for the item whse, then you can use outer joins, etc...to demonstrate that those columns would be NULL in the result set, etc...

Derik's example would be taken care of like so:

SELECT * FROM itemwhse LEFT JOIN job ON itemwhse.item = job.item WHERE job.item IS NULL

The above statement would return all itemwhse records that don't have a related job record.

[PiDiDi]

Thanks, Derik, for this thread.
I agree with you: People, please, try to minimize usage of cursors in your code!
The cursor is very expensive - the data are stored into the temp database and also the overhead is expensive.
Here is small stupid example, but...:

-- create test table and fill it with data
if not object_id('tempdb..#ttt') is null
drop table #ttt
create table #ttt (id int identity(1,1), i int)
declare @i int
set @i = 0
while (@i < 10000)
begin
insert into #ttt(i) values(@i)
set @i = @i + 1
end

select 'START - NO CURSOR', getdate()
update #ttt
set i = i - 10000
select 'END - NO CURSORr', getdate()

-- fill the table again, to have same starting point ;-)
truncate table #ttt

set @i = 0
while (@i < 10000)
begin
insert into #ttt(i) values(@i)
set @i = @i + 1
end
-- now, do the same but using cursor
select 'START - CURSOR', getdate()
declare cur_ cursor local static for
select id from #ttt
open cur_
while (1=1)
begin
fetch next from cur_ into @i
if @@fetch_status <> 0 BREAK
update #ttt
set i = i - 10000
where id = @i
end
close cur_
deallocate cur_
select 'END - CURSOR', getdate()

And the result is:

START - NO CURSOR 2007-02-16 10:25:16.683
END - NO CURSORr 2007-02-16 10:25:16.717
--------------
START - CURSOR 2007-02-16 10:25:16.950
END - CURSOR 2007-02-16 10:25:24.420

Without cursor: "nothing", with cursor: 8 sec