DB Questions
Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: General Discussion
Forum Description: General discussion and chat on any topic.
URL: https://forums.webwiz.net/forum_posts.asp?TID=15866
Printed Date: 31 March 2026 at 5:13am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com
Topic: DB Questions
Posted By: Amateur
Subject: DB Questions
Date Posted: 17 July 2005 at 4:29pm
|
Hi all, I need yer opinion on 4 different matters. 1-- I am currently creating some ASP.NET c# applications with a db back end. Anywhere on the internet where I could read about performace issues in connecting to a db, be it access or sql server? 2-- I am considering using MS Access for my apps. I am aware when a connection is opened to connect to the db, the db is loaded into memorary. I am wondering if each sql command applied to the opened connection is a performance hit or is it just the opening of the connection that the performace hit is with? How would using SQL Server improve performance? 3-- If I decide to use SQL Server, is there anywhere in the internet that I could download a copy of it or something similar that I could use? 4-- Also, is it better to have a number of medium sized Access dbs or one large one? Its just easier to have the one db but performance is a big issue for me. Any help would be great. |
Replies:
Posted By: dfrancis
Date Posted: 17 July 2005 at 6:06pm
|
Just my opinion...
1 ) Don't use access
2) Don't use access
3) Microsoft website free download and $50 (US) for full version (developers)
4) Don't use access.
=============
I've developed with Access for years, I had to rewrite everything to upgrade to SQL. Access will cause more problems than it's worth on a production server. Well there is one exception... if you are going to have less that 8 people on your website at any given time, then okay.
 http:// http://www.ASP.NET - http://www.ASP.NET is the place for the good info.
