Hello, I would appreciate a little help with a little research I'm doing. I have created a small database, which model can be seen here:
Physical model.
I have about 20 products (rows), 100 customers, 7 credit cards, 10 000 payments, 20 000 orders and ~219 000 ordered_products. My goal is to compare Sybase's data retrieval effectiveness and speed to some other DBMS-s, but I haven't figured out, what query (or queries) should I use to effectively reach that goal. I was planning to create one to three queries, which would use as much tables as possible and result in at least about 1000 rows.
I am not really experienced in databases yet and that's why I am struggling with this.

Thanks in advance :)

asked 06 May '13, 16:59

Veiko%20Samma's gravatar image

Veiko Samma
15113
accept rate: 0%

edited 06 May '13, 18:23

Breck%20Carter's gravatar image

Breck Carter
25.7k427587847

2

It is a worthy project, but... that database is too small, in terms of both schema size and data volume, to be of much use for production benchmark testing. Presumably, you are also going to use a single connection.

Is this research being done in an academic setting?

If so, then I'll shut up about the size and volume, and make some actual suggestions :)

Also, consider using SQL Anywhere 16 instead of 12.

Note: All commercial DBMS licence agreements forbid public publishing of benchmark comparisons with other DBMS products without permission... it's the same for all vendors, and AFAIK they never give permission except to the TPC.

(06 May '13, 18:18) Breck Carter
Replies hidden

Yes, actually this is a part of my bachelor's thesis. I am not aware to which extent I can ask help about this, as I am probably supposed to figure it out myself, but I can't help thinking it's not forbidden to ask a little extra help.
My purpose was to evaluate/compare Sybase, SQL Server 2012 and Oracle 11g from an administrative point of view and the creation of a small database was just for the purpose of creating a one or two queries, run them in all of the systems and compare the time spent on the execution.
I would just need some pointers or suggestions which query I should create so it could involve maybe at least 3-4 tables and give at least 1000 rows in the results. Even the idea, what to require from the database, would be helpful :)

It doesn't seem a very difficult task, but I am a little overwhelmed at this point already and my brain isn't working in my advantage :D

(06 May '13, 18:30) Veiko Samma

Wow, that comes as a surprise. So a simple comparison of the execution times of a single query in a similar database would already be considered as a benchmark comparison? Or for example the comparison of the simplicity of installing, understanding, learning the system (all from the user experience point of view), creating a database, inserting test data, comparing database sizes, and comparing the creation of users and backup&recovery?

And why would you recommend SQLA 16? Is it really worth it? Because I don't have much time to redo anything.. :/

(06 May '13, 19:53) Veiko Samma
3

As far as I can tell, Sybase end-user license agreements are not posted online, perhaps because they differ by product and country and are changing all the time. So, you have to install the product and capture the EULA text.

The EULA for SQL Anywhere 11 contains this single mention of "benchmark": "Results of benchmark or other performance tests run on the Program may not be disclosed to any third party without Sybase's prior written consent."

I read that as applying to performance tests only, NOT discussions or comparisons about ease-of-use, solution effectiveness, language power, etc... those kinds of comparisons appear all the time. But, I'm not a lawyer, nor am I an employee of Sybase or SAP.

Microsoft is another story; the following Google search yields many hits: eula benchmark "sql server" site:microsoft.com

Here's an excerpt from one of Microsoft's EULAs covering SQL Server: "If you have a copy of Microsoft Windows Small Business Server 2003, Premium Edition, disclosure of the results of any benchmark test of the SQL Server, or Internet Security and Acceleration Server portions of the Software to any third party without Microsoft Corporation’s prior written approval is prohibited."

Oracle, same thing; the following Google search works well: eula benchmark site:oracle.com

Here's a snippet from Oracle 10g: "You may not: ... disclose results of any program benchmark tests without our prior consent; ..."

IBM is different: they hide their rules in plain sight; i.e., the many posted IBM DB2 EULAs do contain the words "you may disclose" but the surrounding legalese is so complex as to be incomprehensible.

=====

If you're pushed for time, don't bother with SQL Anywhere 16.

=====

Generally speaking, your question falls into the category of "Can someone do my homework for me?" which is not something I'm comfortable with, ESPECIALLY not with a thesis which is so much MORE than mere homework.

You're in a bind, you don't need me bashing you on morality or ethics. Nevertheless, many of us don't even answer this kind of question when it comes from a journeyman developer with a career-ending deadline, never mind a student.

Your project is worthy, not your question, so I have posted a matching answer.

(07 May '13, 05:03) Breck Carter
1

I understand your point, I tried to form this subject in a way it would not seem like I want my work done for me. I just thought maybe I could get some fast suggestions here for creating a query or two which logically uses different tables, functions and gives a rather large amount of results. I did not expect to the work be done for me, but only suggestions, which data could I retrieve.

I did not want to create an uncomfortable situation regarding me, you or anyone else who would have been kind enough to offer their help.

But about these legal issues (I quickly searched for some information myself and also found the information from Oracle), I contacted my supervisor and he told me that I can continue my work, there are a few possibilities, for example I won't publish my results or I could have a closed thesis defense, which would guarantee that my work would not be made publicly available.

Anyways, thanks a lot for your help :)

(07 May '13, 11:19) Veiko Samma
1

@Breck: Thumbs up for this comprehensive answer and your general comment

(07 May '13, 12:11) Martin

As to the queries: Given the data model is designed by yourself, I do not understand how you would need help w.r.t. what to query... - and that's difficult to suggest without knowing the data...

What about something like:

  • "TOP 3 products per customer city and/or year (or quarter) of order date"
(07 May '13, 12:55) Volker Barth

I have heard (but cannot find an actual example) of an academic paper which used "Database A", "Database B" and "Database C" instead of actual product names. I will look again...

... the search failed (again) but I have sent an appeal to someone who surely knows.

(07 May '13, 13:08) Breck Carter
1

Glenn Paulley comes through again!

Here are some snippets from Ivan Bowman's 2005 PhD Thesis "Scalpel: Optimizing Query Streams Using Semantic Prefetching" showing how he dealt with license agreement restrictions:

We ran our tests with three commercial DBMS products. The license agreements prevent us from identifying them. As results for all three systems were consistent (although with different constants) we show results for only one DBMS product.

The DBMS products we tested did not benefit from such a transformation....

The other two DBMS products that we tested were not such strict disciplinarians...

DBMS B does support our optimizations if an appropriate CAST is used to provide a data type for each NULL value.

(08 May '13, 07:33) Breck Carter
showing 1 of 9 show all flat view

Start with the following Google search

SQL database performance benchmark

and go from there... you may want to refine the search somewhat.

permanent link

answered 07 May '13, 05:02

Breck%20Carter's gravatar image

Breck Carter
25.7k427587847
accept rate: 20%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×409
×242
×90

question asked: 06 May '13, 16:59

question was seen: 1,941 times

last updated: 08 May '13, 07:33