Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Web Application Method (ASP?)

Author  Topic 

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2002-01-04 : 06:48:46
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

25 Posts

Posted - 2002-01-04 : 07:30:55
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

505 Posts

Posted - 2002-01-04 : 07:48:01
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

3246 Posts

Posted - 2002-01-04 : 16:43:21
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

15732 Posts

Posted - 2002-01-04 : 18:20:12
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

3246 Posts

Posted - 2002-01-04 : 19:17:34
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

15732 Posts

Posted - 2002-01-04 : 19:52:39
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
   

- Advertisement -