|
When deciding between MySQL or Postgresql look at the granularity of your data. Postressql is designed for big lumps of data like astronomy images or video streams, MySQL for small lumps of data like account information.
Both will likely work but the thinking behind each is different and this will be reflected in performance and ease of use.
"The secret of happiness is freedom, and the secret of freedom, courage."
Thucydides (B.C. 460-400)
|
|
|
|
|
my data would be many small size data like a column of "open price", another column of "highest price of that day", something like that.
|
|
|
|
|
Also consider the operations to be performed on the database. If it's only create-retrieve-update-delete, MySQL will cope with lots of data. But when you want to do some reporting, MySQL becomes too slow early. And for more complicated reports based on several joined subqueries, also Postgres won't do the job - Oracle can handle that (but otherwise Oracle is quite a PITA).
|
|
|
|
|
I have the following table and i would like to generate an XML as shown below. How do i go about doing this please? All help appreciated. Thanks
id school, name, Subject, results, studentID
----------------------------------------------------------------
1 oxford tom Maths 98 1
2 oxford tom English 87 1
3 oxford tom Chemistry 63 1
4 Cambridge john Maths 50 2
5 Cambridge john English 72 2
6 Cambridge john Chemistry 32 2
<Register>
<Schools>
<School>
<Name>Oxford</Name>
<Students>
<Student>
<Name>tom</Name>
<Subjects>
<Subject>
<Name>Maths<Name>
<grde>98<grde>
<Subject>
<Subject>
<Name>English<Name>
<grde>87<grde>
<Subject>
<Subject>
<Name>Chemistry<Name>
<grde>63<grde>
<Subject>
<Subjects>
<Student>
<Students>
<School>
<School>
<Name>Cambridge</Name>
<Students>
<Student>
<Name>john</Name>
<Subjects>
<Subject>
<Name>Maths<Name>
<grde>50<grde>
<Subject>
<Subject>
<Name>English<Name>
<grde>72<grde>
<Subject>
<Subject>
<Name>Chemistry<Name>
<grde>32<grde>
<Subject>
<Subjects>
<Student>
<Students>
<School>
<Schools>
<Register>
|
|
|
|
|
|
Well, there's no "grade" here ... so I'll use "results" instead. But to illustrate only a point:
CREATE TABLE [cpqaAnswers].[cpqa].[cpqa_OT_tblRegister](
[id][int],
[school][nvarchar](15),
[name][nvarchar](38),
[Subject][nvarchar](45),
[results][int],
[studentID][int]
)
That's the table.
BULK INSERT [cpqaAnswers].[cpqa].[cpqa_OT_tblRegister]
FROM 'C:\Users\OT\registerOT(td)_.txt'
Where registerOT(td)_.txt has the posted data tab-delimited.
SELECT * FROM [cpqaAnswers].[cpqa].[cpqa_OT_tblRegister]
WHERE [results] = 72
ORDER BY [id]
FOR XML PATH('Schools'), ELEMENTS, ROOT('Register')
Not what OP is after but perhaps more key than the usual webpage link.
<Register>
<Schools>
<id>5</id>
<school>Cambridge</school>
<name>john</name>
<Subject>English</Subject>
<results>72</results>
<studentID>2</studentID>
</Schools>
</Register>
[edit]
Is it possible that THIS is what is sought (notice the closed tags that have been added)?
<Register>
<Schools>
<School>
<Name>Oxford</Name>
<Students>
<Student>
<Name>tom</Name>
<Subjects>
<Subject>
<Name>Maths</Name>
<grde>98</grde>
</Subject>
<Subject>
<Name>English</Name>
<grde>87</grde>
</Subject>
<Subject>
<Name>Chemistry</Name>
<grde>63</grde>
</Subject>
</Subjects>
</Student>
</Students>
</School>
<School>
<Name>Cambridge</Name>
<Students>
<Student>
<Name>john</Name>
<Subjects>
<Subject>
<Name>Maths</Name>
<grde>50</grde>
</Subject>
<Subject>
<Name>English</Name>
<grde>72</grde>
</Subject>
<Subject>
<Name>Chemistry</Name>
<grde>32</grde>
</Subject>
</Subjects>
</Student>
</Students>
</School>
</Schools>
</Register>
[/edit]
modified 3-Jul-13 14:36pm.
|
|
|
|
|
Hi,
I keep getting the following message on MySQL on Windows Server.
How can I get a permanent fix for it?
Please try again....... Host '192.168.1.254' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
Technology News @ www.JassimRahma.com
|
|
|
|
|
One way of course would be to stop creating that many connections.
One reason that happens is because the client is not closing the connection after usage. A variation on that is where there are many user clients and some one misused connection pools in that thus creating a many connections for each client when either a properly configured connection pool or no pool at all would have been a better solution.
|
|
|
|
|
According to http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_connect_errors[^], the number of failed connection attempts are counted: i.e. the client tries to connect, but the connection fails for which ever reason. Network problems, wrong credentials, etc. But also - see Jeff's answer - having opened too many connections without closing them; in that case, you should find some entries in the logs that the maximum number of connections was excessed.
|
|
|
|
|
Hi,
I am using INSERT into MySQL database. I am inserting arabic character. The database character set is utf8 and collation is utf8_unicode_ci same with the field article_title.
but the title saved in the article_title field is like this ?????????? (just question marks)
look at this : http://www.volow.com/bahrain.images/About[^]
where is the problem?
Thanks,
www.volow.com
Technology News @ www.JassimRahma.com
|
|
|
|
|
|
this is what I did but I still have the same problem.. Please Help..
character_set_client utf8
character_set_connection utf8
character_set_database utf8
character_set_filesystem binary
character_set_results utf8
character_set_server utf8
character_set_system utf8
character_sets_dir C:\Program Files (x86)\Parallels\Plesk\Databases\MySQL51\share\charsets\
collation_connection utf8_general_ci
collation_database utf8_unicode_ci
collation_server utf8_unicode_ci
completion_type 0
concurrent_insert 1
connect_timeout 10
and in my connectionstring:
<add name="SQLdb" connectionString="Server=xx.xx.xx.xx;uid=admin;pwd=xxxxxxx;database=xxxxx;Charset=utf8;" providerName="MySql.Data.MySqlClient"/>
Technology News @ www.JassimRahma.com
|
|
|
|
|
I have a database containing different product types. Each type contains field that differs greatly with each other. The first type of product, is classified in three categories. The second type of product, is classified in three categories. But the third and the fourth one, is not classified in anything.
Each product can have any number of different properties.
I have a huge database,which containing about 500000 product in product table.
So when I am going to fetch a product from database with all its attributes, or going to search product filtering by attributes, it makes effect on performance badly.
Could any one can help me what will be the tables structure in sql or do some more indexing or any feasible solution for this problem. Because different ecommerce sites are using this kind of database and working fine with huge different types of products.
|
|
|
|
|
Member 7702110 wrote: Because different ecommerce sites are using this kind of database and working fine with huge different types of products.
That's not due to a "single" thing, it requires a lot of things to be checked. 500.000 items isn't much.
A normalized design (upto BCNF, see wikipedia) would, IMHO, be the best startingpoint. Next, you'd need to determine indexes. You'd also need to optimize the calls you make to the database - a rewrite of the SQL statement can often improve it's performance.
..and do check the health of database-server once in a while; is the database fragmented? Latest Service Pack installed? Enough memory?
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Consider a parent table which contains all of the fields that are common to every product. (Ex: ProductID and Description, etc) then create a child table which contains all of the varying attributes which make up the product. The structure would be something like (ProductID, AttributeName, AttributeValue)
Example:
100,Color,Black
100,KosherCertified,False
100,InsuranceValue,123.99
A structure like this gives you the maximum flexibility for storing varying attributes for each one of your Products.
Just an idea. ![Java | [Coffee]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/coffee.gif)
|
|
|
|
|
And AttributeType, so you can cast the attribute to the right type. And then normalize the Attribute and AttributeType into its own table.
Just my $0.02
Be excellent to each other. And... PARTY ON, DUDES!
Abraham Lincoln
|
|
|
|
|
Fairly basic question really, if each of my Tables has a Primary Key of UNIQUEIDENTIFIER, when I create a Foreign Key to point at the PK of another Table, what Data Type should it be ?
|
|
|
|
|
Also uniqueidentifier. Types of PK and FK needs to match.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Really ? I (obviously wrongly !) assumed that a UniqueIdentifier value would be generated anew when a record was created, rather than used from another field ... I have a lot to learn here  !!!
|
|
|
|
|
Hi,
As I understand it, A Unique Identifier is only created if you also set it as Primary Key.
Regards 
Bram van Kampen
|
|
|
|
|
Gary Heath wrote: I (obviously wrongly !) assumed that a UniqueIdentifier value would be generated anew when a record was created, rather than used from another field
They're not generated by default; it's just a datatype to hold GUID's. For the Primary Key to generate one, it'll have to have a default - usually coming from the NewId function.
The PK is the unique value that identifies your record. An FK only references it; we copy the unique value of the PK that we want to link, and keep that information in the FK.
Person
PersonId
Name
OtherStuff
User
UserId
PersonId
Person1: 145, Joe, Stuff
User1: 132, 145 -- referencing a user by using it's PK
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
I think it should be on same data type
|
|
|
|
|
hi,
i am using vs.net 2010 to make a web service project with linq to connect to a ms sql server.
however i notice that although i added the annotation for the classes classes that need to be persisted.
it is still very tedious to make changes to the class it self as i need to manually go into the database to make the table changes.
can anyone advice me if i should continuing using linq or is there something better out there? which is as simple to use as linq but can automatically change the ms sql table to reflect the annotation i used in my classes
|
|
|
|
|
|
neodeaths wrote: or is there something better out there
Incremental changes handled over time to a database schema with the only record being C# code annotations would be a nightmare for a non-trivial business system.
Of course in terms of that it makes me wonder how you are tracking your changes to the database now given that you are doing it in "the database".
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
