On 3?16?, ??12?57?, "M A Srinivas" <masri...@gmail.com> wrote:
> On Mar 15, 10:43 pm, Matt Sonic <MattSo...@discussions.microsoft.com>
> wrote:
>
>
>
>
>
> > This dynamic query frequently times out. I'm guessing it's because of all
> > the udfs in the select and where clauses so I attempted to make the
> > RevisionStatus and UsageStatus into fields in the EngineeringDocuments table
> > and update them with triggers. This works fine but the RevisionStatus value
> > will change based on an EffectiveDate so I would have to check or update this
> > field daily. I tried running a DTS to update the field in the table (30K
> > records) but it takes too long and ties up the server for others. I have
> > included the GetRevisionStatus and GetUsageStatus udfs below the stored
> > procedure.
>
> > Any suggestion on how to make this happen without tying up the server?
>
> > CREATE PROCEDURE dbo.EngineeringDocuments_SelectByFilters
> > @hideUnapproved bit,
> > @typeId int = NULL,
> > @fileNameLike varchar(50) = NULL,
> > @creator varchar(30) = NULL,
> > @descriptionLike varchar(50) = NULL,
> > @createdStartDate datetime = NULL,
> > @createdEndDate datetime = NULL,
> > @usageStatus nvarchar(8) = NULL,
> > @productSeriesId int = NULL,
> > @partUsageCategoryId int = NULL,
> > @debug bit = 0
> > AS
> > SET NOCOUNT ON;
> > DECLARE @sql nvarchar(4000), @paramlist nvarchar(4000)
>
> > SELECT @sql =
> > 'SELECT TOP 100 PERCENT dbo.Documents.ID AS EngineeringDocumentId,
> > dbo.EngeneeringDocumentTypes_GetTypeName(dbo.Documents.[DOCUMENT TYPE]) AS
> > EngineeringDocumentTypeName,
> > dbo.Documents.[FILE NAME] AS Number,
> > dbo.Documents.HyplinkAdd AS LinkToFile,
> > dbo.EngineeringDocuments_GetRevisionStatus(dbo.Documents.ID, GETDATE()) AS
> > RevisionStatus,
> > dbo.Documents.DESCRIPTION,
> > dbo.Documents.Creator,
> > dbo.Documents.CREATED AS Created,
> > dbo.EngineeringDocuments_GetUsageStatus(dbo.Documents.ID) AS
> > EngineeringDocumentStatus
> > FROM dbo.MasterSheetsLink RIGHT OUTER JOIN
> > dbo.DocProdLink ON dbo.MasterSheetsLink.DocProdLinkID = dbo.DocProdLink.ID
> > RIGHT OUTER JOIN
> > dbo.Documents ON dbo.DocProdLink.DocumentID = dbo.Documents.ID
> > WHERE 1=1'
>
> > IF @hideUnapproved = 1 SELECT @sql = @sql + ' AND
> > (dbo.EngineeringDocuments_CountApprovedEngineeringReleases (dbo.Documents.ID)> 0)'
>
> > IF @typeId IS NOT NULL SELECT @sql = @sql + ' AND (dbo.Documents.[DOCUMENT
> > TYPE] = @xTypeId)'
> > IF @fileNameLike IS NOT NULL SELECT @sql = @sql + ' AND (dbo.Documents.[FILE
> > NAME] LIKE @xfileNameLike)'
> > IF @creator IS NOT NULL SELECT @sql = @sql + ' AND (dbo.Documents.Creator =
> > @xCreator)'
> > IF @descriptionLike IS NOT NULL SELECT @sql = @sql + ' AND
> > (dbo.Documents.DESCRIPTION LIKE @xdescriptionLike)'
> > IF @createdStartDate IS NOT NULL SELECT @sql = @sql + ' AND
> > (dbo.Documents.CREATED >= @xCreatedStartDate)'
> > IF @createdEndDate IS NOT NULL SELECT @sql = @sql + ' AND
> > (dbo.Documents.CREATED <= @xCreatedEndDate)'
> > IF @usageStatus IS NOT NULL SELECT @sql = @sql + ' AND
> > (dbo.EngineeringDocuments_GetUsageStatus(dbo.Documents.ID) = @xusageStatus)'
> > IF @productSeriesId IS NOT NULL SELECT @sql = @sql + ' AND
> > (dbo.DocProdLink.ProductID LIKE @xProductSeriesId)'
> > IF @partUsageCategoryId IS NOT NULL SELECT @sql = @sql + ' and
> > (dbo.MasterSheetsLink.MastShtCatID LIKE @xPartUsageCategoryId)'
>
> > SELECT @sql = @sql + '
> > GROUP BY dbo.Documents.[FILE NAME], dbo.Documents.ID, dbo.Documents.[DIE
> > SORT], dbo.Documents.HyplinkAdd, dbo.Documents.DESCRIPTION,
> > dbo.Documents.Creator, dbo.Documents.CREATED,
> > dbo.EngeneeringDocumentTypes_GetTypeName(dbo.Documents.[DOCUMENT TYPE]),
> > dbo.EngineeringDocuments_GetRevisionStatus(dbo.Documents.ID, GETDATE()),
> > dbo.EngineeringDocuments_GetUsageStatus(dbo.Documents.ID)
> > ORDER BY dbo.Documents.[DIE SORT], dbo.Documents.[FILE NAME]'
>
> > IF @debug = 1
> > PRINT @sql
>
> > SELECT @paramlist = '@xTypeId int, @xfileNameLike varchar(50), @xCreator
> > varchar(30), @xdescriptionLike varchar(50),
> > @xCreatedStartDate datetime, @xCreatedEndDate datetime, @xusageStatus
> > nvarchar(8), @xProductSeriesId varchar(10),
> > @xPartUsageCategoryId varchar(10)'
>
> > EXEC sp_executesql @sql, @paramList, @typeId, @fileNameLike, @creator,
> > @descriptionLike,
> > @createdStartDate, @createdEndDate, @usageStatus, @productSeriesId,
> > @partUsageCategoryId
> > GO
> > ___________________________________________
> > CREATE FUNCTION dbo.EngineeringDocuments_GetRevisionStatus
> > (@EngineeirngDocumentId int, @TodaysDate datetime)
> > RETURNS varchar(20) AS
> > BEGIN
>
> > DECLARE @RevisionStatus varchar(20)
> > SET @RevisionStatus = 'Error'
>
> > DECLARE @CountLiveEngineeringReleases int
> > SELECT @CountLiveEngineeringReleases = COUNT(ID)
> > FROM dbo.RelAssocDocs
> > WHERE (DocumentID = @EngineeirngDocumentId) AND
> > (dbo.EngineeringReleases_IsLive(ReleaseID, @TodaysDate) = 1)
>
> > DECLARE @CountPreliminaryEngineeirngReleases int
> > SELECT @CountPreliminaryEngineeirngReleases = COUNT(ID)
> > FROM dbo.RelAssocDocs
> > WHERE (DocumentID = @EngineeirngDocumentId) AND
> > (dbo.EngineeringReleases_IsLive(ReleaseID, @TodaysDate) = 0)
>
> > IF @CountPreliminaryEngineeirngReleases = 0 AND
> > @CountLiveEngineeringReleases = 0
> > SET @RevisionStatus = 'Not on E.R.'
> > IF @CountPreliminaryEngineeirngReleases > 0 AND
> > @CountLiveEngineeringReleases = 0
> > SET @RevisionStatus = 'Preliminary'
> > IF @CountPreliminaryEngineeirngReleases = 0 AND
> > @CountLiveEngineeringReleases > 0
> > SET @RevisionStatus = 'Current'
> > IF @CountPreliminaryEngineeirngReleases > 0 AND
> > @CountLiveEngineeringReleases > 0
> > SET @RevisionStatus = 'Being Revised'
>
> > RETURN @RevisionStatus
> > END
> > _________________________________________________
> > CREATE FUNCTION dbo.EngineeringDocuments_GetUsageStatus
> > (@EngineeringDocumentId int)
> > RETURNS varchar(10) AS
> > BEGIN
> > DECLARE @status varchar(10)
> > SET @status = 'Inactive'
>
> > DECLARE @count int
> > SELECT @count = COUNT(dbo.DocProdLink.ID)
> > FROM dbo.DocProdLink INNER JOIN
> > dbo.AssocStatus ON dbo.DocProdLink.StatusID =
> > dbo.AssocStatus.ID
> > WHERE (dbo.AssocStatus.STATUS = N'Active') AND
> > (dbo.DocProdLink.DocumentID = @EngineeringDocumentId)
> > IF @count > 0 SET @status = 'Active'
>
> > RETURN @status
> > END
>
> You may have to check the following
>
> 1. Datatypes in parameter list . It should match column data types.
> Example : column data type is samllint and variable are declared as
> int , performance will be a problem
>
> 2. Proper indexes which matches where clause
>
> 3. I am not sure why you are using top 100 percent ? If the number of
> rows returned is high with this query try to batch it
>
> 4. In function, Since you are just checking for count> 0, you can use
> exists instead of count. If possible avoid calling the function- ??????? -
>
> - ??????? -
i have encoutered a similar issue,that i have a table with column
InitialDate(DateTime),Finished_Work_Day(int)
that means this record which stands for one work must finished before
InitialDate + Finished_Work_Day,
so,in my SELECT statement i first use UDF to calclute ExpectedDate
=InitialDate + Finished_Work_Day
with reference to the Holiday table. this prove to be very slow as i
have 1000 thousands more records.
at last,i add column ExpectedDate and use trigger to catch changes at
columns InitialDate and Finished_Work_Day
to update ExpectedDate dispersively,it work very well. i am pleased
this infomation may help you.