alter view

Question

Post reply

alter view

Hadrian

Hall of Fame

Points: 3742
More actions
February 27, 2006 at 7:24 am

#365350

Hi all,
I had a view which I wish to improve it:
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
CREATE
VIEW [dbo].[vwValoriNum]
AS
SELECT
TOP 100 PERCENT dbo.tblOrganization.OrganizationTitle AS Organizatie, dbo.tblInstallation.InstallationTitle AS Instalatie,
dbo
.tblForm.FormTitle AS Formular, dbo.tblParam.ParamTitle + '[' + ISNULL(dbo.tblValue.MeasUnit, '') + ']' AS Camp, dbo.tblData.Data,
CAST(dbo.tblValue.ValueVal AS float) AS Valoare, DATEPART(MM, dbo.tblData.Data) AS Luna, DATEPART(DD, dbo.tblData.Data) AS Zi, CONVERT(int,
DATEPART(HH, dbo.tblData.Data)) AS Ora, DATEPART(yy, dbo.tblData.Data) AS An, CONVERT(int, DATEPART(Mi, dbo.tblData.Data)) AS Minut,
ISNULL(dbo.tblValue.ProductName, 'Meta') AS Produs, dbo.tblData.DataID, ISNULL(dbo.tblValue.ValMin, '') AS ValMin, ISNULL(dbo.tblValue.ValMax, '')
AS ValMax, dbo.CheckValid(dbo.tblValue.ValMin, dbo.tblValue.ValMax, dbo.tblValue.ValueVal) AS Validare, dbo.tblForm.FormID,
dbo
.tblData.ApproveStatus, dbo.tblData.CodProba, dbo.tblInstallation.InstallationID, dbo.tblValue.isRealTest
FROM
dbo.tblOrganization INNER JOIN
dbo
.tblInstallation ON dbo.tblOrganization.OrganizationID = dbo.tblInstallation.OrganizationID INNER JOIN
dbo
.tblForm ON dbo.tblInstallation.InstallationID = dbo.tblForm.InstallationID INNER JOIN
dbo
.tblData ON dbo.tblForm.FormID = dbo.tblData.FormID INNER JOIN
dbo
.tblValue ON dbo.tblData.DataID = dbo.tblValue.DataID INNER JOIN
dbo
.tblParam ON dbo.tblValue.ParamID = dbo.tblParam.ParamID
WHERE
(dbo.tblData.IsDeleted = 0) AND (isnumeric(dbo.tblValue.ValueVal) = 1) AND (dbo.tblParam.IsNumeric = 1) AND (dbo.tblData.ApproveStatus <> 5) AND
(dbo.tblData.DataID NOT IN
(SELECT dataID
FROM dbo.vwDataSetmeta
WHERE paramTitle = 'PUBLIC' AND valueVal = 'nu')) OR
(dbo.tblData.IsDeleted = 0) AND (isnumeric(dbo.tblValue.ValueVal) = 1) AND (dbo.tblParam.IsNumeric = 0) AND (dbo.tblData.DataID NOT IN
(SELECT dataID
FROM dbo.vwDataSetmeta
WHERE paramTitle = 'PUBLIC' AND valueVal = 'nu')) AND (dbo.tblParam.IsCalculated = 1)
I want to improve the performance ,with derived table or similar.Could somebody help me?
Thanks in advance.
Gabriel Stan

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 1

*DROP TOP 100 PERCENT

*I've rewritten it a bit. Check if the outcome is the same.

SELECT

dbo.tblOrganization.OrganizationTitle AS Organizatie

, dbo.tblInstallation.InstallationTitle AS Instalatie

, dbo.tblForm.FormTitle AS Formular

, dbo.tblParam.ParamTitle + '[' + ISNULL(dbo.tblValue.MeasUnit, '') + ']' AS Camp

, dbo.tblData.Data

, CAST(dbo.tblValue.ValueVal AS float) AS Valoare

, DATEPART(MM, dbo.tblData.Data) AS Luna

, DATEPART(DD, dbo.tblData.Data) AS Zi

, CONVERT(int, DATEPART(HH, dbo.tblData.Data)) AS Ora

, DATEPART(yy, dbo.tblData.Data) AS An

, CONVERT(int, DATEPART(Mi, dbo.tblData.Data)) AS Minut

, ISNULL(dbo.tblValue.ProductName, 'Meta') AS Produs

, dbo.tblData.DataID

, ISNULL(dbo.tblValue.ValMin, '') AS ValMin

, ISNULL(dbo.tblValue.ValMax, '') AS ValMax

, dbo.CheckValid(dbo.tblValue.ValMin

, dbo.tblValue.ValMax

, dbo.tblValue.ValueVal AS Validare

, dbo.tblForm.FormID

,dbo.tblData.ApproveStatus

, dbo.tblData.CodProba

, dbo.tblInstallation.InstallationID

, dbo.tblValue.isRealTest

FROM dbo.tblOrganization

INNER JOIN dbo.tblInstallation

ON dbo.tblOrganization.OrganizationID = dbo.tblInstallation.OrganizationID

INNER JOIN dbo.tblForm

ON dbo.tblInstallation.InstallationID = dbo.tblForm.InstallationID

INNER JOIN dbo.tblData

ON dbo.tblForm.FormID = dbo.tblData.FormID

AND dbo.tblData.IsDeleted = 0 /*common in where*/

INNER JOIN dbo.tblValue

ON dbo.tblData.DataID = dbo.tblValue.DataID

and isnumeric(dbo.tblValue.ValueVal) = 1 /*common in where*/

INNER JOIN dbo.tblParam

ON dbo.tblValue.ParamID = dbo.tblParam.ParamID

LEFT JOIN /*common in where*/

(SELECT dataID

FROM dbo.vwDataSetmeta

WHERE paramTitle = 'PUBLIC' AND valueVal = 'nu') AS EXCLUDED

ON EXCLUDED.dataID=dbo.tblData.DataID

WHERE

/*condition 1*/

(( dbo.tblParam.IsNumeric = convert(bit,1) AND dbo.tblData.ApproveStatus <> 5)

/*condition 2*/*

OR (dbo.tblParam.IsNumeric = convert(bit,0) AND dbo.tblParam.IsCalculated = convert(bit,1)))

/*all conditions*/

AND EXCLUDED.dataID IS NULL /*not EXCLUDED*/

*Have you checked your index placement?

Hadrian Hall of Fame Points: 3742 More actions · Answer 2

Hi,

Thanks so much.I obtained an improvement of speed.