Speed up a SQL query?

[GUJUm0deL]

I am using this query to get back some reporting numbers. According to the SQL Query Analyser it takes about 5-6 minutes for this query to finish, is there a way I can cut back on the time? Even if it's a minute that's better.

NOTE: the only thing diff. b/w these two queries is the first query refrences on table, and the other calls a different one.

select o.offername, o.offer_id, count(distinct f.user_id) as exported, f.offer_id,q.offer_id, q.question_id, q.questionweight
from offers o join [COLOR=red]formanswers[/color] f on f.offer_id = o.offer_id join questions q on q.offer_id = f.offer_id
where exporteddate >= '2/1/2005 00:00:00' and exporteddate <= '2/28/2005 23:59:59' and q.questionweight > 0 and f.exported = 1
group by o.offername, o.offer_id, f.offer_id,q.question_id, q.offer_id, q.questionweight
UNION
select o.offername, o.offer_id, count(distinct f.user_id) as exported, f.offer_id,q.offer_id, q.question_id, q.questionweight
from offers o join [COLOR=red]answers[/color] f on f.offer_id = o.offer_id join questions q on q.offer_id = f.offer_id
where exporteddate >= '2/1/2005 00:00:00' and exporteddate <= '2/28/2005 23:59:59' and q.questionweight > 0 and f.exported = 1
group by o.offername, o.offer_id, f.offer_id, q.question_id, q.offer_id, q.questionweight
order by o.offername

I am using SQL Server 2000 on Windows 2000. And my back-end language is Coldfusion MX.

[sub]
____________________________________
Just Imagine.

http://www.imajinarts.com

[sub]

 

[pkailas]

have you tried the Index Tuning Wizard?

_______
I love small animals, especially with a good brown gravy....

 

[vongrunt]

Column exporteddate, where is it (in which table)?
How long take two separated queries (without UNION) to finish?
How many rows have all tables involved, and how many rows returns this query?

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[GUJUm0deL]

THe column 'ExportedDate' is in both tables. Each query takes abour 2.5 minutes alone, and together takes like 5-6 minutes. The number of rows can vary depending on time od day the script is run, but there are a lot.

My goal is when the page is run, I want it to load faster. Not sure if this is feasable or not.

[sub]
____________________________________
Just Imagine.

http://www.imajinarts.com

[sub]

 

[Pattycake245]

vongrunt means :

where exporteddate >= '2/1/2005 00:00:00' and exporteddate <= '2/28/2005 23:59:59'

what table in each query does this associate with since there is no table alias in front of it. Does this mean when you say it is in both tables that it is in formanswers and answers?

If you are joining all three tables together by a common "offer_id", is it necessary to select the offer id from each table and then group by it? Won't the results be the same for all three offer ids for each record returned? Sometimes eliminating extra columns in the select may help. Sometimes changing the joining tables around may help. IE if offers has 4 million rows and formanswers has 20 you may want to have the formanswers table first as it won't have to scan through all the rows in the offers table to find the 20 that match records in the formanswers table. Are there indexes on these tables? You may need to add some indexes on these. Unless these tables are really huge this should return a result set in seconds.

Just a few things you could look into.

Tim

 

[kishy]

Can you also send the table structure and some records, say around 1000 of them, so that we can analyze it our end and try and tune it to run it faster....

--Kishore

 

[GUJUm0deL]

select o.offername, o.offer_id, count(distinct f.user_id) as exported, f.offer_id,q.offer_id, q.question_id, q.questionweight
from offers o join [COLOR=red]formanswers f[/color] on f.offer_id = o.offer_id join questions q on q.offer_id = f.offer_id
where exporteddate >= '2/1/2005 00:00:00' and exporteddate <= '2/28/2005 23:59:59' and q.questionweight > 0 and f.exported = 1
group by o.offername, o.offer_id, f.offer_id,q.question_id, q.offer_id, q.questionweight

The comlumn 'exporteddate' is in the Formanswers table in the above query. This query takes all values in the 'Formanswers' table that has the same offer_id (from offers table) and question_id (from questions table) where the exporteddats is b/w some range.

The next query does the same thing but with the 'Answers' table.

The main set of values are pulled from the 'Formanswers' table. The other tables are only used to make sure the values being pulled are related to a specific offer and question (since one offer can have many question_id's)

As far as the table structure goes, I can post that when I get to work on Monday.

The 'Formanswers' and 'Answers' table are realllllly huge. They can go into the millions of records phase.

[sub]
____________________________________
Just Imagine.

http://www.imajinarts.com

[sub]

 

[r937]

GUJUmOdeL, which columns involved in the queries (SELECT list, ON clauses, WHERE clause) have indexes? this will be where your improvements will come from

a minor point: if the query ensures that on each row you have

f.offer_id = o.offer_id
q.offer_id = f.offer_id

then there's little point in including all three of these columns in both the SELECT and GROUP BY


rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[vongrunt]

I agree overall. There are 3 tables involved:
- offers (offer_id, offername), no filter conditions
- questions (question_id, questionweight), questionweight > 0
- formanswers ( count(distinct user_id) ), exporteddate BETWEEN, exported=1

All joins are inner joins (on offer_id) and therefore commutative. This makes 3 logically separated queries. Probable weak spots are (in order of relevance IMO):
- most selective filter condition (no index on formanswers.exporteddate)
- join on formanswers (no index on offer_id)
- join on questions (no index on offer_id)

Note there are no guarantees server will use indexes, but exporteddate looks like promising index candidate.

Btw. if you are willing to become Lab Rat (tm) for five minutes , please run the following query:

select o.offername, o.offer_id,
q.question_id, q.questionweight
from offers o 
join questions q on o.offer_id = q.offer_id
join
(	select offer_id, count(distinct user_id) as exported
	from formanswers
	where exporteddate >= '2/1/2005 00:00:00' and exporteddate <= '2/28/2005 23:59:59' and exported = 1
	group by offer_id
) f on q.offer_id = f.offer_id
where q.questionweight > 0 
order by o.offer_id, q.question_id

Is it any faster than original query?

Another minor point: use exporteddate < '3/1/2005'. This is the most correct way.

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[donutman]

Btw. if you are willing to become Lab Rat (tm) for five minutes , please run the following query

So is that how you feel when I ask you to do that? I was only asking you to retrieve performance info like a lab not a rat. [arrf arrf]
-Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[GUJUm0deL]

Sorry, today was one of those days where they wanted a brand new application up and running in the span of 8 hours.

vongrunt I did not get a chance to run your "test" query. I will run it tomorrow. Since its only a select statement, i'm not too worried.

I will have to ask the dB admin which tables are indexed and how often they get indexed.

I was under the impression that if you use a count (distinct ...) syntax then all your columns in the SELECT had to be mentioned in the GROUP BY. Is that not the case? Cause when I leave out a column name in the GROUP BY clause that was mentioned in the SELECT, I get an error saying that 'some column name(s) was not mentioned in the GROUP BY clause but was mentioned in the SELECT clause' (ok ok ok, so that may not be the exact error message, but the point is the same)

Thanks.

[sub]
____________________________________
Just Imagine.

http://www.imajinarts.com

[sub]

 

[r937]

yes, that's not the correct error message, but yes, you understand it perfectly



imagine how hard it would be to learn how grouping works if the database didn't give you an error message when you did that

this is what people learning mysql have to deal with



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[vongrunt]

In other words: ANSI/T-SQL GROUP BY is often strict and redundant, while mySQL GROUP BY is sometimes too loose.

That said... did I mention I am certified derived table addict?

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]