| Author |
Topic |
|
edibusl
Starting Member
4 Posts |
 Posted - 2006-05-16 : 13:13:03
|
| Hello,I have a question.I want to migrate from mySql to Sql Server, and I should decide which version will I use.The problem is that I need partitioning, and it is included only in the Enterprise version. But the Enterprise version costs 5 times more than the standard and the workgroup version.Is it worth to spend so much money only for the partitioning?Also, maybe I am wrong, and the partitioning is also included in the Standard version?Thanks,Edi. |
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-16 : 22:56:21
|
| "The problem is that I need partitioning"Why do you need partitioning? Maybe there is another way someone can suggest ...Kristen |
 |
|
|
edibusl
Starting Member
4 Posts |
Posted - 2006-05-17 : 00:20:08
|
| I have about about million records in a table in my mySql DB. When I make a SELECT on a parcticular field, the records get back very slowly. Even that the field is indexed. So I think that partitioning will solve the performance problem.Maybe the indexing of Sql Server is much better than mySql's, and then I won't even need to use partitioning in Sql Server ?Thank you,Edi. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2006-05-17 : 00:42:20
|
| We have tables with millions of records. Both mySQL and SQL Server can handle it. SQL Server is more robust and scalable. Partitioning might not help you at all if your hardware is not sufficient. You also might just need to tune the database.Have you tried recreating the index? Can you post the table structure, some sample data, and your select? What type of field is it? What do the performance counters look like when you run this select?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-17 : 00:59:40
|
| "Maybe the indexing of Sql Server is much better than mySql's, and then I won't even need to use partitioning in Sql Server"Depends!!For SQL Server to use an index it must be very selective - that is to say that the values must be close to unique.SELECT * FROM MyTable WHERE IsActive = 'YES'is not going to use an index on IsActive if there are millions of rows all with "YES" in them!AlsoSELECT * FROM MyTable WHERE NAME LIKE '%KRISTEN%'is probably not going to use an index.But that said SQL Server has a number of tricks to speed up queries, including clever caching in memory, storing query plans for reuse, and so on.So it tends to be a question of tuning queries that perform badly.Partitioning tends to be useful for multi-million row tables where, for example, data is added monthly - so this-month-last-year can now be deleted, and a new table set up in readiness for next-month's data.But it is usually possible to get good performance from a normal table if it only has a few million records.If you think you still need partitioning then apart from the Enterprise Licence issue you may want to check if you can satisfy the requirements of horizontal partitioning:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Requirements%20for%20Horizontal%20PartitionsKristen |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-05-17 : 02:07:53
|
| the optimizer in mysql can only use one index per table in a query. SQL Server can use multiple indexes per table in a query. I don't know if this has changed with MySQL 5.x, but this was certainly the case with the < 4.x releases.This comes into play when you use things like subqueries and derived tables. btw, MySQL 5.1 will support partitioning http://dev.mysql.com/doc/refman/5.1/en/partitioning.html-ec |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-05-17 : 04:12:42
|
| Partitioning results can be variable at best. You can write your own way around it anyway, plus I'm sure at one point I tested a piece of code for partitioning that it said wasn't included in standard edition, and it worked.If partitioning is your only reason for going to Enterprise Edition, then I'd be tempted to forget it. A more compelling reason might be that Standard Edtn. (2000) only uses 2GB of ram, which may be a limiting factor if you have massive tables.-------Moo. :) |
|
|
|
PSamsig
Constraint Violating Yak Guru
384 Posts |
Posted - 2006-05-18 : 02:45:02
|
| I dont know what the price difference is between 2000 std. and 2005 std., but 2005 has only OS RAM limit, and RAM is cheap and usefull. What is the total size of your database?-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those. |
|
|
|
edibusl
Starting Member
4 Posts |
Posted - 2006-05-18 : 11:53:53
|
| Thanks to all of you for the answers.The size of the DB is 500MB, and as I understood partitioning + indexing of mySql 5 will solve our performance problem.So, I'll use mySql5 instead of Sql Server, because SqlServer 2005 Standard is not better than mySql 5 (for my purposes), and is also free !!!Edi. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-05-18 : 11:57:29
|
quote:
Originally posted by edibusl
Thanks to all of you for the answers.The size of the DB is 500MB, and as I understood partitioning + indexing of mySql 5 will solve our performance problem.So, I'll use mySql5 instead of Sql Server, because SqlServer 2005 Standard is not better than mySql 5 (for my purposes), and is also free !!!Edi.
mysql 5.1 has the partitioning capability and it is still alpha or early beta. -ec |
|
|
|
PSamsig
Constraint Violating Yak Guru
384 Posts |
Posted - 2006-05-19 : 01:03:59
|
| Last I looked mySQL was only free under certain condintions, and how on earth can a 500MB DB be slow, it should fit in memory?For that size you could even run it in Express which is truely free (although you proberly would want/need some 3rd party administration tools), but the OS costs money (a WinXP pro will do though).-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those. |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-05-19 : 04:03:50
|
quote:
Originally posted by edibusl
mySql 5 (for my purposes), and is also free !!!
Nothing is free in this world.-------Moo. :) |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2006-05-19 : 17:45:51
|
quote:
Originally posted by edibusl
Thanks to all of you for the answers.The size of the DB is 500MB, and as I understood partitioning + indexing of mySql 5 will solve our performance problem.So, I'll use mySql5 instead of Sql Server, because SqlServer 2005 Standard is not better than mySql 5 (for my purposes), and is also free !!!Edi.
A 500MB database should be extremely quick. Even on a desktop PC. SQL Server Express will handle it just fine. SQL Server Express AND its tools are free. I'd like to see what query you are running and how quickly it runs. I'd bet that SQL Server Express is just as fast if not faster than MySql.===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-05-19 : 18:30:27
|
quote:
Originally posted by edibusl...The size of the DB is 500MB, and as I understood partitioning + indexing of mySql 5 will solve our performance problem...
You are making a very big assumption that partitioning will fix your problem. I doubt that you will see any benefit from partitioning such a small table.The only table that I have partitioned has about 2 billion rows in it, and I partitioned it for ease of data management, not for performance.More than likely, the design of the table or the queries that you are running are the source of the problem.CODO ERGO SUM |
|
|
|
edibusl
Starting Member
4 Posts |
Posted - 2006-05-20 : 15:27:50
|
You were right about the indexes issue.I found that I had a problem with the indexes in my table.After I fixed the indexes, everything started to work very fast !!!So, I'll continue using mySql... That's a very powerful and good DB for medium DBs !Thanks to all of you for trying to help me  |
|
|
|
|
Sql Server Standard vs Enterprise