Hey Everyone... I am trying to improve some rather terrible web code. One of the problems I am having with this site is a VERY slow report. Sometimes the user will wait several minutes for the following query to return (1) row of data. I have looked at the data base query plan and the database is working very hard on completing the join. Sorry for the huge post but I wanted to be through. Any help optimizing this query would be super appreciated.

Thanks

-Nick

select    distinct p.*, e.fldSurname + ', ' + e.fldForename as fldEmployeeName, r.fldDescription as fldRole, s.fldName, 
                              s.fldAccountNo, s.fldCity, s.fldState, g.fldDescription,
                              (if p.fldType = 1 then right('0000' + convert(char(4), so.fldSignYear), 4) || '-' || right('00' + convert(char(2), so.fldSignMonth), 2) || '-' || right('00' + convert(char(2), so.fldSignDay), 2) else NULL endif) as fldSOSignDate,
                              (if p.fldType = 1 then so.fldInvoiceNo else NULL endif) as fldInvoiceNo,
                              (if p.fldType = 1 then so.fldVehicleType else NULL endif) as fldSOVehicleType,
                              (if p.fldType = 1 then so.fldTechID else NULL endif) as fldTechID,
                              (if p.fldType = 1 then so.fldSplitTechID else NULL endif) as fldSplitTechID,
                              (if p.fldType = 1 then so.fldVIN else NULL endif) as fldSOVIN,
                              (if p.fldType = 1 then sos.fldCarPrice else NULL endif) as fldCarPayroll,
                              (if p.fldType = 1 then sos.fldTruckPrice else NULL endif) as fldTruckPayroll,
                              (if p.fldType = 1 then sov.fldVehicleType else NULL endif) as fldSOVVehicleType,
                              (if p.fldType = 1 then sov.fldVIN else NULL endif) as fldSOVVIN,
                              (if p.fldType = 1 then sos.fldsid else NULL endif)
                      from    TephSeal_tblEmployeePay p inner join 
                              TephSeal_tblEmployees e on e.fldID = p.fldEmployeeID inner join 
                              TephSeal_tblRoles r on r.fldID = e.fldRoleID inner join 
                              TephSeal_tblStores s on s.fldID = p.fldStoreID inner join 
                              TephSeal_tblRegions g on g.fldID = s.fldRegionID left join 
                              TephSeal_tblServiceOrders so on ((so.fldTechID = p.fldEmployeeID) or (so.fldSplitTechID = p.fldEmployeeID)) left join
                              TephSeal_tblServiceOrderServices sos on sos.fldUID = so.fldInvoiceNo left join 
                              TephSeal_tblServiceOrderVehicles sov on sov.fldUID = so.fldInvoiceNo 
                      where    (p.fldStoreID = " + store + ")";

You have a join on an or. That's bad! It can force a table scan or index scan instead of an index seek. I can't say if that's the reason your performance isn't very good or not, but that isn't helping any. Neither is the distinct or *.

Which join in particular is showing pain in the query plan?

The table structure, including indices would be very helpful.

It probably couldn't hurt to take the evaluations out of the query and do them on the code side or change the type so that the comparison isn't necessary. The DB structure would help indicate which would be best.

Also check the indexes on the table. You'd be amazed at how many times I have gone into a company with a bad performing database to find there are no indexes on it what so ever.

I will look at what indexes are set, if any.

Thanks for your input guys!

-Nick

Originally Posted by JeremyMiller

It probably couldn't hurt to take the evaluations out of the query and do them on the code side

In general, this can be expected to hurt very much. It forces more work to be done all around, and more work results in longer wait times. The point of a database server is, when possible, to move the logic to the data, rather than moving ever larger amounts of data around to be evaluated everywhere. An exaggerated example would be if you select an entire table and then do the evaluations in your PHP code - you could transfer 10 GB and actually need 10 KB of it. When you filter in the query, in the same (extreme) case, you'll only send those 10 KB.

Nevermind the fact that the database engine itself is written in C++ and compiled to machine language, while your PHP code is interpreted. In the vast majority of cases, unless you're doing something very unusual, your filters will evaulate much more quickly inside the engine running your SQL than the engine running your PHP.

Originally Posted by vividearth

Also check the indexes on the table. You'd be amazed at how many times I have gone into a company with a bad performing database to find there are no indexes on it what so ever.

But do this carefully, an index is a double edged sword. Every time you change any data in your table, the indexes will be changed to reflect the data, so while they can make select queries faster, insert, update, and delete operations suffer. Index tuning is a game of balance.

I was speaking about the "if p.fldType = 1 then sos.fldsid else NULL endif" entries. Just seems to me that if you need something to be NULL, but returned either way and it's something like an id, then it could help a little. Sitting in the back of my head is a large question mark saying, "If all those comparisons need to be done, then what is up with the DB structure?" A 3NF DB structure is likely to not need all that garbage.

Regarding the indexing. I believe I read in the MySQL optimization book that if you have a lot of insert/update/delete rows, that you may want to drop the index altogether and then recreate it when done with everything (or, in the case of deleting, selecting into a temporary table, dropping the current, and recreating it with the temporary data).

HOWEVER, each situation is highly unique, so stress testing is important. I loved in the MySQL optimization book how they always conducted tests with a few million rows of data to demonstrate their point.

Originally Posted by JeremyMiller

Regarding the indexing. I believe I read in the MySQL optimization book that if you have a lot of insert/update/delete rows, that you may want to drop the index altogether and then recreate it when done with everything (or, in the case of deleting, selecting into a temporary table, dropping the current, and recreating it with the temporary data).

This has proven true time and time again over many years of my own personal experience. There's a break even point, but if you're rebuilding large chunks of a table, at some point, keeping the indexes in sync will be more work than rebuilding them at the end. And they won't be fragmented if you do it that way.

You're probably right about moving the code out of the where clause in this particular instance, too. I was speaking in more general terms, about overall theory, but for anyone reading, Jeremy's specific advice is what ought to be followed here.