| Author |
 Topic  |
|
Shaggy
Support Moderator
Ireland
6780 Posts |
 Posted - 14 July 2004 : 04:55:07
|
Well, it looks like the time has finally come for me to make the leap from Access to an SQL database as my db seems to be getting too large (pushing 30M), causing major problems on my site. The only problem is I am completely clueless about SQL dbs and don't know where to start. I've read a good few threads here recently and they all went over my head and reading documentation on the MySQL site just made me go cross-eyed. So, does anyone have a complete idiot's guide to SQL dbs? Preferably with a section on converting from Access.  I suppose the most immediate question is, should I go with MySQL or MSSQL? I'd be inclined to say the former but that's not really based on anything.
|
 Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
laser
Advanced Member
Australia
3859 Posts |
Posted - 14 July 2004 : 05:05:05
|
So what do you happen to achieve in the conversion ? Faster speed / smaller database / both ??
My forum still uses Access, the database is around the 130Mb mark, with no discernible slow-down. The simultaneous user count is still in double-figures, but with the audience I think that's about all I can hope for.
Which one to choose if you HAVE to migrate ?? ... well, your hoster could influence your decision on cost, but I would prefer MSSQL, I think you can manage that from your Wintel pc anyway. |
 |
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 14 July 2004 : 05:50:15
|
Thanks, laser.  Yeah, I'm looking to achieve both a smaller file size and improved performance with the file size being the main issue right now.
Maybe the database isn't the problem, then, if other people have bigger ones; maybe I just need to optimise some code - my average active users fluctuates between 20 & 40 with the highest ever being 100.
Just looking at my host's features (Windows Business package) and it looks like they're no longer offering MSSQL dbs so I guess it's gonna have to be MySQL!
|
Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
|
Nathan
Help Moderator
USA
7664 Posts |
Posted - 14 July 2004 : 07:49:36
|
| Laser, if you were to download and defrag your database it would probably be alot smaller. 130MB seems awffle large for 24,000 posts. |
 Nathan Bales
