473,326 Members | 2,588 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

generic sql question

My question is it possible to speed up a query doing preselects? What I'm working on could end up being a very large dataset. I hope to have 100-1000 queries per second (0r more?), and if very large tables are joined with very large tables, I imagine that the memory would be get very full, overfull?

So in the schema below the following queries, usrs own articles, articles are of article types, issues have dates and names, and issues_published has an issue id and sets of articles, and article can be in many issues.

So if I wanted to find articles of a certain article type within a certain date range for the article and had actually been published, I believe that this query could find it, joining three tables and then doing the qualifications for date and type: (assume values in {} are escaped and proper syntax)

-----------------------------------------------------------------
SELECT article_id
FROM issues_published, issues, articles

WHERE issues_published.article_id = articles.article_id
AND
issues_published.issue_id = issues.issue_id
AND
articles.article_type = {article_type_id desired}
AND
article.article_date < {highest date}
AND
issues.article_date > {lowest date};

But would the following reduce the size of the join in memory?

SELECT article_id
FROM (select *
from articles
where article_date < {highest date}
AND
article_date > {lowest date} ) as articles_in_range, issues, issues_published

WHERE issues_published.article_id = articles_in_range.article_id
AND
issues_published.issue_id = issues.issue_id
AND
articles_in_range.article_type = {article type desired}

-------------------------------------------------------------------------
CREATE TABLE usr (
usr_id SERIAL NOT NULL,
PRIMARY KEY (usr_id)
);

CREATE TABLE article_types (
ariticle_type_id SERIAL NOT NULL,
article_type VARCHAR(40) NOT NULL,
PRIMARY KEY (ariticle_type_id)
);

CREATE TABLE articles (
article_id SERIAL NOT NULL,
ariticle_type_id INT4 NOT NULL,
author INT4 NOT NULL,
body TEXT NOT NULL,
date_written DATE NOT NULL,
PRIMARY KEY (article_id, ariticle_type_id, author)
);

CREATE TABLE issues (
issue_id SERIAL NOT NULL,
issue_title VARCHAR(40) NOT NULL,
issue_date DATE NOT NULL,
PRIMARY KEY (issue_id)
);

CREATE TABLE issues_published (
issue_id INT4 NOT NULL,
article_id INT4 NOT NULL,
PRIMARY KEY (issue_id, author, ariticle_type_id, article_id)
);

/*================================================= =========================*/
/* Foreign Keys */
/*================================================= =========================*/

ALTER TABLE articles
ADD FOREIGN KEY (author) REFERENCES usr (usr_id);

ALTER TABLE articles
ADD FOREIGN KEY (ariticle_type_id) REFERENCES article_types (ariticle_type_id);

ALTER TABLE issue_articles
ADD FOREIGN KEY (issue_id) REFERENCES issues (issue_id);

ALTER TABLE issue_articles
ADD FOREIGN KEY (author,ariticle_type_id,article_id) REFERENCES articles (author, ariticle_type_id, article_id);
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #1
1 1588
i do no think writing the query in the second form differs from the first
one. In both cases, only the relevent articles (in range and of desired
type) will come out of the scan operator that scans the articles.
--h

"Dennis Gearon" <ge*****@fireserve.net> wrote in message
news:41**************@fireserve.net...
My question is it possible to speed up a query doing preselects? What I'm working on could end up being a very large dataset. I hope to have 100-1000
queries per second (0r more?), and if very large tables are joined with very
large tables, I imagine that the memory would be get very full, overfull?
So in the schema below the following queries, usrs own articles, articles are of article types, issues have dates and names, and issues_published has
an issue id and sets of articles, and article can be in many issues.
So if I wanted to find articles of a certain article type within a certain date range for the article and had actually been published, I believe that
this query could find it, joining three tables and then doing the
qualifications for date and type: (assume values in {} are escaped and
proper syntax)
-----------------------------------------------------------------
SELECT article_id
FROM issues_published, issues, articles

WHERE issues_published.article_id = articles.article_id
AND
issues_published.issue_id = issues.issue_id
AND
articles.article_type = {article_type_id desired}
AND
article.article_date < {highest date}
AND
issues.article_date > {lowest date};

