I.T. Discussion Community!
-Collapse +Expand
Paradox
Search Paradox Group:

Advanced
-Collapse +Expand Paradox To/From
To/FromCODEGuides
-Collapse +Expand Paradox Store
PRESTWOODSTORE

Prestwood eMagazine

May Edition
Subscribe now! It's Free!
Enter your email:

   ► MB LobbyCorel Paradox / ObjectPAL Coding BoardQBE & SQL Topic   Print This    All Groups  

large table/multi table Query Question

large table/multi table Query Question in QBE & SQL topic (part of our Corel Paradox / ObjectPAL Coding group).

Quick Search: table/multi   Question   large table/multi   large table/multi table   Query Question  
S0nY
Baton Rouge, LA USA

Lets say you have a table with customer information and the table has about 30 fields with 1 million records, this table is nearing 2 gigs, and eventually this table will become corrupt.

I have made a program that has made about 20 or so smaller tables with all the information from the customer information table in these tables. I am testing this because i am trying to keep these tables small so queries and scripts will run faster because they wont have to search thru a million records and to bypass the 2gig corrupt problem.

But the only problem i have come across is something like this: What if i wanted to do a query on all the people that live in Louisiana, i would have to write a long script with all the tables to find the information i needed, and i do alot of queries on the almost 2 gig table, anyone have any idea of a way i could query these tables quickly or have any ideas at all ?

 Posted 12 years ago (Thread Starter)
Comment Quote
About S0nY -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #12177, 7 replies
Thread Started 9/28/2007 10:11:35 AM
View Counter=2394
Last Reply Posted 10/5/2007 4:06:12 PM)
Location=Baton Rouge, LA USA 
Joined=12 years ago   MB Posts=53  
Moderator
Mike Prestwood
Prestwood IT
Prestwood IT office in Citrus Heights, CA

Also, right here on our website are some very good articles on the subject:

Data Normalization - The Normal Forms
By jtyzzer (Posted by mprestwood)
The database normal forms.

Power: Chapter 02, Tables and Developing
By mprestwood
Chapter 2 "Tables and Developing" from Paradox 9 Power Programming: The Official Guide by Mike Prestwood.

A Data Normalization Primer - Part 1
By CSuttle (Posted by mprestwood)
A data normalization primer first published in Prestwood eMagazine August 2002 by Cliff Suttle.

A Data Normalization Primer - Part 2
By CSuttle
This article by Cliff Suttle was first published in the Halloween 2002 issue of Prestwood eMagazine.

cSuttle
Home Page
A Data Normalization Primer - Part 3
By CSuttle (Posted by mprestwood)
In October we finally normalized our invoice data base. In this final part III, there will be little in the way of review, so if you"™re lost, don"™t be lazy, read parts I and II.

--
Mike Prestwood
Prestwood IT Solutions

 Posted 12 years ago
Comment Quote
About Mike Prestwood -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
About Mike Prestwood

Mike Prestwood is a drummer, an author, and creator of the PrestwoodBoards online community. He is the President & CEO of Prestwood IT Solutions. Prestwood IT provides Coding, Website, and Computer Tech services. Mike has authored 6 computer books and over 1,200 articles. As a drummer, he maintains play-drums.com and has authored 3 drum books. If you have a project you wish to discuss with Mike, you can send him a private message through his PrestwoodBoards home page or call him 9AM to 4PM PST at 916-726-5675 x205.

