Inherited complex query

Hiya

I have inherited a very complex query which I am having trouble customizing. For starters it takes far too long for it to be useable by anybody. I have tried creating a VIEW but it just makes it even slower. On an old single processor server it was fairly nippy - sub 60 seconds to complete. Since moving to a far superior server it has slowed right down - so far been running to 20 minutes without completion!

Another problem is that it is no longer showing only distinct records.

I have attached the code. I need the CA.IRN to be distinct. I have tried all the usual suspects to fix it with no luck.

Any help greatly apreciated!
str7DayRecDDQuery = "SET NOCOUNT ON; SELECT " &_
                    "CE.CASEID, " &_
                    "CE.EVENTNO, " &_
                    "NM_O.NAME AS Owner, " &_
                    "NM_C.NAME AS Client, " &_
                    "CE.EVENTDUEDATE AS DueDate, " &_
                    "IsNull(EC.EVENTDESCRIPTION,EV.EVENTDESCRIPTION) AS [Description], " &_
                    "IsNull(convert(nvarchar(4000),CE.EVENTLONGTEXT),CE.EVENTTEXT) AS Notes, " &_
                    "CT.CASETYPEDESC AS Type, " &_
                    "CA.IRN AS CaseRef, " &_
                    "CY.COUNTRY AS [Country], " &_
                    "VP.PROPERTYNAME AS [Property Type], " &_
                    "dbo.fn_FormatName(NM_EMP.NAME, NM_EMP.FIRSTNAME, NM_EMP.TITLE, NULL) AS [Staff Member], " &_
                    "dbo.fn_FormatName(NM_SIG.NAME, NM_SIG.FIRSTNAME, NM_SIG.TITLE, NULL) AS [Signatory], " &_
                    "dbo.fn_FormatName(NM_I.NAME, NM_I.FIRSTNAME, NM_I.TITLE, NULL) AS [Instructor], " &_
                    "dbo.fn_FormatName(NM_A.NAME, NM_A.FIRSTNAME, NM_A.TITLE, NULL) AS [Agent], " &_
                    "CN_SIG.NAMENO " &_
                    "FROM CASES AS CA " &_
                    "JOIN CASEEVENT AS CE ON (CE.CASEID = CA.CASEID) " &_
                    "JOIN CASENAME AS CN_O ON CA.CASEID = CN_O.CASEID AND CN_O.NAMETYPE = 'O' " &_
                    "JOIN NAME AS NM_O ON NM_O.NAMENO = CN_O.NAMENO " &_
                    "JOIN CASENAME AS CN_C ON CA.CASEID = CN_C.CASEID AND CN_C.NAMETYPE = 'I' " &_
                    "JOIN NAME AS NM_C ON NM_C.NAMENO = CN_C.NAMENO " &_
                    "LEFT JOIN EVENTCONTROL AS EC ON (EC.CRITERIANO = CE.CREATEDBYCRITERIA AND EC.EVENTNO = CE.EVENTNO) " &_
                    "JOIN EVENTS AS EV ON (EV.EVENTNO = CE.EVENTNO) " &_
                    "JOIN CASETYPE AS CT ON (CT.CASETYPE = CA.CASETYPE) " &_
                    "JOIN COUNTRY AS CY ON (CY.COUNTRYCODE = CA.COUNTRYCODE) " &_
                    "JOIN VALIDPROPERTY AS VP ON (VP.PROPERTYTYPE = CA.PROPERTYTYPE AND VP.COUNTRYCODE = " &_
                     "(SELECT MIN(VP1.COUNTRYCODE) FROM VALIDPROPERTY AS VP1 " &_
                     "WHERE VP1.PROPERTYTYPE = CA.PROPERTYTYPE AND VP1.COUNTRYCODE in (CA.COUNTRYCODE, 'ZZZ'))) " &_
                    "LEFT JOIN CASENAME AS CN_EMP ON (CN_EMP.CASEID = CA.CASEID AND CN_EMP.NAMETYPE = 'EMP' and(CN_EMP.EXPIRYDATE IS NULL or CN_EMP.EXPIRYDATE > getdate() ) AND CN_EMP.SEQUENCE = " &_
                     "(SELECT MIN(SEQUENCE) FROM CASENAME CN " &_
                     "WHERE CN.CASEID = CA.CASEID AND CN.NAMETYPE = 'EMP' AND(CN.EXPIRYDATE IS NULL or CN.EXPIRYDATE > getdate())) AND EXISTS " &_
                     "(SELECT * FROM dbo.fn_FilterUserNameTypes(1,null,0,1) CN_EMP_FUNT " &_
                     "WHERE CN_EMP_FUNT.NAMETYPE = CN_EMP.NAMETYPE)) " &_
                    "LEFT JOIN NAME AS NM_EMP ON (NM_EMP.NAMENO = CN_EMP.NAMENO) " &_
                    "LEFT JOIN CASENAME AS CN_SIG ON (CN_SIG.CASEID = CA.CASEID AND CN_SIG.NAMETYPE = 'SIG' AND(CN_SIG.EXPIRYDATE IS NULL or CN_SIG.EXPIRYDATE > getdate() ) AND CN_SIG.SEQUENCE = " &_
                     "(SELECT MIN(SEQUENCE) FROM CASENAME CN " &_
                     "WHERE CN.CASEID = CA.CASEID AND CN.NAMETYPE = 'SIG' AND(CN.EXPIRYDATE IS NULL or CN.EXPIRYDATE > getdate())) AND EXISTS " &_
                     "(SELECT * FROM dbo.fn_FilterUserNameTypes(1,null,0,1) CN_SIG_FUNT " &_
                     "WHERE CN_SIG_FUNT.NAMETYPE = CN_SIG.NAMETYPE)) " &_
                    "LEFT JOIN NAME AS NM_SIG ON (NM_SIG.NAMENO = CN_SIG.NAMENO) " &_
                    "LEFT JOIN CASENAME AS CN_I ON (CN_I.CASEID = CA.CASEID AND CN_I.NAMETYPE = 'I' AND(CN_I.EXPIRYDATE IS NULL or CN_I.EXPIRYDATE > getdate() ) AND CN_I.SEQUENCE = " &_
                     "(SELECT MIN(SEQUENCE) FROM CASENAME CN " &_
                     "WHERE CN.CASEID = CA.CASEID AND CN.NAMETYPE = 'I' AND(CN.EXPIRYDATE IS NULL or CN.EXPIRYDATE > getdate())) AND EXISTS " &_
                     "(SELECT * FROM dbo.fn_FilterUserNameTypes(1,null,0,1) CN_I_FUNT "&_
                     "WHERE CN_I_FUNT.NAMETYPE = CN_I.NAMETYPE)) " &_
                    "LEFT JOIN NAME AS NM_I ON (NM_I.NAMENO = CN_I.NAMENO) " &_
                    "LEFT JOIN CASENAME AS CN_A ON (CN_A.CASEID = CA.CASEID AND CN_A.NAMETYPE = 'A' AND(CN_A.EXPIRYDATE IS NULL or CN_A.EXPIRYDATE > getdate() ) AND CN_A.SEQUENCE = " &_
                     "(SELECT MIN(SEQUENCE) FROM CASENAME CN " &_
                     "WHERE CN.CASEID = CA.CASEID AND CN.NAMETYPE = 'A' AND(CN.EXPIRYDATE IS NULL or CN.EXPIRYDATE > getdate())) AND EXISTS " &_
                     "(SELECT * FROM dbo.fn_FilterUserNameTypes(1,null,0,1) CN_A_FUNT " &_
                     "WHERE CN_A_FUNT.NAMETYPE = CN_A.NAMETYPE)) " &_
                    "LEFT JOIN NAME AS NM_A ON (NM_A.NAMENO = CN_A.NAMENO) WHERE 1 = 1 AND IsNull(CE.OCCURREDFLAG,0) = 0 AND COALESCE(EC.IMPORTANCELEVEL,EV.IMPORTANCELEVEL,9) IN ('7','8') AND EXISTS " &_
                     "(SELECT 1 FROM OPENACTION OAX " &_
                    "JOIN EVENTCONTROL AS ECX ON (ECX.CRITERIANO = OAX.CRITERIANO AND ECX.EVENTNO = CE.EVENTNO) " &_
                    "JOIN ACTIONS AX ON (AX.ACTION = OAX.ACTION) " &_
                     "WHERE OAX.CASEID = CA.CASEID AND ((OAX.ACTION = 'RN' AND ECX.EVENTNO = '-11') OR ECX.EVENTNO < > '-11') AND OAX.POLICEEVENTS = '1' AND OAX.CYCLE = CASE WHEN(AX.NUMCYCLESALLOWED > '1') THEN CE.CYCLE ELSE 1 END AND IsNull(AX.ACTIONTYPEFLAG,0) < > '1') AND(CE.EVENTDUEDATE < dateadd(dd,7,getdate())) AND EXISTS " &_
                     "(SELECT * FROM CASES CA1 " &_
                     "WHERE 1 = 1 AND EXISTS " &_
                     "(SELECT * FROM CASENAME CN1 " &_
                     "WHERE (CN1.EXPIRYDATE IS NULL or CN1.EXPIRYDATE > getdate()) AND CN1.CASEID = CA1.CASEID) AND CA1.CASEID = CA.CASEID) " &_
                    "ORDER BY 5,10"

