Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Simplifying my select statement

Posted on 2008-10-24
9
Medium Priority
?
300 Views
Last Modified: 2011-10-19
I have a query defined which needs to calculate averages for differtent parts of a questionnaire. There are now 1000 records in for testing, but the real db contains over 40000 already.
My sql statement now takes like minutes to load for only those 1000 records.

Does anyone have any tips on simplifying the sql statement.

QuestionnaireOffline contains the results from the query with also CodePostal, the table CodePostal contains the province. I need to get overview of the responses per province.
SELECT DISTINCT (SELECT Count(*) FROM QuestionnaireOffline WHERE CodePostal IN (SELECT CP FROM CodesPostal WHERE Province='BRABANT WALLON')) AS nTotal, (SELECT count(*) FROM QuestionnaireOffline WHERE mode = "Papier" AND CodePostal IN (SELECT CP FROM CodesPostal WHERE Province='BRABANT WALLON')) AS nPapier, (SELECT count(*) FROM QuestionnaireOffline WHERE mode = "Internet" AND CodePostal IN (SELECT CP FROM CodesPostal WHERE Province='BRABANT WALLON')) AS nInternet, ...
 
FROM QuestionnaireOffline;

Open in new window

0
Comment
Question by:pmelkebeke
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22794871
SELECT Count(q.CodePostal) AS nTotal, Sum(IIf(q.mode = "Papier", 1, 0) AS nPapier,
    Sum(IIf(q.mode = "Internet", 1, 0) AS nInternet, ...
FROM QuestionnaireOffline q INNER JOIN
    CodesPostal c ON q.CodePostal = c.CP
WHERE c.Province = "BRABANT WALLON"
0
 

Author Comment

by:pmelkebeke
ID: 22795173
I'm missing some kind of a DISTINCT in this picture. The total is way too high.
I have 5 different provinces. If i ran this query on my data then the total amount is way higher than the no of records I have.

I must say, If i sum all the results I get the same number of records as I have when I query (without DISTINCT) in a wrongly way like this:
SELECT * FROM QUESTIONNAIREOFFLINE, Codespostal WHERE Codepostal = CP

Open in new window

0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22795523
pmelkebeke,

It would be useful to see some sample data.  EE now allows you to directly upload
files to your question.

Please be advised that once you upload a file, it can be publicly accessed, and that it
may not be possible to fully and permanently delete it.  Therefore, be very careful about
posting proprietary, confidential, or other sensitive information.  If necessary, use "fake"
and/or obfuscated data in your sample.

Regards,

Patrick
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 22795647
This query
SELECT * FROM QUESTIONNAIREOFFLINE, Codespostal WHERE Codepostal = CP
may not work as you expect.  You will get a 'CARTESIAN JOIN'  between the two tables. It should read:
SELECT QUESTIONNAIREOFFLINE.* FROM QUESTIONNAIREOFFLINE JOIN Codespostal on QUESTIONNAIREOFFLINE.CodePostal = Codespostal.CP  
0
 

Author Comment

by:pmelkebeke
ID: 22795682
Okay, here it is.
EE-demodata.mdb
0
 

Author Comment

by:pmelkebeke
ID: 22795858
Arthur,

I know this. That's why I posted it. The result I get with the first proposal gives the same results as this cartesian join result...
0
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 2000 total points
ID: 22798607
pmelkebeke,

The problem is that CodesPostal has >1 record for each post code

Regards,

Patrick
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 22798622
This seems to be working against the sample data:

SELECT Count(*) AS nTotal, Sum(IIf(q.Mode = "Papier", 1, 0)) AS nPapier, Sum(IIf(q.Mode = "Internet", 1, 0)) AS nInternet
FROM QuestionnaireOffline q INNER JOIN
    (SELECT c.CP, Max(c.Province) AS Prov FROM CodesPostal c GROUP BY c.CP) cc ON q.CodePostal = cc.CP
WHERE cc.Prov = "BRABANT WALLON";
0
 

Author Closing Comment

by:pmelkebeke
ID: 31509588
Thank you very much for this solution. I saw indeed that there were more results for some postal codes and I fixed it by using a query which filters out the doubles and using that one in the solution query. Thank you very much for the solution.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question