Welcome to WebmasterWorld Guest from 34.228.115.216
Forum Moderators: open
SELECT
members.Member_ID, companies.CompanyName, vendors.VendorName, sales.SalesAmount
FROM members
Inner Join companies ON members.Company_ID=companies.Company_ID
Inner Join sales ON members.Member_ID=sales.Member_ID
Inner Join vendors ON sales.Vendor_ID=vendors.Vendor_ID
WHERE (SELECT MAX(SalesDate) FROM sales WHERE Member_ID=members.Member_ID)='".$date."'
GROUP BY members.Member_ID;
"$date" is my variable for the current day that the loop is on
any have any ideas on what I should look up or research to speed this process up or to speed the database up?
///////////////////////////////////////////
I tested the last Select statement, it took 91 seconds to run through Navicat. I then tested it on a duplicate of our database that is on my personal server (took 13 seconds). The difference you ask? The server my company uses sucks and I have had plenty of experience with them sucking but I can't change that. However, after cutting the statement down, I got it to run on my companies server in 7.5 seconds: the new select statement:
SELECT
members.Member_ID
FROM members
WHERE (SELECT MAX(SalesDate) FROM sales WHERE Member_ID=members.Member_ID)='2009-02-03'
GROUP BY members.Member_ID;
Throwing more memory at the server may also help (what is it now?). These days, memory is so cheap that your tightwad company may even go for it. :)
The problem is that report has to run once for every single day.
If Postgres, lookup vacuum
If Oracle or Sql Server lookup statistics
If MySql look up Optimize table
What others have said about indexes. Also if you have big queries doing joins, see if you can copy that data to a denormalized table and query it from there. Joins can get expensive.
Can you tell if you are i/o bound, if so that may point to a memory issue, as in not enough. Do you have any stats on if it's finding these things in working memory, or if it's hitting disk. Disk is dog slow compared to memory.
Can you turn your queries into a stored procedure, or plsql or the like?
Its over 10 years since my DBA days when I was debugging issues like this several times a week but that sub-select in the query makes me twitch. Step 1 is always to use EXPLAIN or whatever local equivalent is available. That will track down the sort or table scan that is causing the problem.
An additional index may well be the answer. The other solution that we found was to split a complex query and insert the results of the first step into a temporary table to be read in step 2.
I've got every column that is searched against (anything we reference to try to match it against another value) indexed already.
And is the database system using even one of those indexes? You may need an index over more than one column. What does the planner say it's doing?
An additional index may well be the answer.
But don't just go throwing in another index without looking at the query plan you are trying to fix.
Adding multiple indices will not help queries with complex where criteria. You need to create joined indices (i.. index like col1, col10, col5). the rdbms will use the colums in the correct order (so a select for only col1 will use the index and a select for only for col15 will not)
Its one of the biggest misunderstanding in database usage that an RDBMS will use more then one index for a complex query.
Just a kind FYI
[edited by: Rufal at 10:57 pm (utc) on April 30, 2009]