Open in new window

andyrobjohnsAsked:
Who is Participating?
 
andyrobjohnsConnect With a Mentor Author Commented:
Thanks for all the replies, but they are happy with my eventual solution so will mark this as resolved.

Thanks again
0
 
jogosConnect With a Mentor Commented:
" Since moving to a far superior server it has slowed right down "
Something didn't move with it ... indexes..... . Or the server is faster but the allocated memory is less....

Monitor what is consuming bottleneck: memory, disk i/o, cpu .....
Compare execution plan's on both servers (if you still can) ther will be table scan's where ther used to be index scans.
0
 
jogosConnect With a Mentor Commented:
About the distinct problem:
You don't force any distinct/group and because off the many joins it could be that only one extra record in one off the used tables starts to generate duplicates.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
andyrobjohnsAuthor Commented:
The old server is no more unfortunately so can't compare anything!

How do I check if the indexes were or weren't copied across? And how do I re-enable them?

Thanks
0
 
jogosConnect With a Mentor Commented:
If you moved files or backup/restored indexes came with it, don't worry.
But other server so some performance-parameters changed: CPU consumed by other applications, allocated memory for SQL, storage (disk-type, spread system/temp/data/log), locks that slow down, statistics that are not renewed,  transaction model FULL but not yet a transaction log active so your transactionlog file has exploded.....
Did you move from SQL2000 to SQL2005? Is your compatibility-level still 80 (SQL2000) so the SQL2005 engine can't perform at his best. Did it slowed down from the day it was moved or only after a while?