RE: your db connection questions, http://www.aspfaq.com - http://www.aspfaq.com is priceless. (And he will validate my access opinion too.) |
Posted By: Amateur
Date Posted: 17 July 2005 at 6:27pm
Couple questions.
I have finished one particular with access backend. Is there alot of work in recoding it for sql server?
Is there a big difference from using access to sql server in coding etc? I ask this as you have made the change. What did you find as the hardest change to make between the two? Do i need to create procedures etc?
I already downladed that copy of sql server from ms website, can i download it again or do i have to pay up??
Thanks very much for your help.
|
dfrancis wrote:Posted By: dpyers
Date Posted: 17 July 2005 at 7:43pm
|
Biggest issues I've had is converting data types for those fields that
are different between access and mssql - one of the reasons I don'y use
autonumber - lol. Access 2003 contains a pretty good upsizing wizard,
and mssql can import and access db's - usually take a little tweaking. Most of your sql will remain the same, but check out true/false statements. You don't have to use stored procedures but as "compiled" sql statements, they run a lot faster. The MSSQL 2002 download is a 90 day trial. The developer edition is well worth toh $50 - seen it for < $30. If your host is offering .net 2.0 beta test machines, you might want to check out http://msdn.microsoft.com/getthebetas/ - http://msdn.microsoft.com/getthebetas/ ------------- 
Lead me not into temptation... I know the short cut, follow me. |
Posted By: Mart
Date Posted: 18 July 2005 at 6:46am
| You might also want to use MSDE for developing locally - the only difference is that less concurrent users are allowed and your not allowed to use it for production. MSDE is free from microsoft, theres a tutorial about how to set it up here http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmsde/html/msderoadmap.asp - http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmsde/html/msderoadmap.asp |
Posted By: Mart
Date Posted: 18 July 2005 at 6:48am
| There's an upsizing tutorial also http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k2/html/odc_msdeintro.a sp |
Posted By: dfrancis
Date Posted: 18 July 2005 at 7:14am
|
DP and Mart offer some great advice!
Bottom line to me is, since you are a self proclaimed novice, why not go directly to the technology that you will end up in anyway? What I mean is, I can see no reason to learn Access if you are eventually going to dive into SQL anyway.
You are at the right place to learn the difference. Bruce has coded the WWforum for both SQL and Access, a quick look at the code can really tell you some of the differences.
Can you download the trial version again? Not likely. There are a few registry entries you'd have to find and remove. But from the MS website, you can register your trial as a developer for $50usd.
DP offered a great suggestion. The upsizing wizard in access works great and it will reveal any table structure issues. (because some query's or inserts will stop working.)
Also, re: MSDE - I have gone down that road but when MS came out with the dev price for full SQL, I stopped "playing" with it. It is a viable option.
Like DP said... true/false (Yes/No) is a big issue as are dates. I've had to wrestle with a few null issues in conversions as well.
If it were me... if the access backend is working for you then use it. Try the upsizing wizard in a development environment and then use it as an object lesson. While you're fixing the SQL statements and fields, you should also consider investing time into stored procedures. The whole process of upsizing is a great way of learning the difference. (Well, I'm speaking from experience with Access 2000, I have no idea about 2003... sorry.)
|
Posted By: Mart
Date Posted: 18 July 2005 at 7:32am
|
But if you do want to go down the Beta 2 route, here is a list of hosters that support it: http://msdn.microsoft.com/asp.net/beta2/hosters/default.aspx - http://msdn.microsoft.com/asp.net/beta2/hosters/default.aspx Some of those hosters offer free playground accounts to test asp.net 2.0 out on |
Posted By: Amateur
Date Posted: 19 July 2005 at 5:28am
|
Thanks very much to everyone for there advice and help.
While I understand ASP.NET2.0 is on its way it probably be another 6/8 months before release, tested fully and supported by most hosts. For that reason I think i'll stick with v1.1 for the moment.
If i pay the $50 or whatever, does it mean I can use it as back end on a web server. (Any restrictions put on me??)
|
Posted By: Mart
Date Posted: 19 July 2005 at 5:33am
| No, you can't use developer edition in production... But if you get 1.1 web hosting the chances are that it will come with a SQL Server package included that you can use in production |
Posted By: dfrancis
Date Posted: 19 July 2005 at 7:50am
Exactly!
The dev version allows you to develop and test your application locally, but when choosing a host, make sure they offer SQL server.
The cool thing is, with the developer version you get a utility called "Enterprise Manager" that allows you to administer the database. You are "allowed" to use that utility to access the full version of SQL on your production server. For me, it was worth every penny.
|
Posted By: Mart
Date Posted: 19 July 2005 at 8:29am
You can get enterprise manager and query analyzer with MSDE but that method isn't exactly legal
|
Posted By: dpyers
Date Posted: 19 July 2005 at 11:21am
|
The developer version and MSDE also have a limit of 5 actively concurrent connections. That's not the same as only 5 users. Depending upon the app, 5 concurrently active connections can support a lot of users. One example I read was that the 5 concurrent connections could probably support an hr app for a company with 1000 daily users, but would fail when they all went to put in a time sheet at 5 PM Friday. -------------
Lead me not into temptation... I know the short cut, follow me. |
Posted By: Amateur
Date Posted: 20 July 2005 at 4:34am
|
Thanks very much lads.
So my understanding is I can develop locally a SQL Server DB using the downloaded developer edition. Then when I want to put on a webserver I use the the SQL Server that they supply.
How do I transfer my local DB to the DB on the Web Server?
Must the deleoper edition and the SQL Server suppiled by the hosting company be the same version?
Any experience of problems with connection strings after building locally and then uploading to the server?
Slightly confused by this. Does this not mean that the supposed performance improvements by SQL Server are diluted?
|
Posted By: Mart
Date Posted: 20 July 2005 at 4:59am
|
1. You can either generate a SQL Script using Enterprise Manager and
run it on your remote server, or backup and restore the database on
your remote srever, or develop on your remote server but just use the
client tools from developer edition. 2. No 3. Nope, connection strings work just fine, you just need to change the server and the initial catalog 4. That limit is only on the developer edition and MSDE - and it is only to stop you from using it in production... 5 conncurrent connections won't make much of a difference to you as on average my sql server queries take 0.002 seconds (ado.net) so that means that 6 people would need to simulateously query the database at the exact same 0.002 seconds and the 6th person would get an error - not much of a problem if your just using it for development |
Posted By: Amateur
Date Posted: 20 July 2005 at 11:26am
|
Cheers for that. I'll have a go with and see how it goes.
Also, is then number of times an open connection is queried or the opening of the connection hampers performance?
|
Posted By: Mart
Date Posted: 20 July 2005 at 11:58am
| Well it's quicker to run 2 queries on 1 open connection than to query once, close the connection, open the connection and query again... is that what you mean? |
Posted By: Amateur
Date Posted: 20 July 2005 at 6:01pm
|
Yep, thats it Mart.
Thanks for all yer help.
|