April 1, 2003 at 2:45 pm
Folks,
Please pardon my ignorance: I am new to SQL Server. This is an Access-centric question that requires some SQL Server know-how.
The situation:
My company is preparing to transition our products from Access (MDB) to ADP/SQL Server over the next few years. I am in charge of this initiative. Our Access apps currently utilize temp data storage to minimize performance degradation issues caused by concurrent users. I know the approach to this issue is necessarily different in SS, but I am getting stuck when it comes to handling reporting.
My need:
My question is really a mechanical one. I am not sure what mechanism within ADP/ADO/SQL would be best to hold the large datasets necessary to feed some of our reports without either A) locking up a good portion of the datafile for the duration that the report is open or B) bringing the network to its knees while data is copied locally.
FWIW, these reports are often printed and formally reviewed so formatting is a need (thus HTML is not a great solution). I do not believe, however, that the dataset is so large as to require or benefit from the use of OLAP.
If anyone who has dealt with reporting issues in an ADP/SQL Server environment could help point me in the right direction, I would really appreciate it. I know this is an open-ended request, but that is an indication of how little I have been able to figure out thus far.
Thanks.
April 2, 2003 at 5:46 am
How big are the reports? Handling concurrent users is not as hard with a SQL backend, you can use locking hints to make it even better if you don't care about changes while you're reporting. It's rare that anything you do is going to kill the network, assuming you're not running 10m hubs. Typically (if there is such a thing) you want to keep the data request separate from the formatting and you want to do the formatting either on a reporting server (asp, asp.net) or on the client.
Andy
April 2, 2003 at 6:53 am
Current Access data files can run 150-200 MB and certain reports could consume up to about 40% of that data (standard for largest reports would be more like 20-25%). It's not huge by SQL standards, but it is big enough to make it a concern.
By "formatting", do you mean the format of the report itself or the format of the data into the report?
If you were to pick a "normal" way of handling this situation, what would it be? What would a typical data source for the report be?
Forgive me if I have Access-blinders on. I've spent too long figuring out how to work around Access' shortcomings that I sometimes miss the forest for the trees.
thanks
- J
April 2, 2003 at 7:58 am
Access pretty good at some things, nothing wrong with using it. Format/layout - in other words you want to write a query to bring back the data you need as fast/smooth as you can, then client side you do some formatting. Access binds to the data source directly - ok in some cases, maybe not in others. If you write a query that pulls the data, you can put into a #temp table on the server and bind to that.
I think before you try a lot of changes, see how it works with what you have. Just changing to SQL from Access will help you scale up.
Andy
April 2, 2003 at 9:22 am
I think I was misunderstanding how temp tables worked in SQL Server (specifically global vs local scope). It seems light may be dawning on Marblehead over here. I will give it a shot.
many thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply