Database Discussion Boards

Bernhard Hiller8-Sep-10 22:41

8-Sep-10 22:41

I have done some little work with MySQL, and even less with postgres. My impression was that postgres is slightly more complicated to handle (especially when it comes to automatic row IDs, and querying them from an application), but it performed better when doing reports with "complicated" sql (joining subqueries with real tables) - but the more complicated the queries get, the more likely is also postgres to not complete it within an hour where Microsoft SQL Server or Oracle do that within a few seconds!
By the way, there is a hospital information system using postgres with free source code in Java: http://www.hospital-os.com/en/[^]

Re: PostgreSQL or MySQL?

David Skelly9-Sep-10 1:46

David Skelly

9-Sep-10 1:46

This may help:

http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL[^]

One thing to bear in mind that many people forget: if you are developing a commercial application, MySQL is not free and you will need to purchase a commercial licence for it.

If using MySQL, you also need to consider whether you will have a high read:write ratio as that may influence your choice of MyISAM (faster but no ACID transaction support) versus InnoDB (full transaction support but not as fast).

Re: PostgreSQL or MySQL?

Daniel Casserly9-Sep-10 2:21

Daniel Casserly

9-Sep-10 2:21

i agree with the guys here you need to ask yourself what you need for your particular circumstances but my personal opinion is that postgre sql is a much easier to use and speedy database than mysql. Using pgadmin3(which is free) you have loads of help and docs and its all free. However, one comment is really true, it depends what you are most happy with and which suits your needs.

Re: PostgreSQL or MySQL?

Jassim Rahma9-Sep-10 2:53

Jassim Rahma

9-Sep-10 2:53

thanks everyone.. clear... seems like I am going to use PostreSQGL..

but one question please...

comparing to Microsoft SQL Server 2008 Express Edition, am I going to lose any feature if I decided to move to PostgreSQL?

Re: PostgreSQL or MySQL?

Bernhard Hiller9-Sep-10 3:42

Bernhard Hiller

9-Sep-10 3:42

Yes, some. But generally, the problem can be solved.
Some points to consider:
- searching text with MS databases is not case sensitive, in postgres it is. You can use ILIKE instead of LIKE or the LOWER() function on both sides.
- the table name USER is not allowed, using square brackets is also not allowed (e.g. [USER]); you can use user_ instead.
- the data type Guid is missing. You can use CHAR(36) instead.
- autoincrement columns are implemented differently. Use the data type serial, a sequence is created automatically. For querying the last inserted id, "SELECT @@Identity" does not work, you must query the current value of the sequence instead: SELECT currval(pg_get_serial_sequence(mytable, mycolumn));

Re: PostgreSQL or MySQL?

Daniel Casserly9-Sep-10 4:10

Daniel Casserly

9-Sep-10 4:10

ive not found anything that majorly impairs me from the move however there is a cool feature of arrays as a data type in pgsql as a bonus, therefore you can have an example field like maybe colors type text[] and fill it with info like '{blue, black, grey, green}' etc. similar to an enum in c#. Quite a nice little feature that i cant find in sql server

Re: PostgreSQL or MySQL?

JChrisCompton9-Sep-10 8:13

JChrisCompton

9-Sep-10 8:13

I worked at a teaching hospital for about 18 months and the db licenses there were dirt cheap. (Less than 1/3 the retail price) I assume because it was attched to a University.

This applied to Oracle and SQL Server (and yes, I am talking about a production license in both cases).

YMMV - A lot has changed in seven years, so this may be completely irrelevant.

HTH,
-Chris C.

sql server 2005 maintainance plan good practice....

Sasmi_Office8-Sep-10 1:52

Sasmi_Office

8-Sep-10 1:52

Dear Friend,

help me come out of this issue....

i have one application on which more then 1000 user access daily and its create huge data transaction which need to be manage.

we have maintainance plan which run on weekly basis.

but still some time it slower down help me for this...

tell me some good practice for this issue...

i have some questions
1. What is statistics job does.

2.What task(s) is/are suitable for daily maintenance and what for weekly/monthly maintenance

3.Do database need to be offline while in some task, example : reorganize/rebuild index, Shrinking database, etc... (since we need to keep 90% uptime)

4.How long can check database integrity, reorganize/rebuild index, cleanup history be?

5.Should we do both reorganize and rebuild index?

6.Do we need to update statistic after reorganize index? Since rebuild index will auto update statistic

Please help me asap..

thanks

Sasmi

Re: sql server 2005 maintainance plan good practice....

Mycroft Holmes8-Sep-10 13:15

Mycroft Holmes

8-Sep-10 13:15

This is where DBAs earn their living, I suggest you either start reading a number of articles on database specific sites (sqlservercentral is one) or hire a DBA consultant to design a maintenance regime for you.

Never underestimate the power of human stupidity
RAH

Re: sql server 2005 maintainance plan good practice....

Corporal Agarn9-Sep-10 1:18

Corporal Agarn

9-Sep-10 1:18

I agree with Mycroft (second time today) this is more of an art than a "do it this way" type thing. There are so many variables that there is no easy answer.

Some things to look into (learn about) would be Data Base Tuning Advisor and audit trace for a start. These may or may not help.

There are also several third party programs that might help, again Google may help. You might look at Idera as one provider.

SQL Server CAL

divyesh14327-Sep-10 20:56

divyesh1432

7-Sep-10 20:56

Hi,

I just want to understand CAL licenses of microsoft sql server!

If I implement Service Oriented Architecture then also I need to buy CALs for all my clients?

And is it different from SQLEXPRESS to SQL SERVER?

Thanks,
Divyesh

Re: SQL Server CAL

Corporal Agarn8-Sep-10 6:33

Corporal Agarn

8-Sep-10 6:33

It is best to go to Microsoft's books on line to get their take on CAL's

There are two types of licensing server and client side. It depends mainly on how many users as to which what to go.

Good luck

Re: SQL Server CAL

divyesh14328-Sep-10 18:05

divyesh1432

8-Sep-10 18:05

If u know or can provide the link which mention the service oriented architecture with sql server CALs?

Thanks,
Divyesh

Re: SQL Server CAL

Corporal Agarn9-Sep-10 1:12

Corporal Agarn

9-Sep-10 1:12

Had to Google SOA as I have always been in small shops. You might look into the 'per CPU' license.

Re: SQL Server CAL

Mycroft Holmes8-Sep-10 13:12

Mycroft Holmes

8-Sep-10 13:12

Best to go to a bloody magician, you certainly can't rely on a forum post to clarify this subject. MS licencing is one of the most arcane subject in the software industry. Good luck.

Never underestimate the power of human stupidity
RAH

Re: SQL Server CAL

divyesh14328-Sep-10 18:09

divyesh1432

8-Sep-10 18:09

One more thing is there, if a public hosted web site is there. It has one web service which populate data from sql server.

Now you never know how many connection will be there for that web site, in that case how to buy CALs?

Same thing apply in my case!

Even though I dont have public server. It will be a static ip server in organization's intranet! There will be one .net application which has hosted one .net remote object and my client communicate with that .net remoting object and fetch the data!!!

Thanks,
Divyesh

Re: SQL Server CAL

Mycroft Holmes8-Sep-10 19:11

Mycroft Holmes

8-Sep-10 19:11

There are plenty of resources[^] out there that can help!

Never underestimate the power of human stupidity
RAH

Re: SQL Server CAL

Corporal Agarn9-Sep-10 1:09

Corporal Agarn

9-Sep-10 1:09

Mycroft brings up a good point, MS licensing is a nightmare. Thus if you go to the MS SQL Server site they have several pages of information.

As to a general license that would be per CPU.

Re: SQL Server CAL

The Man from U.N.C.L.E.9-Sep-10 8:01

The Man from U.N.C.L.E.

9-Sep-10 8:01

SQL Express is totally free, and has no requirement for CALs or any other form of license.

Other editions of SQL Server can be licensed per processor or by CAL as per SQL Server 2008: SQL Server 2008 Licensing[^]

If you have knowledge, let others light their candles at it.
Margaret Fuller (1810 - 1850)
[My Articles] [My Website]

Partioning table

geeeeeeeetha7-Sep-10 17:44

geeeeeeeetha

7-Sep-10 17:44

Hi,

I'm having table like

empid,empname,cityname,statename,countryname.

how to splitt this table structure.

Regards
Bharathi

Re: Partioning table

Mycroft Holmes7-Sep-10 18:01

Mycroft Holmes

7-Sep-10 18:01

Partitioning a table is NOT what you want to do, that is dealing with VERY large tables and requires DBA skills.

You are after a data structure.
You have 3 tables crammed into 1.

Employee
 EmployeeID
 EmployeeName
 StateID

Country
 CountryID
 CountryName

State
 StateID
 CountryID
 StateName

Employee View

Select *
From Employee E
 Inner Join State S on S.StateID = E.StateID
 Inner Join Country C on C.CountryID = S.CountryID

Never underestimate the power of human stupidity
RAH

How to keep one more backup copy of current database.

shiva.kore6-Sep-10 22:05

shiva.kore

6-Sep-10 22:05

How to keep one more backup copy of current database.. where i am using only single database to keep my 10000 record of info. if something goes wrong what to do...So i want to keep one more copy of that keeps continues changes info of main database..

************ S G KORE *******************

Re: How to keep one more backup copy of current database.

Mycroft Holmes6-Sep-10 22:46

Mycroft Holmes

6-Sep-10 22:46

No don't keep a copy of the database keep a BACKUP of the database. If you are using 2008 you can get compression with the backup and keep multiple versions. A typical backup regime is something like

backup every night mon-fri
keep the last 5 fri backups
backup end of month regardless of the DOW and keep for 12 months
keep the EOQtr indefinately

Never underestimate the power of human stupidity
RAH

Re: How to keep one more backup copy of current database.

Corporal Agarn8-Sep-10 6:36

Corporal Agarn

8-Sep-10 6:36

Look into replication (publication) or log shipping. Depending on your usage and resources the type you use may very.

You can replicate to the same SQL server.

String manipulation in stored procedures (MySQL 5.1)

Sylv336-Sep-10 7:10

Sylv33

6-Sep-10 7:10

Hello,

I need to write a stored procedure in MySQL that loops through all rows in a table (the column types are TEXT and VARCHAR) and replaces certain characters in the column values with another character. For example, a certain column contains the text "ababa" and I want to change it to "acaca".

I can't quite figure out the part about replacing a character in a string with another and could use some help with this!

Thank you!
Sylv

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.