Zafif, The suggestion of your colleagues is a little out of date, I think. In a relational database, which SQLServer 7.0 is, and if you have designed your database schema correctly to represent the relationships between the tables, then having several inner/outer joins will not really affect your performance, especially if you are using a Type IV JDBC driver. I have had joins nested four/five deep in some instances, and I have had no performance issues. In the old days, having many nested joins did affect performance. But it's not really an issue with the newer relational database's. Not unless you have a REALLY poorly designed database schema, or a HUGE amount of data in the database, or a poor JDBC driver. And if your database becomes so big (number of records wise) that it does affect your join performance, you need to consider archiving some of your older data. So don't go with temporary tables to store result sets in, that will only set you up for data concurrency problems, as you have already noted in your email.
How were you able to get Crystal Reports to reside on the same server with JRUN? The one time I tried this, with Crystal reports server 8.5, I got conflicts out the wazzoo! Or are you calling your Crystal Reports from another server than the one JRUN resides on? And if so, did you have to use RMI? If you did get Crystal Server to work with JRUN, can you share with us how you configured the two to work together? And how you are calling the reports from within your JSP pages? I'm not that knowledgeable regarding Crystal Reports. I spent about a week playing with it on the same server I had JRUN and IIS on about a year ago, and gave up and moved my reports over to XML/XSLT. That worked for me only because the reports we were looking at were simple. I am interested in knowing how you got Crystal working in JSP pages, and with JRUN server, in the event I am faced with more complex reporting requirements. Celeste -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 07, 2002 2:14 PM To: JRun-Talk Subject: RE: Using Views Thanks Celeste. My background in programming is not even MS, it's Lotus Notes. (I know I know.. it's not a real programming language but knowing lotus script helped me get into real programming.) Creating and dropping tables was what some colleagues (non java) of mine suggested to avoid having too many joints. I think I explained wrong what I meant to say. They said to create these tables to store the resultsets of queries that have a lot of joins. I wanted to call them temporary tables but didn't want to get people confused with SQL temporary tables. But the problem I see in their suggested solution, is that everytime the data is updated, then these tables would have to be dropped and recreated. Since they are not the ones being updated but the normalized tables. I was told that if you have to make more than 3 joints, then you will have performance issues. I thought of using views and then storing them as appl variables. This specific application is small (around 5000 records) As for the reports, I first started rewriting them in SQL and JSP. But then I got access to Crystal Report and I saved all the SQLs as Stored Procedures. I would love to have used XML/XSLT but there were all these different components I had to learn to implement, that I decided to take the easy way for reporting and use Crystal Report or just JSP/SQL. But I'll definitely check out your suggestion about using Jrun stored procedure tag for the updates. Thanks! ps We are using MS SQL Server 7. "Haseltine, Celeste" To: Jrun-Talk <[EMAIL PROTECTED]> <CHaseltine@magt cc: icket.com> Subject: RE: Using Views 03/07/2002 03:01 PM Please respond to jrun-talk Zeina, Congradulations on your first JSP/JRUN product. I also made the switch from MS to Java/JSP about 2 years ago, and I haven't regretted it yet. I'm afraid I don't have any advice to offer regarding database views, as I prefer to use SQL inner or outer joins to return the exact data I need in a result set. But your statement regarding "creating and dropping tables everytime I need to update the database" caught my attention. When I need to update my database, I don't create any temporary tables, and I'm not sure why you would want to do it that way. I prefer to use stored procedures to update my database, and use the JRUN stored procedure tag. You can return the @@error value from the execution of a stored procedure to determine whether the stored procedure executed correctly, or if not, if you need to do a roll back on the table(s) in question and send an error message to either your user or to the webmaster/network admin. I don't have Drew's book, but if you get a good book on SQLServer 2000, you can read up on using stored procuedures to update your tables and to return result sets to you. Again, you can use the JRUN stored proceudre tag (if you using 3.1), to execute your stored procedures. By the way, how did you duplicate your Access reports in JSP? Did you use XML/XSLT to create the reports for viewing on the browser, or did you find another means of creating reports? Celeste -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 07, 2002 1:30 PM To: JRun-Talk Subject: Using Views First, want to thank you all for telling me about, pooling, jrun tags, using QueryTable, cashing resultset and application variables. Oh and avoiding having many resultsets. That last part was more difficult to implement not from the SQL part, but in the jsp, to avoid duplicate rows. I used conditions and looping but the way the data was presented, did not make it easy. Oh and Drew, great book. Found it extremely helpful and useful. I actually refer to it all the time. My assignment was to take an access database that was designed in 1994 and redesign it in JSP and Jrun. As you can imagine, I had to go through thousands of queries, tables and macros to really identify all the elements being used. Worse part, the reports done by the application were all wrong because they were pulling data from wrong queries and tables. The first stage of my redesign, I tried to replicate the design and functionalities of the existing application in JRUN and MS SQL Server. (Not to mention that's how I learned JSP ;). Anyway, I was able to do that, and was able to produce all the reports correctly, all the functionalities etc.. Now that I understand all the parts of the application and business side, I'm remodeling the data, and simplifying the processes and making them more generic and reusable modules. . Sorry for the lengthy background... anyway, my question is the following: I have normalized the tables, and we ended up needing 1/4 of the fields and tables originally created. I was planning on using Views to avoid having too many joints in each SQL. I am also planning to catch the resultsets. Do any of you know the disadvantages of using views? I like them because they are dynamic so it's better than having to create and drop tables every time we need to update the database. Thanks, Zeina ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
