Nakuru1234
asked on
SQL Query Performance
Hello:
Below is a sql code that has been timing out. When I execute, it takes about 20 mins. The database expert links might help...I'll attach them shortly...
The report uses multiple versions of the same tables as it searches/looks or returns to the ED within 72 hours of the original arrival. Please can you advice/help on tuning the SQL Query or what I should do to solve the time out issue? TIA.
SELECT DISTINCT "PAT_ENC_HSP"."ED_EPISODE_
FROM (((((((((((("Clarity"."dbo
WHERE "PAT_ENC_HSP"."SSM_TEST_TY
Below is a sql code that has been timing out. When I execute, it takes about 20 mins. The database expert links might help...I'll attach them shortly...
The report uses multiple versions of the same tables as it searches/looks or returns to the ED within 72 hours of the original arrival. Please can you advice/help on tuning the SQL Query or what I should do to solve the time out issue? TIA.
SELECT DISTINCT "PAT_ENC_HSP"."ED_EPISODE_
FROM (((((((((((("Clarity"."dbo
WHERE "PAT_ENC_HSP"."SSM_TEST_TY
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi David:
I understand. What do you mean by "I'd get that join performing first, and then worry about the related information" Please can you elaborate or give an example?
Please see the FULL table definition for PAT_ENC_HSP in the attachment...TIA
N.
PAT-ENC-HSP-Table-Definition-090.doc
I understand. What do you mean by "I'd get that join performing first, and then worry about the related information" Please can you elaborate or give an example?
Please see the FULL table definition for PAT_ENC_HSP in the attachment...TIA
N.
PAT-ENC-HSP-Table-Definition-090.doc
Without real data it's hard to help you but there's some rules that you can always use in queries:
- Avoid OR operator
- Create indexes on foreign keys columns to speed up joins
If you can post the query plan will be helpfull.
Greetings
- Avoid OR operator
- Create indexes on foreign keys columns to speed up joins
If you can post the query plan will be helpfull.
Greetings
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Think the idea for PAT_ENC_HSP_past is to get the previous EPISODE within a specific date range (seems to be prior month), and getting that, he then needs the associated data for that previous ed_episode_id hence the other _past table aliases as well.
But it could be an inner join because of that where clause, and maybe that where clause needs to still exist because possibly ed_episode_id is not mandatory.
So, can see why the _past might be needed, and why the is not null is also needed, and can even see why the left joins are being used for everything other than PAT_ENC_HSP_past where it looks like it could be an inner join (and would even move the check for episode_id being less than up there).
But this is being constructed in Access and there are a few gotchas when doing complex joins...
But it could be an inner join because of that where clause, and maybe that where clause needs to still exist because possibly ed_episode_id is not mandatory.
So, can see why the _past might be needed, and why the is not null is also needed, and can even see why the left joins are being used for everything other than PAT_ENC_HSP_past where it looks like it could be an inner join (and would even move the check for episode_id being less than up there).
But this is being constructed in Access and there are a few gotchas when doing complex joins...
Let me put it this way as I obviously was not clear the first time, if the author understands the concept of a LEFT JOIN and really needs it than the conditions in the WHERE clause are simply not going to produce the right result. Period.
And an inner join might not negate the need to check for NULLS either :)
Not sure it was considered that way.
Besides which I do agree that PAT_ENC_HSP_past could be an inner join...
Not sure it was considered that way.
Besides which I do agree that PAT_ENC_HSP_past could be an inner join...
>>And an inner join might not negate the need to check for NULLS either<<
Absolutely.
If the LEFT JOIN is really needed than the following conditions should not be in the WHERE clause but rather on the OUTER JOIN:
CLARITY_LOC_past.LOC_ID = 130992023
CLARITY_LOC.LOC_ID = 130992023
IDENTITY_ID.IDENTITY_TYPE_
Absolutely.
If the LEFT JOIN is really needed than the following conditions should not be in the WHERE clause but rather on the OUTER JOIN:
CLARITY_LOC_past.LOC_ID = 130992023
CLARITY_LOC.LOC_ID = 130992023
IDENTITY_ID.IDENTITY_TYPE_
ASKER
Hi Mark Wills:
Why are you saying this is being constructed in Access? TIA.
N.
Why are you saying this is being constructed in Access? TIA.
N.
ASKER
Hi Zberteoc:
I totally agree, the _past JOIN to itself is not necessary. TIA.
N.
I totally agree, the _past JOIN to itself is not necessary. TIA.
N.
ASKER
mark_wills:
You said, was hard to read, so, made it suitable for the above task, and still unsure about all those double ups (yes, can see some distinctions in the where clause and will take some more work to figure it all out)
// Have you figured out anything? TIA.
N.
You said, was hard to read, so, made it suitable for the above task, and still unsure about all those double ups (yes, can see some distinctions in the where clause and will take some more work to figure it all out)
// Have you figured out anything? TIA.
N.
Would simply run it up in a query window in SSMS, and then (under the "query" menu option) analyse the query in database engine query analyser.
See what it says about indexing... And probably should ask, are you in a position to make changes on the back end SQL server ?
Would also be making a Stored Procedure out of the query (if possible) and passing a few parameters to the stored procedure.
If we can do that, then we can reconstruct some of those joins into a more SQL Server profficient way. Access will complain about a couple of the techniques we have been discussing.
See what it says about indexing... And probably should ask, are you in a position to make changes on the back end SQL server ?
Would also be making a Stored Procedure out of the query (if possible) and passing a few parameters to the stored procedure.
If we can do that, then we can reconstruct some of those joins into a more SQL Server profficient way. Access will complain about a couple of the techniques we have been discussing.
The main problem here is the query itself and the indexes. What needs to be done is to make sure the indexes are how are supposed to be and then maybe rewriting the code a little bit to optimize it. Last concern here is encapsulate the query in a stored procedure. That easily can be done after the main problem is solved.
Ah well, I guess we will never know whether the author intended to use a LEFT JOIN or not. Perhaps they do not know the difference, because as written they are certainly not OUTER JOINs.
In any case, it looks like they are in capable hands so I will bow out.
Good luck.
In any case, it looks like they are in capable hands so I will bow out.
Good luck.
Dont bow out just yet - the fun is yet to happen :)
ASKER
I am going through all the experts comments/advice as we speak before I close the question...just in case I might have one last question to ask. TIA.
N.
N.
Great, will be good to hear some feedback.
And just noticed a question you asked me...
>> Why are you saying this is being constructed in Access
An assumption because of all the brackets that access insists on doing, and the word / line wraps, and thought I saw it mentioned somewhere.
And just noticed a question you asked me...
>> Why are you saying this is being constructed in Access
An assumption because of all the brackets that access insists on doing, and the word / line wraps, and thought I saw it mentioned somewhere.
ASKER
To all Experts:
You were very helpful in all your comments/advice. I really appreciate even though it took some time for me to close the question. Again, kudos to everybody who contributed. TIA.
N.
You were very helpful in all your comments/advice. I really appreciate even though it took some time for me to close the question. Again, kudos to everybody who contributed. TIA.
N.
ASKER
All the Experts did an excellent job to help me understand the problem and the solution!
ASKER
TIA,
N
Database-Table-links-09022010.doc