ODBC Performance

One more time: Not all of the spacing worked out right on the code snippet
(don't want to mess anybody up) :)

----------------------
Code Snippet
----------------------
'***** Connect To Progress Database (Vantage)
strConnect = "DSN=ProgressRC;UID=sysprogress;PWD=sysprogress;"
Set dbsVantage = Server.CreateObject("ADODB.Connection")
dbsVantage.Open strConnect
dbsVantage.Execute "SET SCHEMA 'PUB'"

Dim arrOrder
Set rstOrder = Server.CreateObject("ADODB.Recordset")
strSQL = "Select OrderNum, NeedByDate FROM OrderHed WHERE OpenOrder <> 0"
rstOrder.Open strSQL, dbsVantage
arrOrder = rstOrder.GetRows
rstOrder.Close

dbsVantage.Close

Response.Write "<html>" & vbCrLf
Response.Write "<head><title>Sample</title></head>" & vbCrLf
Response.Write "<body>" & vbCrLf

Response.Write "Order, Need By Date<br />" & vbCrLf

Dim i
For i = 0 To UBound(arrOrder, 2)
Response.Write arrOrder(0, i) & ", " & arrOrder(1, i) & "<br />" & vbCrLf
Next

Response.Write "</body>" & vbCrLf
Response.Write "</html>" & vbCrLf

----------------------
End Code Snippet
----------------------

-----Original Message-----
From: Shirley Graver [mailto:shirleyg@...]
Sent: Thursday, March 17, 2005 3:10 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] ODBC Performance



Christopher,
How do you have your server set up to do your web based
reporting I would love to do this on our intranet. Shirley Graver Rubber
Associates
_____

From: Gitzlaff, Christopher [mailto:cgitzlaff@...]
Sent: Wednesday, March 16, 2005 5:12 PM
To: vantage@yahoogroups.com
Subject: [Vantage] ODBC Performance

I was working on a report for our internal company website (Intranet) and
ran across an interesting performance issue. On occasion, we use Report
Builder to generate the SQL statement for our Intranet pages. We create a
basic report in Report Builder, press "<ctl><alt>P", then press "F12". This
will give the SQL statement being used by Report Builder.

Running the base report in Report Builder took less than a second. When I
copied the SQL statement from Report Builder to use with ODBC, it took
approximately 15 seconds to retrieve the data.

Am I missing something? Is there a better way to pull data for web pages?

Any help would be greatly appreciated.


Thanks,

Chris


Christopher Gitzlaff, MSCIS
Manager - Information Systems & Technology
Major Industries, Inc.
Phone: 715.842.4616 ext. 323
Email: cgitzlaff@...


Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
<http://groups.yahoo.com/group/vantage/files/>
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links





Yahoo! Groups Sponsor


ADVERTISEMENT