Web Presence
Facebook, Prestwood IT Facebook page -- fan page. (Visit Me)
Twitter, Follow Prestwood IT on Twitter. (Visit Me)
LinkedIn, Prestwood IT company page on LinkedIn. (Visit Me)
YouTube, Prestwood IT YouTube Channel (Visit My Channel)
Website, My drum website where I sell my drum books. (http://www.play-drums.com)

Post ID #12181 (Level 1.1)  Reply to 12177
Thread Started 9/28/2007 5:58:44 PM
View Counter=2
Location=Prestwood IT office in Citrus Heights, CA 
Joined=19 years ago   MB Posts=1410   KB Posts=1805   KB Comments=75   BLOG, Topics=4  
Moderator
Mike Prestwood
Prestwood IT
Prestwood IT office in Citrus Heights, CA
You might want to consider upgrading the database to an SQL server. You could then use views to consolidate, at least conceptually, all the various tables. In Paradox, the closest thing we have to a view is a query.

--
Mike Prestwood
Prestwood IT Solutions

 Posted 12 years ago
Comment Quote
About Mike Prestwood -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
About Mike Prestwood

Mike Prestwood is a drummer, an author, and creator of the PrestwoodBoards online community. He is the President & CEO of Prestwood IT Solutions. Prestwood IT provides Coding, Website, and Computer Tech services. Mike has authored 6 computer books and over 1,200 articles. As a drummer, he maintains play-drums.com and has authored 3 drum books. If you have a project you wish to discuss with Mike, you can send him a private message through his PrestwoodBoards home page or call him 9AM to 4PM PST at 916-726-5675 x205.

Web Presence
Facebook, Prestwood IT Facebook page -- fan page. (Visit Me)
Twitter, Follow Prestwood IT on Twitter. (Visit Me)
LinkedIn, Prestwood IT company page on LinkedIn. (Visit Me)
YouTube, Prestwood IT YouTube Channel (Visit My Channel)
Website, My drum website where I sell my drum books. (http://www.play-drums.com)

Post ID #12182 (Level 1.2)  Reply to 12177
Reply Posted 9/28/2007 6:02:59 PM
Location=Prestwood IT office in Citrus Heights, CA 
Joined=19 years ago   MB Posts=1410   KB Posts=1805   KB Comments=75   BLOG, Topics=4  
Daniel Fought
Prestwood IT
Home office in Fresno, CA.

I agree that upgrading to SQL server would probably be the best route but if that is cost prohibitive.  You should take a look at data normalization. If you dont have 1 million customers then data normalization should be seriously considered. 

If you determine that these two options are not the answer to your needs then you will need to use some code to get the info you need.  There really isnt any faster way to get the data other than to query each table and then compile the results into a single result table.  You could use a fairly simple function to loop through an array or table containing the table names.  Then use a query based on one of the table and substitute the table name from the array or table.  Finally add the results of your query to a results table.  This shouldnt take a lot of code.

Try the normalization route first.  This should be done anyway even if  you upgrade to SQL server.

Dan Fought
Senior Programmer Analyst
Prestwood IT Solutions
http://www.prestwood.com

 Posted 12 years ago
Comment Quote
About Daniel Fought -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
About Daniel Fought
Danial Fought is a senior programmer analyst with Prestwood IT where he develops custom Windows software and custom websites. When Dan is not coding for clients, he participates in this online community. Coding specialties include Paradox/ObjectPAL, MS Access, Visual Basic, and VS.Net/VB.Net.

Post ID #12183 (Level 1.3)  Reply to 12177
Reply Posted 9/28/2007 8:05:12 PM
Location=Home office in Fresno, CA. 
Joined=17 years ago   MB Posts=401   KB Posts=12   KB Comments=4  
Most Recent Post
S0nY
Baton Rouge, LA USA
Id like to thank everyone for their posts and ideas, i have been thinking on what i should do based on the ideas and thoughts you guys have given me, SQL would be great but i am not sure my boss would want to pay all the money for it haha, we do have over a million customers, and i have been trying a few different methods using the data normalization methods i read about on the site ( Thanks Mike ). When i finally decide on what is best or i think is best in my situation i will write back and let everyone know what i did, Thanks again guys!
 Posted 12 years ago (Thread Starter)
Comment Quote
About S0nY -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #12214 (Level 1.4)  Reply to 12177
Reply Posted 10/5/2007 4:06:12 PM
Location=Baton Rouge, LA USA 
Joined=12 years ago   MB Posts=53  
chomp
 (Inactive)
-- USA

If you 'normalize' your data/tables, you probably won't have this problem.  Takes some time setting up and getting used to, perhaps, but it will give you longterm flexibility and storage capacity.  AND you'll understand it should you decide to move to a different platform.

http://rdaworldwide.com/pdox/datanorm.pdf is a good place to start.

There are several good articles on that site.  Check out

http://www.rdaworldwide.com/pdoxres.htm

And scroll to the articles section.  Well worth the reads. 

 Posted 12 years ago
Comment Quote
About chomp -Collapse +Expand
Visit Profile
Inactive member.
Member does not subscribe to this thread.
Old Account!
If this is your account, sign in to activate web presence data (sign in quarterly to keep active). Alternatively, you can subscribe to our monthly eMag with a valid email address.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #12178 (Level 1.5)  Reply to 12177
Reply Posted 9/28/2007 11:26:26 AM
Location=-- USA 
Joined=12 years ago   MB Posts=45  
chomp
 (Inactive)
-- USA

If you 'normalize' your data/tables, you probably won't have this problem.  Takes some time setting up and getting used to, perhaps, but it will give you longterm flexibility and storage capacity.  AND you'll understand it should you decide to move to a different platform.

http://www.rdaworldwide.com/pdox/datanorm.pdf is a good place to start.

There are several good articles on that site.  Check out

http://www.rdaworldwide.com/pdoxres.htm

And scroll to the articles section (Paradox related documents).  Well worth the reads.

(Sorry for the double post.  Edits don't 'stick'.) 

 Posted 12 years ago
Comment Quote
About chomp -Collapse +Expand
Visit Profile
Inactive member.
Member does not subscribe to this thread.
Old Account!
If this is your account, sign in to activate web presence data (sign in quarterly to keep active). Alternatively, you can subscribe to our monthly eMag with a valid email address.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #12179 (Level 1.6)  Reply to 12177
Reply Posted 9/28/2007 11:33:10 AM
Location=-- USA 
Joined=12 years ago   MB Posts=45  
chomp
 (Inactive)
-- USA


There really isnt any faster way to get the data other than to query each table and then compile the results into a single result table.


Even without knowing the structure of the tables, nor the end result desired, I still have to disagree.  Particularly if the tables aren't local.  At least quite often. 

But a flat statement that quer[y,ies] are the fastest isn't appropriate I don't think.

One or more setRange, or setRange/setGenFilter combinations may be faster, depending on indexes and specific requirements.

Even doing multiple setRange/setGenfilter combinations on several tables is quite often faster than one or more queries that accomplish the same thing.  Again, particularly if the table(s) aren't local.

A bunch of details would help. 

 Posted 12 years ago
Comment Quote
About chomp -Collapse +Expand
Visit Profile
Inactive member.
Member does not subscribe to this thread.
Old Account!
If this is your account, sign in to activate web presence data (sign in quarterly to keep active). Alternatively, you can subscribe to our monthly eMag with a valid email address.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #12184 (Level 1.7)  Reply to 12177
Reply Posted 9/28/2007 9:25:14 PM
Location=-- USA 
Joined=12 years ago   MB Posts=45  

Revive Thread!

Add a comment to revive this old thread and make this archived thread more useful.

Write a Comment...
Full Editor
...
Sign in...

If you are a member, Sign In. Or, you can Create a Free account now.


Anonymous Post (text-only, no HTML):

Enter your name and security key.

Your Name:
Security key = P1235A1
Enter key:
Icon: A Post    Thread    Idea    Important!    Cool    Sad    No    Yes    Includes a Link...   
Thread #12177 Counter
2394
Since 4/2/2008
Follow PrestwoodBoards on: 


©1995-2019 PrestwoodBoards  [Security & Privacy]
Professional IT Services: Coding | Websites | Computer Tech