Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 28

Displaying posts with tag: sqlserver (reset)

Generating dimension data for dates
+0 Vote Up -0Vote Down

Most analytical and BI databases have date dimension table(s). One frequently needs to generate and populate such data. I present a solution below for such data generation, written in Python. Please use different database drivers/modules to connect to your specific database server (MySQL, SQL Server, Oracle, etc.) for data population.

Notes:

1. It takes 2 parameters, start date and end date, in YYYYMMDD format, inclusive. Extensive error checking is built in, but let me know if you have comments/suggestions;

2. The script produce a Python dictionary (associated array) and print out its content;

3. The output includes dayNumber: a day’s position in a year. For example, 2011-02-01 is the 32ed day in 2011, therefore its dayNumber is 32;

4. The output includes weekNumber: a week’s position in a year. The week number in year is based

  [Read more...]
Data generation with TPC-H’s dbgen for load testing
+1 Vote Up -0Vote Down

2011-06-26 update:

I am not sure if there are any changes in the latest make and gcc packages. Anyway, I noticed when run make, I encountered the message below:

make: g: Command not found
make: [qgen] Error 127 (ignored)

To fix this, find where gcc is at, then created a symbolic link g that points to gcc. All is well afterwards:
[root@ip-10-245-209-196 dbgen]# which gcc
/usr/bin/gcc
[root@ip-10-245-209-196 dbgen]# cd /usr/bin/
[root@ip-10-245-209-196 bin]# ln -s gcc g

End update
Recently I found myself doing some data loading benchmark testing with table partition. Data loading and storing for BI/DW/DSS stuff almost always involves data partitioning. SQL Server partition has a nice feature called partition switch, where you can swap data in and out of a partitioned table.






  [Read more...]
SSDs and their impact on database servers
+1 Vote Up -0Vote Down

Vadim Tkachenko published interesting benchmark results with PCI-E based SSDs here. I recently got a chance to benchmark FusionIO’s 320 GB PCI-E drive. It was really impressive. My results, done on Windows with sqlio, are consistent (not identical, of course, but in the same ballpark) with what Vadim reported in that blog post, done with sysbench on Linux.

sqlio is a popular IO throughput testing tool from Microsoft. I didn’t get to test the throughput when the SSD is close to full. The key takeaways that I learned from my testing are:

1. I can confirm that there is no difference between random and sequential IO, contrary to the traditional spindle based hard disks;

2. Read is significantly faster than write. Reads and writes

  [Read more...]
Testing Windows IO with SQLIO and SysBench
+1 Vote Up -0Vote Down

To benchmark IO on Linux and MySQL transaction processing, SysBench is a popular choice that can do both. After poking around at the source code, it seems PostgreSQL and Oracle are also included for transaction processing testing if you have the proper header files, but I didn’t test those.

To benchmark IO on Windows and SQL Server transaction processing, Microsoft provides two tools, SQLIO and SQLIOSim. SQLIO is a misnomer in that it really doesn’t have much to do with SQL Server. It is a general purpose disk IO benchmark tool.

So today I was playing with SysBench and noticed that I can compile and build it on Windows as well. I decided I should run IO benchmark on a single machine with both tools (SQLIO and SysBench), and see if I could reconcile the results.

To make things simple, I thought I would just benchmark random read of 3G

  [Read more...]
How to control changes in your database
+0 Vote Up -0Vote Down
Database schema's need version control. Here you can find some guidelines help you control your changes.
Log Buffer #178, a Carnival of the Vanities for DBAs
+2 Vote Up -1Vote Down
Dave Edwards has offered me to write this week's Log Buffer, and I couldn't help but jump at the opportunity. I'll dive straight into it.

Oracle

I'll start with Oracle, the dust of the Sun acquisition has settled, so maybe it's time to return our attention to the regular issues.

Lets start with Hemant Chitale's Common Error series and his Some Common Errors - 2 - NOLOGGING as a Hint explaining what to expect from NOLOGGING. Kamran Agayev offers us an insight into Hemant's personality with his Exclusive Interview with Hemant K Chitale. My favorite quote is:

Do you





  [Read more...]
Free 10-day trial of Safari Books Online
+2 Vote Up -0Vote Down

That’s right — get your free 10-day trial! All the information I know is here:

http://bit.ly/37E9ld

But the basics are: No access to Rough Cuts or Downloads, for new subscribers only. It’s one of those “sign up and if you do not cancel after 10 days, we bill you” — and at $42.99 a month, that’s not a mistake you want to make. Must sign up by Nov. 24th.

To sign up now: https://ssl.safaribooksonline.com/tryitfree

I was asked to send this information along, so I am…Now’s your chance to skim High Performance MySQL, among other high quality books!

