How complex can SQL be in ASPDesigner

[Adrian N B Gay]

Using oracle 8.1.6 and evaluating the ASPDesigner product, I am not able to determine easily if it possible to create SQL statements across tables using the the Designer interface.

Can you only access one table at a time in the Designer interface ?

Also I am experiencing some very heavy IO activity on my sytem when accessing our Oracle database and it takes up to 5 minutes to display a new form in Designer, after it has been selected - Is there a limit on the size of schema that can be handled by Designer ? Ours is somewhat large !!!

Hopefully Adrian

[Frank Kwong]

ASP-db is a pass through middleware and has very little to do with SQL. The JOINs can come in a mile long and as long as it is valid, it will go. WHen using JOINs remembe rto use the nammap to make it possible for the filter and sort. In order, I always use the express switch to bypass the counting and I also specify the dbCountSQL all the time. IF ASP-db has to do MoveNext to count how many records is in the table then it'll take a long time.

Designer and ASPdb works on The dbSQL. If you put in an SQL of multiple table JOINs, it'll honor that.

Finally, Oracel is never been a speed demon as far as I know. It is supposed to be big, heavy, stable and slow. I have a 330,000 flat records test database replicated across Access, MS-SQL and Oracle 8.0.5. The result indicates that Oracle is pretty sick as far as speed. The new Oracle OLEDB driver produced by Oracle seems to be faster than the old MS Oracle ODBC driver.

FK


------------
Adrian N B Gay at 7/17/01 10:35:24 AM

Using oracle 8.1.6 and evaluating the ASPDesigner product, I am not able to determine easily if it possible to create SQL statements across tables using the the Designer interface.

Can you only access one table at a time in the Designer interface ?

Also I am experiencing some very heavy IO activity on my sytem when accessing our Oracle database and it takes up to 5 minutes to display a new form in Designer, after it has been selected - Is there a limit on the size of schema that can be handled by Designer ? Ours is somewhat large !!!

Hopefully Adrian

[Adrian Gay]

Frank, Thank you vary much for your input ……

Well we have made some small progress.

I have switched all our DSN connections to ORACLE ODBC driver’s version 8.01.06.00 – it seems to have made some small difference – but I am still experiencing performance problem using ASPdesigner.

Just out of interest our ORACLE DB is around 5GB and has around 2800 tables and views, our largest tables containing around 1.5 million rows. Could this be the problem – Can ASPdesigner handle a database of this size?

Every time I select a new tab such as code or display I must wait an eternity for the focus to return to the ASPdesigner interface after and age of IO access on my system. By the way my machine is a 933 Mhz unit wit 512 Mbytes of Memory.

I have not managed to make further progress with the complex SQL queries using ASPdesigner as a result of the slow performance. I have had success with complex SQL’s when I code for ASPdb by hand

Any suggestions how to improve on the ASPdesigner performance?

You seem to suggest that once I have a complex SQL statement developed I can paste it into the ASPdesigner interface and it will honor this statement? Have I understood correctly ? Can one then use tabs such as “Name Map” to control the final report?

Thanks in advance

Regards Adrian
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~
INTERGRAPH
Adrian N B Gay
Customer Service Consultant
Intergraph Schweiz AG. Thurgauerstarsse 40. CH 8050 Zürich. SWITZERLAND.
WEB: http://www.intergraph.ch/
Tel: + 41 (0)1 308 48 48
Fax: + 41 (0)1 308 49 19
Mob: + 41 (0)78 768 6208
Email: agay@ingr.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~



------------
Frank Kwong at 7/17/01 5:42:45 PM

ASP-db is a pass through middleware and has very little to do with SQL. The JOINs can come in a mile long and as long as it is valid, it will go. WHen using JOINs remembe rto use the nammap to make it possible for the filter and sort. In order, I always use the express switch to bypass the counting and I also specify the dbCountSQL all the time. IF ASP-db has to do MoveNext to count how many records is in the table then it'll take a long time.

Designer and ASPdb works on The dbSQL. If you put in an SQL of multiple table JOINs, it'll honor that.

Finally, Oracel is never been a speed demon as far as I know. It is supposed to be big, heavy, stable and slow. I have a 330,000 flat records test database replicated across Access, MS-SQL and Oracle 8.0.5. The result indicates that Oracle is pretty sick as far as speed. The new Oracle OLEDB driver produced by Oracle seems to be faster than the old MS Oracle ODBC driver.

FK


------------
Adrian N B Gay at 7/17/01 10:35:24 AM

Using oracle 8.1.6 and evaluating the ASPDesigner product, I am not able to determine easily if it possible to create SQL statements across tables using the the Designer interface.

Can you only access one table at a time in the Designer interface ?

Also I am experiencing some very heavy IO activity on my sytem when accessing our Oracle database and it takes up to 5 minutes to display a new form in Designer, after it has been selected - Is there a limit on the size of schema that can be handled by Designer ? Ours is somewhat large !!!

Hopefully Adrian

[Frank Kwong]

Wait, something is not right....

You said Designer is the problem. Designer access the DB soruce locally or within the LAN and it should be real fast. Designer only generates code segments of the entire projects. YOur DB size is definitely big. But if you want to publish that to the web using ADO then ASP-db will not be any slower than aother alternatives. My first question is what is the performance with Oracle Designer 2000 and then what is the performance of ASP code. only (no ASP-db). If you are manipulating that many tables, I assume you are building a lot of views instead of a driectly query with JOINs.

Another suggestion is that working with that much data. The art of SQL and OLAP has to come in and make decisions.

