date and time is the biggest the difference between the databases.SQL server 2005 has datetime (with microsecond) and smalldatetime(no microsecond) data type where as mysql has date, time, datetime and timestamp datatypes to handle date time components.
In mysql, if a blank value is inserted into datetime data type, it is stored as 0000-00-00 00:00:00 where as in sql server is stored as 1900-01-01 00:00:00.000. And if the application had been developed around mysql and logic in the application to handle the peculiarities with datetime in mysql will have to be thrown out of the window if the application needs to support sql server.
as mysql has no concept of microseconds where as sql server datetime has microseconds. If the application does not handle microsecond, the application will have to be rewritten to handle it.
Thursday, November 19, 2009
Tuesday, November 10, 2009
ad hoc queries and sql server and mysql
when ever a query is fired to a database, it is parsed and various plans are generated. The best or good enough plan is chosen by the engine and is used to execute the query.
Once a plan is generated, it is maintained in the plan cache by the sql server for future re use in case same query is executed where as mysql generates the plan every time the query is executed. The caveat in the sql server for ad hoc queries, is that the queries must match text to text, case sensitive for the query plan to be reused.
For eg:
query 1:
SELECT col1 FROM table1 WHERE col2 = 100;
query 2:
SELECT col1 FROM table1 WHERE col2 = 200;
The above 2 queries will generate 2 query plans and will be cached in the plan cache as 2 different plans even though the only difference is in the predicate value.
This leads to couple of interesting issue.
1. SQL server 2005 introduced TokenAndPermUserStore cache which affects the performance for ad hoc queries.
http://support.microsoft.com/default.aspx/kb/927396
This issue seems to have been fixed in sql server 2005 sp3 and sql server 2008.
2. The size of plan cache grows streadily as sql server caches all the plans until it hits the memory pressure.
The sql server can be forced to use the same plan for similar type of queries by
1. By changing database setting from simple parameterization to forced parameterization. This will replace the literal value in the queries to parameter. So when a new query with different predicate is fired it will reuse the parameterized query plan. The plan cache will have the parametrized query plan and the shell query plans for similar query holding a pointer to the parameterized query plan. I will elobrate on this in one of the follow up blog.
additional inforamtion on forced parameterization.
http://technet.microsoft.com/en-us/library/ms175037.aspx
2. Using prepared statements in java. This will help in creating plans for parameterized queries and will be reused by the sql server. The only drawback of using prepared statement is that it adds an additional network round trip but doesn't increase the plan cache for similar queries.
We have implemented the forced parameterization and it boosted the app performance by 30 % and reduced the TokenAndPermUserStore cache growth from rate of 2 to 2.5 MB per 10 minutes to 600 kb per 10 minutes.
Once a plan is generated, it is maintained in the plan cache by the sql server for future re use in case same query is executed where as mysql generates the plan every time the query is executed. The caveat in the sql server for ad hoc queries, is that the queries must match text to text, case sensitive for the query plan to be reused.
For eg:
query 1:
SELECT col1 FROM table1 WHERE col2 = 100;
query 2:
SELECT col1 FROM table1 WHERE col2 = 200;
The above 2 queries will generate 2 query plans and will be cached in the plan cache as 2 different plans even though the only difference is in the predicate value.
This leads to couple of interesting issue.
1. SQL server 2005 introduced TokenAndPermUserStore cache which affects the performance for ad hoc queries.
http://support.microsoft.com/default.aspx/kb/927396
This issue seems to have been fixed in sql server 2005 sp3 and sql server 2008.
2. The size of plan cache grows streadily as sql server caches all the plans until it hits the memory pressure.
The sql server can be forced to use the same plan for similar type of queries by
1. By changing database setting from simple parameterization to forced parameterization. This will replace the literal value in the queries to parameter. So when a new query with different predicate is fired it will reuse the parameterized query plan. The plan cache will have the parametrized query plan and the shell query plans for similar query holding a pointer to the parameterized query plan. I will elobrate on this in one of the follow up blog.
additional inforamtion on forced parameterization.
http://technet.microsoft.com/en-us/library/ms175037.aspx
2. Using prepared statements in java. This will help in creating plans for parameterized queries and will be reused by the sql server. The only drawback of using prepared statement is that it adds an additional network round trip but doesn't increase the plan cache for similar queries.
We have implemented the forced parameterization and it boosted the app performance by 30 % and reduced the TokenAndPermUserStore cache growth from rate of 2 to 2.5 MB per 10 minutes to 600 kb per 10 minutes.
Saturday, November 7, 2009
The first step
Mysql had been the only database we were supporting for a long time.This had served the company very well for a long time but was falling short for some potential customers as they did not support mysql in their data centers.
So we decided to support SQL server in addition to mysql. I will blog about the various issues we faced when adding new database support, the differences in sql syntax and how we were able to accomplish the support for SQL server in a relatively short time.
Our application is a three tier application with middleware being developed in java. The application server connects through jdbc to the database for DML operations. This helped us in supporting the new database vendor as we could intercept all the sql's passing through the jdbc driver and manipulating most of the sql's to conform with SQL server syntax.
So we decided to support SQL server in addition to mysql. I will blog about the various issues we faced when adding new database support, the differences in sql syntax and how we were able to accomplish the support for SQL server in a relatively short time.
Our application is a three tier application with middleware being developed in java. The application server connects through jdbc to the database for DML operations. This helped us in supporting the new database vendor as we could intercept all the sql's passing through the jdbc driver and manipulating most of the sql's to conform with SQL server syntax.
Subscribe to:
Posts (Atom)