But would the following reduce the size of the join in memory?

SELECT article_id
FROM (select *
from articles
where article_date < {highest date}
AND
article_date > {lowest date} ) as articles_in_range, issues, issues_published
WHERE issues_published.article_id = articles_in_range.article_id
AND
issues_published.issue_id = issues.issue_id
AND
articles_in_range.article_type = {article type desired}

-------------------------------------------------------------------------
CREATE TABLE usr (
usr_id SERIAL NOT NULL,
PRIMARY KEY (usr_id)
);

CREATE TABLE article_types (
ariticle_type_id SERIAL NOT NULL,
article_type VARCHAR(40) NOT NULL,
PRIMARY KEY (ariticle_type_id)
);

CREATE TABLE articles (
article_id SERIAL NOT NULL,
ariticle_type_id INT4 NOT NULL,
author INT4 NOT NULL,
body TEXT NOT NULL,
date_written DATE NOT NULL,
PRIMARY KEY (article_id, ariticle_type_id, author)
);

CREATE TABLE issues (
issue_id SERIAL NOT NULL,
issue_title VARCHAR(40) NOT NULL,
issue_date DATE NOT NULL,
PRIMARY KEY (issue_id)
);

CREATE TABLE issues_published (
issue_id INT4 NOT NULL,
article_id INT4 NOT NULL,
PRIMARY KEY (issue_id, author, ariticle_type_id, article_id)
);

/*================================================= =========================
*/ /* Foreign Keys */ /*================================================= =========================
*/
ALTER TABLE articles
ADD FOREIGN KEY (author) REFERENCES usr (usr_id);

ALTER TABLE articles
ADD FOREIGN KEY (ariticle_type_id) REFERENCES article_types (ariticle_type_id);
ALTER TABLE issue_articles
ADD FOREIGN KEY (issue_id) REFERENCES issues (issue_id);

ALTER TABLE issue_articles
ADD FOREIGN KEY (author,ariticle_type_id,article_id) REFERENCES articles (author, ariticle_type_id, article_id);

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Richard Brown | last post by:
Ok, I've been looking through the .NET SDK docs and stuff. I'm wondering if you can provide a control extender that does generic validation or functionality just by dropping it on the form. For...
25
by: Lars | last post by:
Hi, I have a base class holding a generic list that needs to be accessed by both the base class and its subclasses. What is the best solution to this? I am fairly new to generics, but I am...
4
by: Charles Churchill | last post by:
I apologize if this question has been asked before, but after about half an hour of searching I haven't been able to find an answer online. My code is beloiw, with comments pertaining to my...
1
by: interX | last post by:
Hi I'm new in VC++ and have a question to generics. I have a generic class, which contains an array of the generic type. This array I can pin and then I would like to get an unmanaged pointer to...
1
by: Suds | last post by:
Hi, I'm having an issue with invoking a Generic method that takes Generic Arguments. My method signature is public void GenericMethodWithGenericArguments<E, V>(List<EtheFirstList,...
7
by: Dave | last post by:
I've got these declarations: public delegate void FormDisplayResultsDelegate<Type>(Type displayResultsValue); public FormDisplayResultsDelegate<stringdisplayMsgDelegate; instantiation:...
9
by: tadmill | last post by:
Is it possible to pass a generic parameter of the same class to to its constructor, where the "T" type passed in the constructor is different than the "T" type of the instanced class? ie, ...
4
by: Tony | last post by:
Hello! Below I have a complete working program.with some simple classes one of these is a generic class. The question is about this method GetCows() {...} which is a member in the generic...
15
by: Lloyd Dupont | last post by:
Don't mistake generic type for what you would like them to be!! IFoo<Ahas nothing in common with IFoo<B>! They are completely different type create dynamically at runtime. What you ask is a...
11
by: Scott Stark | last post by:
Hello, The code below represents a singly-linked list that accepts any type of object. You can see I'm represting the Data variable a System.Object. How would I update this code to use...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.