Large Databases 1

bookor · Apr 21, 1999

I have a fairly large Oracle database and need to provide a certain amount of info through a browser to our intranet and possibly eventually through the internet. For example, I have a database of 100,000 individuals with a multitude of tables containing name, address, phone, and other information. I have to provide an up-to-date list, accessible upon demand to show any portion of the list the user wants to access, taking the place of a paper record/log book; like deed records etc. My test pages work fine on the test database, but when I test against the production side, they time out even on our ethernet lan connections. How do I make large dynamic datasets available? Is ASP the right choice? Should I look at something else for a project like this.

robherc · Apr 21, 1999

You might want to try Perl for this undertaking. I'm sure you could get a Perl search engine to accomplish this task...you might also consider breaking the db up into separate parts searchable through separate pages (ie. a-f, g-l, m-z) you could have your main search page contain a link to each subordinate db's search page. Sorry I don't know of anything else that works, you might give it a try though.

bookor · Apr 21, 1999

I was considering breaking up the database into "annual" databases but most of the inquiries, 98% or better are concerned only with the last couple of weeks data, and that would still be pulling datasets from the main database. Ive never checked out Perl. I always associated it with being a Unix scripting language. How difficult would it be for someone unfamiliar with Perl to get up to speed enough to create useable database front ends? 
 
thanks for the info.

robherc · Apr 22, 1999

Well, I've JUST NOW started learning PERL, so I can't really tell you, but if you know much JavaScript, I've found PERL to be extremely similar to JS. 
About breaking up the database: You might try breaking it up alphabetically according to the Name of the person; or geographically according to the region in which they live. 
 
I would probably suggest doing one of these as an immediate recouse; then you can have more time to experiment with PERL to find out if it will work for you.

Courval · May 10, 1999

Hi there, Can you give me a little more information with regards to how you are connecting to the database. 
I take it that when you say ASP you actually use the ADO component. Have you tried changing the timeout of the Command object (default is 30 seconds). 
Learning Perl is an alternative, but, it takes a while and I am sure you need this yesterday. 
Another possibility is to program stored procedures which you call. This should actually increase the efficiency of your queries as the server does all the work. Reorganising the database is a good idea, but 100,000 records !!!! 
Give me a little more details and I'll try and help.

bookor · May 10, 1999

The only connection Ive tried successfully is an ado connection created through frontpage. I havnt tried to tinker with settings yet. Want to make sure what Im trying to do is doable. If Im off on the wrong track I want to find out right up front. Any you're right, I need to get this done and really don't have time to try to learn another develop tool right now. How would you use stored procedures in ASP/VB/VBScript? Is it possible to retrieve a recordset for a date range or name search that retrieves the entire record set to the client machine and scroll through the recordset from there? I currently have an Access97 front end on the database and it is useable but extremely slow. An asp page might be no slower if I tinker with the timeout so it wont crash, but whatever method, I have to improve performance. 
 
any help would be appreciated.. 
 
thanks again

zazz · Jun 4, 1999

I believe that if everything is set up correctly then changeing the tmeout will work. If you application is still booging down then I suggest dumping you data into a heavy duty database such as SQL Server of Oracle. Access is a great database but with that many records your queries are bound to be slower than with a more powerful db. 
 
I hope you get everything worked out.

gms · Jun 15, 1999

Hi there booker, 
 
From your initial description, I think that you need to take a step back. If the ASP queries are working fine on your test machine, and terrible on the production - your problem is not with ASP. I think that you need to see what differences there between machines is. 
 
Have you checked indexes, statistics etc. 
 
Hope this is not too late.

Surn · Jun 16, 1999

Get Visual Interdev and stop using Frontpage. Frontpage is very limited. I agree with the previous posts to break up the query into alphabetical groups. 
 
Have links as such... 
in ASP of course.. 
The first page.. 
<HTML><body> 
<table><tr><td><a href=info.asp?start=a&stop=B>A</a></td> 
</tr> 
</table> 
</html> 
Info.asp.. 
<script language=VBscript runat=server> 
Sub Recordset1_onBeforeopen() ' this might be the wrong name 
dim SQLText,vAlpha,vBeta 
vAlpha = request.querystring("start") 
vBeta = request.querystring("stop") 
sqltext= "Select * from TABLENAME where ((NAME >=" 
sqltext = sqltext & " vAlpha) AND (NAME < vBeta)" 
' may need a ";" and may need to convert to ASCII values 
Recordset1.setSQLtext(SQLText) 
Recordset1.requery 
End Sub 
</script> 
<HTML><body> 
<table><tr><td>TITLE1 ie NAME</td><td>TITLE2 ie ADDRESS</td> 
</tr> 
<td><%=Recordset1.field.getvalue("Name") %></td> <td><%=Recordset1.field.getvalue("Address") %></td>...... 
</table> 
</html> 
 
I may have left a few bugs in there for you.. but you should get the picture. SHOULD.

bookor · Jun 16, 1999

Ill wade through that and give it a shot. Is Visual Interdev worth the investment? Seems like Ive read some uncomplementary reviews? 
 
thanks for the reply

Xopas · Sep 16, 1999

Hi, 
I haven't done this with VBScript before, but using a different language (webspeed, progress) I was able to stop that problem by writing a NULL value to the screen every 50 (you can go more or less) iterations. In your case you can do this every 50 records or so. If you are not buffering the data that is to be displayed, you can do this. 
 
Hope that helps.

Dataman · Sep 20, 1999

Hi, 
Just another two-cents-worth... 
The overhead introduced by ADO, is quite minumal, at least when working with a single query. The problem appears to be with the query itself. Two things would need to be addressed: 
 
1) The design of your SQL statement - it should be optimized using the tools from Oracle 
2) The indexing of your data. Check to be sure that the fields in your "where" clause are indexed. 
 
I'm currently running queries against a database (Oracle) involving as many as twelve tables and 25,000,000+ records that return in less than 5 seconds. One particular query used to take around 5 minutes to return, but after testing and reviewing the Oracle "plan", I was able to add some indexes, change how I joined two tables, and the speed improved to about 2 seconds. 
 
While splitting the query into letter groups might offer some improvement, it's a band-aid solution. Fix the real, underlying, problem.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Large Databases 1

bookor

MIS

robherc

Programmer

bookor

MIS

robherc

Programmer

Courval

Programmer

bookor

MIS

zazz

MIS

gms

Programmer

Surn

Programmer

bookor

MIS

Xopas

Programmer

Dataman

Programmer

Similar threads

Part and Inventory Search

Sponsor