[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Inherited complex query

Posted on 2008-11-17
18
Medium Priority
?
295 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:andyrobjohns
  • 11
  • 6
17 Comments
 
LVL 25

Assisted Solution

by:jogos
jogos earned 920 total points
ID: 22974934
" 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
 
LVL 25

Assisted Solution

by:jogos
jogos earned 920 total points
ID: 22975174
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
 

Author Comment

by:andyrobjohns
ID: 22975430
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 25

Assisted Solution

by:jogos
jogos earned 920 total points
ID: 22981468
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
 
LVL 25

Assisted Solution

by:jogos
jogos earned 920 total points
ID: 22981587
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
 

Author Comment

by:andyrobjohns
ID: 22984499
"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
 
LVL 25

Assisted Solution

by:jogos
jogos earned 920 total points
ID: 22992537
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
 

Author Comment

by:andyrobjohns
ID: 22993285
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
 

Author Comment

by:andyrobjohns
ID: 22993752
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
 
LVL 25

Assisted Solution

by:jogos
jogos earned 920 total points
ID: 22994228
<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
 
LVL 25

Assisted Solution

by:jogos
jogos earned 920 total points
ID: 22994335
<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
 

Author Comment

by:andyrobjohns
ID: 22994591
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
 
LVL 25

Assisted Solution

by:jogos
jogos earned 920 total points
ID: 22996174
<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
 

Accepted Solution

by:
andyrobjohns earned 0 total points
ID: 23087264
Thanks for all the replies, but they are happy with my eventual solution so will mark this as resolved.

Thanks again
0
 
LVL 25

Assisted Solution

by:jogos
jogos earned 920 total points
ID: 23087921
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
 
LVL 25

Assisted Solution

by:jogos
jogos earned 920 total points
ID: 23087922
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
 
LVL 25

Expert Comment

by:jogos
ID: 23103949
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question