SQL code for SQL and Relational Theory
+1 Vote Up -0Vote Down

I am reading SQL and Relational Theory by C. J. Date. Baron Schwartz wrote a nice review for it. I am reading the online version, about half way through, so am not sure if it has an accompanying CD with source code. In any case, if you want to play with some SQL code listed in the book, here is the script to generate the tables and rows (or should I say relations at a certain point in time with tuples?)

CREATE TABLE S
   ( SNO    VARCHAR(5)   NOT NULL ,
     SNAME  VARCHAR(25)  NOT NULL ,
     STATUS INTEGER      NOT NULL ,
     CITY   VARCHAR(20)  NOT NULL ,
     UNIQUE ( SNO ) ) ;

 CREATE TABLE P
   ( PNO    VARCHAR(6)   NOT NULL ,
     PNAME  VARCHAR(25)  NOT NULL ,
     COLOR  CHAR(10)     NOT NULL ,
     WEIGHT NUMERIC(5,1) NOT NULL ,
     CITY
  [Read more...]
Video: Interview with Microsoft’s PHP Evangelist
+0 Vote Up -0Vote Down

I caught up with Zach Skyles Owens, a PHP Evangelist at Microsoft. If you missed the embed, watch the video. I have some sparse notes below.



I learned some new things:

  • Microsoft spends time working with the PHP community
  • They are porting applications to work with an SQL Server backend
  • They are ensuring that the language should “just work”, with the IIS and SQL Server stack. This is quite different from the usual AMP (Apache = server, MySQL = database, PHP = language) stack that we’re quite accustomed to.
  • There is a Microsoft Web Platform, and there’s a


  [Read more...]
Moving data from Sql Server to MySQL
+0 Vote Up -0Vote Down

To move data from Sql Server to MySQL, it is certainly possible to use tools that can make connections to both data stores and manipulate data that way, such as Access, Excel, or SSIS. Here I will introduce a process that does not need any special tools or data drivers. Instead, we can use the utilities and methods that come with a standard Sql Server and MySQL install to accomplish that task.

With this approach, it is assumed that matching tables already exist on MySQL. If not, they need to be created first.

This process is comprised of these steps: first bcp command will be generated based on Sql Server database meta data (sysobjects, think information_schema in MySQL); then the generated bcp commands will be executed; the resulting csv files can then be transferred to the MySQL server, optionally it is possible to compress them if the size is big; and finally

  [Read more...]
Thoughts on Data Masking
+0 Vote Up -0Vote Down

Often times, production data needs to be moved to different environments for testing/developing purposes. However, some of that data can be people’s name, birthday, address, account number, etc., that we don’t want testers and/or developers to see, due to privacy and regulatory concerns. Hence the need to mask those data. I can certainly see this needs grow over time for all database platforms. There are software out there that does this sort of task, or similar tasks, such as data generation tool. Oracle actually has a Data Masking Pack since 10g for this purpose. Here are some of my thoughts on this topic.

One method of masking data is through reshuffling, which shuffles the value in target column(s) that you want to protect randomly across different rows.

Another way of doing it is through data generation. For instance, for target column(s), we just replace

  [Read more...]
MySQL perspectives from a SQL Server guru
Employee_Team +0 Vote Up -0Vote Down

Ben Kaufman at SQLServerCentral introduces MySQL to the SQL Server DBA crowd. All in all his views seem to be fairly positive, in particular the MySQL Cluster experience:


NDB is the gem of MySQL, originally developed by Ericson to track cell phone calls this is a share nothing cluster engine stored in memory. This is a true cluster that supports both high availability and load balancing. [...]
This engine is similar to synchronous mirroring in SQL Server in that it is a 2-phase commit, the difference being the commit is done in memory at the data layer not the log. Logs are hardened at a later time, with the theory being that since data is committed on multiple nodes the data is safe and doesn't require a log flush as part of the transaction. [...]
For pure performance the


  [Read more...]
MySQL perspectives from a SQL Server guru
Employee_Team +0 Vote Up -0Vote Down

Ben Kaufman at SQLServerCentral introduces MySQL to the SQL Server DBA crowd. All in all his views seem to be fairly positive, in particular the MySQL Cluster experience:


NDB is the gem of MySQL, originally developed by Ericson to track cell phone calls this is a share nothing cluster engine stored in memory. This is a true cluster that supports both high availability and load balancing. [...]
This engine is similar to synchronous mirroring in SQL Server in that it is a 2-phase commit, the difference being the commit is done in memory at the data layer not the log. Logs are hardened at a later time, with the theory being that since data is committed on multiple nodes the data is safe and doesn't require a log flush as part of the transaction. [...]
For pure performance


  [Read more...]
MySQL perspectives from a SQL Server guru
Employee_Team +0 Vote Up -0Vote Down

Ben Kaufman at SQLServerCentral introduces MySQL to the SQL Server DBA crowd. All in all his views seem to be fairly positive, in particular the MySQL Cluster experience:


NDB is the gem of MySQL, originally developed by Ericson to track cell phone calls this is a share nothing cluster engine stored in memory. This is a true cluster that supports both high availability and load balancing. [...]
This engine is similar to synchronous mirroring in SQL Server in that it is a 2-phase commit, the difference being the commit is done in memory at the data layer not the log. Logs are hardened at a later time, with the theory being that since data is committed on multiple nodes the data is safe and doesn't require a log flush as part of the transaction. [...]
For pure performance


  [Read more...]
Some notes on Sql Server and MySQL
+0 Vote Up -0Vote Down

I will take MySQL certification exams, therefore I am going through MySQL 5.0 Certification Study Guide. It is a pretty cool book in that it goes to the point right away without many wasted words. Too many technical books dance around the topic, fill the volume with screen shots, but do not deliver the goods in the end. I am reading the Safari online version. With so many pages, I can see the physical book can be bulky.

Anyway, some comments and observations:

1. Sybase, MySQL, and Sql Server’s definition and implementation of database and schema are somewhat close. I dislike Microsoft’s definition and usage of schema, especially in Sql Server 2005. It is very confusing to new or DBAs from other

  [Read more...]
Analyzing low performance SQL code
+0 Vote Up -0Vote Down

As an independent consultant and trainer , I found myself doing a lot of existing code analysis and enhancement, mostly for stored procedures and ad-hoc SQL statements. I suspect a lot of people do the same thing as well, so I am really interested in learning how you do it. That’s the main purpose of this post. This post is tagged with Oracle, Sql Server, and MySQL, as the principals should be the same for all platforms.

Let me share with you how I do it. Notice that I look at table/column statistics and indexes in almost all the steps below. Therefore I purposely left them out in the discussion.

1. I always talk to the original author or the current owner of the code, asking him/her to walk me through it. I listen mostly, trying to understand why s/he writes code this way. I may ask a few questions, just to help me understand. I almost never do any

  [Read more...]
Migrating from one RDBMS to another
+0 Vote Up -0Vote Down

Here is some of my thoughts on migrating MySQL to Sql Server. It came out of an email discussion. I’d love to hear your thoughts on migrating to a different database platform, not just MySQL to Sql Server.

I actually thought about writing a white paper or even a course on migrating from MySQL to Sql Server, but never got the time to do it. Sometimes a project doing similar things can serve as a launchpad for this endeavour, but that never came along, at least not yet. I am very interested in database interoperability field though. I’ve done MySQL and Oracle admin in the past and have published some MySQL and Oracle stuff in blogs. I have much better technical skills on Sql Server than any other RDBMS platforms, primarily because I’ve worked on it longer.

Here are some of my thoughts. I think most of it applies equally on migration from

  [Read more...]
INFORMATION_SCHEMA Support in MySQL, PostgreSQL
+0 Vote Up -0Vote Down

I've known about the INFORMATION_SCHEMA views (or system tables) in SQL Server for a while, but I just leared recently that they are actually part of the SQL-92 standard and supported on other database platforms.

The INFORMATION_SCHEMA views provide meta data information about the tables, columns, and other parts of your database. Because the structure of these tables are standardized you can write SQL statements that work on various database platforms.

For example suppose you want to return a resultset with a list of all columns in a table called employees

SELECT table_name, column_name, is_nullable, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'employees'

Quite a handy feature, but it's hard to find what versions the of various database platforms started supporting this feature, here's a quick

  [Read more...]
MySQL Migration Toolkit
+0 Vote Up -0Vote Down
I've been evaluating the latest version of the MySQL Migration Toolkit to work on migrations from SQLServer 2005 to MySQL 5.1Here are some of the things I found so far:Ease of use: Very simple and fast to get up and running - I was able to get multiple databases migrated from SQLServer to MySQL without any special setup or configuration changes. Gui is straight forward and intuitive.Took a
CTAS and Select Into
+0 Vote Up -0Vote Down

In both Oracle and MySQL, you can do:

create table T1 as select * from T1

This CREATE TABLE AS statement basically clones table T1 with its structure and data in T2. This can be pretty handy at times.

The equivalent of that in Sql Server is SELECT INTO. For example, you can do:

select * into T2 from T1

to achieve similar results.

Delete permission implementation differences
+0 Vote Up -0Vote Down

I mentioned when grant statements take into effect in Sql Server, MySql, and Oracle here.

I found out recently that there are some implementation differences when you grant only delete permission on a table to a user. MySql and Sql Server do this the same way, whereas Oracle is different.

Suppose you have:

1. Table t1: create table t1 (c1 int);
2. User TestLogin. The only permission of this TestLogin is delete on t1.

In all 3 database platforms, TestLogin can find out what columns t1 has by default, using either

desc t1

or

sp_columns t1

In both Sql Server and MySql, the only thing you can do is:

delete from t1;

which essentially wipes out the whole table. You can do the same thing in Oracle.


  [Read more...]
Backwards LIKE Statements
+0 Vote Up -0Vote Down

Sometimes you need to think backwards.

Here was the problem. I needed to match up some IP address ranges to the company that owns them. Looking for a simple solution to the problem I came up with storing the IP address block patterns in the database as follows:

ip_pattern
----------------
127.%.%.%
192.168.%.%
10.%.%.%

Any idea why I choose % as the wildcard?

That's right - it's the wildcard operator in SQL for the LIKE statement.

So now when I have have an IP address 192.168.1.1, I can do what I like to call a backwards LIKE query:

SELECT company, ip_pattern
FROM company_blocks
WHERE '192.168.1.1' LIKE ip_pattern

This works on SQL Server and MySQL, and I would think it should work fine on any database server.

When does grant statement take into effect
+0 Vote Up -0Vote Down

In both Sql Server and Oracle, permission changes to a user take into effect right away, even when said user is connected at the time you made the change.

In MySql, it is a little different, depending on how the permissions are given. If you use the GRANT statement, then it takes into effect right away. However, if you create user and give it permissions by manipulating the user table in the mysql system database directly, that is, using Sql statements, then you need to issue:

flush privileges

for those changes to be picked up.

Executing sql scripts using command line tools
+0 Vote Up -0Vote Down

Sql Server 2005 has a command line tool named sqlcmd. MySQL has a command line tool named mysql. Oracle has a command line tool called sqlplus. They can all be used for interactive query processing and batch scripts processing. They do similar things, albeit in different ways. They are functionally equivalent.

For Sql Server 2005, when in interactive mode of sqlcmd, use

:r c:MyFolderMyScript.sql

to read and execute a script file. You may have to type

go

afterwards, if the last line of the script file does not end with the word go.

To use sqlcmd in batch mode, that is, to run a sql script and then get out, use:

sqlcmd -i c:MyFolderMyScript.sql -S MyServerName -E

Replace -E with -U LoginName if you use Sql authentication

For MySQL, while in interactive mode of mysql, use

.
  [Read more...]
desc is sp_columns in Sql Server
+0 Vote Up -0Vote Down

In Oracle and MySql, to get the column name and data type of a table, you can use:

desc MyTable

or

describe MyTable

The equivalent of desc in Sql Server is sp_columns. Therefore, run the command below will get similar results:

sp_columns MyTable

Connect to a different database server within command line utility
+0 Vote Up -0Vote Down

In sqlcmd, you can connect to a different server by this command:

:connect MyServer

The above command will attempt to use integrated / Windows Active Directory authentication. To use traditional Sql Server authentication, use this instead:

:connect MyServer -U MyLogin

You can do the same kind of thing in Oracle Sql Plus by using this command:

conn[ect] MyLogin@OracleSID

For mysql command line, use this:

connect -h MyServer -u MyLogin -p

That is assuming that you are allowed to login to the MySql server from whereever you are now.

64-bit, virturalization, and their impact
+0 Vote Up -0Vote Down

VMWare recently released a freeware called VMWare Player that can play a pre-built virtual machine file. A virtual machine is an OS bundled with whatever the virtual machine creator put there. This is perfect for people to test-drive various operating systems and software, without going through the hassle of installing themselves. VMWare currently provides virtual machines preloaded with RedHat, Novell Suse, ubuntu, Oracle, MySql, and Bea, among others.

Memory used to be a bottleneck for virtualization software to take off. However, on the hardware side of things, both Intel and AMD are pushing 64-bit processors pretty aggressively now. With 64-bit architecture, the memory space the operating system can access increases exponentially (from 2^32 to 2^64). With the push towards 64-bit and the emergence of

  [Read more...]
SQL to Select a random row from a database table
+0 Vote Up -0Vote Down

There are lots of ways to select a random record or row from a database table. Here are some example SQL statements that don't require additional application logic, but each database server requires different SQL syntax.

Select a random row with MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Select a random row with PostgreSQL:

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

Select a random row with Microsoft SQL Server:

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Select a random row with IBM DB2

SELECT column FROM table
ORDER BY RAND() 
FETCH FIRST 1 ROWS ONLY

Thanks Tim

Select a random record with Oracle:

SELECT column FROM 
( SELECT
  [Read more...]
Showing entries 1 to 28

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.