<http://us.ard.yahoo.com/SIG=129dvqev4/M=298184.6191685.7192823.3001176/D=gr
oups/S=1705007183:HM/EXP=1111097525/A=2593423/R=0/SIG=11el9gslf/*http:/www.n
etflix.com/Default?mqso=60190075> click here


<http://us.adserver.yahoo.com/l?M=298184.6191685.7192823.3001176/D=groups/S=
:HM/A=2593423/rand=517590561>

_____

Yahoo! Groups Links
* To visit your group on the web, go to:
http://groups.yahoo.com/group/vantage/

* To unsubscribe from this group, send an email to:
vantage-unsubscribe@yahoogroups.com
<mailto:vantage-unsubscribe@yahoogroups.com?subject=Unsubscribe>

* Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .

Tracking #: 7E9F867FA475DF43B287C6B7D3DFFDE216E3B2D9


[Non-text portions of this message have been removed]



Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
Yahoo! Groups Links
I was working on a report for our internal company website (Intranet) and
ran across an interesting performance issue. On occasion, we use Report
Builder to generate the SQL statement for our Intranet pages. We create a
basic report in Report Builder, press "<ctl><alt>P", then press "F12". This
will give the SQL statement being used by Report Builder.

Running the base report in Report Builder took less than a second. When I
copied the SQL statement from Report Builder to use with ODBC, it took
approximately 15 seconds to retrieve the data.

Am I missing something? Is there a better way to pull data for web pages?

Any help would be greatly appreciated.


Thanks,

Chris


Christopher Gitzlaff, MSCIS
Manager - Information Systems & Technology
Major Industries, Inc.
Phone: 715.842.4616 ext. 323
Email: cgitzlaff@...
Welcome to life with ODBC and Progress. You may want to try to change some
of the links or selection criteria.

Jeremy


[Non-text portions of this message have been removed]
What language are you using for web based reporting?



We use Coldfusion here and I was having some horrible ODBC issues as
well. I just finished switching to using the native JDBC driver rather
than the ODBC and it works 10 times better. If you're using ASP or
something similar that relies on Windows creating the datasource for you
then you might be stuck using ODBC which does seem to have some issues.



On a similar note, with the latest release of Coldfusion MX 7 they have
released a report builder that is absolutely incredible. I'm not the
Vantage person here but I have created some web apps that tie into
Vantage so I have used it a bit here and there. But when I showed the
new report builder in Coldfusion to our Vantage guy he fell in love with
it and has started moving everything away from the Vantage report
builder and will most likely be getting rid of Crystal Reports. I know
most companies can't justify spending the money to buy CFMX 7 but if
anyone on this list is currently using Coldfusion, definitely look into
upgrading to use their reporting tool. It's years ahead of Vantage's.



Ben



-----Original Message-----
From: Gitzlaff, Christopher [mailto:cgitzlaff@...]
Sent: Wednesday, March 16, 2005 5:12 PM
To: vantage@yahoogroups.com
Subject: [Vantage] ODBC Performance



I was working on a report for our internal company website (Intranet)
and
ran across an interesting performance issue. On occasion, we use Report
Builder to generate the SQL statement for our Intranet pages. We create
a
basic report in Report Builder, press "<ctl><alt>P", then press "F12".
This
will give the SQL statement being used by Report Builder.

Running the base report in Report Builder took less than a second. When
I
copied the SQL statement from Report Builder to use with ODBC, it took
approximately 15 seconds to retrieve the data.

Am I missing something? Is there a better way to pull data for web
pages?

Any help would be greatly appreciated.


Thanks,

Chris


Christopher Gitzlaff, MSCIS
Manager - Information Systems & Technology
Major Industries, Inc.
Phone: 715.842.4616 ext. 323
Email: cgitzlaff@...


Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder
and Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
<http://groups.yahoo.com/group/vantage/files/>
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links





Yahoo! Groups Sponsor

ADVERTISEMENT
click here
<http://us.ard.yahoo.com/SIG=129dvqev4/M=298184.6191685.7192823.3001176/
D=groups/S=1705007183:HM/EXP=1111097525/A=2593423/R=0/SIG=11el9gslf/*htt
p:/www.netflix.com/Default?mqso=60190075>


<http://us.adserver.yahoo.com/l?M=298184.6191685.7192823.3001176/D=group
s/S=:HM/A=2593423/rand=517590561>



________________________________

Yahoo! Groups Links

* To visit your group on the web, go to:
http://groups.yahoo.com/group/vantage/


* To unsubscribe from this group, send an email to:
vantage-unsubscribe@yahoogroups.com
<mailto:vantage-unsubscribe@yahoogroups.com?subject=Unsubscribe>


* Your use of Yahoo! Groups is subject to the Yahoo! Terms of
Service <http://docs.yahoo.com/info/terms/> .



[Non-text portions of this message have been removed]
Christopher,
How do you have your server set up to do your web based
reporting I would love to do this on our intranet.
Shirley Graver
Rubber Associates
_____

From: Gitzlaff, Christopher [mailto:cgitzlaff@...]
Sent: Wednesday, March 16, 2005 5:12 PM
To: vantage@yahoogroups.com
Subject: [Vantage] ODBC Performance

I was working on a report for our internal company website (Intranet) and
ran across an interesting performance issue. On occasion, we use Report
Builder to generate the SQL statement for our Intranet pages. We create a
basic report in Report Builder, press "<ctl><alt>P", then press "F12". This
will give the SQL statement being used by Report Builder.

Running the base report in Report Builder took less than a second. When I
copied the SQL statement from Report Builder to use with ODBC, it took
approximately 15 seconds to retrieve the data.

Am I missing something? Is there a better way to pull data for web pages?

Any help would be greatly appreciated.


Thanks,

Chris


Christopher Gitzlaff, MSCIS
Manager - Information Systems & Technology
Major Industries, Inc.
Phone: 715.842.4616 ext. 323
Email: cgitzlaff@...


Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
<http://groups.yahoo.com/group/vantage/files/>
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links





Yahoo! Groups Sponsor


ADVERTISEMENT

<http://us.ard.yahoo.com/SIG=129dvqev4/M=298184.6191685.7192823.3001176/D=gr
oups/S=1705007183:HM/EXP=1111097525/A=2593423/R=0/SIG=11el9gslf/*http:/www.n
etflix.com/Default?mqso=60190075> click here


<http://us.adserver.yahoo.com/l?M=298184.6191685.7192823.3001176/D=groups/S=
:HM/A=2593423/rand=517590561>

_____

Yahoo! Groups Links
* To visit your group on the web, go to:
http://groups.yahoo.com/group/vantage/

* To unsubscribe from this group, send an email to:
vantage-unsubscribe@yahoogroups.com
<mailto:vantage-unsubscribe@yahoogroups.com?subject=Unsubscribe>

* Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .

Tracking #: 7E9F867FA475DF43B287C6B7D3DFFDE216E3B2D9


[Non-text portions of this message have been removed]
Hi Shirley. I had replied to a message yesterday, but just realized it
didn't go back to the group. I'll include that in my response.

We have two separate groups configured on the Progress / Vantage database (a
4GL group and an ODBC group). We're on version 6.0 so the port for ODBC on
the Progress / Vantage server is 6050.

I installed Progress on our internal Intranet server (web server). We are
using Microsoft IIS and Active Server Pages (ASP). A standard client
install on the web server should work fine - the Merant ODBC drivers for
Progress will be installed. Then you go to "Control Panel -> Administrative
Tools -> Data Sources (ODBC)" and add a system DSN pointing to the Progress
server. Epicor has documentation on how to do this.

We connect to the database using ADODB - simple example:

----------------------
Code Snippet
----------------------
'***** Connect To Progress Database (Vantage)
strConnect = "DSN=ProgressRC;UID=sysprogress;PWD=sysprogress;"
Set dbsVantage = Server.CreateObject("ADODB.Connection")
dbsVantage.Open strConnect
dbsVantage.Execute "SET SCHEMA 'PUB'"

Dim arrOrder
Set rstOrder = Server.CreateObject("ADODB.Recordset")
strSQL = "Select OrderNum, NeedByDate FROM OrderHed WHERE OpenOrder <> 0"
rstOrder.Open strSQL, dbsVantage arrOrder = rstOrder.GetRows rstOrder.Close

dbsVantage.Close

Response.Write "<html>" & vbCrLf
Response.Write "<head><title>Sample</title></head>" & vbCrLf Response.Write
"<body>" & vbCrLf

Response.Write "Order, Need By Date<br />" & vbCrLf

Dim i
For i = 0 To UBound(arrOrder, 2)
Response.Write arrOrder(0, i) & ", " & arrOrder(1, i) & "<br />" & vbCrLf
Next

Response.Write "</body>" & vbCrLf
Response.Write "</html>" & vbCrLf

----------------------
End Code Snippet
----------------------

We have found that dumping the recordset to an array produces much better
results than working with the recordset directly. Instead of all the
traffic back and forth between servers, the array is stored in memory - much
faster.

Still, this is slower than the native Progress tools like Report Builder.

Chris


Christopher Gitzlaff, MSCIS
Manager - Information Systems & Technology
Major Industries, Inc.
Phone: 715.842.4616 ext. 323
Email: cgitzlaff@...



-----Original Message-----
From: Shirley Graver [mailto:shirleyg@...]
Sent: Thursday, March 17, 2005 3:10 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] ODBC Performance



Christopher,
How do you have your server set up to do your web based
reporting I would love to do this on our intranet. Shirley Graver Rubber
Associates
_____

From: Gitzlaff, Christopher [mailto:cgitzlaff@...]
Sent: Wednesday, March 16, 2005 5:12 PM
To: vantage@yahoogroups.com
Subject: [Vantage] ODBC Performance

I was working on a report for our internal company website (Intranet) and
ran across an interesting performance issue. On occasion, we use Report
Builder to generate the SQL statement for our Intranet pages. We create a
basic report in Report Builder, press "<ctl><alt>P", then press "F12". This
will give the SQL statement being used by Report Builder.

Running the base report in Report Builder took less than a second. When I
copied the SQL statement from Report Builder to use with ODBC, it took
approximately 15 seconds to retrieve the data.

Am I missing something? Is there a better way to pull data for web pages?

Any help would be greatly appreciated.


Thanks,

Chris


Christopher Gitzlaff, MSCIS
Manager - Information Systems & Technology
Major Industries, Inc.
Phone: 715.842.4616 ext. 323
Email: cgitzlaff@...


Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
<http://groups.yahoo.com/group/vantage/files/>
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links





Yahoo! Groups Sponsor


ADVERTISEMENT

<http://us.ard.yahoo.com/SIG=129dvqev4/M=298184.6191685.7192823.3001176/D=gr
oups/S=1705007183:HM/EXP=1111097525/A=2593423/R=0/SIG=11el9gslf/*http:/www.n
etflix.com/Default?mqso=60190075> click here


<http://us.adserver.yahoo.com/l?M=298184.6191685.7192823.3001176/D=groups/S=
:HM/A=2593423/rand=517590561>

_____

Yahoo! Groups Links
* To visit your group on the web, go to:
http://groups.yahoo.com/group/vantage/

* To unsubscribe from this group, send an email to:
vantage-unsubscribe@yahoogroups.com
<mailto:vantage-unsubscribe@yahoogroups.com?subject=Unsubscribe>

* Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .

Tracking #: 7E9F867FA475DF43B287C6B7D3DFFDE216E3B2D9


[Non-text portions of this message have been removed]



Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
Yahoo! Groups Links