• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 335
  • Last Modified:

500Pts WORTH SILLY QUESTIONS ABOUT POSTGRESQL

Hello Experts,
BACKGROUND------------------------------------------------------------------------------------------->>>
Currently My Application keeping a binary database(file base data) and Using VB6
I have about 50000 products in my database. There are lot of competitors in the field Im
working on and I want to secure the database; especially from competitors.
In My main table there about 64 columns and about 50,000 rows ( can be grown up to 200,000).
At this stage it take about 1.141 seconds to 1.172 Seconds to Select a row. (Im prefer to do it atleast
0.2 seconds- as it happens in current file base data with hash table) So my major issues are
1 Speed , 2 Security
My Intention is First change current file base data base to proper database  by writing aditional layerr to VB6
current Application abd then Migrate to VB.NET from VB6.
My Database Option, As most preffered on top
# Still looking/testing for perfect solution(Open to new Ideas)
1 USE SQL Express
2 USE SQLite
3 USE SQL Express combination with SQLite
4 USE SQL Express combination with current file base binary data
5 USe SQLite with Current file base binary data
-----------------------------------------------------------------------------------------------------------------------

QUESTION (Im totall new to POSTGRESQL


1 HOW HARD IS MIGRATING POSTGRESQLFROM SQL SERVER/SQLite/MYSQL ?
2 SPEED AND SECURITY WISE WHAT ARE THE ADVANTAGES IN POSTGRESQL?
3 HOW FAST WOULD BE TO SELECT ONE ROW FROM 200,000 ROWS
4 HOW DO WE ACCESS POSTGRESQL FROM VB6 AND VB.NET code examples
5 WHY SHOULD USE POSTGRESQL? SHOULD I?
6 HOW MUCH WILL IT COST TO BUY A ORACLE PRODUCT FOR MY REQUIREMENTS?
-----------------------------------------------------------------------------------------------------------------------
I have posted few 500Pts worth question on this Scenario.
Expecting a simple explaination, rather than links..:), but links welcome too

Thank you very much for helping me Udana
0
udanabanana
Asked:
udanabanana
  • 3
  • 3
  • 3
  • +1
3 Solutions
 
earth man2Commented:
1)  You can dump the data from the databases via CSV files and import those using PostgreSQL COPY command.
      If there are stored procedures then they will need to be translated into say plpgsql.
2)  Speed - it depends if your hashing algorithm is faster than the standard indexes or hash index of PostgreSQL.  But then if you don't need to spend the money on a software licence you can buy more memory/ faster disks/processors.  With the new TABLESPACE concept in PostgreSQL you can hive off your index to another physical disk, so you do not make your disks chatter.  
  Security - you can use the module contrib/pgcrypto to encrypt your commercially sensitive data.  You can use various authentication methods in a client-server framework, try kerberos.
3) That's a bit like asking how fast a Rolls-Royce goes !  The salesmen usually said "fast enough sir".  It depends upon your hardware.
4) ODBC ADO .Net are all possible means of accessing the database.
5) PostgreSQL has a common ancestry as Ingres.  It had good server side stored procedural languages long before SQL Server and MySQL.
     The best reason of course is that it is "free" and "open"
6)  That depends what country you are operating in and what hardware you are operating on.  It can also be free http://www.oracle.com/technology/products/database/xe/index.html  if you can fit your database into  4GB  - which you probably can.
0
 
earth man2Commented:
3)  HOW FAST  -  It's possible that a hash algorithm coded in C will be faster than using a relational database.  The main reason for using a relational database is to make it easier to devlop applications that have complex queries and to administer the data eg simultaneous multi-user access.
0
 
gheistCommented:
1) cc
2) CAL price. If you grant that performance-wise to extra ram/quick disk, postgresql is much faster at same total cost of ownership.
3) With appropriate index it takes small parts of second ( about 10 random 8k reads at worst) - for parallelism use SATA NCQ or SCSI disks.
4) cc
5) Price.
6) Oracle ships binary drivers for select platforms, no access for others
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
fridomCommented:
Well you can see Postgres as the opposite side of you SQLITE3 question Postgres is a fully fledged database system, with all kinds of extra facilities for Data integrity diverse Datatypes etc etc.

Access to it via VB Basic should be easier.

Regards
Friedrich

0
 
earth man2Commented:
Perhaps you are approaching this from the wrong direction.  It is common for many software developers to not base their product on one database but allow the client to choose.  Each business will have their own preferred systems and expertise in them.  It is more important to choose the interface software that gives you best flexibility/performance ie .Net or ODBC
0
 
udanabananaAuthor Commented:
Well I have issues with SQL Express on testing envioronment. As you can see in my question , its bit slow for my requirement.
Would you suggest that Oracle or Postgres  do better?
 On the Same time if I encrypt, will it make even slower?
Udana
0
 
gheistCommented:
WTF "SQL Express" ???
SQL Express and Oracle forbid benchmarking, so all I can tell is that PostgreSQL works well.
0
 
udanabananaAuthor Commented:
Speed and Security is a major issue for me.  This Final product traget to upgrade our current software usedby Supermarkets. So I need to get rows pretty fast as they scaned. I tested and configure SQL Express to selct row from 120,000 in less than 0.02 Seconds Which is Excellent.
 But Im worrying about Security.
At this stage our software running on 100+ stores (on binary database, which is very fast but we got humangas amount of unrecoverble corruption) our Software package up with
1) Software 2) Hardware 3) Maintainace
We basically go for 5 yr contract with our customers. So We Initially spend heaps on Hardware and Software, We basically make profit after a year or so from Maintainance Contract. We have to do this way due to high competition. But some cheaky clients break the contract after a year or so and sign up for new company or so. So we need to prevent accessing database without our permision. Basically database should access through our program only.
 I can do encryption but will it make slower? How Can I secure the Database?

gheist>>>
Im happy if database running on my requirements, whether its SQLExpress or SQLite or so... Also Im prefered to use uncommon database, due to this security issue. do you think POSTGRE will meet my requirements?
0
 
fridomCommented:
Would be nice if you could post this on one go in the Database area. It's not very pleasan to follow there a threa and there a thread with basically the same question.

Both databases you are asking for PostgreSQL and SQLite will be fast enough, both can not prevent someont to access the database via some command line tool and if the have "root" access to the Database they can change whatever permissions there are on the tables.

But even if they can access your database what does it buy them? The logic is still placed in your program. So whatever they do it will get quite expensive to redesign it. So what is your problem with security really?

Regards
Friedrich
0
 
udanabananaAuthor Commented:
Friedrich,
It might expensive to redisgn but its not that hard to covert our database to another; if they having a similar software.
So Really, I dont want some one else transfrom my database to there own database.
Udana
0
 
fridomCommented:
I disagree, converting from one database to another can be quite challenging to "nearly" impossible. If the will change you database to another they still have to implement the whole program logic, I can not think of anyone going that way to play fould on you.

YMMV
Friedrich
0
 
gheistCommented:
Depends on logic you put inside database - stored functions, custom types etc. If you put none, conversion is painless.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 3
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now