So to many possible causes.

Quick working plan
- is it only this query that is out off shape or does every (large) db-action seemed to have slowed down (-> server-settings, backup-model..;)
- complex query: why not a stored procedure so statistics for this procedure can speed it up
 




0
 
jogosConnect With a Mentor Commented:
Looking at the query there (as readable as it is) there are some things that can benifit from your attention
- A test on '=' on an indexed column will benifit from the index, '<>', 'in' , 'is null or >' most likely won't (and   your execution plan will show a 'table scan')
- many joins on NAME or CASENAME so indexes/free space per page...  off these tables need attention
- join on CASENAME several times in combination with
" EXISTS (SELECT * FROM dbo.fn_FilterUserNameTypes(1,null,0,1) CN_SIG_FUNT " &_
=> fn_FilterUserNameTypes called probably to many times ther is no link with a specific value for that join , so probably a lot of this could be isolated in one view could gain a lot of 'temporay return tables allocations'  

 
0
 
andyrobjohnsAuthor Commented:
"complex query: why not a stored procedure so statistics for this procedure can speed it up"

As I said I have inherited this so quick fixes are needed. I have never used stored procedures so wouldn't know where to begin (apart from Google!!).

I think it slowed down from the day it was moved over to the new server. It is a 2000 DB, which is running in 2000 mode, but the server itself is in 2005 mode.

It's only this query which is slow. Basically we have a 3rd party database which has a client application installed on the PCs. The application is fine, but they got someone to write the above query as a one off thing, which is what I have inherited.

If you could help with moving this over to a stored procedure, that would be appreciated!

Thanks
0
 
jogosConnect With a Mentor Commented:
Moving it to a stored procedure will not be the magic tric but could be a final tuning. Most likely there is a lack of indexes and there is a lot to gain in filtering and joining.

You moved from one server to the other, only this query is slow. What makes it diferent from the others? The only heavy query?  The new server plays here a role, if your SQL does not get as much memory as it used to have on the previous server. Your SQL2005 is emulating SQL2000 in case of getting 'best of both worlds' it could be 'worst of both worlds'.  Can you get rid of the SQL2000-mode?

Only way to know for sure where your bottleneck is: monitor (sql*profiler, performance monitor ...).  Monitoring in many cases guides you to the real problem and even important rules out other things.

For stored procedure, put same query in a CREATE/ALTER PROCEDURE- statement. From the code you have you must get rid off all concatination-stuff (double quotes , +-sign,  &_)  Only doing that your query gets more readable.
see books online
http://msdn.microsoft.com/en-us/library/ms187926.aspx
CREATE PROCEDURE MyMassive_querry 
AS
BEGIN
 SET NOCOUNT ON; 
 SELECT CE.CASEID, CE.EVENTNO
  ,NM_O.NAME AS Owner
