Recomendations for conceptual design stage 1

[jcale4]

I have a very generic question regarding the suggested method for returning very large recordsets and paging through the recordset. I am building a system that potentially (user dependent) pull recordsets of 2000+ rows. I am working on developing a paging system that will allow me to "Pull once Query many", for two reasons: To conserve server resources and to allow faster client side results. Can anyone suggest a "best practice" methodology for this type of procedure? or are there any materials that anyone has found useful?

MY resources: web:ASP (vbscript) - DBMS: DB2 UDB

Thanks!!

 

[steven290]

you need to be careful with sql injection, etc. people running scripts like

delete from tblname, update tblname set somefield='something',

notice no where so you affect all the records

 

[a6m1n0]

Hm...might want to take a look at using XML. If we are talking about view vs. add/change/delete then xml might be the way to go and probably the simplest implementation.

-a6m1n0

Curiosity only kills cats.

 

[bslintx]

how 'bout

Using RecordSet.Filter

ryou can reuse a single recordset by using the filter method.

It is much more efficient and so much easier to maintain, than opening up multiple recordsets
If you know you are going to need a large amount of data, just get everything once and then filter it as you need it.

 

[jcale4]

a6m1n0 - Great suggestion, but i know very little about XML. I'm very interested, and agree that (even with my limited knowlege of XML) XML could be an excellent solution. Here is the basic functionallity of the system - i run a LOAD nightly to pull in about 20,000 rows of data. Then from a users perspective, the data is ONLY selected against (essentially a trending tool), NEVER add/change/delete. Do you still think XML is a viable solution?

bslintx - also a great suggestion, and one that i previously considered. However, i had some performance issues previously when using recordset.filter. I assume that what happens is that when the initial recordset is gained, the ".filtering" is performed by ADO/IIS (or your favorite web server/data provider), resulting in potentially less sort/filter power since it is simply "emulating" DBMS capabilities. Problem is, i could have up to 10-15 users with open recordsets at one time. With a 20,000 row recordset and 10 users, thats 200,000 rows in memory at once, yes? Do you know any way's to open the recordset and make it available to all users in all sessions?

Thank you everyone!!

 

[a6m1n0]

Apologies for such a late reply.

Here is a tut that should help get you started:

http://www.4guysfromrolla.com/webtech/101200-1.shtml

-a6m1n0

Curiosity only kills cats.

 

[jcale4]

a6m1n0,

I read through the articles and have experimented a little with XML, but i'm still having trouble understanding how to actually implement XML as a technology that will make data access more efficent for the client. Most of the information in the tutorials explain how to create or send XML *FILES*. Does this mean that my RDBMS will be replaced by a collection of XML files? Or does the general process look more like -

1. client selects criteria and chooses to pull report
2. data is pulled from RDBMS and inserted into XML files
3. XML files are read from and queried against to generate HTML content

If that is the case, I cant find anything that explains how to EFFICIENTLY pull data from the DB and create the XML files. Pulling the data into an ASP page and looping through a recordset to create the XML file would be the only way i can think of. Can you point me in the right direction as far as BASIC program flow using XML?

Sorry for so many questions.

j

 

[damber]

DB2 can support exporting data as XML directly - with an add-on component.. the 'XML Extender' for v7.2 upwards - here's a link:

http://www.devx.com/getHelpOn/Article/11004/1954?pf=true

This will cut out any processing by ASP to generate the XML and you can simply output the XML Document with a related XSLT to present it the way you want. XSLT is a pain in the a*&e to learn but once you get into the mindset it becomes relatively simple, and can be quite powerful at certain tasks.

You could then use Javascript and XPath to manipulate the data client side.

http://www.w3schools.com/xpath/xpath_intro.asp

Step by step:

1. Run Stored Procedure to retrieve data as XML (it may require a specific function in DB2 - see the documentation for the XML Extender)

2. Prepare the page and create an XML Data Island.. IE has supported these for ages, Mozilla browsers do support them, but they're managed slightly differently.

3. Client-side Javascript Uses XPath and the pages Form to apply user selection and manipulate the data and apply the stylesheet. and render the Data inside a DIV element (for example)


Personally I would be cautious of this route, it can be really effective for some scenarios, but could be more trouble than it is worth:

1. If you send ALL your data to the client, you may have to move a lot of bytes through each component - DB, IIS/ASP, Network, Client - What if they only want to see the first couple of records ?

2. Potential incompatibilities with browsers - or fat code to cater for the differences. IE & mozilla browsers (e.g. Firefox) are probably fine - it's just the others that may be an issue - but do you care ?

3. Lot of technology to achieve a simple thing - complex code leads to large, overworked support teams. And if it is a steep learning curve then you could find the development effort exceeds ROI.


If your dataset is small or you want an offline version this would be fine, if you are dealing with big datasets then why not just use a form and POST or GET the results in the normal way ? Or if you want the user to think they are on the same page, use XML-HTTP. Or do you expect the users to do lots of processing (filtering/sorting) of the data each time?

XMLHTTP info:

http://www.w3schools.com/dom/dom_http.asp

The reason most paging is done server side is because it is more efficient on all the components involved - but it's not a strict rule... If your users do a lot of manipulation EACH AND EVERY session of use of the same dataset, then this may be the way to go.


Here's a ready made library:

http://www.perfectdownloads.com/web-development/asp-php/ccxi-xml-data-island-library.htm

and

http://www.uolweb.com/webgrid.aspx

Here's an ASP.NET guide:

http://aspnet.4guysfromrolla.com/articles/020905-1.aspx

And a sorting only guide:

http://builder.com.com/5100-6373-1044724.html

And an IE specific one..

http://www.biglist.com/lists/xsl-list/archives/200302/msg00119.html

That should keep you busy for a while...! ;o)

Good Luck !


A smile is worth a thousand kind words. So smile, it's easy!

 

[jcale4]

Excellent!

That helps a lot. Thank you everyone for your help in this!

j