September 26, 2008 at 8:12 am
I have the following problem:
View1 is called with a value ID which is CHAR(7). This view performs very poorly. Analyzing this I find that in the FROM clause another Views is called, which is causing the problem. This View2 consists of a UNION which connects equivalent SELECTS on 2 different tables, 1 of the tables has more then 1 mil records. Showplan tells me this table is approached with a Clustered Index Scan.
If I use a WHERE clause in this UNION and thus limit the result to the value I use when calling VIEW1 I'll get the results almost immediately. This is what I need. However, I cannot find a way for VIEW2 to dynamically use the parameter I use when calling VIEW1. At least not as a View, and the limits posed by the calling application demand the use of a view, or a table, not a SP or such.
(Clustered) Indices are on the appropriate places, I have changed the orde of the tables/views to be called in the select and have got some improvement from 30 secs to 17 secs, but this is still not enough.
If only to pass this parameter from View1 to View2... Is there anyway to do this? Do I miss something else?
Greetz,
Hans Brouwer
September 26, 2008 at 8:36 am
Views don't have parameters, so that's not in the options to track down. This is unfortunately one of the red flags (when you start encapsulating views within other views), that your perf is about to take a nosedive.
If you need a parameter in the appropriate place then you should probably be looking at using a stored proc to do this for you. That way you can get your parameter in the place where it will make the most difference.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 26, 2008 at 10:29 am
Can you please post the views and the SELECT command being used?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 27, 2008 at 4:37 am
Tnx for answering all. I'll post DDL monday, but it's a bit: several views & tables.
With parameter I mean basically the WHERE clause. As I said, currently it is not an option to change the offensive view in a SP. If things cannot improve otherwise I'll suggest it again.
Greetz,
Hans Brouwer
September 29, 2008 at 1:27 am
Here the DDL of the involved views/tables. The view REV_KLIGEN_PATIENTEN is the main view, the problematic view is hl7_allePatientnummers.
TIA
Greetz,
Hans Brouwer
September 29, 2008 at 7:20 am
There is no definition for the Table/View [hl7_allePatientnummers].
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 29, 2008 at 7:29 am
An example of the Select command being issued would be helpful too.
Thanks,
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 30, 2008 at 3:34 am
You're right, sorry about that. Now added the complete script.
The SELECT statement is, for instance:
SELECT * FROM REV_KLIGEN_PATIENTEN
WHERE ZOEKPATIENTNUMMER = '0000007'
Greetz,
Hans Brouwer
September 30, 2008 at 5:40 am
Your problem is UNION in dbo.hl7_allePatientnummers.
UNION implies GROUP BY for all selected values.
GROUP BY changes all further WHERE checks to HAVING ones. What makes it impossible to use indexes for finding requested values.
Replace UNION with UNION ALL and it should be all right.
_____________
Code for TallyGenerator
September 30, 2008 at 6:07 am
What are the rowcounts of the two tables: [hl7_patienten], and [kop_synoniempatientnummers]?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 30, 2008 at 6:37 am
From you DDL code:
CREATE VIEW dbo.REV_KLIGEN_PATIENTEN
AS
SELECT
...
ISNULL (s.patientnummer, p.patientnummer) AS ZOEKPATIENTNUMMER, -- voorkeur-of synoniemnummer
...
FROMhl7_allePatientnummers s
LEFT JOIN dbo.hl7_patientenMetGesplitsteGebDat p
ONs.voorkeurspatientnummer = p.patientnummer
--
CREATE VIEW dbo.hl7_allePatientnummers
AS
SELECTsynoniempatientnummer AS patientnummer,
voorkeurspatientnummer,
koppeldatum
FROMrefbes.dbo.synoniempatientnummers
UNION
SELECTpatientnummer,
patientnummer,
NULL
FROMdbo.hl7_patienten
--
CREATE TABLE [dbo].[kop_synoniempatientnummers] (
[synoniempatientnummer] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[voorkeurspatientnummer] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[koppeldatum] [datetime] NOT NULL ,
[synoniempatientnummer_vwz] [int] NULL ,
[voorkeurspatientnummer_vwz] [int] NOT NULL ,
[verwerkingsdatum_zis] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[kop_synoniempatientnummers] WITH NOCHECK ADD
CONSTRAINT [pk_kop_synoniempatientnummers] PRIMARY KEY CLUSTERED
(
[synoniempatientnummer],
[voorkeurspatientnummer_vwz],
[koppeldatum]
) ON [PRIMARY]
GO
You will note that since s.patientnummer is synoniempatientnummer in table kop_synoniempatientnummers, it is part of the primary key and thus can never be null. This makes the view's ISNULL(s.patientnummer, p.patientnummer) useless overhead. It should be changed to just "s.patientnummer".
Note also that the first view joins on "s.voorkeurspatientnummer", but this field is NOT indexed in the base table [kop_synoniempatientnummers]. I suggest adding an index for it.
It has always been my experience that "the problem with Views" is not with Views in general but rather that either A) the Views were written incorrectly or B) the Views are being used incorrectly.
There were some rare minor problems with query logic using Views in SQL 2000, but as far as I know, they were cleared up in 2005. Other than that, the only problems with Views, IMHO, is that stacking too many of them up can make it difficult to tell what the actual combined query logic is, and that can usually be fixed by having an actual architecture and design for the use of Views within a Database/Application.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 30, 2008 at 6:46 am
Sergiy, I know the UNION is causing the problem; I'm looking for another way to do this within the parameters explained earlier.
RBarry, the HL7_patienten table hold close to 1500000 records, the other table holds about 45000 records.
Greetz,
Hans Brouwer
September 30, 2008 at 7:20 am
FreeHansje (9/30/2008)
Sergiy, I know the UNION is causing the problem; I'm looking for another way to do this within the parameters explained earlier.
Actually, Sergy makes an execellent point, one that I had missed. Why can't you fix the UNION statement in the View? The current UNION is returning the same rows that a UNION ALL would return since [koppeldatum] cannot be NULL in the base table, but it is specifically set to NULL in the second half of the UNION. So changing the UNION to a UNION ALL would just return the same rows, only faster.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 30, 2008 at 8:00 am
Tnx for answering all. I will take your advise to heart and use it.
Tnx again.
Greetz,
Hans Brouwer
September 30, 2008 at 8:09 am
Please let us know how it works out.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply