SQL Does and Don'ts 
Author Message
 SQL Does and Don'ts

Hello all,

Having never written an application in Clarion using SQL, I could use some
advise on what to do and what not to do.  I've found that an application can
be converted over to SQL quite easily but I've been told that the end result
isn't nearly as efficient as it should be.  For instance,  LOOP with a
hundred NEXT statements would result in a thousand sql queries being sent
back and forth to the server.

The application I'm going to be involved in developing is likely to have a
lot of users and a lot of heavy access.  It needs to be as efficient as is
reasonably possible.  Any advice and discussion would be appreciated.

Thanks,

-Troy



Sun, 06 Feb 2005 23:40:22 GMT  
 SQL Does and Don'ts

Quote:
>>(I assume you're talking specifically about handcoded processing in embeds
>>- ABC does reasonably well with most SQL flavors, in most situations.)

Actually, I was talking about both.

How does legacy do with SQL?  We have a very short time frame for this next
project and while we all want to learn ABC, I think we'd spend way too much
time learning the little details of ABC to be able to meet our deadline.
Next project though !!

I would like to know how the VIEW with PROP:SQL works.  Does anyone have any
handy sample code?

Thanks,

-Troy



Mon, 07 Feb 2005 01:32:16 GMT  
 SQL Does and Don'ts
Troy,
        take a look at http://www.icetips.com/sql/

Written by Dan Pressnell, it is what you should look before going in
developpement.

Some hint:
    You should not use ABC/Legacy generated browse, it does way too much
"hit" on the SQL backend. (Look at Dan's example to see how to do)
    Use {Prop:SQL} as much as you can ! (don't use set/next if you can)

--
Patrick Boisclair
Programmeur - Analyste / Programmer - Analyst
PGCTI Inc.
http://www.pgcti.com



Quote:
> Hello all,

> Having never written an application in Clarion using SQL, I could use some
> advise on what to do and what not to do.  I've found that an application
can
> be converted over to SQL quite easily but I've been told that the end
result
> isn't nearly as efficient as it should be.  For instance,  LOOP with a
> hundred NEXT statements would result in a thousand sql queries being sent
> back and forth to the server.

> The application I'm going to be involved in developing is likely to have a
> lot of users and a lot of heavy access.  It needs to be as efficient as is
> reasonably possible.  Any advice and discussion would be appreciated.

> Thanks,

> -Troy



Mon, 07 Feb 2005 04:12:00 GMT  
 SQL Does and Don'ts
you also might want to look at this site.
http://www.clarion.nettservice.net/mysql/documentation/

Joe Ryan
www.vib.tv


Quote:
> Hello all,

> Having never written an application in Clarion using SQL, I could use some
> advise on what to do and what not to do.  I've found that an application
can
> be converted over to SQL quite easily but I've been told that the end
result
> isn't nearly as efficient as it should be.  For instance,  LOOP with a
> hundred NEXT statements would result in a thousand sql queries being sent
> back and forth to the server.

> The application I'm going to be involved in developing is likely to have a
> lot of users and a lot of heavy access.  It needs to be as efficient as is
> reasonably possible.  Any advice and discussion would be appreciated.

> Thanks,

> -Troy



Mon, 07 Feb 2005 04:39:37 GMT  
 SQL Does and Don'ts
Troy,

Quote:
> I would like to know how the VIEW with PROP:SQL works.  Does anyone have any
> handy sample code?

Here's one example. I have an admin application that manages the Clarion
Magazine database, and one of the reports I run is the quarterly author
earnings report. Among other things, this report needs to print the total
amount paid to authors to date, and the date of the last payment. In a TPS
database, that would take one access in reverse key order to get the latest
payment, and a set/next on the payments table to sum the payments to date.
Instead, I hand coded a view in the report's data section as follows:

PaymentsView   view(Payments)
                  project(Pay:Amount)
                  project(Pay:PayDate)
               end

I open the view in the report's init method:

  Open(PaymentsView)
  ! Check for errors

For each author, I issue the following:

PaymentsView{prop:sql} = 'select sum(Amount),max(PayDate) from Payments where
AuthorID = ' & CurrAuthor
next(PaymentsView)

I populate Pay:Amount and Pay:PayDate on the report, and after the
next(PaymentsView) they contain the total payments and the most recent payment
date, respectively. This is *much* more efficient than retrieving all the data
across the network (especially in this case, as I'm accessing the database
server across the internet).

Dave

Dave Harms

In-depth Clarion articles, news, tips & tricks
Plus the Clarion Online Archives!
Clarion Magazine: http://www.clarionmag.com



Mon, 07 Feb 2005 12:08:18 GMT  
 SQL Does and Don'ts

Quote:
> You should not use ABC/Legacy generated browse, it does way too much
>"hit" on the SQL backend.

This is where my confusion sets in.  Some people say not to use the
generated browse and others seem to indicate that for the most part,
everything could be done the same as normal.  I wouldn't call hand coding
all of my browses "normal".  Does anyone have any statistics on just how bad
the generated browses are with SQL?  There is a level of inefficiency that I
would tolerate if it would mean avoiding hours of hand coding for things
that I can normally do in 1 minute.  I just wonder whether Clarion generated
browses are above or below that level.

Thanks,

-Troy



Mon, 07 Feb 2005 19:19:46 GMT  
 SQL Does and Don'ts
There's nothing dramatically wrong with ABC browses and SQL.  But there are
always better ways to do things too, depending on the circumstances <g>


Quote:
> > You should not use ABC/Legacy generated browse, it does way too much
> >"hit" on the SQL backend.

> This is where my confusion sets in.  Some people say not to use the
> generated browse and others seem to indicate that for the most part,
> everything could be done the same as normal.  I wouldn't call hand coding
> all of my browses "normal".  Does anyone have any statistics on just how
bad
> the generated browses are with SQL?  There is a level of inefficiency that
I
> would tolerate if it would mean avoiding hours of hand coding for things
> that I can normally do in 1 minute.  I just wonder whether Clarion
generated
> browses are above or below that level.

> Thanks,

> -Troy



Mon, 07 Feb 2005 20:18:51 GMT  
 SQL Does and Don'ts
Troy,
        in fact ABC/Legacy generated browses perform quite good if you set
"file loaded". Page loaded always "hit" the SQL backend when the user uses
the scrolls bars, page up, page down, ...(it should be use in some
circumstances but should not be the way to make all your browses)

    So you can use "File loaded" ABC/Legacy browses and it will do the job
quite efficiently.

However, if you need a locator, watch out !!!  Every keystroke generate a
query that is sent to the back end, can you imagine how many times the
server gets hit, and it is true for both "file loaded" and "page loaded"
browses!!

--
Patrick Boisclair
Programmeur - Analyste / Programmer - Analyst
PGCTI Inc.
http://www.pgcti.com



Quote:
> > You should not use ABC/Legacy generated browse, it does way too much
> >"hit" on the SQL backend.

> This is where my confusion sets in.  Some people say not to use the
> generated browse and others seem to indicate that for the most part,
> everything could be done the same as normal.  I wouldn't call hand coding
> all of my browses "normal".  Does anyone have any statistics on just how
bad
> the generated browses are with SQL?  There is a level of inefficiency that
I
> would tolerate if it would mean avoiding hours of hand coding for things
> that I can normally do in 1 minute.  I just wonder whether Clarion
generated
> browses are above or below that level.

> Thanks,

> -Troy



Mon, 07 Feb 2005 21:57:20 GMT  
 SQL Does and Don'ts
Well, I meant "INCREMENTAL" locator !

Tanx Micheal, I forgot that detail.

--
Patrick Boisclair
Programmeur - Analyste / Programmer - Analyst
PGCTI Inc.
http://www.pgcti.com



Quote:
> Troy,
>         in fact ABC/Legacy generated browses perform quite good if you set
> "file loaded". Page loaded always "hit" the SQL backend when the user uses
> the scrolls bars, page up, page down, ...(it should be use in some
> circumstances but should not be the way to make all your browses)

>     So you can use "File loaded" ABC/Legacy browses and it will do the job
> quite efficiently.

> However, if you need a locator, watch out !!!  Every keystroke generate a
> query that is sent to the back end, can you imagine how many times the
> server gets hit, and it is true for both "file loaded" and "page loaded"
> browses!!

> --
> Patrick Boisclair
> Programmeur - Analyste / Programmer - Analyst
> PGCTI Inc.
> http://www.pgcti.com



> > > You should not use ABC/Legacy generated browse, it does way too much
> > >"hit" on the SQL backend.

> > This is where my confusion sets in.  Some people say not to use the
> > generated browse and others seem to indicate that for the most part,
> > everything could be done the same as normal.  I wouldn't call hand
coding
> > all of my browses "normal".  Does anyone have any statistics on just how
> bad
> > the generated browses are with SQL?  There is a level of inefficiency
that
> I
> > would tolerate if it would mean avoiding hours of hand coding for things
> > that I can normally do in 1 minute.  I just wonder whether Clarion
> generated
> > browses are above or below that level.

> > Thanks,

> > -Troy



Mon, 07 Feb 2005 22:32:07 GMT  
 SQL Does and Don'ts
Hi !

First : Remember ALLWAYS to use a primary field in all af your databases. If
not, you are lost again and again.
Second : USE a long variable (INT in MSSQL) as primary field, and let the
SQL backend update the field - It made a speeed difference on several 100 %
on my application when i changed from letting clarion make the auto
increment to let MSSQL do the job.

You will find that Handy Tools make browses etc that works in SQL/ABC. If
you want to make them in legacy, you will almost all of the time have to
hand code each and every queue you want to display in your browses, and you
wil have to make 3 different procedures for (Too handcoded!) for the insert,
change and delete. The standard templates just do not work.

Best regards and good luck!

Edvard Korsb?k



Quote:
> Hello all,

> Having never written an application in Clarion using SQL, I could use some
> advise on what to do and what not to do.  I've found that an application
can
> be converted over to SQL quite easily but I've been told that the end
result
> isn't nearly as efficient as it should be.  For instance,  LOOP with a
> hundred NEXT statements would result in a thousand sql queries being sent
> back and forth to the server.

> The application I'm going to be involved in developing is likely to have a
> lot of users and a lot of heavy access.  It needs to be as efficient as is
> reasonably possible.  Any advice and discussion would be appreciated.

> Thanks,

> -Troy



Tue, 08 Feb 2005 17:57:50 GMT  
 SQL Does and Don'ts
When you say that Handy Tools make browses etc that works in SQL/ABC, do you
have to do anything to an existing app other than add an extension? I'm
considering SQL in the near future and want to know what impact using it
will have on my  existing apps.

Ed Campbell


Quote:
> Hi !

> First : Remember ALLWAYS to use a primary field in all af your databases.
If
> not, you are lost again and again.
> Second : USE a long variable (INT in MSSQL) as primary field, and let the
> SQL backend update the field - It made a speeed difference on several 100
%
> on my application when i changed from letting clarion make the auto
> increment to let MSSQL do the job.

> You will find that Handy Tools make browses etc that works in SQL/ABC. If
> you want to make them in legacy, you will almost all of the time have to
> hand code each and every queue you want to display in your browses, and
you
> wil have to make 3 different procedures for (Too handcoded!) for the
insert,
> change and delete. The standard templates just do not work.

> Best regards and good luck!

> Edvard Korsb?k



> > Hello all,

> > Having never written an application in Clarion using SQL, I could use
some
> > advise on what to do and what not to do.  I've found that an application
> can
> > be converted over to SQL quite easily but I've been told that the end
> result
> > isn't nearly as efficient as it should be.  For instance,  LOOP with a
> > hundred NEXT statements would result in a thousand sql queries being
sent
> > back and forth to the server.

> > The application I'm going to be involved in developing is likely to have
a
> > lot of users and a lot of heavy access.  It needs to be as efficient as
is
> > reasonably possible.  Any advice and discussion would be appreciated.

> > Thanks,

> > -Troy



Tue, 08 Feb 2005 18:29:34 GMT  
 SQL Does and Don'ts

Quote:
> You will find that Handy Tools make browses etc that works in SQL/ABC. If
> you want to make them in legacy, you will almost all of the time have to
> hand code each and every queue you want to display in your browses, and
you
> wil have to make 3 different procedures for (Too handcoded!) for the
insert,
> change and delete. The standard templates just do not work.

IMHO, the standard clarion templates works very well in SQL. The only
handcoded to do is to change manualy the prop:filter (if any) by a
prop:sqlfilter in the browses. I write a very large financial application
with a lot of related tables and some of them contains more than a million
of records whitout any problems !
So I don't understand your assertion.

Guy



Tue, 08 Feb 2005 19:51:18 GMT  
 SQL Does and Don'ts
Ok, my head feels like a tennis ball now.  Some people say it works fine
with minimal effort and some people say you have to hand code everything.
Which is it?

Quote:
>The only handcoded to do is to change manualy the prop:filter (if any) by a

prop:sqlfilter in the

Quote:
>browses

That sounds encouraging.  Where do I put the Prop:filter statement and when
do I need to use it?

I've been experimenting today and I've found that for hand coding, I get a
major improvement in speed if I use prop:filter instead of SET.  For
example:

SET(KEY)
LOOP
 NEXT(FILE)   !first occurence generates a query that gets the ENTIRE FILE !
 IF ERRORCODE() THEN BREAK.
END

gets changed to:

FILE{PROP:SQL}='SELECT * FROM FILE WHERE .......whatever I want for a query'
LOOP
 NEXT(FILE)
 IF ERRORCODE() THEN BREAK.
END

I'm not sure where the data is going with Prop:sql but it seems to work
well.  Any problems with the above code?

Thanks,

-Troy



Tue, 08 Feb 2005 21:18:24 GMT  
 SQL Does and Don'ts
Quote:
> That sounds encouraging.  Where do I put the Prop:filter statement and
when
> do I need to use it?

The Prop:Filter is generated by the browse template if you put a filter on
the browse. In this case, it's beter to change it by Prop:SqlFilter in the
embed point "before opening view"

Quote:
> I've been experimenting today and I've found that for hand coding, I get a
> major improvement in speed if I use prop:filter instead of SET.  For
> example:

> SET(KEY)
> LOOP
>  NEXT(FILE)   !first occurence generates a query that gets the ENTIRE FILE
!
>  IF ERRORCODE() THEN BREAK.
> END

> gets changed to:

> FILE{PROP:SQL}='SELECT * FROM FILE WHERE .......whatever I want for a
query'
> LOOP
>  NEXT(FILE)
>  IF ERRORCODE() THEN BREAK.
> END

> I'm not sure where the data is going with Prop:sql but it seems to work
> well.  Any problems with the above code?

That's a way I do. The only thing you have to take care with this code is
that the fields in your database must be declared exatly in the same order
than in your dictionary :
With the "select * from file", the back end return you all the fields of
your table, in the order they are declared. Then Clarion try to read them in
the order they are declared in the dictionary.
I personaly prefer to declare a view in Clarion, in which I just declare the
fields that I need, then use the Prop:Sql on the view.

Example :

ViewSql  VIEW(FILE)
                   PROJECT(FIL:Number)
                   PROJECT(FIL:Desciption)
               END

  CODE
  OPEN(ViewSql)
  ViewSql{Prop:Sql} = 'select Number,Description from File where ...'
  LOOP
      NEXT(ViewSql)
      IF ERRORCODE() = 33
           BREAK
      ELSIF ERRORCODE()
           MESSAGE('Error is ' & ERROR())
           BREAK
      END
      ! Your stuff
  END

Now, Michael Gould send me a message that I copy here (I don't know why he
don't post it to the group) about a problem that I personaly never had, but
maybe you can ask him some precision

Quote:
> There are some inherent problems with the Clarion chain which hasn't had
any
> overhauls since CW2 days.  We were doing SQL back then when I worked for
> TopSpeed and the biggest problem we saw is that for every Do RefreshWindow
> it would loop through that 3 times.

> This causes extra work on the backedn.

HTH,

Guy



Tue, 08 Feb 2005 23:22:40 GMT  
 
 [ 20 post ]  Go to page: [1] [2]

 Relevant Pages 

1. Top Ten Consultants' DON'Ts

2. Top Ten Consultants' DON'Ts

3. Timesaver error: Unknown Variable '%TS'

4. Ts 3.1 src in Ts 1.17

5. SQL-DMO: anybody has done this?

6. Clarion Browse using SQL doing a SELECT ASC and then SELECT DESC

7. who is doing smalltalk/sql development?

8. What's with TS Marketing

9. errors when doing 'make test'

10. Have you ever done 'namespace delete ::'?

11. Dolphin Applet's don't work...

12. match works, don't match doesn't

 

 
Powered by phpBB® Forum Software