Archive for July, 2006

RDBMS search performance isn’t all that bad

July 17, 2006

Pretty much all of the Search vendors would have you believe that RDBMS’s can’t scale beyond a couple hundred searches per second no matter how much horsepower you throw at them.

I don’t doubt this is true for very large numbers of records where freetext and/or Assisted / Guided Navigation is involved, however, if your search criteria and data is structured and indexed properly, e.g. Price, Location, Number Of Bedrooms, Property Type, Car Make, Car Model, etc., then you can achieve very high numbers of searches per second using traditional RDBMS’s without the need for fancy Search technology.

As part of our load testing of Microsoft SQL Server 2005 we have developed scripts to replay searches, which were executed by users of our website, against Microsoft SQL Server 2005.

A single Dell PowerEdge 1850, 2 x 3Ghz Intel processors (not dual core), 8GB RAM, 10K RPM SCSI disks, Windows 2003 Server SP1 x64 and Microsoft SQL Server 2005 SP1 x64 averaged 75 searches (complex query involving several database tables and recording search criteria) per second at approximately 70% CPU utilisation. For a while, whilst 4 threads were running, it averaged over 100 searches per second although the CPU was much higher. I see no reason why this wouldn’t scale almost linearly if more, RAM, processors, faster processors, disks and 15K RPM SCSI disks were used.

Your mileage may vary so don’t take my word for it.

Advertisements

Import load testing results

July 12, 2006

Our latest load testing performance results show that our Import application which bulk imports (not using BCP) adverts into our database runs faster against Microsoft SQL Server 2005 than Sybase ASE 12.5.3 on identical hardware with identical data even when using the poor performing Microsoft SQL Server 2005 JDBC driver. I would have been happy with it being 10% slower.

Newsgroup provides open source solution to closed source problem?

July 10, 2006

As indicated in a previous post I posted a question to a newsgroup regarding the poor performance of the Microsoft SQL Server 2005 JDBC driver.

Peter T kindly suggested that we try

http://sourceforge.net/projects/jtds

I was initially a little hesitant to try an open source driver, especially one that claimed to have the fastest performance for SQL Server 2005 and Sybase ASE. However…

With the jTDS driver, SQL Server 2005 managed stored procedure calls at the rate of: 966.88 per second. This is effectively twice that of the Microsoft driver!

And Sybase ASE 12.5.3 managed stored procedure calls at the rate of: 829.18 per second.

We are now going to start end to end testing with the jTDS driver although my preference would be to have a high performing Microsoft driver.

Shame on Microsoft and Sybase for writing such badly performing JDBC drivers!!!

Oh by the way my contact at Microsoft was of no help.

Meeting an old boss

July 8, 2006

I met my former boss at easyGroup, Stelios Haji-Ioannou (now Sir Stelios) today (6th July). We hadn’t met for about 12 months prior to this.

Ian ‘Crispy’ Duckhouse back at work

July 8, 2006

Despite the house Ian ‘Crispy’ Duckhouse lives in catching fire he is back to work after only one day off. He and his housemates are still without water and electricity though.

His commitment is appreciated and I am glad to see he is a reader of my blog. Well I did say I would fire him if he didn’t read it…..only joking….

My flat is on fire excuse

July 5, 2006

I have just had a member of my staff leave a message for me saying that he won’t be in today as his flat caught fire during the night and that the fire brigade have only just let him back in the house.

Data access (JDBC / ADO / OLEDB) performance comparison for Sybase ASE 12.5.3 and Microsoft SQL Server 2005

July 3, 2006

As I mentioned in an earlier post we are currently in the process of migrating from Sybase ASE 12.5.3 to Microsoft SQL Server 2005.

One of my big concerns from the outset was the performance of Microsoft SQL Server 2005 or more to the point the performance of the Microsoft SQL Server 2005 JDBC driver.

We have been doing some performance analysis which is producing some interesting results.

We have developed a Java console application which uses our applications database abstraction layer to call a stored procedure several million times. The stored procedure simply inserts a record into a database table.

We ran this program against Sybase ASE 12.5.3 on Redhat Enterprise Linux and Microsoft SQL Server 2005 x64 SP1 / Windows 2003 Server x64 SP1. Both RDBMS’s were installed on servers of the same hardware specification.

  • Syabse ASE 12.5.3 using the Sybase JDBC driver managed 780 calls of the stored procedure a second.
  • Microsoft SQL Server 2005 using the Microsoft JDBC driver managed 463 calls of the stored procedure a second.
  • At first glance it appears that Sybase ASE 12.5.3 is much faster than Microsoft SQL Server 2005.

    However we developed an equivalent program in Visual Basic 6 using ADO and the respective OLEDB drivers to call the same stored procedures. The results were

  • Syabse ASE 12.5.3 using the Sybase OLEDB driver only managed 120 calls of the stored procedure a second.
  • Microsoft SQL Server 2005 using the Microsoft OLEDB driver managed 1,106 calls of the stored procedure a second.
  • The Sybase ASE 12.5.3 results were so poor that we downloaded the new OLEDB drivers that Sybase have recently released from their web-site. The performance was significantly better, although still well short of the Microsoft SQL Server OLEDB driver, but it appears the new Syabse OLEDB drivers have a serious memory leak and we were unable to complete the test.

    What does this prove? It goes a long way to proving that the performance of your application depends a lot on the technology you use to connect your application to your database and that you should thoroughly test the performance of the drivers.

    I am now off to post an article on one of Microsoft’s MSDN Managed Newsgroups and email somebody I know at Microsoft.

    Over the next few days I will post on the raw performance of Sybase ASE 12.5.3 and Microsoft SQL Server 2005.

    Another day another Sybase ASE / EFTS issue

    July 3, 2006

    We suffered yet another web-site outage of a couple of hours on Thursday 29th June as Sybase ASE 12.5.3 and Sybase EFTS lost the plot.

    Datawarehouse instance of Sybase failed at weekend

    July 3, 2006

    Our Datawarehouse instance of Sybase ASE 12.5.3 failed during the early hours of Sunday 25th June. This resulted in people being called out and having to work on Sunday. It also meant we spent most of Monday playing catch-up. Huge inconvenience and waste of time.

    According to Sybase support the issue which we experienced is a known issue and has been fixed in Sybase 12.5.4. Funny Sybase 12.5.4 was only released a few days ago. If only I had a $ for every occasion I had been told this by a vendor….I would be as rich as Bill Gates.

    More worrying is RDBMSs have been in development for over 20 years and the vendors still can’t make them ultra reliable.

    Projects

    July 3, 2006

    I have a large number of projects I am currently managing. Unfortunately for competitive reasons I cannot mention many of the product enhancements we are currently working on otherwise our competitors, Monster, Rightmove, Autotrader, etc. will have an unfair advantage over fish4. These are just some of the projects I will post on over the next few months.

  • Migration from Sybase ASE 12.5.3 to Microsoft SQL Server 2005
  • Upgrade from Business Objects 6.5 to Business Objects XIr2
  • New Data Centre Build and Migration
  • Search technology procurement, e.g. Autonomy, Endeca, FAST, OmniQ