I visited a Cold Fusion shop recently and I was asked to duplicate a Cold Fusion job. After working with the Cold Fusion programmer we found out that CF is really a serial tool. You have to practically program in serial to generate every report. After I demonstrated that ASP-db is a parallel tool using modules and templates plus a ton of features that CF cannot perform, we decided that instead of duplicating the job, we should trash the old one and build a new template that can be easily modified to fit the job. (I was very surprised that CF cannot do a drop list from a DB with VALUE=#0# and TEXT=#1# + #3#). Instead of working on a lot of JOINs, we work on individual tables and link them together with a template. So, the bottom line is that good planning would gain a lot of response time.

Since your DB is so huge, it is difficult to have me take a look. If your data is not confidnetial then let me know. May be we can make some arrangement. The biggest sample Oracel DB I has 330,000 records. Not even close to your league.

There are examples in complex JOINs and namemap. I'll dig it out and point you to that.


FK

------------
Adrian Gay at 7/18/01 8:48:59 AM


Frank, Thank you vary much for your input ……

Well we have made some small progress.

I have switched all our DSN connections to ORACLE ODBC driver’s version 8.01.06.00 – it seems to have made some small difference – but I am still experiencing performance problem using ASPdesigner.

Just out of interest our ORACLE DB is around 5GB and has around 2800 tables and views, our largest tables containing around 1.5 million rows. Could this be the problem – Can ASPdesigner handle a database of this size?

Every time I select a new tab such as code or display I must wait an eternity for the focus to return to the ASPdesigner interface after and age of IO access on my system. By the way my machine is a 933 Mhz unit wit 512 Mbytes of Memory.

I have not managed to make further progress with the complex SQL queries using ASPdesigner as a result of the slow performance. I have had success with complex SQL’s when I code for ASPdb by hand

Any suggestions how to improve on the ASPdesigner performance?

You seem to suggest that once I have a complex SQL statement developed I can paste it into the ASPdesigner interface and it will honor this statement? Have I understood correctly ? Can one then use tabs such as “Name Map” to control the final report?

Thanks in advance

Regards Adrian
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~
INTERGRAPH
Adrian N B Gay
Customer Service Consultant
Intergraph Schweiz AG. Thurgauerstarsse 40. CH 8050 Zürich. SWITZERLAND.
WEB: http://www.intergraph.ch/
Tel: + 41 (0)1 308 48 48
Fax: + 41 (0)1 308 49 19
Mob: + 41 (0)78 768 6208
Email: agay@ingr.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~



------------
Frank Kwong at 7/17/01 5:42:45 PM

ASP-db is a pass through middleware and has very little to do with SQL. The JOINs can come in a mile long and as long as it is valid, it will go. WHen using JOINs remembe rto use the nammap to make it possible for the filter and sort. In order, I always use the express switch to bypass the counting and I also specify the dbCountSQL all the time. IF ASP-db has to do MoveNext to count how many records is in the table then it'll take a long time.

Designer and ASPdb works on The dbSQL. If you put in an SQL of multiple table JOINs, it'll honor that.

Finally, Oracel is never been a speed demon as far as I know. It is supposed to be big, heavy, stable and slow. I have a 330,000 flat records test database replicated across Access, MS-SQL and Oracle 8.0.5. The result indicates that Oracle is pretty sick as far as speed. The new Oracle OLEDB driver produced by Oracle seems to be faster than the old MS Oracle ODBC driver.

FK


------------
Adrian N B Gay at 7/17/01 10:35:24 AM

Using oracle 8.1.6 and evaluating the ASPDesigner product, I am not able to determine easily if it possible to create SQL statements across tables using the the Designer interface.

Can you only access one table at a time in the Designer interface ?

Also I am experiencing some very heavy IO activity on my sytem when accessing our Oracle database and it takes up to 5 minutes to display a new form in Designer, after it has been selected - Is there a limit on the size of schema that can be handled by Designer ? Ours is somewhat large !!!

Hopefully Adrian

[Frank Kwong]

try -> http://www.aspdb.org/casestudies.shtm#case19


FK


------------
Frank Kwong at 7/19/01 1:04:10 AM

Wait, something is not right....

You said Designer is the problem. Designer access the DB soruce locally or within the LAN and it should be real fast. Designer only generates code segments of the entire projects. YOur DB size is definitely big. But if you want to publish that to the web using ADO then ASP-db will not be any slower than aother alternatives. My first question is what is the performance with Oracle Designer 2000 and then what is the performance of ASP code. only (no ASP-db). If you are manipulating that many tables, I assume you are building a lot of views instead of a driectly query with JOINs.

Another suggestion is that working with that much data. The art of SQL and OLAP has to come in and make decisions.

I visited a Cold Fusion shop recently and I was asked to duplicate a Cold Fusion job. After working with the Cold Fusion programmer we found out that CF is really a serial tool. You have to practically program in serial to generate every report. After I demonstrated that ASP-db is a parallel tool using modules and templates plus a ton of features that CF cannot perform, we decided that instead of duplicating the job, we should trash the old one and build a new template that can be easily modified to fit the job. (I was very surprised that CF cannot do a drop list from a DB with VALUE=#0# and TEXT=#1# + #3#). Instead of working on a lot of JOINs, we work on individual tables and link them together with a template. So, the bottom line is that good planning would gain a lot of response time.

Since your DB is so huge, it is difficult to have me take a look. If your data is not confidnetial then let me know. May be we can make some arrangement. The biggest sample Oracel DB I has 330,000 records. Not even close to your league.

There are examples in complex JOINs and namemap. I'll dig it out and point you to that.


FK

------------
Adrian Gay at 7/18/01 8:48:59 AM


Frank, Thank you vary much for your input ……

Well we have made some small progress.

I have switched all our DSN connections to ORACLE ODBC driver’s version 8.01.06.00 – it seems to have made some small difference – but I am still experiencing performance problem using ASPdesigner.

Just out of interest our ORACLE DB is around 5GB and has around 2800 tables and views, our largest tables containing around 1.5 million rows. Could this be the problem – Can ASPdesigner handle a database of this size?

Every time I select a new tab such as code or display I must wait an eternity for the focus to return to the ASPdesigner interface after and age of IO access on my system. By the way my machine is a 933 Mhz unit wit 512 Mbytes of Memory.

I have not managed to make further progress with the complex SQL queries using ASPdesigner as a result of the slow performance. I have had success with complex SQL’s when I code for ASPdb by hand

Any suggestions how to improve on the ASPdesigner performance?

You seem to suggest that once I have a complex SQL statement developed I can paste it into the ASPdesigner interface and it will honor this statement? Have I understood correctly ? Can one then use tabs such as “Name Map” to control the final report?

Thanks in advance

Regards Adrian
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~
INTERGRAPH
Adrian N B Gay
Customer Service Consultant
Intergraph Schweiz AG. Thurgauerstarsse 40. CH 8050 Zürich. SWITZERLAND.
WEB: http://www.intergraph.ch/
Tel: + 41 (0)1 308 48 48
Fax: + 41 (0)1 308 49 19
Mob: + 41 (0)78 768 6208
Email: agay@ingr.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~



------------
Frank Kwong at 7/17/01 5:42:45 PM

ASP-db is a pass through middleware and has very little to do with SQL. The JOINs can come in a mile long and as long as it is valid, it will go. WHen using JOINs remembe rto use the nammap to make it possible for the filter and sort. In order, I always use the express switch to bypass the counting and I also specify the dbCountSQL all the time. IF ASP-db has to do MoveNext to count how many records is in the table then it'll take a long time.

Designer and ASPdb works on The dbSQL. If you put in an SQL of multiple table JOINs, it'll honor that.

Finally, Oracel is never been a speed demon as far as I know. It is supposed to be big, heavy, stable and slow. I have a 330,000 flat records test database replicated across Access, MS-SQL and Oracle 8.0.5. The result indicates that Oracle is pretty sick as far as speed. The new Oracle OLEDB driver produced by Oracle seems to be faster than the old MS Oracle ODBC driver.

FK


------------
Adrian N B Gay at 7/17/01 10:35:24 AM

Using oracle 8.1.6 and evaluating the ASPDesigner product, I am not able to determine easily if it possible to create SQL statements across tables using the the Designer interface.

Can you only access one table at a time in the Designer interface ?

Also I am experiencing some very heavy IO activity on my sytem when accessing our Oracle database and it takes up to 5 minutes to display a new form in Designer, after it has been selected - Is there a limit on the size of schema that can be handled by Designer ? Ours is somewhat large !!!

Hopefully Adrian

[Adrian Gay]

Hi Frank

Thanks once again for getting back – I hope you do not mind continuing this dialogue in this forum.

I think I now understand what the problem has been that appeared to be ASPdesigner poor performance: As I explained the Oracle database that I would like to access is large – 5 GB with 2800 tables & views.

The database by the way belongs to a very large organization in Switzerland and contains asset information covering the whole of Switzerland – I can supply the customer name in private email but not here – sorry. I am hoping that I can persuade this organization to invest in ASPdb products as a basis for building WEB enabled applications that access this data. I have in fact already made some progress but I want the customer to invest in your enterprise suite so that we can produce web-enabled apps much more quickly.

Any way back to the problem I was experiencing – the database of course is remote and housed in the customer data center – as a result I was accessing it over the WAN using an ODBC connector – and as you suggested I switched this to use the ORACLE driver not the one supplied by Microsoft. The problem I was seeing is that it took an age to access the database in ASPdesigner and this was made worse because each time I selected one of the option tags in the left hand vertical menu of ASPdesigner a connection to the database was made again to, I assume to get schema information.

One of the worst cases is when I hit the “CODE” menu tag it was taking 6 minutes & 15 seconds (just timed it) to return a result and display the code – during which time I would here a tremendous amount of disc access and see 100% cpu activity at points during this time.

Any way I was fortunate to have a local copy of the database on a machine here (we are of course not allowed to install anything on the production database server) which we use for test and analysis work – when I installed ASPdesigner on the same machine as the oracle database and connected directly to the database using the oracle SID using Driver={ORACLE ODBC Driver}; UID=dfap; PWD=dfap; Server=orac I found that when I used ASPdesigner it would fly and would after hitting the “CODE” menu tab , display code for ASPdb in under 25 seconds.

This means that if the database and ASPdesigner are installed on the same machine then we have a usable tool – all thought it is still a little sluggish but definetly better than trying to code all this by hand.

By the way once we have the ASP code for ASPdb it performs VERY WELL and provides some great features – the customer that I am working with is very impressed.

Here is a simple example of an SQL used by the customer – you can see why we could really make use of ASPdesigner !!!!!

MySQL = ""
MySQL = MySQL & "select "
MySQL = MySQL & "ST_NR_TAG as ""Strecke No"", "
MySQL = MySQL & "RB_PRMRY, "
MySQL = MySQL & "RB_SCNDRY, "
MySQL = MySQL & "A0113_GEOM_DATUM, "
MySQL = MySQL & "A0113_GEOM_UID, "
MySQL = MySQL & "A0113_KSTLE_STR as ""Cost Center"", "
MySQL = MySQL & "A0341_KM_VON as ""km von"", "
MySQL = MySQL & "A0341_KM_BIS as ""km bis"", "
MySQL = MySQL & "A0340_ABKUERZUNG as ""Srecke Abkurz"", "
MySQL = MySQL & "A0340_BESCHREIBUNG as ""Srecke Name"", "
MySQL = MySQL & "A.A0105_NR as ""von BPS Nr"", "
MySQL = MySQL & "A.A0105_ABKUERZUNG as ""von BPS ABK"", "
MySQL = MySQL & "A.A0105_BEZEICHNUNG as ""von BPS BEZ"", "
MySQL = MySQL & "B.A0105_NR as ""bis BPS Nr"", "
MySQL = MySQL & "B.A0105_ABKUERZUNG as ""bis BPS ABK"", "
MySQL = MySQL & "B.A0105_BEZEICHNUNG as ""bis BPS BEZ"" "
MySQL = MySQL & "from dfap.vstta, dfap.ta0113, dfap.ta0341, dfap.ta0340, dfap.ta0105 A, dfap.ta0105 B "
MySQL = MySQL & "where "
MySQL = MySQL & "vstta.rb_prmry=" & Request.querystring("RB_PRMRY&#34 & " and vstta.rb_scndry=" & Request.querystring("RB_SCNDRY&#34 & " "
MySQL = MySQL & "and vstta.st_nr_tag = ta0113.a0113_nr "
MySQL = MySQL & "and vstta.st_nr_tag = ta0341.a0341_a0113_nr "
MySQL = MySQL & "and vstta.st_nr_tag = ta0341.a0341_a0113_nr "
MySQL = MySQL & "and ta0341.A0341_A0340_NR = A0340_A0340_NR "
MySQL = MySQL & "and ta0340.A0340_A0340_NR = ta0341.A0341_A0340_NR "
MySQL = MySQL & "and a0113_a0112_bp_von = A.a0105_nr "
MySQL = MySQL & "and a0113_a0112_bp_bis = B.a0105_nr "


In closing – We seem to have a way forward we must replicate our database in order to use ASPdesigner to develop applications using ASPdb. Performance of ASPdesigner over our WAN and ODBC is not good in our case.

Having said this the benefits of the quick development achieved using ASPdesigner will definitely pay off.

Thanks in advance

Regards Adrian
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~
INTERGRAPH
Adrian N B Gay
Customer Service Consultant
Intergraph Schweiz AG. Thurgauerstarsse 40. CH 8050 Zürich. SWITZERLAND.
WEB: http://www.intergraph.ch/
Tel: + 41 (0)1 308 48 48
Fax: + 41 (0)1 308 49 19
Mob: + 41 (0)78 768 6208
Email: agay@ingr.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~

------------
Frank Kwong at 7/19/01 1:05:54 AM

try -> http://www.aspdb.org/casestudies.shtm#case19


FK


------------
Frank Kwong at 7/19/01 1:04:10 AM

Wait, something is not right....

You said Designer is the problem. Designer access the DB soruce locally or within the LAN and it should be real fast. Designer only generates code segments of the entire projects. YOur DB size is definitely big. But if you want to publish that to the web using ADO then ASP-db will not be any slower than aother alternatives. My first question is what is the performance with Oracle Designer 2000 and then what is the performance of ASP code. only (no ASP-db). If you are manipulating that many tables, I assume you are building a lot of views instead of a driectly query with JOINs.

Another suggestion is that working with that much data. The art of SQL and OLAP has to come in and make decisions.

I visited a Cold Fusion shop recently and I was asked to duplicate a Cold Fusion job. After working with the Cold Fusion programmer we found out that CF is really a serial tool. You have to practically program in serial to generate every report. After I demonstrated that ASP-db is a parallel tool using modules and templates plus a ton of features that CF cannot perform, we decided that instead of duplicating the job, we should trash the old one and build a new template that can be easily modified to fit the job. (I was very surprised that CF cannot do a drop list from a DB with VALUE=#0# and TEXT=#1# + #3#). Instead of working on a lot of JOINs, we work on individual tables and link them together with a template. So, the bottom line is that good planning would gain a lot of response time.

Since your DB is so huge, it is difficult to have me take a look. If your data is not confidnetial then let me know. May be we can make some arrangement. The biggest sample Oracel DB I has 330,000 records. Not even close to your league.

There are examples in complex JOINs and namemap. I'll dig it out and point you to that.


FK

------------
Adrian Gay at 7/18/01 8:48:59 AM


Frank, Thank you vary much for your input ……

Well we have made some small progress.

I have switched all our DSN connections to ORACLE ODBC driver’s version 8.01.06.00 – it seems to have made some small difference – but I am still experiencing performance problem using ASPdesigner.

Just out of interest our ORACLE DB is around 5GB and has around 2800 tables and views, our largest tables containing around 1.5 million rows. Could this be the problem – Can ASPdesigner handle a database of this size?

Every time I select a new tab such as code or display I must wait an eternity for the focus to return to the ASPdesigner interface after and age of IO access on my system. By the way my machine is a 933 Mhz unit wit 512 Mbytes of Memory.

I have not managed to make further progress with the complex SQL queries using ASPdesigner as a result of the slow performance. I have had success with complex SQL’s when I code for ASPdb by hand

Any suggestions how to improve on the ASPdesigner performance?

You seem to suggest that once I have a complex SQL statement developed I can paste it into the ASPdesigner interface and it will honor this statement? Have I understood correctly ? Can one then use tabs such as “Name Map” to control the final report?

Thanks in advance

Regards Adrian
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~
INTERGRAPH
Adrian N B Gay
Customer Service Consultant
Intergraph Schweiz AG. Thurgauerstarsse 40. CH 8050 Zürich. SWITZERLAND.
WEB: http://www.intergraph.ch/
Tel: + 41 (0)1 308 48 48
Fax: + 41 (0)1 308 49 19
Mob: + 41 (0)78 768 6208
Email: agay@ingr.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~



------------
Frank Kwong at 7/17/01 5:42:45 PM

ASP-db is a pass through middleware and has very little to do with SQL. The JOINs can come in a mile long and as long as it is valid, it will go. WHen using JOINs remembe rto use the nammap to make it possible for the filter and sort. In order, I always use the express switch to bypass the counting and I also specify the dbCountSQL all the time. IF ASP-db has to do MoveNext to count how many records is in the table then it'll take a long time.

Designer and ASPdb works on The dbSQL. If you put in an SQL of multiple table JOINs, it'll honor that.

Finally, Oracel is never been a speed demon as far as I know. It is supposed to be big, heavy, stable and slow. I have a 330,000 flat records test database replicated across Access, MS-SQL and Oracle 8.0.5. The result indicates that Oracle is pretty sick as far as speed. The new Oracle OLEDB driver produced by Oracle seems to be faster than the old MS Oracle ODBC driver.

FK


------------
Adrian N B Gay at 7/17/01 10:35:24 AM

Using oracle 8.1.6 and evaluating the ASPDesigner product, I am not able to determine easily if it possible to create SQL statements across tables using the the Designer interface.

Can you only access one table at a time in the Designer interface ?

Also I am experiencing some very heavy IO activity on my sytem when accessing our Oracle database and it takes up to 5 minutes to display a new form in Designer, after it has been selected - Is there a limit on the size of schema that can be handled by Designer ? Ours is somewhat large !!!

Hopefully Adrian

[Frank Kwong]

How do you come to the conclusion that ASP-dbDesigner performance is poor in such remote operation ? What is the Oracle Designer2000's performance ? Our experience is that ADO, MTS remote performance is very poor. Just placing the component is a remote MTS will kill the performance.

FK

------------
Adrian Gay at 7/19/01 4:22:41 AM


Hi Frank

Thanks once again for getting back – I hope you do not mind continuing this dialogue in this forum.

I think I now understand what the problem has been that appeared to be ASPdesigner poor performance: As I explained the Oracle database that I would like to access is large – 5 GB with 2800 tables & views.

The database by the way belongs to a very large organization in Switzerland and contains asset information covering the whole of Switzerland – I can supply the customer name in private email but not here – sorry. I am hoping that I can persuade this organization to invest in ASPdb products as a basis for building WEB enabled applications that access this data. I have in fact already made some progress but I want the customer to invest in your enterprise suite so that we can produce web-enabled apps much more quickly.

Any way back to the problem I was experiencing – the database of course is remote and housed in the customer data center – as a result I was accessing it over the WAN using an ODBC connector – and as you suggested I switched this to use the ORACLE driver not the one supplied by Microsoft. The problem I was seeing is that it took an age to access the database in ASPdesigner and this was made worse because each time I selected one of the option tags in the left hand vertical menu of ASPdesigner a connection to the database was made again to, I assume to get schema information.

One of the worst cases is when I hit the “CODE” menu tag it was taking 6 minutes & 15 seconds (just timed it) to return a result and display the code – during which time I would here a tremendous amount of disc access and see 100% cpu activity at points during this time.

Any way I was fortunate to have a local copy of the database on a machine here (we are of course not allowed to install anything on the production database server) which we use for test and analysis work – when I installed ASPdesigner on the same machine as the oracle database and connected directly to the database using the oracle SID using Driver={ORACLE ODBC Driver}; UID=dfap; PWD=dfap; Server=orac I found that when I used ASPdesigner it would fly and would after hitting the “CODE” menu tab , display code for ASPdb in under 25 seconds.

This means that if the database and ASPdesigner are installed on the same machine then we have a usable tool – all thought it is still a little sluggish but definetly better than trying to code all this by hand.

By the way once we have the ASP code for ASPdb it performs VERY WELL and provides some great features – the customer that I am working with is very impressed.

Here is a simple example of an SQL used by the customer – you can see why we could really make use of ASPdesigner !!!!!

MySQL = ""
MySQL = MySQL & "select "
MySQL = MySQL & "ST_NR_TAG as ""Strecke No"", "
MySQL = MySQL & "RB_PRMRY, "
MySQL = MySQL & "RB_SCNDRY, "
MySQL = MySQL & "A0113_GEOM_DATUM, "
MySQL = MySQL & "A0113_GEOM_UID, "
MySQL = MySQL & "A0113_KSTLE_STR as ""Cost Center"", "
MySQL = MySQL & "A0341_KM_VON as ""km von"", "
MySQL = MySQL & "A0341_KM_BIS as ""km bis"", "
MySQL = MySQL & "A0340_ABKUERZUNG as ""Srecke Abkurz"", "
MySQL = MySQL & "A0340_BESCHREIBUNG as ""Srecke Name"", "
MySQL = MySQL & "A.A0105_NR as ""von BPS Nr"", "
MySQL = MySQL & "A.A0105_ABKUERZUNG as ""von BPS ABK"", "
MySQL = MySQL & "A.A0105_BEZEICHNUNG as ""von BPS BEZ"", "
MySQL = MySQL & "B.A0105_NR as ""bis BPS Nr"", "
MySQL = MySQL & "B.A0105_ABKUERZUNG as ""bis BPS ABK"", "
MySQL = MySQL & "B.A0105_BEZEICHNUNG as ""bis BPS BEZ"" "
MySQL = MySQL & "from dfap.vstta, dfap.ta0113, dfap.ta0341, dfap.ta0340, dfap.ta0105 A, dfap.ta0105 B "
MySQL = MySQL & "where "
MySQL = MySQL & "vstta.rb_prmry=" & Request.querystring("RB_PRMRY&#34 & " and vstta.rb_scndry=" & Request.querystring("RB_SCNDRY&#34 & " "
MySQL = MySQL & "and vstta.st_nr_tag = ta0113.a0113_nr "
MySQL = MySQL & "and vstta.st_nr_tag = ta0341.a0341_a0113_nr "
MySQL = MySQL & "and vstta.st_nr_tag = ta0341.a0341_a0113_nr "
MySQL = MySQL & "and ta0341.A0341_A0340_NR = A0340_A0340_NR "
MySQL = MySQL & "and ta0340.A0340_A0340_NR = ta0341.A0341_A0340_NR "
MySQL = MySQL & "and a0113_a0112_bp_von = A.a0105_nr "
MySQL = MySQL & "and a0113_a0112_bp_bis = B.a0105_nr "


In closing – We seem to have a way forward we must replicate our database in order to use ASPdesigner to develop applications using ASPdb. Performance of ASPdesigner over our WAN and ODBC is not good in our case.

Having said this the benefits of the quick development achieved using ASPdesigner will definitely pay off.

Thanks in advance

Regards Adrian
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~
INTERGRAPH
Adrian N B Gay
Customer Service Consultant
Intergraph Schweiz AG. Thurgauerstarsse 40. CH 8050 Zürich. SWITZERLAND.
WEB: http://www.intergraph.ch/
Tel: + 41 (0)1 308 48 48
Fax: + 41 (0)1 308 49 19
Mob: + 41 (0)78 768 6208
Email: agay@ingr.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~

------------
Frank Kwong at 7/19/01 1:05:54 AM

try -> http://www.aspdb.org/casestudies.shtm#case19


FK


------------
Frank Kwong at 7/19/01 1:04:10 AM

Wait, something is not right....

You said Designer is the problem. Designer access the DB soruce locally or within the LAN and it should be real fast. Designer only generates code segments of the entire projects. YOur DB size is definitely big. But if you want to publish that to the web using ADO then ASP-db will not be any slower than aother alternatives. My first question is what is the performance with Oracle Designer 2000 and then what is the performance of ASP code. only (no ASP-db). If you are manipulating that many tables, I assume you are building a lot of views instead of a driectly query with JOINs.

Another suggestion is that working with that much data. The art of SQL and OLAP has to come in and make decisions.

I visited a Cold Fusion shop recently and I was asked to duplicate a Cold Fusion job. After working with the Cold Fusion programmer we found out that CF is really a serial tool. You have to practically program in serial to generate every report. After I demonstrated that ASP-db is a parallel tool using modules and templates plus a ton of features that CF cannot perform, we decided that instead of duplicating the job, we should trash the old one and build a new template that can be easily modified to fit the job. (I was very surprised that CF cannot do a drop list from a DB with VALUE=#0# and TEXT=#1# + #3#). Instead of working on a lot of JOINs, we work on individual tables and link them together with a template. So, the bottom line is that good planning would gain a lot of response time.

Since your DB is so huge, it is difficult to have me take a look. If your data is not confidnetial then let me know. May be we can make some arrangement. The biggest sample Oracel DB I has 330,000 records. Not even close to your league.

There are examples in complex JOINs and namemap. I'll dig it out and point you to that.


FK

------------
Adrian Gay at 7/18/01 8:48:59 AM


Frank, Thank you vary much for your input ……

Well we have made some small progress.

I have switched all our DSN connections to ORACLE ODBC driver’s version 8.01.06.00 – it seems to have made some small difference – but I am still experiencing performance problem using ASPdesigner.

Just out of interest our ORACLE DB is around 5GB and has around 2800 tables and views, our largest tables containing around 1.5 million rows. Could this be the problem – Can ASPdesigner handle a database of this size?

Every time I select a new tab such as code or display I must wait an eternity for the focus to return to the ASPdesigner interface after and age of IO access on my system. By the way my machine is a 933 Mhz unit wit 512 Mbytes of Memory.

I have not managed to make further progress with the complex SQL queries using ASPdesigner as a result of the slow performance. I have had success with complex SQL’s when I code for ASPdb by hand

Any suggestions how to improve on the ASPdesigner performance?

You seem to suggest that once I have a complex SQL statement developed I can paste it into the ASPdesigner interface and it will honor this statement? Have I understood correctly ? Can one then use tabs such as “Name Map” to control the final report?

Thanks in advance

Regards Adrian
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~
INTERGRAPH
Adrian N B Gay
Customer Service Consultant
Intergraph Schweiz AG. Thurgauerstarsse 40. CH 8050 Zürich. SWITZERLAND.
WEB: http://www.intergraph.ch/
Tel: + 41 (0)1 308 48 48
Fax: + 41 (0)1 308 49 19
Mob: + 41 (0)78 768 6208
Email: agay@ingr.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~



------------
Frank Kwong at 7/17/01 5:42:45 PM

ASP-db is a pass through middleware and has very little to do with SQL. The JOINs can come in a mile long and as long as it is valid, it will go. WHen using JOINs remembe rto use the nammap to make it possible for the filter and sort. In order, I always use the express switch to bypass the counting and I also specify the dbCountSQL all the time. IF ASP-db has to do MoveNext to count how many records is in the table then it'll take a long time.

Designer and ASPdb works on The dbSQL. If you put in an SQL of multiple table JOINs, it'll honor that.

Finally, Oracel is never been a speed demon as far as I know. It is supposed to be big, heavy, stable and slow. I have a 330,000 flat records test database replicated across Access, MS-SQL and Oracle 8.0.5. The result indicates that Oracle is pretty sick as far as speed. The new Oracle OLEDB driver produced by Oracle seems to be faster than the old MS Oracle ODBC driver.

FK


------------
Adrian N B Gay at 7/17/01 10:35:24 AM

Using oracle 8.1.6 and evaluating the ASPDesigner product, I am not able to determine easily if it possible to create SQL statements across tables using the the Designer interface.

Can you only access one table at a time in the Designer interface ?

Also I am experiencing some very heavy IO activity on my sytem when accessing our Oracle database and it takes up to 5 minutes to display a new form in Designer, after it has been selected - Is there a limit on the size of schema that can be handled by Designer ? Ours is somewhat large !!!

Hopefully Adrian

[Adrian Gay]

Hi Frank, You seem to have miss-read my post.

When clicking the code tab in ASPDesigner ...

ASPdesigner over WAN to our ORACLE database - performance of 6 mins & 15 secs.
ASPdesigner local to our ORACLE database - performance of 25 secs.

Conclusion accessing our ORACLE database from ASPdesigner over the WAN is greatly effected by the performance of the ODBC driver and the performance of our network. ASPdesigner is NOT the bottle – neck, the ODBC driver and the network are.

Note: Using the ASPdb code after it has been created in ASPdesigner to both remote and local database shows very good performance when producing reports.

Unfortunately we do not have Oracle Designer2000, but I suspect I would see similar results over the network.

Regards Adrian


------------
Frank Kwong at 7/21/01 10:43:43 AM

How do you come to the conclusion that ASP-dbDesigner performance is poor in such remote operation ? What is the Oracle Designer2000's performance ? Our experience is that ADO, MTS remote performance is very poor. Just placing the component is a remote MTS will kill the performance.

FK

------------
Adrian Gay at 7/19/01 4:22:41 AM


Hi Frank

Thanks once again for getting back – I hope you do not mind continuing this dialogue in this forum.

I think I now understand what the problem has been that appeared to be ASPdesigner poor performance: As I explained the Oracle database that I would like to access is large – 5 GB with 2800 tables & views.

The database by the way belongs to a very large organization in Switzerland and contains asset information covering the whole of Switzerland – I can supply the customer name in private email but not here – sorry. I am hoping that I can persuade this organization to invest in ASPdb products as a basis for building WEB enabled applications that access this data. I have in fact already made some progress but I want the customer to invest in your enterprise suite so that we can produce web-enabled apps much more quickly.

Any way back to the problem I was experiencing – the database of course is remote and housed in the customer data center – as a result I was accessing it over the WAN using an ODBC connector – and as you suggested I switched this to use the ORACLE driver not the one supplied by Microsoft. The problem I was seeing is that it took an age to access the database in ASPdesigner and this was made worse because each time I selected one of the option tags in the left hand vertical menu of ASPdesigner a connection to the database was made again to, I assume to get schema information.

One of the worst cases is when I hit the “CODE” menu tag it was taking 6 minutes & 15 seconds (just timed it) to return a result and display the code – during which time I would here a tremendous amount of disc access and see 100% cpu activity at points during this time.

Any way I was fortunate to have a local copy of the database on a machine here (we are of course not allowed to install anything on the production database server) which we use for test and analysis work – when I installed ASPdesigner on the same machine as the oracle database and connected directly to the database using the oracle SID using Driver={ORACLE ODBC Driver}; UID=dfap; PWD=dfap; Server=orac I found that when I used ASPdesigner it would fly and would after hitting the “CODE” menu tab , display code for ASPdb in under 25 seconds.

This means that if the database and ASPdesigner are installed on the same machine then we have a usable tool – all thought it is still a little sluggish but definetly better than trying to code all this by hand.

By the way once we have the ASP code for ASPdb it performs VERY WELL and provides some great features – the customer that I am working with is very impressed.

Here is a simple example of an SQL used by the customer – you can see why we could really make use of ASPdesigner !!!!!

MySQL = ""
MySQL = MySQL & "select "
MySQL = MySQL & "ST_NR_TAG as ""Strecke No"", "
MySQL = MySQL & "RB_PRMRY, "
MySQL = MySQL & "RB_SCNDRY, "
MySQL = MySQL & "A0113_GEOM_DATUM, "
MySQL = MySQL & "A0113_GEOM_UID, "
MySQL = MySQL & "A0113_KSTLE_STR as ""Cost Center"", "
MySQL = MySQL & "A0341_KM_VON as ""km von"", "
MySQL = MySQL & "A0341_KM_BIS as ""km bis"", "
MySQL = MySQL & "A0340_ABKUERZUNG as ""Srecke Abkurz"", "
MySQL = MySQL & "A0340_BESCHREIBUNG as ""Srecke Name"", "
MySQL = MySQL & "A.A0105_NR as ""von BPS Nr"", "
MySQL = MySQL & "A.A0105_ABKUERZUNG as ""von BPS ABK"", "
MySQL = MySQL & "A.A0105_BEZEICHNUNG as ""von BPS BEZ"", "
MySQL = MySQL & "B.A0105_NR as ""bis BPS Nr"", "
MySQL = MySQL & "B.A0105_ABKUERZUNG as ""bis BPS ABK"", "
MySQL = MySQL & "B.A0105_BEZEICHNUNG as ""bis BPS BEZ"" "
MySQL = MySQL & "from dfap.vstta, dfap.ta0113, dfap.ta0341, dfap.ta0340, dfap.ta0105 A, dfap.ta0105 B "
MySQL = MySQL & "where "
MySQL = MySQL & "vstta.rb_prmry=" & Request.querystring("RB_PRMRY&#34 & " and vstta.rb_scndry=" & Request.querystring("RB_SCNDRY&#34 & " "
MySQL = MySQL & "and vstta.st_nr_tag = ta0113.a0113_nr "
MySQL = MySQL & "and vstta.st_nr_tag = ta0341.a0341_a0113_nr "
MySQL = MySQL & "and vstta.st_nr_tag = ta0341.a0341_a0113_nr "
MySQL = MySQL & "and ta0341.A0341_A0340_NR = A0340_A0340_NR "
MySQL = MySQL & "and ta0340.A0340_A0340_NR = ta0341.A0341_A0340_NR "
MySQL = MySQL & "and a0113_a0112_bp_von = A.a0105_nr "
MySQL = MySQL & "and a0113_a0112_bp_bis = B.a0105_nr "


In closing – We seem to have a way forward we must replicate our database in order to use ASPdesigner to develop applications using ASPdb. Performance of ASPdesigner over our WAN and ODBC is not good in our case.

Having said this the benefits of the quick development achieved using ASPdesigner will definitely pay off.

Thanks in advance

Regards Adrian
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~
INTERGRAPH
Adrian N B Gay
Customer Service Consultant
Intergraph Schweiz AG. Thurgauerstarsse 40. CH 8050 Zürich. SWITZERLAND.
WEB: http://www.intergraph.ch/
Tel: + 41 (0)1 308 48 48
Fax: + 41 (0)1 308 49 19
Mob: + 41 (0)78 768 6208
Email: agay@ingr.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~

------------
Frank Kwong at 7/19/01 1:05:54 AM

try -> http://www.aspdb.org/casestudies.shtm#case19


FK


------------
Frank Kwong at 7/19/01 1:04:10 AM

Wait, something is not right....

You said Designer is the problem. Designer access the DB soruce locally or within the LAN and it should be real fast. Designer only generates code segments of the entire projects. YOur DB size is definitely big. But if you want to publish that to the web using ADO then ASP-db will not be any slower than aother alternatives. My first question is what is the performance with Oracle Designer 2000 and then what is the performance of ASP code. only (no ASP-db). If you are manipulating that many tables, I assume you are building a lot of views instead of a driectly query with JOINs.

Another suggestion is that working with that much data. The art of SQL and OLAP has to come in and make decisions.

I visited a Cold Fusion shop recently and I was asked to duplicate a Cold Fusion job. After working with the Cold Fusion programmer we found out that CF is really a serial tool. You have to practically program in serial to generate every report. After I demonstrated that ASP-db is a parallel tool using modules and templates plus a ton of features that CF cannot perform, we decided that instead of duplicating the job, we should trash the old one and build a new template that can be easily modified to fit the job. (I was very surprised that CF cannot do a drop list from a DB with VALUE=#0# and TEXT=#1# + #3#). Instead of working on a lot of JOINs, we work on individual tables and link them together with a template. So, the bottom line is that good planning would gain a lot of response time.

Since your DB is so huge, it is difficult to have me take a look. If your data is not confidnetial then let me know. May be we can make some arrangement. The biggest sample Oracel DB I has 330,000 records. Not even close to your league.

There are examples in complex JOINs and namemap. I'll dig it out and point you to that.


FK

------------
Adrian Gay at 7/18/01 8:48:59 AM


Frank, Thank you vary much for your input ……

Well we have made some small progress.

I have switched all our DSN connections to ORACLE ODBC driver’s version 8.01.06.00 – it seems to have made some small difference – but I am still experiencing performance problem using ASPdesigner.

Just out of interest our ORACLE DB is around 5GB and has around 2800 tables and views, our largest tables containing around 1.5 million rows. Could this be the problem – Can ASPdesigner handle a database of this size?

Every time I select a new tab such as code or display I must wait an eternity for the focus to return to the ASPdesigner interface after and age of IO access on my system. By the way my machine is a 933 Mhz unit wit 512 Mbytes of Memory.

I have not managed to make further progress with the complex SQL queries using ASPdesigner as a result of the slow performance. I have had success with complex SQL’s when I code for ASPdb by hand

Any suggestions how to improve on the ASPdesigner performance?

You seem to suggest that once I have a complex SQL statement developed I can paste it into the ASPdesigner interface and it will honor this statement? Have I understood correctly ? Can one then use tabs such as “Name Map” to control the final report?

Thanks in advance

Regards Adrian
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~
INTERGRAPH
Adrian N B Gay
Customer Service Consultant
Intergraph Schweiz AG. Thurgauerstarsse 40. CH 8050 Zürich. SWITZERLAND.
WEB: http://www.intergraph.ch/
Tel: + 41 (0)1 308 48 48
Fax: + 41 (0)1 308 49 19
Mob: + 41 (0)78 768 6208
Email: agay@ingr.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~



------------
Frank Kwong at 7/17/01 5:42:45 PM

ASP-db is a pass through middleware and has very little to do with SQL. The JOINs can come in a mile long and as long as it is valid, it will go. WHen using JOINs remembe rto use the nammap to make it possible for the filter and sort. In order, I always use the express switch to bypass the counting and I also specify the dbCountSQL all the time. IF ASP-db has to do MoveNext to count how many records is in the table then it'll take a long time.

Designer and ASPdb works on The dbSQL. If you put in an SQL of multiple table JOINs, it'll honor that.

Finally, Oracel is never been a speed demon as far as I know. It is supposed to be big, heavy, stable and slow. I have a 330,000 flat records test database replicated across Access, MS-SQL and Oracle 8.0.5. The result indicates that Oracle is pretty sick as far as speed. The new Oracle OLEDB driver produced by Oracle seems to be faster than the old MS Oracle ODBC driver.

FK


------------
Adrian N B Gay at 7/17/01 10:35:24 AM

Using oracle 8.1.6 and evaluating the ASPDesigner product, I am not able to determine easily if it possible to create SQL statements across tables using the the Designer interface.

Can you only access one table at a time in the Designer interface ?

Also I am experiencing some very heavy IO activity on my sytem when accessing our Oracle database and it takes up to 5 minutes to display a new form in Designer, after it has been selected - Is there a limit on the size of schema that can be handled by Designer ? Ours is somewhat large !!!

Hopefully Adrian

[Frank Kwong]

Have you tried this ->
Load a copy of ASP-db in the remote CPU. Use a local copy of the DB shell to develop the code in the local CPU. Upload the temp.asp to the remote CPU and execute that via http://remoteIP/aspdb/temp.asp. This way, there is no need for the Oracle client. We use this to debug for cusotmers and is very effective.

FK

------------
Adrian N B Gay at 7/17/01 10:35:24 AM

Using oracle 8.1.6 and evaluating the ASPDesigner product, I am not able to determine easily if it possible to create SQL statements across tables using the the Designer interface.

Can you only access one table at a time in the Designer interface ?

Also I am experiencing some very heavy IO activity on my sytem when accessing our Oracle database and it takes up to 5 minutes to display a new form in Designer, after it has been selected - Is there a limit on the size of schema that can be handled by Designer ? Ours is somewhat large !!!

Hopefully Adrian