SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Web Application Method (ASP?)
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

davidpardoe
Constraint Violating Yak Guru

United Kingdom
324 Posts

Posted - 01/04/2002 :  06:48:46  Show Profile
Hope everyone had a great Christmas and is bright and shiny for the New Year!

We are developing a web application that runs queries across a SQL Server database. The database holds a series of "dictionary" tables which are used to dynamically create the query from within a stored procedure.

Selections of various parameters are made on the web page. The possible values are loaded from the dictionary tables.

My question is what is the most appropriate way of running this application?

Is it better to pull all of the dictionary values from the server to the web page (client) then run things locally on the client (drop down lists etc) or is it better to make a cll to the server every time something is selected on the web page?

More specifically; I have two dictionary tables one of which is report_type and one of which is report_type_fields (one-to-many link). A drop down list for report_types is first populated on the web page. Now, when the user selects a specific report type a second drop down should be populated with the report_type_fields associated with that report type. When the report type is selected should I be sending a query back to the server to pull the appropriate report_type_fields or should I have loaded them to the client/web page already and hence can run this query on the client/web page?

Sorry for the long winded explanation - I'm not back in the swing of things yet!





============
The Dabbler!

Rafiq
Starting Member

India
25 Posts

Posted - 01/04/2002 :  07:30:55  Show Profile  Visit Rafiq's Homepage
Hi David,

I understand your query. Its a valid query. I want to tell a suggestion for this. We can solve this problem both way. but Its differed from your data size. If your data is very large,please choose server side query.otherwise choose clientside query. We are solved this problem client side. Why becoz? Our data size is small.

Regards,

Rafiq
Go to Top of Page

smccreadie
Aged Yak Warrior

USA
505 Posts

Posted - 01/04/2002 :  07:48:01  Show Profile  Visit smccreadie's Homepage
It all depends on how the application is used. If the report_type and report_type_fields change a lot in the database, it makes sense to always feed them dynamically into web page. If however, they don't change a lot and especially if you have a lot of users, performance will be better by not calling the database each time.

I pretty much make all my drop-downs dynamic out of the database. Much easier to maintain that way.

Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 01/04/2002 :  16:43:21  Show Profile  Visit AjarnMark's Homepage
I have written a some pages that query the database for all the values for all drop-down lists (normally I'm just dealing with a couple on a page) and put those into some JavaScript arrays (Writing Server-side VBScript to build a client-side JavaScript is LOTS of fun... kinda like a root canal is lots of fun...). Then, when the user makes a selection in the first list, an onChange event fires and populates the second list with the possible values from the array. But, as a Rafi pointed out, if you have many, many data values, this could be a real ____ to send down the wire. It also requires that the user have JavaScript enabled in their browser, which not everyone does.

Also consider whether this is an internal-only (i.e. high-speed access) or external site (i.e. people may access via dial-up) to help decide the delay in making the round-trip to the server.

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...
Go to Top of Page

robvolk
Most Valuable Yak

USA
15683 Posts

Posted - 01/04/2002 :  18:20:12  Show Profile  Visit robvolk's Homepage
I agree with Mark...no surprise there!...but I've got a shortcut that might help with the dynamic JavaScript array (I know, I know, this is a SQL Server site, forgive me).

The way I do client-side arrays is to grab the data as a CSV and dump it into an HTML tag that is hidden on the page:

rs.Open "SELECT * FROM pubs..authors", connObj
data=rs.GetString(, , ";", "|", "")
rs.Close
set rs=Nothing
response.write "<DIV id=authors style=display:none;visibility:hidden>" & data & "</DIV>"


I then add some standard JavaScript to the page:

<SCRIPT>
var arrAuthors=new Array;
arrAuthors=document.all.authors.innerText.split("|");
</SCRIPT>


This creates a public JavaScript array. The split function cuts the string into array elements at each "|" boundary (this puts each row into an array element; you can then split each element on the ";" to get each column). From there you can use whatever JavaScript array system you have already, or you can get several from a JavaScript site.

This is pretty lightweight and flexible, and by using GetString on the server side you get REALLY fast performance. The <DIV> element contains the data but is hidden on the page. The only downside is that you need to modify this to work with Netscape, and the JavaScript will change if you need to keep this cross-browser compatible.

While it is definitely a pain to get this going at first, once you get a technique figured out, believe me, you can crank these out in no time. The code is totally reusuable.

Another option, maybe better, maybe not, is XML. Since this would involve client-side XML I can't say it's a good idea, but you could probably find a solution on a web site pretty easily.

Edited by - robvolk on 01/04/2002 18:21:17
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 01/04/2002 :  19:17:34  Show Profile  Visit AjarnMark's Homepage
Nice trick Rob. If I ever have to do this again, I'll keep that in mind. But I hope I don't have to do it again.

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...
Go to Top of Page

robvolk
Most Valuable Yak

USA
15683 Posts

Posted - 01/04/2002 :  19:52:39  Show Profile  Visit robvolk's Homepage
Thanks Mark! Like I said, it gets easier once you do it a few times, but oh man, I will not want to relive the early days when I first started doing it.

I should also mention that you could put the data into a hidden form control:

response.write "<form name=auth><input type=hidden name=authors value=""" & data & """></form>"

The JavaScript is a little more cross-browser friendly:

<SCRIPT>
var arrAuthors=new Array;
arrAuthors=document.forms["auth"].authors.value.split("|");
</SCRIPT>


The really nice thing about elaborate client-side scripting is, when it's done well, you eliminate a ton of problems that plague ASP development: maintaining state or values across pages (don't need to...one page does all); dynamic content (don't need to requery); and....PERFORMANCE. No matter how well you tweak a multi-tier app, or how fast a server you have, you will always get faster response with client-side code, and limiting server calls as much as possible. Imagine if sorting an HTML table was done on the client....mmmmmmmmmmmmmm! No call to the web server OR the database server! It is great fun to click a column heading and watch a 500 row table sort in 1 second!

If that seems really tantalizing, you should think hard about doing it, because you WILL love the results! As strange as this may sound, developing web apps made me a better database developer, and focusing on client-side stuff made my database code much leaner and more focused on DATA, not HTML or ASP or Java.

Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000