....
 FROM.....
END
GO
 
To execute 
EXECUTE MyMassive_querry ;  
GO 

Open in new window

0
 
andyrobjohnsAuthor Commented:
Thanks for all the help, but I have finally solved the DISTINCT problem.

I will make it a stored procedure and test the performance. How can I check what tables are indexed and which ones are not, as I assume if even 1 of the table this code references is not indexed, performance will be slower.

Thanks
0
 
andyrobjohnsAuthor Commented:
I also managed to remove a load of code (almost half!!) as what was being selected wasn't actually being displayed.

The output is no different but since removing the unneeded code it's a bit quicker as well.
str7DayRecDDQuery = "SET NOCOUNT ON; " &_
                    "SELECT " &_
                    "CA.IRN AS CaseRef, " &_
                    "MIN(CE.EVENTDUEDATE) AS DueDate, " &_
                    "MIN(IsNull(EC.EVENTDESCRIPTION,EV.EVENTDESCRIPTION)) AS [Description], " &_
                    "MIN(NM_O.NAME) AS Owner, " &_
                    "MIN(NM_C.NAME) AS Client " &_
                    "FROM CASES AS CA " &_
                    "JOIN CASEEVENT AS CE ON (CE.CASEID = CA.CASEID) " &_
                    "JOIN CASENAME AS CN_O ON CA.CASEID = CN_O.CASEID AND CN_O.NAMETYPE = 'O' " &_
                    "JOIN NAME AS NM_O ON NM_O.NAMENO = CN_O.NAMENO " &_
                    "JOIN CASENAME AS CN_C ON CA.CASEID = CN_C.CASEID AND CN_C.NAMETYPE = 'I' " &_
                    "JOIN NAME AS NM_C ON NM_C.NAMENO = CN_C.NAMENO " &_
                    "LEFT JOIN EVENTCONTROL AS EC ON (EC.CRITERIANO = CE.CREATEDBYCRITERIA AND EC.EVENTNO = CE.EVENTNO) " &_
                    "JOIN EVENTS AS EV ON (EV.EVENTNO = CE.EVENTNO) " &_
                    "LEFT JOIN CASENAME AS CN_EMP ON (CN_EMP.CASEID = CA.CASEID AND CN_EMP.NAMETYPE = 'EMP' AND (CN_EMP.EXPIRYDATE IS NULL OR CN_EMP.EXPIRYDATE > getdate()) AND CN_EMP.SEQUENCE = " &_
                     "(SELECT MIN(SEQUENCE) FROM CASENAME CN " &_
                     "WHERE CN.CASEID = CA.CASEID AND CN.NAMETYPE = 'EMP' AND (CN.EXPIRYDATE IS NULL OR CN.EXPIRYDATE > getdate())) AND EXISTS " &_
                     "(SELECT * FROM dbo.fn_FilterUserNameTypes(1,null,0,1) CN_EMP_FUNT " &_
                     "WHERE CN_EMP_FUNT.NAMETYPE = CN_EMP.NAMETYPE)) " &_
                    "LEFT JOIN NAME AS NM_EMP ON (NM_EMP.NAMENO = CN_EMP.NAMENO) " &_
                     "WHERE 1 = 1 AND IsNull(CE.OCCURREDFLAG,0) = 0 AND COALESCE(EC.IMPORTANCELEVEL,EV.IMPORTANCELEVEL,9) IN ('7','8') AND EXISTS " &_
                     "(SELECT 1 FROM OPENACTION OAX " &_
                    "JOIN EVENTCONTROL AS ECX ON (ECX.CRITERIANO = OAX.CRITERIANO AND ECX.EVENTNO = CE.EVENTNO) " &_
                    "JOIN ACTIONS AX ON (AX.ACTION = OAX.ACTION) " &_
                     "WHERE OAX.CASEID = CA.CASEID AND ((OAX.ACTION = 'RN' AND ECX.EVENTNO = '-11') OR ECX.EVENTNO <> '-11') AND OAX.POLICEEVENTS = '1' AND OAX.CYCLE = CASE WHEN(AX.NUMCYCLESALLOWED > '1') THEN CE.CYCLE ELSE 1 END AND IsNull(AX.ACTIONTYPEFLAG,0) <> '1') AND(CE.EVENTDUEDATE < dateadd(dd,7,getdate())) AND EXISTS " &_
                     "(SELECT * FROM CASES CA1 " &_
                     "WHERE 1 = 1 AND EXISTS " &_
                     "(SELECT * FROM CASENAME CN1 " &_
                     "WHERE (CN1.EXPIRYDATE IS NULL OR CN1.EXPIRYDATE > getdate()) AND CN1.CASEID = CA1.CASEID) AND CA1.CASEID = CA.CASEID) " &_
                    "GROUP BY CA.IRN " &_
                    "ORDER BY DueDate, CaseRef"

