March 19, 2015 at 11:39 pm
Hi,
I seem to have an odd situation.
I have a view that when created holds about 40k of rows.
when I use
SELECT DISTINCT RefTypeWord FROM vw_Referrals where ClientRef = 'EPS' or any other client ref the response time is very quick
when I use
SELECT DISTINCT RefTypeWord FROM vw_Referrals where ClientRef = 'PRE' it times out.
when I don't use the DISTINCT word then EPS brings back about 18k of rows and PRE brings back about 900 rows (not K just 900) and it does it very quickly again.
The view references several tables but it's the same tables regardless of the ClienRref.
So I'm a bit stumped as to why this is happening.
thanks,
March 19, 2015 at 11:40 pm
just to add to this post, I'm using Azure sql server.
Not sure how to view the server logs on azure sql server compared to local servers through SSMS.
thanks,
March 20, 2015 at 1:38 am
have you checked the execution plan? which will show what exactly is happening behind the sense.
try the recompile option with timeout query. Do check your statistics are properly updated. you can also check if there is any index of that is fragmented.
March 20, 2015 at 1:56 am
Hi twin.devil,
they sound like good suggestions but..
check the execution plan - how?
recompile option with timeout query - how?
statistics properly updated - how?
check if index is fragmented - how? - I'm not sure the view has an index.
Here's the view code if it helps...
SELECT dbo.PatientRefKPI.ClientRef, dbo.PatientRefKPI.dbPatID, dbo.PatientRefKPI.dbPatLastName, dbo.PatientRefKPI.dbPatBirthday, dbo.PatDetail.dbAddDate,
dbo.vw_PatReferrals.FirstName, dbo.vw_PatReferrals.LastName, dbo.StaffRefKPI.dbStaffLastName, dbo.Status.dbStatusDesc,
dbo.vw_PatReferrals.dbOtherRefType, dbo.vw_PatReferrals.RefTypeWord
FROM dbo.PatDetail
LEFT OUTER JOIN dbo.StaffRefKPI
RIGHT OUTER JOIN dbo.PatientRefKPI ON dbo.StaffRefKPI.dbStaffCnt = dbo.PatientRefKPI.dbDocID AND StaffRefKPI.ClientRef = PatientRefKPI.ClientRef
RIGHT OUTER JOIN dbo.Status ON dbo.PatientRefKPI.dbStatusID = dbo.Status.dbStatusID AND Status.ClientRef = PatientRefKPI.ClientRef
ON dbo.PatDetail.dbPatCnt = dbo.PatientRefKPI.dbPatCnt AND PatDetail.ClientRef = PatientRefKPI.ClientRef
LEFT OUTER JOIN dbo.vw_PatReferrals ON dbo.PatientRefKPI.dbPatCnt = dbo.vw_PatReferrals.dbPatCnt AND vw_PatReferrals.ClientRef = PatientRefKPI.ClientRef
WHERE (dbo.vw_PatReferrals.dbRefType = 'i')
March 20, 2015 at 6:36 am
All the question you have asked means you yet to get to know the working of sql server. i have share you some links to understand what is what. understanding is key once you have the understanding you can do a lot of things to manage it. but 1st you need to know what happening.
check the execution plan - how?
recompile option with timeout query - how?
RECOMPILE Hints and Execution Plan Caching[/url]
statistics properly updated - how?
SQL Server Statistics Questions We Were Too Shy to Ask[/url]
check if index is fragmented - how? - I'm not sure the view has an index.
1st need to hear this
Why Index Fragmentation and Bad Statistics Aren’t Always the Problem (Video)[/url]
once you get the idea
you can use this
Fragmentation and Index Maintenance Tips[/url]
As far as your query is concern, there are a lot of right/Left join used, which means you need all the data of the base table. Also a view better check the performance of the inner most view.
but 1st thing you need to understand how thing work in sql server after that you will be able to identify your problem.
In case of any issue/query do let us know. we would like to help u in this.
hope it helps
March 20, 2015 at 8:13 am
mattech06 (3/20/2015)
Hi twin.devil,they sound like good suggestions but..
check the execution plan - how?
recompile option with timeout query - how?
statistics properly updated - how?
check if index is fragmented - how? - I'm not sure the view has an index.
Here's the view code if it helps...
SELECT dbo.PatientRefKPI.ClientRef, dbo.PatientRefKPI.dbPatID, dbo.PatientRefKPI.dbPatLastName, dbo.PatientRefKPI.dbPatBirthday, dbo.PatDetail.dbAddDate,dbo.vw_PatReferrals.FirstName, dbo.vw_PatReferrals.LastName, dbo.StaffRefKPI.dbStaffLastName, dbo.Status.dbStatusDesc,
dbo.vw_PatReferrals.dbOtherRefType, dbo.vw_PatReferrals.RefTypeWord
FROM dbo.PatDetail
LEFT OUTER JOIN dbo.StaffRefKPI
RIGHT OUTER JOIN dbo.PatientRefKPI ON dbo.StaffRefKPI.dbStaffCnt = dbo.PatientRefKPI.dbDocID AND StaffRefKPI.ClientRef = PatientRefKPI.ClientRef
RIGHT OUTER JOIN dbo.Status ON dbo.PatientRefKPI.dbStatusID = dbo.Status.dbStatusID AND Status.ClientRef = PatientRefKPI.ClientRef
ON dbo.PatDetail.dbPatCnt = dbo.PatientRefKPI.dbPatCnt AND PatDetail.ClientRef = PatientRefKPI.ClientRef
LEFT OUTER JOIN dbo.vw_PatReferrals ON dbo.PatientRefKPI.dbPatCnt = dbo.vw_PatReferrals.dbPatCnt AND vw_PatReferrals.ClientRef = PatientRefKPI.ClientRef
WHERE (dbo.vw_PatReferrals.dbRefType = 'i')
Here's that view defeinition reformatted and aliased for readability:
SELECT
prk.ClientRef,
prk.dbPatID,
prk.dbPatLastName,
prk.dbPatBirthday,
pd.dbAddDate,
pr.FirstName,
pr.LastName,
srk.dbStaffLastName,
s.dbStatusDesc,
pr.dbOtherRefType,
pr.RefTypeWord
FROM dbo.PatDetail pd
LEFT OUTER JOIN dbo.StaffRefKPI srk
RIGHT OUTER JOIN dbo.PatientRefKPI prk
ON srk.dbStaffCnt = prk.dbDocID AND srk.ClientRef = prk.ClientRef
RIGHT OUTER JOIN dbo.[Status] s
ON prk.dbStatusID = s.dbStatusID AND s.ClientRef = prk.ClientRef
ON pd.dbPatCnt = prk.dbPatCnt AND pd.ClientRef = prk.ClientRef
LEFT OUTER JOIN dbo.vw_PatReferrals pr
ON prk.dbPatCnt = pr.dbPatCnt AND pr.ClientRef = prk.ClientRef
WHERE pr.dbRefType = 'i'
Two things stand out: Firstly, a view calling a view, as TD pointed out. Do you really need vw_PatReferrals or can you strip it down to the table sources which are relevant and necessary for your result set?
Secondly, the curse of query (view) design tools - right joins. Can you understand the relationships between these tables? It can be tricky enough when there's only one right join. Can you rewrite this query using your ERD for reference so that it's human-readable? That's going to take you some way towards making the thing modifiable. As it stands, anything you want to do will be trial and error with your design tool.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 20, 2015 at 9:03 am
twin.devil (3/20/2015)
All the question you have asked means you yet to get to know the working of sql server. i have share you some links to understand what is what. understanding is key once you have the understanding you can do a lot of things to manage it. but 1st you need to know what happening.check the execution plan - how?
recompile option with timeout query - how?
RECOMPILE Hints and Execution Plan Caching[/url]
statistics properly updated - how?
SQL Server Statistics Questions We Were Too Shy to Ask[/url]
check if index is fragmented - how? - I'm not sure the view has an index.
1st need to hear this
Why Index Fragmentation and Bad Statistics Aren’t Always the Problem (Video)[/url]
once you get the idea
you can use this
Fragmentation and Index Maintenance Tips[/url]
As far as your query is concern, there are a lot of right/Left join used, which means you need all the data of the base table. Also a view better check the performance of the inner most view.
but 1st thing you need to understand how thing work in sql server after that you will be able to identify your problem.
In case of any issue/query do let us know. we would like to help u in this.
hope it helps
Even though this is a SQL 2008 forum they OP mentioned that they are using Azure so some of the links you provided won't be relevant. 🙁
-- Itzik Ben-Gan 2001
March 20, 2015 at 9:29 am
mattech06 (3/19/2015)
Hi,I seem to have an odd situation.
I have a view that when created holds about 40k of rows.
when I use
SELECT DISTINCT RefTypeWord FROM vw_Referrals where ClientRef = 'EPS' or any other client ref the response time is very quick
when I use
SELECT DISTINCT RefTypeWord FROM vw_Referrals where ClientRef = 'PRE' it times out.
when I don't use the DISTINCT word then EPS brings back about 18k of rows and PRE brings back about 900 rows (not K just 900) and it does it very quickly again.
The view references several tables but it's the same tables regardless of the ClienRref.
So I'm a bit stumped as to why this is happening.
thanks,
It looks like possible parameter sniffiing which will cause the optimizer to create a terrible query plan. To add with Twin.Devll suggested, you should take a look at look at the query plan. I'll be honest, I have only played with SQL Azure at SQL PASS and read about it a little and have never looked at a query plan in Azure. That said, I ran across this article by Grant Fritchey: on how to do it: Execution Plans in Azure SQL Database[/url]
when I don't use the DISTINCT word then EPS brings back about 18k of rows and PRE brings back about 900 rows (not K just 900) and it does it very quickly again.
A DISTINCT creates a SORT operator in the query plan and, depending on how many rows you are starting out with, it can be a very expensive sort. A non-clustered index on RefTypeWord may help you in this case.
-- Itzik Ben-Gan 2001
March 21, 2015 at 9:51 am
Thanks for a lot of useful info guys.
In the end I deleted all data everywhere that had clientref = 'PRE' then re sync'd and it now works ok.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply