Start a new topic
Answered

When it is convenient to use Sql Server?

I tried to import a Helium 11 database with 71.000 tracks / 5.800 albums / 3.200 artists into a Sql Server database and another Sql Server Compact. I do not appreciate differences in performance, or to display data or when searching.


When it is convenient to use Sql Server?


Best Answer

Here's a good article to read about main differences (scalability etc):

http://erikej.blogspot.se/2011/01/comparison-of-sql-server-compact-4-and.html


When it comes to performance, some operations are much slower on SQL Server Compact compared to SQL Server due to how indexes (amongst other things) are handled.

An example of this can be to open the artist/album details which can  get slow with a larger Compact database (8s for a query, compared to ~0.5s with SQL Server).


The quickest database is MySql/MariaDB which performs select operations the quickest way, but it is a bit slower on insert (and sometimes also on update) operations.



Answer

Here's a good article to read about main differences (scalability etc):

http://erikej.blogspot.se/2011/01/comparison-of-sql-server-compact-4-and.html


When it comes to performance, some operations are much slower on SQL Server Compact compared to SQL Server due to how indexes (amongst other things) are handled.

An example of this can be to open the artist/album details which can  get slow with a larger Compact database (8s for a query, compared to ~0.5s with SQL Server).


The quickest database is MySql/MariaDB which performs select operations the quickest way, but it is a bit slower on insert (and sometimes also on update) operations.


Okay, thanks for the explanation

 Hi Mikael,


Do you have any specific use cases or SQL I could have that demonstrate the performance difference between SQL server and MySql/MariaDB?  Which file system driver is that using?  I have a fairly large database and I would like to swap over if there is a material difference, I am currently using SQL Server 2016 SP1.  There is also a chance that if I know which operations are having the issue I can fix it on the SQL side.


Thanks!

Joe


Hi Joe,


We have no specific cases defined but to get the best speed when browing music you should:

1) Keep your database on an SSD drive

2) Use MariaDB/MySql 5.6+


With this configuration browsing and searching will be quick (be sure to configurate your database engine properly!), adding music will be a little more slower compared to when using Sql Server.


When using Sql Server you should also place your database on an SSD drive as well as adjusting connection timeouts and similar settings so that it fit your needs.

Browsing and searching will still be quite quick and adding music quicker than with MariaDB/MySql


The best recommendation I can give you is to do a test on your actual setup, since you might need to configure setting specific to your machine so that it works in the best possible way.

Thanks Mikael, going to play around with Maria, I have not used it in years because I didn't think for a second that any operation would really be faster than SQL server honestly.  Something new and fun to do thank you!

 

Login or Signup to post a comment