Open in new window

0
 
jogosConnect With a Mentor Commented:
<How can I check what tables are indexed>
In management studio you can see and modify the table design ... also it's indexes.

But it's not the 'tables without indexes'  that you kneed to identify it's the tables that in your query are accessed ineffiently. So even if it is indexed that the right index not is beeing used.
Where to find? Execution plan, Profiler ....

Sorry, but I can' give a whole 'performance tuning' course in a few comments.
0
 
jogosConnect With a Mentor Commented:
<I also managed to remove a load of code (almost half!!) as what was being selected wasn't actually being displayed.>
I want to mention that by using the group by you have suppressed the duplicate rows it may not be what you expect it to be.  
Did you know which join created the duplicate and why?  

ex  you select MIN(CE.EVENTDUEDATE) AS DueDate. If this table CE was the origin off the duplicates there could be 2 value's for CE.EVENTDUEDATE .... you randomly keep the lowest one. If you select another value from that same table and also took a min- or max-value it could return the value from another record in database.  No problem if that's what you want ... but since there was no grouping in the original select it could be unintentional.
0
 
andyrobjohnsAuthor Commented:
When I say duplicates, I don't mean EXACT duplicates, as in the 2 rows have identical data.

Lets take the following as example:

Field1     Field2     Field3     Field4     Field5
Joe        Jill           James    John       Jenny
Joe        Jill           James    John       Jennifer
Sue       Steve      Sara      Sandra   Senita
Sue       Steve      Sara      Sandra   Sally

As you can see the 2 rows with similar data are not identical all the way. What we need here is to only show 1 row based on the distinct value of 1 field, say Field1, so we should be left with:

Field1     Field2     Field3     Field4     Field5
Joe        Jill           James    John       Jenny
Sue       Steve      Sara      Sandra   Senita

It's not me who want's this by the way!!!!!! I don't think they care which one gets dropped, as long as they don't all show.
0
 
jogosConnect With a Mentor Commented:
<When I say duplicates, I don't mean EXACT duplicates, as in the 2 rows have identical data.>
I know, if it where exact duplicates there would not be a problem, neither when you have a meaningfull min/max/avg/count(*) - value returned. If you get duplicates on a level you don't want .... your query could have a join-level too much.

But in your example: what's the added value off returning Field5?   On an 'random' manar you return Senita, where the next time it could return Sally.  Could nobody be interessed in the fact that it could be Sally and Senita?
Next month someone asks to add a Field6 whitch should be the lastname from the person mentioned in Field5.  Unlikely that the min(LastName) and min(FirstName) in any case will match and you will end up with odd things like  'John Obama' or a 'Barac McCain' .  

For me my point is made, I won't comment again on this one.
0
 
jogosConnect With a Mentor Commented:
Thanks a lot for not apreciating the help I gave by not awarding any points and wanting to delete the topic.

You asked 2 questions in one topic (duplicates + performance) on both I helped you by pointing to problems (index, stored procedure, unneeded joins in select) or possible unwanted side-effects.(random duplicate record dumped).
Remember "Sorry, but I can' give a whole 'performance tuning' course in a few comments." And we only can work with the things your provide.
0
 
jogosConnect With a Mentor Commented:
Thanks a lot for not apreciating the help I gave by not awarding any points and wanting to delete the topic.

You asked 2 questions in one topic (duplicates + performance) on both I helped you by pointing to problems (index, stored procedure, unneeded joins in select) or possible unwanted side-effects.(random duplicate record dumped).
Remember "Sorry, but I can' give a whole 'performance tuning' course in a few comments." And we only can work with the things your provide.
0
 
jogosCommented:
Not begging for any points.

But the way you accept looks still confusing, please take that with you in future
1) Rated comments
My comments seems to get points awarded for the effort (because I complained)
anyhow I the stripped query I see my advises followed
- group by
- fn_FilterUserNameTypes called probably to many times

2) For the sollution you will markt the comment
"Thanks for all the replies, but they are happy with my eventual solution so will mark this as resolved.

Thanks again"
Looks very valluable. And in every case the one who asked the question solved it on his machine. On this site we try to guide people to find the sollution, sometimes typed out so you can copy paste... sometiimes by saying 'look there, try this ... during there lunch break'

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.