Improving query speed (1 Viewer)

[choaspes]

Dear All

My database employs an extremely long and complicated union query, which is only going to get longer and more complicated. It struck me today that all of the 30-ish select statements in the query share one common WHERE clause. Would that union query run more quickly if I based it on another query whose only job was to run that single WHERE statement (thus significantly reducing the dataset before running the union query) or would the whole process still run at about the same speed?

Any other tips on improving the speed of queries?

Thanks in advance!

 

[jdraw]

Can you tell us about
a) your business in plain English
b) WHAT you database is about
c) why so many UNION queries

 

[choaspes]

a) I would rather not
b) As above
c) It's one union query, just a very long one, because I need the user of the database to be able to ask their choice of up to 30 questions of the data at the same time, and display the results on the same report.

Edit - I do not intend to be rude with my answers to a) and b) above, I just have an unfashionable reticence about broadcasting my personal business on the internet. I quite understand if you no longer wish to help.

 

[pr2-eugin]

a) I would rather not
b) As above
c) It's one union query, just a very long one, because I need the user of the database to be able to ask their choice of up to 30 questions of the data at the same time, and display the results on the same report.

(a) We rather would not help !
Explanation : People here are trying to help, if you do not wish to give us some basic info that is required for us to help you, we will not be able to help you at all. Even if we try, we will be going round and round in circles. Waste of time, when we could actually help people who genuinely needs help and wants to learn.

(b) You in your wise words explained :

My database employs an extremely long and complicated union query,

This only suggests your problem is not on the surface level, but deep down - Your Design "is" wrong, you have "not normalized" your tables, you have inefficient SQL code.

(c) One Union Query, what you see.. "Qry_UnionOf30Queries", what others see..

SELECT something FROM somewhere
UNION
SELECT something FROM somewhere
UNION
SELECT something FROM somewhere1
UNION
SELECT something FROM somewhere2
UNION
SELECT something FROM somewhere3
UNION
SELECT something FROM somewhere4
UNION
SELECT something FROM somewhere5
UNION
SELECT something FROM somewhere6
UNION
SELECT something FROM somewhere7
:
:
UNION
SELECT something FROM somewhere27
UNION
SELECT something FROM somewhere28
UNION
SELECT something FROM somewhere29
UNION
SELECT something FROM somewhere30;

My advice,

1. Learn How to talk to a programmer.
2. JD, is very good at what he is doing, if you do not wish to take his advice or provide something he (or any user on the forum, for that matter) request, say it politely. Don't go "I'd rather not"
3. We take our time to help you, so respect that.

I do not wish to be harsh or rude, but you really need to know what you are saying on a Public Forum.

 

[pr2-eugin]

Edit - I do not intend to be rude with my answers to a) and b) above, I just have an unfashionable reticence about broadcasting my personal business on the internet. I quite understand if you no longer wish to help.

Just saw your EDIT note : When we request information,

What we look for :

• Fundamental business schema (Retail, Library, Employees etc.)
• Table level information on Indexes, Foreign and Primary keys or Relationship Schema.
• Sample data like Sam, Paul, Dan/ 02415842, 785442, 12588742

What we do not want:

• All of your data, unaltered.
• Names, business contacts, Phone numbers / email address.
• Who do you work for, what is your job role.

Sounds silly does it not? Really, that's what we are after as a DB Developer or Admin we should be worried/concerned about your Data Structure not your Data. Data comes second. Without a proper skeleton your body will not be able to do any functions normally. The same applies for your Databases, get the structure correct.

I hope you will find the help from someone, who can work with no information ! Good Luck !