CoreBoard | Active Users Download |
|
|
|
laser
Advanced Member
Australia
3859 Posts |
Posted - 14 July 2004 : 08:42:33
|
| 24000 posts ?? ... I don't understand where you got that from ?? |
|
|
|
davemaxwell
Access 2000 Support Moderator
USA
3020 Posts |
Posted - 14 July 2004 : 09:08:21
|
quote:
Originally posted by laser
24000 posts ?? ... I don't understand where you got that from ??
Your V8Central forums in your sig? That's where I got the number from...
quote:
587 of 858 Members have made 24805 posts in 33 forums, with the last post on 14 Jul 2004 23:04:30 by: Sanman.
Is there a different forum you're talking about? |
Dave Maxwell
Barbershop Harmony Freak |
Edited by - davemaxwell on 14 July 2004 09:10:10 |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 14 July 2004 : 09:23:39
|
Shaggy - my pref is for MS SQL Server as it has more friendly features for a starter (for instance it is self-tuning).
http://lab.msdn.microsoft.com/express/sql/default.aspx is raw, but free, and is based on the 'Yukon' (newest and frankly fabulous) SQL Server |
|
|
|
kwhipp
Junior Member
United States
407 Posts |
Posted - 14 July 2004 : 10:27:21
|
quote:
Originally posted by davemaxwell
quote:
Originally posted by laser
24000 posts ?? ... I don't understand where you got that from ??
Your V8Central forums in your sig? That's where I got the number from...
quote:
587 of 858 Members have made 24805 posts in 33 forums, with the last post on 14 Jul 2004 23:04:30 by: Sanman.
Is there a different forum you're talking about?
24,000 plus all of the archived topics. |
- Kevin |
|
|
|
sr_erick
Senior Member
USA
1318 Posts |
Posted - 14 July 2004 : 13:47:39
|
| You will think access is fast until you actually use something like MSSQL. Saving .25 to half a second on some page generations is a long time. |
Erick
Snowmobile Fanatics
|
|
|
|
laser
Advanced Member
Australia
3859 Posts |
Posted - 14 July 2004 : 17:06:07
|
quote:
24,000 plus all of the archived topics.
Well, as a guest you'll see that  , but as kwhipp says there's the archives and the hidden forums so to be precise it's more like ...
quote:
587 of 858 Members have made 31006 posts in 49 forums, with the last post on 15 Jul 2004 06:53:24 by: lowndesfan6.
There are currently 2432 topics and 10 active topics since you last visited.
There are 63052 archived posts in 4776 archived topics
That still might be a bit too bit at 130Mb, but the point I'm trying to make is that size is relative ... Shaggy thinks 30Mb is "too large", I'm simply saying that my 130Mb is fine with me. |
|
|
|
mortioli
Average Member
United Kingdom
898 Posts |
Posted - 14 July 2004 : 17:33:51
|
To give my two cents, this is what my forum looks like:
3668 Members
94669 posts
15 forums,
3618 topics
27865 archived posts
1164 archived topics
My database stands at 175MB (although when compacted, it reduces to approx. 90MB...which I need to do), and it runs fine to me.
However, I've just changed my database to MSSQL, and running Snitz Speedball II at www.mortioli.co.uk/forums and it's running like a bat out of hell  Most likely to be from the Speedball coding, but still seems a lot faster than Access
|
Edited by - mortioli on 14 July 2004 17:34:31 |
|
|
|
redbrad0
Advanced Member
USA
3725 Posts |
Posted - 14 July 2004 : 18:05:19
|
quote:
Originally posted by mortioli
Most likely to be from the Speedball coding, but still seems a lot faster than Access
From what I know the speedball does not optimize any of the code buy reduces bandwidth from removing blank spaces and adding CSS. |
Brad
Oklahoma City Online Entertainment Guide
Oklahoma Event Tickets |
|
|
|
mortioli
Average Member
United Kingdom
898 Posts |
Posted - 14 July 2004 : 18:32:37
|
Wow! Then it is a hell of a lot faster from using MS Sql!? Cool! I recommened it *thumbs up* lol
...apologies for highjacking your topic... |
|
|
|
sr_erick
Senior Member
USA
1318 Posts |
Posted - 14 July 2004 : 18:44:09
|
quote:
Originally posted by Shaggy
Well, it looks like the time has finally come for me to make the leap from Access to an SQL database as my db seems to be getting too large (pushing 30M), causing major problems on my site. The only problem is I am completely clueless about SQL dbs and don't know where to start. I've read a good few threads here recently and they all went over my head and reading documentation on the MySQL site just made me go cross-eyed. So, does anyone have a complete idiot's guide to SQL dbs? Preferably with a section on converting from Access. I suppose the most immediate question is, should I go with MySQL or MSSQL? I'd be inclined to say the former but that's not really based on anything.
It's really pretty simple to convert to MSSQL. Sure, you'll have little quirks here and there but they will get worked out. Access and MSSQL interface very nicely together. You can use Enterprise Manager to do a variety of things (probably everything you need to do) but I use query anylizer a lot and just run queries against the database to do things like manipulate data, transfer data to and from tables, etc. In your case, Id just use DTS to transfer the existing data you have into a blank database. You might need to go through afterwards and set up the primary keys and such however (I had this problem when I transfered to MSSQL)
EDIT: As for MySQL...It's not my favorite but it may do the trick for you. Either case, coming form access it will be easier to convert directly to MSSQL. |
Erick
Snowmobile Fanatics
|
Edited by - sr_erick on 15 July 2004 09:24:07 |
|
|
|
Davio
Development Team Member
Jamaica
12217 Posts |
Posted - 14 July 2004 : 19:01:07
|
quote:
As for MySQL...STAY AWAY FROM IT!!! It is evil and not a true database platform!!!
Uhmmm....what are you talking about? |
Support Snitz Forums
|
|
|
|
sr_erick
Senior Member
USA
1318 Posts |
Posted - 14 July 2004 : 20:17:15
|
| Plain and simple, I hate it. It's not a true DBMS and doesn't have support for things you'd expect out of one. |
Erick
Snowmobile Fanatics
|
|
|
|
Topic |
|
Topic Locked
