Cursor, Set Based Approach
I am replacing cursor logic in a SP to a setbased approach to scale better. My setbased approach seems to be better but it runs very fractionaly faster (execution time) than the cursor approach for a single run in test environment. I think resource cost wise, my set based approach should be better. Number of rows iterated thru this cursor is small (0-150). This particular SP is called over 2000 times in production everyday. Is it worth the trouble changing this if we get only marginally benefits, will my set-based approach work better on a server that has lot of activity (lot of connections etc). Our db server runs at about 75-85% cpu usage daily and this particular SP accounts to 13% CPU usage for 2000+ executions.
If the data set involved in cursors is small, is it worth the trouble changing them to set based approaches?
Am I doing right to change this SP to setbased approach.
View Complete Forum Thread with Replies
Related Forum Messages:
Set Approach Instead Of Cursor
Hi,I am trying a Set Approach instead of Using of Cursor (which works).I am attaching the SQL to create tables and the my Procedure, and apiece of code to execute the Procedure.I would like the Procedure ReplaceTags to work with 'a' the same aswith 'C'.Thanks in advance.Hareesh/*****************************//* Create Tables */IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID =OBJECT_ID(N'GlobalTags') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)DROP TABLE GlobalTagsGOCREATE TABLE GlobalTags(Project VARCHAR(50) NULL,TagName VARCHAR(50) NULL,[Value] VARCHAR(50) NULL)GOIF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID =OBJECT_ID(N'ProductDetails') and OBJECTPROPERTY(id, N'IsUserTable') =1)DROP TABLE ProductDetailsGOCREATE TABLE ProductDetails(Project VARCHAR(50) NULL,KeyName VARCHAR(50) NULL,[Value] VARCHAR(50) NULL)GO/*********************************//* Populate Tables */TRUNCATE TABLE GlobalTagsINSERT INTO GlobalTags (Project, TagName, Value)VALUES('ProjectName', 'FirstName', 'John')INSERT INTO GlobalTags (Project, TagName, Value)VALUES('ProjectName', 'LastName', 'Doe')INSERT INTO GlobalTags (Project, TagName, Value)VALUES('ProjectName', 'PhoneNo', '5248')INSERT INTO GlobalTags (Project, TagName, Value)VALUES('ProjectName', 'ZIPCode', '55555')TRUNCATE TABLE ProductDetailsINSERT INTO ProductDetails (Project, KeyName, Value)VALUES('ProjectName', 'FirstName', '%FirstName%')INSERT INTO ProductDetails (Project, KeyName, Value)VALUES('ProjectName', 'LastName', '%LastName%')INSERT INTO ProductDetails (Project, KeyName, Value)VALUES('ProjectName', 'PhoneNo', '%PhoneNo%')INSERT INTO ProductDetails (Project, KeyName, Value)VALUES('ProjectName', 'ZIPCode', '%ZIPCode%')/****************************//* Procedure */IF EXISTS (SELECT * FROM sysobjects WHERE name = 'ReplaceTags')DROP PROCEDURE ReplaceTagsGOCREATE PROCEDURE ReplaceTags(@aProjectName VARCHAR(50),@aProcessType CHAR(1))ASBEGINDECLARE @TagName VARCHAR(50)DECLARE @Value VARCHAR(50)IF @aProcessType = 'C'BEGINDECLARE REPLACE_CURSOR CURSOR FAST_FORWARD READ_ONLY FORSELECT TagName, Value FROM GlobalTagsWHERE Project = @aProjectNameOPEN REPLACE_CURSORFETCH NEXT FROM REPLACE_CURSOR INTO @TagName, @ValueWHILE (@@FETCH_STATUS = 0)BEGINUPDATE ProductDetailsSETValue =CASE WHEN CHARINDEX('%' + @TagName + '%', Value, 1) > 0THENREPLACE(Value, '%' + @TagName + '%', @Value)ELSEValueENDWHERE Project = @aProjectNameFETCH NEXT FROM REPLACE_CURSOR INTO @TagName, @ValueENDCLOSE REPLACE_CURSORDEALLOCATE REPLACE_CURSORENDELSEBEGINUPDATE ProductDetailsSETValue =CASE WHEN CHARINDEX('%' + GlobalTags.TagName + '%',ProductDetails.Value, 1) > 0 THENREPLACE(ProductDetails.Value, '%' +GlobalTags.TagName + '%', GlobalTags.Value)ELSEProductDetails.ValueENDFROM ProductDetails INNER JOIN GlobalTagsON (ProductDetails.Project = GlobalTags.Project)WHERE ProductDetails.Project = @aProjectNameENDEND/***********************************//* Run Procedure */EXECUTE ReplaceTags 'ProjectName', 'a'-- EXECUTE ReplaceTags 'ProjectName', 'C'SELECT * FROM GlobalTagsSELECT * FROM ProductDetails/* End*//***********************************/
View Replies !
Can A Set Based Approach Help Me Here...
im practicing set based approaches... and what im trying to do is grab each value from a table , scramble it and put it back in the table... i dont want the solution to this as id rather figure it out myself for practice... the thing im stuck at is i can do this with a cursor but i want to avoid cursors in future, how would i use a set based approach to get each value of a table and work with it?
View Replies !
Moving From Access To A Web Based Approach
Hi all. I am not a programer but managed to piece together an Access database that is the backbone of my company. My IT group advised me to move to an SQL server back end and move to a web based front end. My question. I am very comfortible with Access and modify the databases regularly. What front end is most like Access for modifying forms, reports, macros, etc. Thanks!
View Replies !
What's The Best Approach To Time-based Checking From App. To SQL Server?
Hi I have an VB.NET application connected to a SQL Server Express. I want to let the application to run in either "Normal mode" or "Holiday mode" according to current weekday is normal day or state public holiday. My approach is to find out all the public holidays in a year and enter them into a Holiday Table. Then some code in my application constantly check the current weekday against the one in the holiday table, if matches, the application goes into holiday mode. This approach is not perfect as "State public holidays" are confirmed by the state government in the current year and the coming year. So state public holidays are unconfirmed for the third year afterward. The system is required by client to support public holiday in the next 10 years. I wonder what is the best approach to this problem? Thanks
View Replies !
Efficient Store Proc For Paging Very Large DataSet Using Cursor Approach
This approach I found very efficient and FAST when compared to therowcount, or Subquery Approaches.This is before the advent of a ranking function from DB such asROW_NUMBER() in SQL Server 2005 and the likes of it. SoThis one works with SQL2000What do you think?==The Generic paging Cursor Approach in Stored Procedure/*Generic Paging Routine using Cursor approach.--------------------------------------------Built to use with ASPNET custom paging. Just pass the parametersyou your query and it builds the dynamic SQL to return only therequested page.This seems to be working for me. I tried the other two pagingapproaches (1) Paging By RowCount (which has some errors on sorting)and (2) Paging by Subquery (which is too slow). This proceduregoes to show that cursors are not necessarily evil at all times.DON'T FORGET TO HANDLE SQL-INJECTION in your code!ONE CAVEAT/restriction: Primary key type is set to INT. I normallyuse identity column anyway.NOTE: This returns 2 results: 1 for dataset 1 for the total countwhich is useful when consumed by ASPNET or the like.References:http://www.thecodeproject.com/aspne...p?select=820618*/CREATE PROCEDURE uspPagingCursor (@Fields VARCHAR(1000) = '*',@Tables VARCHAR(1000) ,@PK VARCHAR(100) ,@PageSize INT,@PageNumber INT = 1,@Sort VARCHAR(1000) = '',@Filter VARCHAR(2000) = '' ,@Group VARCHAR(1000) = null)AS/*Find the @PK type*/DECLARE @PKTable varchar(100)DECLARE @PKName varchar(100)DECLARE @type varchar(100)DECLARE @prec intIF CHARINDEX('.', @PK) 0BEGINSET @PKTable = SUBSTRING(@PK, 0, CHARINDEX('.',@PK))SET @PKName = SUBSTRING(@PK, CHARINDEX('.',@PK) + 1, LEN(@PK))ENDELSEBEGINSET @PKTable = @TablesSET @PKName = @PKEND/*This is the part removed from orig code for speed.I know my @type is INT always.SELECT @type=t.name, @prec=c.precFROM sysobjects oJOIN syscolumns c on o.id=c.idJOIN systypes t on c.xusertype=t.xusertypeWHERE o.name = @PKTable AND c.name = @PKNameIF CHARINDEX('char', @type) 0SET @type = @type + '(' + CAST(@prec AS varchar) + ')'*/SET @TYPE = ' int 'DECLARE @strPageSize varchar(50)DECLARE @strStartRow varchar(50)DECLARE @strFilter varchar(1000)DECLARE @strGroup varchar(1000)/*Default Sorting*/IF @Sort IS NULL OR @Sort = ''SET @Sort = @PK/*Default Page Number*/IF @PageNumber < 1SET @PageNumber = 1/*Set paging variables.*/SET @strPageSize = CAST(@PageSize AS varchar(50))SET @strStartRow = CAST(((@PageNumber - 1)*@PageSize + 1) ASvarchar(50))/*Set filter & group variables.*/IF @Filter IS NOT NULL AND @Filter != ''SET @strFilter = ' WHERE ' + @Filter + ' 'ELSESET @strFilter = ' WHERE TRUE '/* SET @strFilter = '' */IF @Group IS NOT NULL AND @Group != ''SET @strGroup = ' GROUP BY ' + @Group + ' 'ELSESET @strGroup = ''/*Execute dynamic query*/EXEC('DECLARE @PageSize intSET @PageSize = ' + @strPageSize + 'DECLARE @PK ' + @type + 'DECLARE @tblPK TABLE (PK ' + @type + ' NOT NULL PRIMARY KEY)DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FORSELECT ' + @PK + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup+ ' ORDER BY ' + @Sort + 'OPEN PagingCursorFETCH RELATIVE ' + @strStartRow + ' FROM PagingCursor INTO @PKSET NOCOUNT ONWHILE @PageSize 0 AND @@FETCH_STATUS = 0BEGININSERT @tblPK (PK) VALUES (@PK)FETCH NEXT FROM PagingCursor INTO @PKSET @PageSize = @PageSize - 1ENDCLOSE PagingCursorDEALLOCATE PagingCursorSELECT ' + @Fields + ' FROM ' + @Tables + ', @tblPK tblPK ' +@strFilter+ ' and ' + @PK + ' = tblPK.PK ' + @strGroup + ' ORDER BY '+ @Sort)EXEC('SELECT (COUNT(' +@Pk +') - 1)/' + @strPageSize + ' + 1 AS PageCountFROM ' + @tables + @strFilter)GO==Sample Stored Procedure calling the above proc uspPagingCursor/************************************************** ***********Description:This simply returns page data from tbTransactions Table.Returns:Result Set (WHICH web like ASPNET can consume )Notes:This invokes the generic paging procedure uspPagingCursor.************************************************** ***********/CREATE PROCEDURE uspGetTransactionsPage@PageSize INT,@PageIndex INT = 1,@SortField VARCHAR(1000) = '',@QueryFilter VARCHAR(2000) = ''ASDECLARE @FieldNames VARCHAR(1000)DECLARE @TableNames VARCHAR(1000)DECLARE @PrimaryKey VARCHAR(1000)DECLARE @JoinExpr VARCHAR(1000)IF @SortField = '' SET @SortField = 'SubmitDate DESC'/* an identity/unique column is needed for Paging to work */SET @PrimaryKey = 'rowid'SET @TableNames = ' tbTransaction, tbResponseCode , tbUser'/* Put your SQL SELECT Here */SET @FieldNames ='rowid,MerchantTransactionId,MerchantIdProcessor,TransactionOrigin,SubmitDate,ExpirationDate,TransactionAmount,CustomerName,AccountNumber'/* Put your SQL SELECT JOIN Here */SET @JoinExpr =' tbTransaction.ResponseCode *= tbResponseCode.ResponseCodeand tbTransaction.EmployeeId *= tbUser.userid'IF @QueryFilter = ''SET @QueryFilter = @JoinExprELSESET @QueryFilter = @JoinExpr + ' AND ' + @QueryFilterEXEC uspPagingCursor @FieldNames, @TableNames, @PrimaryKey,@PageSize, @PageIndex, @SortField, @QueryFilter, NULLGO
View Replies !
Cursor Based SQL?
Does anyone have any good references they could recommend on Cursorbased SQL writing? I have to create SQL that can loop though recordssimular to VB loops and I have been told that this is the way to go.Any recommendations would be helpful.
View Replies !
How To Write Set-based SQL Instead Of Cursor
Guys Here's the scenario create table data1 (dealid varchar(6) , datex smalldatetime , Tn INT) insert data1 (dealid , datex , Tn ) values ('12345' , '31-12-2005' , 9999) insert data1 (dealid , datex , Tn ) values ('12345' , '30-11-2005' , 9999) insert data1 (dealid , datex , Tn ) values ('12345' , '31-10-2005' , 9999) insert data1 (dealid , datex , Tn ) values ('98765' , '31-12-2005' , 2) insert data1 (dealid , datex , Tn ) values ('98765' , '30-11-2005' , 1) insert data1 (dealid , datex , Tn ) values ('98765' , '30-11-2005' , 0) select * from data1 I need to update the Tn column from the default 9999 for the 3 rows in this table where the dealid is 12345 based on the value in the datex column so the row with the 'highest ie most recent date' gets a 0. I then need to assign the value 1 to the next highest and so on until all rows (in this case 3) get incrementing integer values. It's easy with a cursor but can't get my head round doing it in a set-based way Any ideas
View Replies !
Avoiding Cursor - Want Set Based Solution
Hi there! Here is my situation: table 'ReceiptHeader' IDCustomerIDDateCreated 1225102/06/2002 1332102/09/2002 1444002/15/2002 table 'ReceiptDiscount' IDDiscountIDReceiptHeaderIDAmount 111210.00 241250.00 311325.00 **a receipt can have multiple discounts** Table 'ReceiptDetail' ReceiptHeaderID LineItemIDTotal 121155.33 131145.33 141241.66 **for this example there is only one line item per receipt** Without using a cursor, i would like to return a result set like this one below using a set based solution... ReceiptIDCustomerIDDiscountTotal 1225160.00155.33 1332125.00145.33 144400.00241.66 Thanks, SF
View Replies !
How To Replace Cursor Based Statements With Set Ba
Hey All, I am trying to convert cursor based stored proc in to set based simple statements stored proc. As this stored proc has created alot of performance issues. I am confuse now as I spent my most of time creating this stored proc. Please advise how can I convert this stored proc into set base simple statment. Thanks in advance. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Procedure [SampleStoredProc] @Var1varchar(20), @Var2varchar(3), @Var3 varchar(2) = 'Dummy' As declare @selectlist varchar(5000) declare @tableBuild varchar(1000) declare @FieldName varchar(50) declare @FieldSelect varchar(500) declare @FieldTitle varchar(50) declare @TableName varchar(50) declare @holdTable varchar(50) declare @title varchar(50) declare @holdTitle varchar(50) declare @PageName varchar(50) declare @sequence varchar(100) declare @extraCriteria varchar(200) declare @holdCriteria varchar(200) declare @insertSQL varchar(5000) declare @ConvertRoutine varchar(500) declare @loopCtrl1 bit declare @loopCtrl2 bit declare @ConvertSQL varchar(5000) declare @PrevValue varchar(50) declare @NewValue varchar(50) declare @ActionTxtvarchar(1) declare @Descriptionvarchar(20) declare @effDatevarchar(10) declare @transEffDatevarchar(10) declare @expDatevarchar(10) declare @lastTransDatevarchar(10) declare @policyStatusvarchar(2) declare @reasAmendDescvarchar(50) declare @policyNumbervarchar(20) declare @riskStatevarchar(20) declare @PriorPremmoney declare @AmendPremmoney declare @PremDiffmoney declare mtcursor cursor for select TableName, FieldName, FieldSelectTxt, FieldTitleTxt, SequenceFieldName, ExtraCriteriaTxt, PageTitleTxt, ConversionRoutineTxt from MyTable1 where Column1 = @Var2 order by PageDisplaySequenceNbr, TableName, ExtraCriteriaTxt, SequenceFieldName open mtcursor fetch next from mtcursor into @TableName, @FieldName, @FieldSelect, @FieldTitle, @sequence, @extraCriteria, @title, @ConvertRoutine set @selectlist = 'Val1, Val2,' + @sequence + ' as UnitNbr' set @tableBuild = 'Create table #tempTable (Val1 varchar, Val2 int, UnitNbr varchar(20)' set @loopCtrl1 = 0 set @loopCtrl2 = 0 WHILE (@loopCtrl1 = 0) begin set @holdTable = @TableName set @holdCriteria = @extraCriteria set @holdTitle = @title if @FieldSelect = '' set @selectlist = @selectlist + ',' + @FieldName else set @selectlist = @selectlist + ',' + @FieldSelect set @tableBuild = @tableBuild + ',' + @FieldName + ' varchar(50)' fetch next from mtcursor into @TableName, @FieldName, @FieldSelect, @FieldTitle, @sequence, @extraCriteria, @title, @ConvertRoutine if @@fetch_status <> 0 set @loopCtrl2 = 1 if (@TableName <> @holdTable) or (@extraCriteria <> @holdCriteria) or (@title <> @holdTitle) or (@loopCtrl2 = 1) begin set @tableBuild = @tableBuild + ')' set @insertSQL = ' declare mtcursor2 cursor for select FieldName, FieldTitleTxt, ExtraUpdateMatchTxt, PullForUpdateInd, PullForAddInd, PullForDeleteInd, PullForAnyUpdateInd from MyTable1 where TableName = ''' + @holdTable + ''' and ExtraCriteriaTxt = ''' + @holdCriteria + ''' and PageTitleTxt = ''' + @holdTitle + ''' and Column1 = ''' + @Var2 + ''' order by FieldDisplaySequenceNbr declare @FieldName varchar(50) declare @FieldTitle varchar(50) declare @ExtraUpdateMatch varchar(500) declare @PullUpdate bit declare @PullAdd bit declare @PullDelete bit declare @PullAnyUpdate bit open mtcursor2 fetch next from mtcursor2 into @FieldName, @FieldTitle, @ExtraUpdateMatch, @PullUpdate, @PullAdd, @PullDelete, @PullAnyUpdate WHILE (@@fetch_status = 0) begin if substring(@FieldTitle,1,1) = ''#'' set @FieldTitle = substring(@FieldTitle,2,len(@FieldTitle) - 1) else set @FieldTitle = '''''''' + @FieldTitle + '''''''' if @PullAnyUpdate = 1 begin exec (''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', A.UnitNbr, ''''' + @holdTitle + ''''', '' + @FieldTitle + '', A.'' + @FieldName + '', B.'' + @FieldName + '', ''''U'''' from #tempTable A left join #tempTable B on B.UnitNbr = A.UnitNbr and B.Val1 = ''''U'''' '' + @ExtraUpdateMatch + '' where A.Val1 = ''''O'''' and B.Val1 = ''''U'''''') end else begin if @PullUpdate = 1 exec (''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', A.UnitNbr, ''''' + @holdTitle + ''''', '' + @FieldTitle + '', A.'' + @FieldName + '', B.'' + @FieldName + '', ''''U'''' from #tempTable A left join #tempTable B on B.UnitNbr = A.UnitNbr and B.Val1 = ''''U'''' '' + @ExtraUpdateMatch + '' where A.Val1 = ''''O'''' and B.Val1 = ''''U'''' and ((A.'' + @FieldName + '' <> B.'' + @FieldName + '') or (A.'' + @FieldName + '' is null and B.'' + @FieldName + '' is not null) or (A.'' + @FieldName + '' is not null and B.'' + @FieldName + '' is null)) '') end if @PullAdd = 1 exec(''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', UnitNbr, ''''' + @holdTitle + ''''','' + @FieldTitle + '', ''''n/a'''', '' + @FieldName + '', ''''A'''' from #tempTable A where Val1 = ''''A'''''') if @PullDelete = 1 exec(''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', UnitNbr, ''''' + @holdTitle + ''''','' + @FieldTitle + '', '' + @FieldName + '', ''''n/a'''', ''''D'''' from #tempTable A where Val1 = ''''D'''''') fetch next from mtcursor2 into @FieldName, @FieldTitle, @ExtraUpdateMatch, @PullUpdate, @PullAdd, @PullDelete, @PullAnyUpdate end close mtcursor2 deallocate mtcursor2' exec (@tableBuild + ' insert into #tempTable select ' + @selectlist + ' from ' + @holdTable + ' where Id = ' + '''' + @Var1 + '''' + @holdCriteria + @insertSQL) set @selectlist = 'Val1, Val2,' + @sequence + ' as UnitNbr' set @tableBuild = 'Create table #tempTable (Val1 varchar, Val2 int, UnitNbr varchar(20)' end if @loopCtrl2 = 1 set @loopCtrl1 = 1 end close mtcursor deallocate mtcursor Delete from MyTable2 where ltrim(rtrim(PreviousValueTxt)) = ltrim(rtrim(EndorsedValueTxt)) and ActionTxt='U' and ID=@Var1 declare deletecursor cursor for select distinct PageNm from MyTable2 where Id = @Var1 and ActionTxt = 'U' open deletecursor fetch next from deletecursor into @PageName while @@fetch_status = 0 begin if (SELECT count(*) from MyTable2 where Id = @Var1 and PageNm = @PageName and ActionTxt = 'U' and PreviousValueTxt <> EndorsedValueTxt ) = 0 DELETE FROM MyTable2 where Id = @Var1 and PageNm = @PageName and ActionTxt = 'U' fetch next from deletecursor into @PageName end close deletecursor deallocate deletecursor declare convertcursor cursor for select a.PreviousValueTxt, a.EndorsedValueTxt, A.EntrySequenceNbr, A.ActionTxt, b.ConversionRoutineTxt from MyTable2 a inner join MyTable1 b on a.PageNm = b.PageTitleTxt and a.FieldNm = b.FieldTitleTxt and b.ConversionRoutineTxt <> '' where a.Id = @Var1 open convertcursor fetch next from convertcursor into @PrevValue, @NewValue, @Sequence, @ActionTxt, @ConvertRoutine while @@fetch_status = 0 begin set @ConvertSQL = 'declare @PrevConverted varchar(50) declare @NewConverted varchar(50)' set @ConvertSQL = @ConvertSQL + ' declare @ConvertInput varchar(50) ' set @ConvertSQL = @ConvertSQL + ' declare @Var3 varchar(2) ' set @ConvertSQL = @ConvertSQL + ' set @Var3 = ''' + @Var3 + '''' if @ActionTxt = 'A' set @ConvertSQL = @ConvertSQL + ' set @PrevConverted = ''' + @PrevValue + '''' else begin set @ConvertSQL = @ConvertSQL + ' set @ConvertInput = ''' + @PrevValue + '''' set @ConvertSQL = @ConvertSQL + ' set @PrevConverted = (' + @ConvertRoutine + ')' end if @ActionTxt = 'D' set @ConvertSQL = @ConvertSQL + ' set @NewConverted = ''' + @NewValue + '''' else begin set @ConvertSQL = @ConvertSQL + ' set @ConvertInput = ''' + @NewValue + '''' set @ConvertSQL = @ConvertSQL + ' set @NewConverted = (' + @ConvertRoutine + ')' end set @ConvertSQL = @ConvertSQL + ' update MyTable2 set PreviousValueTxt = @PrevConverted, EndorsedValueTxt = @NewConverted where EntrySequenceNbr = ''' + @Sequence + '''' exec (@ConvertSQL) fetch next from convertcursor into @PrevValue, @NewValue, @Sequence, @ActionTxt, @ConvertRoutine end close convertcursor deallocate convertcursor if @Var2 = 'PA ' --exec PAConfirmCovConversions @Var1 = @Var1 exec PAConfirmCovConversions @Var1 = @Var1, @Var3 = @Var3 Create table #pageSeqTable (PageTitle varchar(50), PageSeq int) insert into #pageSeqTable select distinct PageTitleTxt, PageDisplaySequenceNbr from MyTable1 where Column1 = @Var2 select PageNm, RowNumber, FieldNm, PreviousValueTxt, EndorsedValueTxt, ActionTxt from MyTable2, #pageSeqTable b where Id = @Var1 and PageNm = b.PageTitle order by b.PageSeq, RowNumber, ActionTxt desc, EntrySequenceNbr select @effDate = convert(char,EffectiveDate,101), @transEffDate = convert(char,TransactionEffectiveDt,101), @expDate = convert(char,LastTransactionEffectiveDt,101), @policyStatus = PolicyStatusCd, @reasAmendDesc = ReasonAmendedDes, @policyNumber = PolicyNumber, @riskState = StateName, @AmendPrem = convert(money,PremiumAmount) from SHPlaninfo A, SHSeleReasonAmended B, SHSeleStateCode C where Id = @Var1 AND Val2 = (select max(Val2) from SHPlanInfo where Id = @Var1) AND B.ReasonAmendedCd = A.ReasonAmendedCd AND C.StateCode = A.RiskState Select @PriorPrem = convert(money,PremiumAmount) FROM SHPlanInfo WHERE Id = @Var1 and Val2 = '0' Set @PremDiff = @AmendPrem - @PriorPrem select EffectiveDate = @effDate select TransactionEffectiveDt = @transEffDate, ExpirationDate = @expDate, LastTransactionEffectiveDt = @lastTransDate select AmendXPolStat = @policyStatus select ReasonAmendedDes = @reasAmendDesc select PolicyNumber = @policyNumber select RiskState = @riskState select PriorPremium = @PriorPrem select AmendPremium = @AmendPrem select PremiumDifference = @PremDiffSelect ClientNumber from SHClient with (nolock) where Id=@Var1 and ApplicantRecordInd = 1 delete from MyTable2 where Id = @Var1 return
View Replies !
Cursor Looping Versus Set-based Queries
I know this question has been asked. And the usual answer is don't usecursors or any other looping method. Instead, try to find a solutionthat uses set-based queries.But this brings up several questions / senarios:* I created several stored procedures that take parameters and insertsthe data into the appropriate tables. This was done for easy access/usefrom client side apps (i.e. web-based).Proper development tactics says to try and do "code reuse". So, if Ialready have stored procs that do my logic, should I be writing asecond way of handling the data? If I ever need to change the way thedata is handled, I now have to make the same change in two (or more)places.* Different data from the same row needs to be inserted into multipletables. "Common sense" (maybe "gut instinct" is better) says to handleeach row as a "unit". Seems weird to process the entire set for onetable, then to process the entire set AGAIN for another table, and thenYET AGAIN for a third table, and so on.* Exception handling. Set based processing means that if one row failsthe entire set fails. Looping through allows you to fail a row butallow everything else to be processed properly. It also allows you togather statistics. (How many failed, how many worked, how many wereskipped, etc.)?? Good idea ?? The alternative is to create a temporary table (sandboxor workspace type thing), copy the data to there along with "status" or"valdation" columns, run through the set many times over looking forany rows that may fail, marking them as such, and then at the end onlydealing with those rows which "passed" the testing. Of course, in orderfor this to work you must know (and duplicate) all constraints so youknow what to look for in your testing.
View Replies !
Declare Cursor Based On Dynamic Query
Hi, I am declaring the cursor based on a query which is generated dynamically. but it is not working Declare @tempSQL varchar(1000) --- This query will be generated based on my other conditon and will be stored in a variable set @tempsql = 'select * from orders' declare cursor test for @tempsql open test This code is not working. please suggest Nitin
View Replies !
Breakout Records Based On UNIT Total Without Cursor
I have a set of revenue records where there is a UNIT column and a REVCHARGE column. What I need to do is breakout the records into single records where the unit count is > 1 and calc the actual charge: Ex: Units REVCHG FIELD_A FIELD_B ..... 3 3.00 ABCD EFGH Needs to be converted to: Units REVCHG FIELD_A FIELD_B ..... 1 1.00 ABCD EFGH 1 1.00 ABCD EFGH 1 1.00 ABCD EFGH The calc is obvious but how can I do this with a cursor but would like to do it without a cursor if possible? Anybody got an idea? Thanks.
View Replies !
Moving Average Using Select Statement Or Cursor Based?
ID DATE(dd/mm/yy) TYPE QTYIN COST_IN_AMT COST_OUT_AMT(MOVING AVERAGE) 1 01/01/2007 PURCHASE 10 1000 2 01/01/2007 PURCHAES 5 1100 3 01/01/2007 SALES -5 *TobeCalculated 4 02/01/2007 Purchase 20 9000 5 02/01/2007 SALES -10 *TobeCalculated 5 02/01/2007 purchase 50 8000 6 03/01/2007 Sales -10 *TobeCalculate 7 01/01/2007 Purchase 20 12000 I have a table when user add new sales or puchase will be added to this table ITEM_TXNS. The above date is part of the table for a ProductID . (The field is removed here) In order to calculate the balance amount using moving average, I must calculated the cost_out_amt first on the fly. When user add new sales I also need to determine the cost/unit for a product id using moving average. The problem is I can not just use sum, because i need to determine cost_out_amt for each sales first which will be calculated on the fly. The reason i dont store the cost_out_amt (instead calculate on the fly) because User could Edit the previous sales/purchase txn or Insert new sales for a previous date. Example THe record with ID 9. By Adding this txn with ID 9, would cause all the cost_out_amt will be incorrect (Using moving Average) if i store the cost_amout_out on entrying txn and need to be recalculated. Instead I just want to calculate on the fly and able to determine the cost avr for a specific point of time. Should I just use Cursor and loop all the record and calculate the cost or maybe I can just use on Select Statement?
View Replies !
How To Convert Cursor Based Stored Proc In Set Based Simple Stored Proc.
Hey All, I am trying to convert cursor based stored proc in to set based simple statements stored proc. As this stored proc has created alot of performance issues. I am confuse now as I spent my most of time creating this stored proc. Please advise how can I convert this stored proc into set base simple statment. Thanks in advance. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Procedure [SampleStoredProc] @Var1 varchar(20), @Var2 varchar(3), @Var3 varchar(2) = 'Dummy' As declare @selectlist varchar(5000) declare @tableBuild varchar(1000) declare @FieldName varchar(50) declare @FieldSelect varchar(500) declare @FieldTitle varchar(50) declare @TableName varchar(50) declare @holdTable varchar(50) declare @title varchar(50) declare @holdTitle varchar(50) declare @PageName varchar(50) declare @sequence varchar(100) declare @extraCriteria varchar(200) declare @holdCriteria varchar(200) declare @insertSQL varchar(5000) declare @ConvertRoutine varchar(500) declare @loopCtrl1 bit declare @loopCtrl2 bit declare @ConvertSQL varchar(5000) declare @PrevValue varchar(50) declare @NewValue varchar(50) declare @ActionTxt varchar(1) declare @Description varchar(20) declare @effDate varchar(10) declare @transEffDate varchar(10) declare @expDate varchar(10) declare @lastTransDate varchar(10) declare @policyStatus varchar(2) declare @reasAmendDesc varchar(50) declare @policyNumber varchar(20) declare @riskState varchar(20) declare @PriorPrem money declare @AmendPrem money declare @PremDiff money declare mtcursor cursor for select TableName, FieldName, FieldSelectTxt, FieldTitleTxt, SequenceFieldName, ExtraCriteriaTxt, PageTitleTxt, ConversionRoutineTxt from MyTable1 where Column1 = @Var2 order by PageDisplaySequenceNbr, TableName, ExtraCriteriaTxt, SequenceFieldName open mtcursor fetch next from mtcursor into @TableName, @FieldName, @FieldSelect, @FieldTitle, @sequence, @extraCriteria, @title, @ConvertRoutine set @selectlist = 'Val1, Val2,' + @sequence + ' as UnitNbr' set @tableBuild = 'Create table #tempTable (Val1 varchar, Val2 int, UnitNbr varchar(20)' set @loopCtrl1 = 0 set @loopCtrl2 = 0 WHILE (@loopCtrl1 = 0) begin set @holdTable = @TableName set @holdCriteria = @extraCriteria set @holdTitle = @title if @FieldSelect = '' set @selectlist = @selectlist + ',' + @FieldName else set @selectlist = @selectlist + ',' + @FieldSelect set @tableBuild = @tableBuild + ',' + @FieldName + ' varchar(50)' fetch next from mtcursor into @TableName, @FieldName, @FieldSelect, @FieldTitle, @sequence, @extraCriteria, @title, @ConvertRoutine if @@fetch_status <> 0 set @loopCtrl2 = 1 if (@TableName <> @holdTable) or (@extraCriteria <> @holdCriteria) or (@title <> @holdTitle) or (@loopCtrl2 = 1) begin set @tableBuild = @tableBuild + ')' set @insertSQL = ' declare mtcursor2 cursor for select FieldName, FieldTitleTxt, ExtraUpdateMatchTxt, PullForUpdateInd, PullForAddInd, PullForDeleteInd, PullForAnyUpdateInd from MyTable1 where TableName = ''' + @holdTable + ''' and ExtraCriteriaTxt = ''' + @holdCriteria + ''' and PageTitleTxt = ''' + @holdTitle + ''' and Column1 = ''' + @Var2 + ''' order by FieldDisplaySequenceNbr declare @FieldName varchar(50) declare @FieldTitle varchar(50) declare @ExtraUpdateMatch varchar(500) declare @PullUpdate bit declare @PullAdd bit declare @PullDelete bit declare @PullAnyUpdate bit open mtcursor2 fetch next from mtcursor2 into @FieldName, @FieldTitle, @ExtraUpdateMatch, @PullUpdate, @PullAdd, @PullDelete, @PullAnyUpdate WHILE (@@fetch_status = 0) begin if substring(@FieldTitle,1,1) = ''#'' set @FieldTitle = substring(@FieldTitle,2,len(@FieldTitle) - 1) else set @FieldTitle = '''''''' + @FieldTitle + '''''''' if @PullAnyUpdate = 1 begin exec (''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', A.UnitNbr, ''''' + @holdTitle + ''''', '' + @FieldTitle + '', A.'' + @FieldName + '', B.'' + @FieldName + '', ''''U'''' from #tempTable A left join #tempTable B on B.UnitNbr = A.UnitNbr and B.Val1 = ''''U'''' '' + @ExtraUpdateMatch + '' where A.Val1 = ''''O'''' and B.Val1 = ''''U'''''') end else begin if @PullUpdate = 1 exec (''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', A.UnitNbr, ''''' + @holdTitle + ''''', '' + @FieldTitle + '', A.'' + @FieldName + '', B.'' + @FieldName + '', ''''U'''' from #tempTable A left join #tempTable B on B.UnitNbr = A.UnitNbr and B.Val1 = ''''U'''' '' + @ExtraUpdateMatch + '' where A.Val1 = ''''O'''' and B.Val1 = ''''U'''' and ((A.'' + @FieldName + '' <> B.'' + @FieldName + '') or (A.'' + @FieldName + '' is null and B.'' + @FieldName + '' is not null) or (A.'' + @FieldName + '' is not null and B.'' + @FieldName + '' is null)) '') end if @PullAdd = 1 exec(''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', UnitNbr, ''''' + @holdTitle + ''''','' + @FieldTitle + '', ''''n/a'''', '' + @FieldName + '', ''''A'''' from #tempTable A where Val1 = ''''A'''''') if @PullDelete = 1 exec(''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', UnitNbr, ''''' + @holdTitle + ''''','' + @FieldTitle + '', '' + @FieldName + '', ''''n/a'''', ''''D'''' from #tempTable A where Val1 = ''''D'''''') fetch next from mtcursor2 into @FieldName, @FieldTitle, @ExtraUpdateMatch, @PullUpdate, @PullAdd, @PullDelete, @PullAnyUpdate end close mtcursor2 deallocate mtcursor2' exec (@tableBuild + ' insert into #tempTable select ' + @selectlist + ' from ' + @holdTable + ' where Id = ' + '''' + @Var1 + '''' + @holdCriteria + @insertSQL) set @selectlist = 'Val1, Val2,' + @sequence + ' as UnitNbr' set @tableBuild = 'Create table #tempTable (Val1 varchar, Val2 int, UnitNbr varchar(20)' end if @loopCtrl2 = 1 set @loopCtrl1 = 1 end close mtcursor deallocate mtcursor Delete from MyTable2 where ltrim(rtrim(PreviousValueTxt)) = ltrim(rtrim(EndorsedValueTxt)) and ActionTxt='U' and ID=@Var1 declare deletecursor cursor for select distinct PageNm from MyTable2 where Id = @Var1 and ActionTxt = 'U' open deletecursor fetch next from deletecursor into @PageName while @@fetch_status = 0 begin if (SELECT count(*) from MyTable2 where Id = @Var1 and PageNm = @PageName and ActionTxt = 'U' and PreviousValueTxt <> EndorsedValueTxt ) = 0 DELETE FROM MyTable2 where Id = @Var1 and PageNm = @PageName and ActionTxt = 'U' fetch next from deletecursor into @PageName end close deletecursor deallocate deletecursor declare convertcursor cursor for select a.PreviousValueTxt, a.EndorsedValueTxt, A.EntrySequenceNbr, A.ActionTxt, b.ConversionRoutineTxt from MyTable2 a inner join MyTable1 b on a.PageNm = b.PageTitleTxt and a.FieldNm = b.FieldTitleTxt and b.ConversionRoutineTxt <> '' where a.Id = @Var1 open convertcursor fetch next from convertcursor into @PrevValue, @NewValue, @Sequence, @ActionTxt, @ConvertRoutine while @@fetch_status = 0 begin set @ConvertSQL = 'declare @PrevConverted varchar(50) declare @NewConverted varchar(50)' set @ConvertSQL = @ConvertSQL + ' declare @ConvertInput varchar(50) ' -- start 33385 set @ConvertSQL = @ConvertSQL + ' declare @Var3 varchar(2) ' set @ConvertSQL = @ConvertSQL + ' set @Var3 = ''' + @Var3 + '''' --end 33385 if @ActionTxt = 'A' set @ConvertSQL = @ConvertSQL + ' set @PrevConverted = ''' + @PrevValue + '''' else begin set @ConvertSQL = @ConvertSQL + ' set @ConvertInput = ''' + @PrevValue + '''' set @ConvertSQL = @ConvertSQL + ' set @PrevConverted = (' + @ConvertRoutine + ')' end if @ActionTxt = 'D' set @ConvertSQL = @ConvertSQL + ' set @NewConverted = ''' + @NewValue + '''' else begin set @ConvertSQL = @ConvertSQL + ' set @ConvertInput = ''' + @NewValue + '''' set @ConvertSQL = @ConvertSQL + ' set @NewConverted = (' + @ConvertRoutine + ')' end set @ConvertSQL = @ConvertSQL + ' update MyTable2 set PreviousValueTxt = @PrevConverted, EndorsedValueTxt = @NewConverted where EntrySequenceNbr = ''' + @Sequence + '''' exec (@ConvertSQL) fetch next from convertcursor into @PrevValue, @NewValue, @Sequence, @ActionTxt, @ConvertRoutine end close convertcursor deallocate convertcursor /* LOB-specific data conversions */ if @Var2 = 'PA ' --exec PAConfirmCovConversions @Var1 = @Var1 exec PAConfirmCovConversions @Var1 = @Var1, @Var3 = @Var3 -- End issue 33385 Create table #pageSeqTable (PageTitle varchar(50), PageSeq int) insert into #pageSeqTable select distinct PageTitleTxt, PageDisplaySequenceNbr from MyTable1 where Column1 = @Var2 select PageNm, RowNumber, FieldNm, PreviousValueTxt, EndorsedValueTxt, ActionTxt from MyTable2, #pageSeqTable b where Id = @Var1 and PageNm = b.PageTitle order by b.PageSeq, RowNumber, ActionTxt desc, EntrySequenceNbr select @effDate = convert(char,EffectiveDate,101), @transEffDate = convert(char,TransactionEffectiveDt,101), @expDate = convert(char,LastTransactionEffectiveDt,101), @policyStatus = PolicyStatusCd, @reasAmendDesc = ReasonAmendedDes, @policyNumber = PolicyNumber, @riskState = StateName, @AmendPrem = convert(money,PremiumAmount) /* Case 33385 */ from SHPlaninfo A, SHSeleReasonAmended B, SHSeleStateCode C where Id = @Var1 AND Val2 = (select max(Val2) from SHPlanInfo where Id = @Var1) AND B.ReasonAmendedCd = A.ReasonAmendedCd AND C.StateCode = A.RiskState Select @PriorPrem = convert(money,PremiumAmount) FROM SHPlanInfo WHERE Id = @Var1 and Val2 = '0' Set @PremDiff = @AmendPrem - @PriorPrem select EffectiveDate = @effDate select TransactionEffectiveDt = @transEffDate, ExpirationDate = @expDate, LastTransactionEffectiveDt = @lastTransDate select AmendXPolStat = @policyStatus select ReasonAmendedDes = @reasAmendDesc select PolicyNumber = @policyNumber select RiskState = @riskState select PriorPremium = @PriorPrem select AmendPremium = @AmendPrem select PremiumDifference = @PremDiff Select ClientNumber from SHClient with (nolock) where Id=@Var1 and ApplicantRecordInd = 1 delete from MyTable2 where Id = @Var1 return
View Replies !
Help Cursor Based Stored Procedure Is Getting Slower And Slower!
I am begginner at best so I hope someone that is better can help.I have a stored procedure that updates a view that I wrote using 2cursors.(Kind of a Inner Loop) I wrote it this way Because I couldn'tdo it using reqular transact SQL.The problem is that this procedure is taking longer and longer to run.Up to 5 hours now! It is anaylizing about 30,000 records. I thinkpartly because we add new records every month.The procedure works like this.The first Cursor stores a unique account and duedate combination fromthe view.It then finds all the accts in the view that have that account duedatecombo and loads them into Cursor 2 this groups them together for datamanipulation. The accounts have to be grouped this way because aaccount can have different due dates and multiple records within eachaccount due date combo and they need to be looked at this way aslittle singular groups.Here is my procedure I hope someone can shead some light on this. Myboss is giving me heck about it. (I think he thinks Girls cant code!)I got this far I hope someone can help me optimize it further.CREATE PROCEDURE dbo.sp_PromiseStatusASBEGINSET NOCOUNT ON/* Global variables */DECLARE @tot_pay moneyDECLARE @rec_upd VARCHAR(1)DECLARE @todays_date varchar(12)DECLARE @mActivityDate2_temp datetimeDECLARE @tot_paydate datetime/* variables for cursor ACT_CUR1*/DECLARE @mAcct_Num1 BIGINTDECLARE @mDueDate1 datetime/* variables for ACT_CUR2 */DECLARE @mAcct_Num2 BIGINTDECLARE @mActivity_Date2 datetimeDECLARE @mPromise_Amt_1 moneyDECLARE @mPromise_Status varchar(3)DECLARE @mCurrent_Due_Amt moneyDECLARE @mDPD intDECLARE @mPromise_Date datetimeSELECT @todays_date =''+CAST(DATEPART(mm,getdate()) AS varchar(2))+'/'+CAST(DATEPART(dd,getdate()) AS varchar(2))+'/'+CAST(DATEPART(yyyy,getdate()) AS varchar(4))+''DECLARE ACT_CUR1 CURSOR FORSELECT DISTINCTA.ACCT_NUM,A.DUE_DATEFROM VWAPPLICABLEPROMISEACTIVITYRECORDS AOPEN ACT_CUR1FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1WHILE (@@FETCH_STATUS = 0)BEGINSELECT @rec_upd = 'N 'DECLARE ACT_CUR2 CURSOR FORSELECTB.ACCT_NUM,B.ACTIVITY_DATE,B.PROMISE_AMT_1,B.PROMISE_STATUS,B.CURRENT_DUE_AMT,B.DAYS_DELINQUENT_NUM,B.PROMISE_DATE_1FROM VWAPPLICABLEPROMISEACTIVITYRECORDS B (UPDLOCK)WHERE B.ACCT_NUM = @mAcct_Num1ANDB.DUE_DATE = @mDueDate1ORDER BY B.ACCT_NUM,B.DUE_DATE,B.ACTIVITY_DATE,CASEB.Time_ObtainedWHEN 0 THEN 0ELSE 1END Desc, B.Time_ObtainedOPEN ACT_CUR2FETCH NEXT FROM ACT_CUR2INTO @mAcct_Num2 ,@mActivity_Date2,@mPromise_Amt_1,@mPromise_Status ,@mCurrent_Due_Amt,@mDPD,@mPromise_DateWHILE (@@FETCH_STATUS = 0)BEGIN----CHECK------------------------------------------------------------------------DECLARE @PrintVariable2 VARCHAR (8000)--SELECT @PrintVariable2 = CAST(@MACCT_NUM2 AS VARCHAR)+''+CAST(@MACTIVITY_DATE2 AS VARCHAR)+' '+CAST(@MPROMISE_AMT_1 ASVARCHAR)+' '+CAST(@MPROMISE_STATUS AS VARCHAR)+''+CAST(@mCurrent_Due_Amt AS VARCHAR)+' '+CAST(@mDPD AS VARCHAR)+''+CAST(@mPromise_Date AS VARCHAR)--PRINT @PrintVariable2----ENDCHECK------------------------------------------------------------IF @mDPD >= 30BEGINSELECT @tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))FROM vwAplicablePayments CWHERE C.ACCT_NUM = @mAcct_Num2ANDC.ACTIVITY_DATE >= @mActivity_Date2ANDC.ACTIVITY_DATE < @mActivity_Date2 + 15----CHECK------------------------------------------------------------------------DECLARE @PrintVariable3 VARCHAR (8000)--SELECT @PrintVariable3 ='Greater=30 DOLLARS COLLECTED'--PRINT @PrintVariable3----ENDCHECK------------------------------------------------------------ENDELSE IF @mDPD < 30BEGINSELECT @tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))FROM vwAplicablePayments CWHERE C.ACCT_NUM = @mAcct_Num2ANDC.ACTIVITY_DATE >= @mActivity_Date2ANDC.ACTIVITY_DATE BETWEEN @mActivity_Date2 AND@mPromise_Date + 5----CHECK----------------------------------------------------------------------DECLARE @PrintVariable4 VARCHAR (8000)--SELECT @PrintVariable4 ='Less 30 DOLLARS COLLECTED'--PRINT @PrintVariable4----END CHECK------------------------------------------------------------END----------------------------------------MY REVISEDLOGIC-------------------------------------------------------IF @rec_upd = 'N'BEGINIF @mDPD >= 30BEGINSELECT @mActivityDate2_temp = @mActivity_Date2 + 15--DECLARE @PrintVariable5 VARCHAR (8000)--SELECT @PrintVariable5 =' GREATER= 30 USING ACTVITY_DATE+15'--PRINT @PrintVariable5ENDELSE IF @mDPD < 30BEGINSELECT @mActivityDate2_temp = @mPromise_Date + 5--DECLARE @PrintVariable6 VARCHAR (8000)--SELECT @PrintVariable6 =' LESS 30 USING PROMISE_DATE+5'--PRINT @PrintVariable6ENDIF @tot_pay >= 0.9 * @mCurrent_Due_Amt--used to be promise amtBEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET PROMISE_STATUS = 'PK',TOTAL_DOLLARS_COLL = @tot_payWHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto PK.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDSELECT @rec_upd = 'Y 'ENDIF ((@tot_pay < 0.9 * @mCurrent_Due_Amt) OR @tot_pay IS NULL)AND( @mActivityDate2_temp > @todays_date )--need to put 1dayof month here for snapshot9/01/2004BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'OP'WHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto OP which is the original Activity Date.--The record will hold this date until it goes into PK,PB,orIP.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @mActivity_Date2WHERE CURRENT OF ACT_CUR2ENDENDELSE IF ((@tot_pay < 0.9 * @mCurrent_Due_Amt) OR @tot_pay ISNULL)AND( @mActivityDate2_temp <= @todays_date )--need to put 1dayof month here for snapshot 9/01/2004BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'PB',TOTAL_DOLLARS_COLL = case when @tot_pay is nullthen 0 else @tot_pay endWHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto PB.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDENDENDELSE IF @rec_upd = 'Y'BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'IP',TOTAL_DOLLARS_COLL = 0WHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto IP.IF @mPromise_Status NOT IN ('IP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDENDFETCH NEXT FROM ACT_CUR2 INTO @mAcct_Num2,@mActivity_Date2,@mPromise_Amt_1,@mPromise_Status ,@mCurrent_Due_Amt,@mDPD,@mPromise_DateENDCLOSE ACT_CUR2DEALLOCATE ACT_CUR2FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1ENDCLOSE ACT_CUR1DEALLOCATE ACT_CUR1SET NOCOUNT OFFENDGO
View Replies !
Dynamic Cursor Versus Forward Only Cursor Gives Poor Performance
Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin
View Replies !
Could Not Complete Cursor Operation Because The Set Options Have Changed Since The Cursor Was Declared.
I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker I get the following error can you please explain this problem to me so I can over come the issue. Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31 Could not complete cursor operation because the set options have changed since the cursor was declared. Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32 Could not complete cursor operation because the set options have changed since the cursor was declared. Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153 Cursor is not open. here is the stored procedure: Alter PROCEDURE [dbo].[sp_MSforeachsp] @command1 nvarchar(2000) , @replacechar nchar(1) = N'?' , @command2 nvarchar(2000) = null , @command3 nvarchar(2000) = null , @whereand nvarchar(2000) = null , @precommand nvarchar(2000) = null , @postcommand nvarchar(2000) = null AS /* This procedure belongs in the "master" database so it is acessible to all databases */ /* This proc returns one or more rows for each stored procedure */ /* @precommand and @postcommand may be used to force a single result set via a temp table. */ declare @retval int if (@precommand is not null) EXECUTE(@precommand) /* Create the select */ EXECUTE(N'declare hCForEachTable cursor global for SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME) FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = ''PROCEDURE'' AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 ' + @whereand) select @retval = @@error if (@retval = 0) EXECUTE @retval = [dbo].sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0 if (@retval = 0 and @postcommand is not null) EXECUTE(@postcommand) RETURN @retval GO example useage: EXEC sp_MSforeachsp @command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]" GO
View Replies !
Best Approach With DTS
Let me see if I can explain this. I have the need to pull data from multiple tables from a DB2 system via ODBC and update or insert as needed into tables in a SQL200 DB. Step 1. The data from the initial parent table will need to be limited to being a set number of days old, which I have in place and working. Step 2 The next tables data needs to be limited from the data retrieved in step 1 (I’d like to use the paprent table retrieved in step 1, that is in SQL now, rather than doing it on the DB2 side. Step 3 The returned rows here, need to be limited to key values returned from step 2 Additional steps apply, but nearly all will be limited to the results of parent tables from the prior step. What is the best approach to this? I really want to pull table A to SQL, and limit the next child set from Table A, that was pulled to SQL in the prior step. I also need to do updates rather than dropping and creating the needed tables each time. Insert if no key exists, etc .etc. What is the best approach?
View Replies !
Best Approach
I have what I feel like is a simple package I am working to create. I am teaching myself SSIS as I go along. Source server SQL 2000 database allows NULL values in columns. Destination Server also SQL 2000 but the database required a value in each column. So I do a basic source select what I want. I next need to read the values and determine if null then insert a space, do some column matching and insert them into the destination sever. I believe I should use a Derived Column and an expression ISNULL to accomplish what I want. Maybe there is a better way. Suggestion and comment appreciated. Ryan
View Replies !
Help With Using The Right Approach
Greetings my friends I am attempting to solve the following problem using SSIS, actually I am attempting to convert a SQL Server 2000 DTS package in to a SSIS package. The package does the following : 1) Retrieve the maximum Price_ID (PK) from a PRICE dimension table. 2) Populate a staging table with data coming from a source system where the PRICE_ID > (Price_ID from above) 3) Update the actual DIM table with the new data help in the staging table. For this task I want to learn the use of the Lookup component which I think is appropriate. My questions are as follows : If I create a global variable to hold the maximum PRICE_ID (see point 1). How do I get to use the variable in the my Data Flow Data source?! I am totally confused... I don't even know where to start with this. Your help would be appreciated. Thanks SQL friends.
View Replies !
Best Approach
I've been banging my head for a while now, and it is sore! :-P I'm a best practice/Microsoft approach type of person and want to make sure I do things correctly. I have a database, kind of like a forum. Obviously executing multiple queries in one "batch" (stored proc) would have an impact on the performance. Now, I would like to give a more detailed/specific error back to the caller (either by aid of error code or whatever) with such situations like... "EditReply" Edit reply takes the threadID, replyID and userID. Before actually commiting the changes, it needs to check: 1) does the user exist in the database? (during the editing of the reply, perhaps the user may have been deleted before running the stored proc, who knows) 2) does the thread exist? 3) does the reply exist? if the conditions are met, only then will it go ahead and update the database. Now that is 3 queries, and 4 statements overall to make a change to a field/table. Obviously if one of the commands returns false, in other words if say "does the thread exist" returns 0 (thread doesnt exist) it will return back to the caller an errorcode, which they will handle in their application. Thats all fine but the question is Am I doing this correctly? (no) - how can I improve this? What do I need to think about? Of course I would like to give a more detailed error back to the caller (aid of errorcode designed in the application overall) instead of just "no, databases not updated". In this situation, am I wrongly assuming that the database designers use this type of approach? Please help, I value your feedback and suggestions. I want to improve and think of the right lines of doing these things.
View Replies !
Appropriate MDX Approach ???
Hello all. I use MDX on a cube which provides data about animal population. The cube contains the keyfigure "ANIMALS" that takes the number of animals. The cube has a dimension "VERSION" which is used to identify the keyfigure as a target or an actual value (possible values: "actual" and "target") The cube has another dimension "ZONE" for the population zones. Possible values for zones: "A", "B", "C" and "D". Now I want to create an MDX statement, that gives me a result row like this: Actual number of animals (as sum of all 4 zones) in column no. 1, Target number of animals (as sum of all 4 zones) in column no. 2, Achieved percentage (as actual number / target number * 100) in column no. 3. Until here my statement works and it looks like this: WITH MEMBER [VERSION].[achieved] AS '[VERSION].[actual] / [VERSION].[target] * 100' SELECT {[VERSION].[actual], [VERSION].[target], [VERSION].[achieved]} on COLUMNS FROM [$MYCUBE] WHERE ([Measures].[ANIMALS]) It surely is possible that the achieved value for all zones together is equal to or greater than 100%, while single zones might have an achieved values less than 100%. In order to account on this, i would like column no. 4 to display one of these words: "ok" if none of the single zones has an achieved value smaller than 100%, "warning" if any of the single zones has an achieved value between 96 and 99%, "alert" if any of the single zones has an achieved value smaller than 95%. That means, i want e.g. the word "yellow" if the lowest achieved value of the 4 zones is between 96 and 99. I want to have "red" if the lowest value is smaller than 95. I am quite new to MDX and I have struggled quite a long time with this. I would be grateful for a hint on how i have to modify / enhance my MDX statement. Regards. Peter.
View Replies !
Best Way To Approach This
I've been working on this project, and had it working in MySQL, but it was badly done and couldn't last more than a few hours without growing so large that everything slowed way down. I don't expect anyone to tell me exactly what to do, just please provide an outline of what the best way to approach this in SQL Server 2005 is. To simplify it, I have one table "Items" and another table "ItemPrices". Items has an id and a name. Each row in ItemPrices has an id for the item, a price and two datestamps (added, last updated). On average, there's about 15,000 active items, 50% of them have new prices every couple minutes, so I'm looking at what seems like a ton of data being constantly imported. There's probably a good way to do this but I only know the bad way :) So.. what I want to be able to do is have maybe a stored procedure (?) that takes the item name and price as parameters. (In MySQL I was using "INSERT... ON DUPLICATE KEY UPDATE") A. If it's a new item name, it will add a row to the Items table and a row to ItemPrices B. If it's an existing item with the same price as the current price (the most recent price for that item in ItemPrices) it will update the "last updated" date field C. If it's a new price it will insert a row into ItemPrices for that item Also, I want historical pricing data, but if I ever release this, 95% of the users will just be looking at current prices. I need the current prices to be very fast to query, in my MySQL version I was using something like this: "SELECT... join on lastupdated=(SELECT Max(lastupdated) FROM ItemPrices ...", after I had 300k price updates querying a list of items took like 15 seconds.. there's got to be a better way? What should I do to make this faster? Does this make any sense? Hopefully someone can lead me in the right direction. Thank you very much!
View Replies !
Join Cursor With Table Outside Of Cursor
part 1 Declare @SQLCMD varchar(5000) DECLARE @DBNAME VARCHAR (5000) DECLARE DBCur CURSOR FOR SELECT U_OB_DB FROM [@OB_TB04_COMPDATA] OPEN DBCur FETCH NEXT FROM DBCur INTO @DBNAME WHILE @@FETCH_STATUS = 0 BEGIN SELECT @SQLCMD = 'SELECT T0.CARDCODE, T0.U_OB_TID AS TRANSID, T0.DOCNUM AS INV_NO, ' + + 'T0.DOCDATE AS INV_DATE, T0.DOCTOTAL AS INV_AMT, T0.U_OB_DONO AS DONO ' + + 'FROM ' + @DBNAME + '.dbo.OINV T0 WHERE T0.U_OB_TID IS NOT NULL' EXEC(@SQLCMD) PRINT @SQLCMD FETCH NEXT FROM DBCur INTO @DBNAME END CLOSE DBCur DEALLOCATE DBCur Part 2 SELECT T4.U_OB_PCOMP AS PARENTCOMP, T0.CARDCODE, T0.CARDNAME, ISNULL(T0.U_OB_TID,'') AS TRANSID, T0.DOCNUM AS SONO, T0.DOCDATE AS SODATE, SUM(T1.QUANTITY) AS SOQTY, T0.DOCTOTAL - T0.TOTALEXPNS AS SO_AMT, T3.DOCNUM AS DONO, T3.DOCDATE AS DO_DATE, SUM(T2.QUANTITY) AS DOQTY, T3.DOCTOTAL - T3.TOTALEXPNS AS DO_AMT INTO #MAIN FROM ORDR T0 JOIN RDR1 T1 ON T0.DOCENTRY = T1.DOCENTRY LEFT JOIN DLN1 T2 ON T1.DOCENTRY = T2.BASEENTRY AND T1.LINENUM = T2.BASELINE AND T2.BASETYPE = T0.OBJTYPE LEFT JOIN ODLN T3 ON T2.DOCENTRY = T3.DOCENTRY LEFT JOIN OCRD T4 ON T0.CARDCODE = T4.CARDCODE WHERE ISNULL(T0.U_OB_TID,0) <> 0 GROUP BY T4.U_OB_PCOMP, T0.CARDCODE,T0.CARDNAME, T0.U_OB_TID, T0.DOCNUM, T0.DOCDATE, T3.DOCNUM, T3.DOCDATE, T0.DOCTOTAL, T3.DOCTOTAL, T3.TOTALEXPNS, T0.TOTALEXPNS my question is, how to join the part 1 n part 2? is there posibility?
View Replies !
SQL OPENXML: Best Approach
Hello everyone. I am new to.Net and here is what I have to do. I needto update a SQL table with data coming from a XML file. I have seen some Microsoft documentation on this (the nice SQL statement that updates and inserts in the same stored procedure) but I don'tknow what is the best approach for passing my XML file to the stored procedure. The XML contains about 12 000 records, kind of phonebook info (name, email, phone). What would be the best approach to do this? What objects should I use? Thanks a million, Ben
View Replies !
SqlBulkInsert ?? Or Better Approach?
Hello, I'm doing my best here, but need some help. I have a client that has a company list that they want searched by key word. This is exported from another program (in excel) that they want used and searched on their website. Bad news, is each Keyword is listed with the company separately. So if a company has 5 different key words, they will be listed in the excel file 5 times. The info I have is Name, Address, City, State, Phone, Keywords: So example of excel is:Company A, 123 Main St, Mycity, Mystate, 123-123-1234, Green Company A, 123 Main St, Mycity, Mystate, 123-123-1234, Furry Company A, 123 Main St, Mycity, Mystate, 123-123-1234, Large Company A, 123 Main St, Mycity, Mystate, 123-123-1234, Circular Company B, 746 Sparrow Ave, Diffcity, Diffstate, 987-987-9876, Blue Company B, 746 Sparrow Ave, Diffcity, Diffstate, 987-987-9876, Furry Company B, 746 Sparrow Ave, Diffcity, Diffstate, 987-987-9876, Small I am able to import this large (4.2 MB) file into a table called fctable What i'm trying to do is write SQL scripts or queries that can insert into a Company Table and Keyword Table. I'm trying to write this through asp.net 2.0 (so the excel file is uploaded) and have tried to write my inserts like INSERT INTO [Company] ([Name], [address], [City], [State], [Phone]) VALUES (SELECT DISTINCT Name, Address, City, State, Phone FROM fctable ) But that doesn't seem to be working. This is the only way my client can get the info to me, and it will be changed probably twice per month, so I'd hate to have to try to manipulate an excel file 24 times a year to import. Any suggestions Appreciated
View Replies !
What Is The Best Way To Approach Setup?
Guys/Gals, I am in the throws of developing a Sales & Marketing application. Here is the general overview of what I want to acheive. I am looking for the best way to setup the database and connections for optimal performance. I have to write an application, (VB front-end), using SQL Server. I have the following site settings to worry about. I have 15-20 workstations on an NT LAN network. Most of these are desktops, but some are laptops which are plugged in the when they are in the office, but may also need to access the application remotely when they are out on the road, (they are salesmen). I also have head office needing to access the application. They will have 20-30 users maybe needing access. Some of these users will also need to look at the application remotely. We are connected via a frame relay that handles our AS400 (JDEdwards) and Lotus Notes traffic at the moment. I will also have another two branches but not sure of the configuration for them yet. I was thinking of replication to the branches so as not to tie up the frame relay during the day but was not sure of the remote machines. How to handle via dialup? If people need some more clarification, please contact me via email. Thanks in advance, Shane.
View Replies !
MS SQL Server 7.0 And Approach
Environment NT Server 4.0 w/ SP4 SQL Server 7.0 w/ SP1 Win98 Client w/ Lotus Approach 9.5 I recently added SQL 7.0 to be a back end for my Approach front end. I transferred all the data from a dbase IV in approach to SQL. Most of the conversions worked ok. I have two big problems. 1) One particular repeat panel in Approach loses the children records of the master record. If I delete some of the records, more will appear. It's as if there is an imaginary limit of the number of records it can read in the repeat panel. I don't have this problem with any other records and children in repeat panels. I called Lotus and they don't have an answer. This is important because the children records need to be summed up so I can have a running total. 2) I original configured the clients to use the TCP/IP Netlib w/ the default port. I couldn't open enough databases so I changed to Multiprotocol. This allowed certain clients to open more databases, but others can't open additional databases. Also, after the change, the NT authentication login has had problems. I had to change to the SQL login to get all my clients back on line. Sometimes the same client can't open more than 10 databases while other times it will open 15. There is no consistent patten to when it can and can't open the additional databases. If anyone knows how to fix either of these problems, I would greatly appreciate any advice. I'm getting tired of my boss yelling at me. Thanks. Keith akumaboyz@aol.com
View Replies !
Should I Use A Trigger Or A Different Approach?
Lets say I have a dynamic table of 10 to 30 varchar(255) columns that I do not know the names of. Each of these columns is updated with either an 'OK' or an error msg. Here's the issue. I have a Status column (varchar(MAX)) which I want to be the concatenated sum of all the other columns. I'd like to do this via a trigger because the way I understand it, theres no way a trigger can skip over another update/trigger and write the wrong 'Status' value. Example Row 1: RandomColName1 = "1||Access Denied" RandomColName2 = "0||OK" RandomColName3 = "2||ID10T Error at Kbrd" Status = "[RandomColName1] Access Denied | [RandomColName3] = "ID10T Error at Kbrd" Example Row 2: RandomColName1 = "0||OK" RandomColName2 = "0||OK" RandomColName3 = "0||OK" Status = "0||OK" The rules: 1. There is no chance of someone updating the same column at the same time. 2. There's a good chance that other columns in the same row will be updated at the same time. 3. Sometimes a column can be updated every 5 secs. 4. About 100 to 5000 Rows I'm a SQL newb and this would be my first trigger to write (lol) but I feel if it could be done without causing a DEADLOCK then it should be done this way. Unless someone with more experience can shed some light With up to 300 .net clients over fast and slow links, I don't want to xfer the whole 5000 row / 50 column table back to the app and have it weed it out, especially if the client only needs 100 of the rows. On the same token, I don't want to make an individual call back to the SQL server should the client need 4000 rows. Thanks for the help!
View Replies !
Need Advice On Approach
I am new to DTS/SSIS and would like some input on an approach to solve a problem. I have a solution using SQL Server 2005 and another legacy solution running on Access. Status records are written to a table in SQL Server and as they are written, I need to write a record to a table in Access. This needs to be as real-time as possible. I thought about writting a managed stored procedure in C# so I could also access MS Access, but someone pointed me to DTS. Records are constantly written to the status table and need to remain in there even after they are processed. Can you recommend an approach or an article to read? Thanks, Steve
View Replies !
Question On An ETL Approach
Let me use a simplied example for what I'm trying to do. Say, I have the following source tables. T_EmpDept Emp_Name, Dept_Name John , IT Mary , IT Ted , HR T_Employee EmpID, Emp_Name 1 , John 2 , Mary 3 , Ted T_Dept DeptID, Dept_Name 1 , IT 2 , HR Now, I wan to populate a target table that looks like T_Target EmpID, DeptID 1 , 1 2 , 1 3 , 2 So, it's basically a lookup by name and translated to the appropriate keys. The way I've done this before, is I do one lookup at a time in serial (one data stream) and get the keys I need. But I want to do them in parallel (split the data stream in two), which I believe involves a Multicast Task and some sort of a Merge Task. I used to shy away from the parallel lookup because I never really understood how to bring the two data streams back together. So, the question I have is this. Are Multicast and Merge task the right tasks to use for this? I don't want to go researching something that may not be appropriate. Lastly, if someone has a link that can lay this out for me, I would appreciate it.
View Replies !
Best Approach For A Solution
I don't have much background with SSIS or SQLServer or development for that matter. I was thrust in to this and I apologize if my questions are not up to snuff in any way. I have built an SSIS package based on the import/export wizard and have customized it by adding a For Each Loop container for processing multiple source data files and it works great so far but I need to add some advanced functionality to make it more flexible. I'm having some problems with the best way to enhance the solution and I'm getting frustrated with variables and expressions, which is what I think I need to use. Hopefull what I need is something that's doable. The package processes mutliple source files using the For Each Loop. The data is stored in a destination table that is dropped and recreated each time. I've also added a couple of CREATE INDEX statements to the source SQL. I need to make the SQL DROP, CREATE TABLE and INDEX statements dynamic based on part of the filename string. I'm capturing the filename in the FOR EACH container using Variable Mapping. What I need to do is parse out part of the string from the filename and use it in the SQL statements. An example would be CREATE TABLE XXX_[stringvar] where [string] comes from the variable mapping done in the For Each loop. My thinking was to setup a new variable within the scope of the SSIS package and enable Evaluate as Expression and then build an expression for the varible where I parse out the string from the filename variable that already exists. So if the filename was filename.filetype and I needed the last 4 characters from the filename the string value would be whatever characters were in 'name' each time. But there isn't an expression builder editor built in to the variable properties expression element, so I'm not clear that I can actually create a variable in SSIS and then have it's value be set using an expression. It seems to me that if this was doable, then the expression builder I've seen elsewhere in SSIS would be visable. Then I was hoping I could edit the SQL behind the DROP and CREATE statements where I've hard coded the table name and insert the variable right in the SQL. My SQL isn't that strong and I don't know how to work with variables but I was thinking the SQL would look like CREATE TABLE XXX_@[stringvar].... I believe another approach would be to setup another string variable for the entire SQL statement and build the statement string dynamically. But I don't know if this is necessary or better. Anyway, I hope someone can help and that my original post is clear enough. Thanks
View Replies !
Configuration Approach
Background I use four database connections strings. I have about 30 packages that will use one or more of the connection stings. I store the connection strings in XML configuration files. I know that I can share configuration files across packages. Should I ? Have one configuration file with all 4 connection strings? If I use this approach will I get errors in the packages that only use 1 of the connection strings OR Create four separate configuration files (CnnString1.dtsconfig, CnnString2.dtsconfig, CnnString3.dtsconfig, CnnString4.dtsconfig) and use the appropriate ones for each package Take another approach
View Replies !
SCD Type 2 Approach Help
Hi , I am not sure about what approach i need for my ssis package to use either SCD type 2 wizard or Checksum transformation or any other optimal method to load the Data and Maintain the Historical Changes . The number of columns are ranging from 10 to 15 . and the number of rows are from 10,000 to 140000 what approach would help me to obtain better performance . Please let me know
View Replies !
What's The Best Way To Approach This Situation?
Hello everyone, I'm creating a database for a new application and I'm currently facing a design problem, regarding a business requierment for the membership module. The Membership Module of the application has several business requierments, specified by the client. One of them is the ability to add and remove details about their members. So far, I've created a schema, named Person, which will contain a number of tables responsible for everything related with the membership, as an individual. To help you guys understand the design I'm trying to implement, I'll post the fields of two of the tables that belong to the Person schema, as follows: Person.Base Id (uniqueidentifier) UniqueIdTypeId (uniqueidentifier) UniqueId (uniqueidentifier) Password (char(88)) PasswordSalt (char(10)) PasswordRecoveryQuestion (nvarchar(256)) [NULLABLE] PasswordRecoveryAnswer (char(88)) [NULLABLE] CreationDate (datetime) AuthenticationWindowStartDate (datetime) AuthenticationWindowAttemptCount (tinyint) IsActive (bit) IsBanned (bit) IsLocked (bit) Status (bit) Person.Emails PersonId (uniqueidentifier) EmailAddress (nvarchar(256)) CreationDate (datetime) ValidationCode (char(10)) ValidationDate (datetime) [NULLABLE] IsValid (bit) IsPrimary (bit) IsRollbackTarget (bit) Status (bit) So far so good. This design works great to preserve the data integrety. Nonetheless, this is where the problems start. Now, imagin you need to let someone from that company add an item to the user (through the application). Let's say we want to allow the company application manager to add an item to the person called "PreviousEmployer". Such item would then be used for statics, thus would probably need to be indexed. In order to meet this business requierment I would create some addicional tables. Let's get started: Person.CustomFields Id (uniqueidentifier) Name (nvarchar(50)) Description (nvarchar(3000)) [NULLABLE] Status (bit) Person.CustomField_Value FieldId (uniqueidentifier) Value (nvarchar(450)) This could work just fine if both are indexed (that's why the nvarchar size is set to 450). But I'm guessing this is far from the optimal solution for many reasons, one of them being the efficiency of the index if the company decides to go and use this for a flag (true or false [bit]). Another "solution" breaking scenario would be if the company wants to add the CV of the person, situation in which we were unlikely to be able to add a file in this datafield. How would you guys approach this issue? The bottom line is that the client needs to be able to add pretty mcuh any type of custom field and perform searches againts it. So, besides being a dynamic solution it needs to be efficient. Best regards and thanks in advance.
View Replies !
Mining Approach?
I have a scenario in mind and was wondering if anyone had any suggestions on an approach. Lets say I have a dataset where I have captured various attributes about blade servers in a rack. I have internal temperature, fan speed, disk reads, cpu temperature and failure events. Each of these are continuous variables except for the failure event, which has only two states, 'true' or 'false' which indicates whether a failure occurred at that point in time. The table looks like this: MachineID Timestamp Temperature FanSpeed Reads CPUTemp Failure At the time of failure, only the MachineID, Timestamp and Failure values will be populated in the table. The failures at this time are not categorized or discriminated from each other in any way although they will be in the future. We're looking to use a mining algorithm to determine which variable is the best predictor of failure, or combination of variables. What do you think is the right approach? How might this approach change once the failures do become categorized and differentiated? Thanks, Frank
View Replies !
Not Sure How To Approach This, If /then Select?
I have a select statement where I need to test two values that are returned and perform a different calculation if they return null. Basically, if TESTA is null and TESTB is not null return TESTB if TESTB is null and TESTA is not null return TESTA ELSE if both are not null TestA + TestB / 2 is value is returned is this possible to do in a select statement? thanks in advance. See select statement below: Select S.StudentDimKey, (select ISNULL(R.Score, NULL) from CLT_StudentAssessmentRawFact R, CLT_LessonPlanDim L where L.LessonPlanKey = 1 and L.LessonPlanDimKey = R.LessonPlanDimKey and StudentDimKey = S.StudentDimKey) as TESTA, (select ISNULL(R.Score, NULL) from CLT_StudentAssessmentRawFact R, CLT_LessonPlanDim L where L.LessonPlanKey = 2 and L.LessonPlanDimKey = R.LessonPlanDimKey and StudentDimKey = S.StudentDimKey) as TESTB, from CLT_StudentPlacementFact P, CLT_StudentDim S, CLT_ClassHierarchyDim H, CLT_StudentClassFact C where P.StudentDimKey = S.StudentDimKey and H.ClassHierarchyDimKey = C.ClassHierarchyDimKey and S.StudentDimKey = C.StudentDimKey and S.StatusCode = 'A' and S.CurrentRecord = 1 and S.SchoolID = 87577
View Replies !
How To Approach Complex SQL Query
Hi there! I'm looking for advice/suggestions on how to approach a query that's going to be much more complex than I have previously had to deal with. I have a web form where users can select from a number of possible search parameters (say, City, State, Zip Code, etc...) and pull a list of users from this database, and can select the specific data fields they want displayed as well. The complicated part is that the possible search parameters (and data to display) are spread out over several tables. How should I go about tackling this little monster? If it's any help, I am using Visual Web Developer 2005 Express Edition and Microsoft ASP.NET Web Matrix to create this form. Thanks a million, Kaiti
View Replies !
Joins: Strategy And How-to Approach
My SQL acumen stems from just a couple courses, and everything since fromthe trenches. Fun + angst over time.I'm needing some advice on joins. Though I understand the basics, I'mhaving problems abstracting from instances where it's easy to think aboutdiscrete key values (. . . and studentid = 1234) to entire sets of users,with the joins doing their work.For example, currently I'm going nuts trying to return dates for whichattendance has not been taken for students, but should have been. Studentshave active and inactive periods of enrollment in our schools, so we have ahistory table of when they were active and inactive -- as well as two moretables that layer other bounds on eligible dates (what range of dates fallwithin a given school's term? What of holidays and staff institute days?).I also have a populated calendar table, and a table where students areidentified. Finally, there's a site history table which is a REAL pain inthe butt for me to think about.CREATE TABLE Student (StudentID int IDENTITY(1,1) NOT NULL,CurrentStatus varchar(2) NOT NULL)CREATE TABLE Calendar (Dateid int NOT NULL,Date datetime NULL,Workdaybit NULL )CREATE TABLE DailyAttendance (StudentID int NOT NULL,AttendanceDate datetime NOT NULL,SiteID varchar(6) NOT NULL,Attend_Status varchar(2) NOT NULL)(the last field is, e.g., present or absent)CREATE TABLE StudentActivityHistory (StudentID int NOT NULL,StatusStartDate datetime NOT NULL,StatusEndDate datetime NULL,Activity_Statusvarchar(2) NULL,StudentStatusHistoryIDint IDENTITY(1,1) NOT NULL)(the activity_status is either A or I; the important records in this tableare the 'A' records. A student's most recent status record always has anend date of '12/31/9999 12:00:00 AM', whether that's an A or I record. Nodates not between start/end dates of students' A records would needattendance taken. students may have many periods of activity -- A records-- as well as many inactive periods.)CREATE TABLE SiteTerms (SiteID varchar(6) NOT NULL,Term varchar(3) NOT NULL,StartOfTerm datetime NOT NULL,Quarter varchar(2) NOT NULL,SchoolYear varchar(9) NOT NULL,EndOfTerm datetime NOT NULL)(different schools vary their term start and end dates. No dates notbetween term start and end dates would need attendance taken by studentsassigned to and active in that school during that period.)CREATE TABLE SiteExceptionDays (SiteID varchar(6) NOT NULL,SchoolDayStartTimedatetime NOT NULL,SchoolDayEndTime datetime NOT NULL,SchoolDayType varchar(2) NOT NULL)(there are two kinds of days -- partial attendance, and no attendance. Inshort, if the type of day is "N" no attendance needs to be taken forstudents assigned to that school and active on that day)CREATE TABLE StudentSiteHistory (StudentID int NOT NULL,SiteStartDate datetime NOT NULL,SiteID varchar(6) NOT NULL,SiteEndDate datetime NULL,StudentSiteHistoryIDint IDENTITY(1,1) NOT NULL)(Pain. The attendance table tells which site a student was assigned whenattendance was taken. To find which school a student was a assigned to ondays attendance was NOT taken, this table's implicated 'cause it's the onlyway of connecting everything else together)Dangitall, I know this can be done but I've beat my head against the wall.Due diligence has gotten me a headache and a hankerin' for whiskey, and I'mnot much of a drinker. Is there anyone in the group for whom this kind ofthing is a no-brainer? I'd just as soon get some tips on how to approachthis kind of thing, and figure it out myself with some guidance.Any takers? Gotta run, dang I'm late for something.TIA--Scott
View Replies !
Pros / Cons To This Approach
I have a requirement where I need to perform a query for positioninformation. But for some types of entries, I need to "expand" the rowto include additional position rows. Let me explain with an example:An index is a security that is made up of components where eachcomponent has a "weight" or a number of shares. So if I have 1 share ofthe index, I have X shares of each component.AAPL is an Equity, CSCO is an Equity, SPY is an Index. Lets say thatSPY has one component, AAPL, with shares being 10. (1 share of SPY = 10shares of AAPL).So, I do some trading and I end up with positions as follows:+10 AAPL-5 CSCO+2 SPYThe query I need returns:+10 AAPL-5 CSCO+2 SPY+20 AAPL (from 2 SPY * 10 shares)which becomes (after grouping):+30 AAPL-5 CSCO+2 SPY-----------------------------------------Based on that criteria and the following schema (and sample data):-- Drop tablesDROP TABLE [SecurityMaster]DROP TABLE [Position]DROP TABLE [IndexComponent]-- Create tablesCREATE TABLE [SecurityMaster] ([Symbol] VARCHAR(10), [SecurityType] VARCHAR(10))CREATE TABLE [Position] ([Account] VARCHAR(10), [Symbol] VARCHAR(10), [Position] INT)CREATE TABLE [IndexComponent] ([IndexSymbol] VARCHAR(10), [ComponentSymbol] VARCHAR(10), [Shares] INT)--Populate tablesINSERT INTO [SecurityMaster] VALUES ('AAPL', 'Equity')INSERT INTO [SecurityMaster] VALUES ('MSFT', 'Equity')INSERT INTO [SecurityMaster] VALUES ('MNTAM', 'Option')INSERT INTO [SecurityMaster] VALUES ('CSCO', 'Equity')INSERT INTO [SecurityMaster] VALUES ('SPY', 'Index')INSERT INTO [Position] VALUES ('001', 'AAPL', 10)INSERT INTO [Position] VALUES ('001', 'MSFT', -5)INSERT INTO [Position] VALUES ('001', 'CSCO', 10)INSERT INTO [Position] VALUES ('001', 'SPY', 15)INSERT INTO [Position] VALUES ('001', 'QQQQ', 21)INSERT INTO [Position] VALUES ('002', 'MNTAM', 10)INSERT INTO [Position] VALUES ('002', 'APPL', 20)INSERT INTO [Position] VALUES ('003', 'SPY', -2)INSERT INTO [IndexComponent] VALUES ('SPY', 'AAPL', 25)INSERT INTO [IndexComponent] VALUES ('SPY', 'CSCO', 50)INSERT INTO [IndexComponent] VALUES ('QQQQ', 'AAPL', 33)-- *****************************-- Based on the rules:-- 1) Index positions appear like other positions (account /symbol) pair, but-- its components show up as new rows of account (of index),symbol (equal--to component symbol), position (equal to shares * index position)-- 2) One row for each account / symbol pair (GROUP BY account andsymbol, SUM position)-- Expected output (without grouping) (sorted by account / symbol)-- 001 AAPL 10-- 001 AAPL 375 (component shares * index position) (25* 15) (SPY)-- 001 AAPL 693 (component shares * index position) (33* 21) (QQQQ)-- 001 CSCO 10-- 001 CSCO 750 (component shares * index position) (50* 15) (SPY)-- 001 MSFT -5-- 001 QQQQ 21-- 001 SPY 15-- 002 AAPL 20-- 002 MNTAM 10-- 003 AAPL -50 (component shares * index position) (25* -2) (SPY)-- 003 CSCO -100 (component shares * index position) (50* -2) (SPY)-- 003 SPY -2-- Expected output (with grouping account / symbol) (sorted by account/ symbol)-- 001 AAPL 1078-- 001 CSCO 760-- 001 MSFT -5-- 001 QQQQ 21-- 001 SPY 15-- 002 AAPL 20-- 002 MNTAM 10-- 003 AAPL -50-- 003 CSCO -100-- 003 SPY -2---------------------------------------------Is a UNION the best way to perform the query. What are the pros andcons? What, if any, is a better way?SELECT[Account], [Symbol], SUM([Position]) AS [Position]FROM(SELECT[Account], [Symbol] , [Position]FROM[Position]UNION ALLSELECTP.[Account] , IC.[ComponentSymbol] AS [Symbol] , (P.[Position] *IC.[Shares]) AS [Position]FROM[IndexComponent] ICJOIN[Position] PONP.[Symbol] = IC.[IndexSymbol]) DGROUP BY[Account], [Symbol]ORDER BY[Account], [Symbol]
View Replies !
Variable Parameters - Which Approach.
ASP.NET / VB.NETSQL Server 7.0Our client has insisted that we change our established practice ofbuilding SQL in-line and move it all to SPROCs. Not a problem for 80%of the app. However, we have a number of Search/Filter forms thatcontain a number of controls to allow more or less focused searchcriteria to be built up.I have a class that does this quite nicely, examining all controls forvalid input and, if found, concatenated onto the end of what becomes a"WHERE" clause. Once all is done the "WHERE" clause is appended to aSQL Statement and passed to an ExecuteReader function. Only atruntime is it known how many, and which, controls will be used in thefilter.I can think of two methods of approaching this variable parameter listwhich will be required if moving to SPROCs.METHOD 1.Create a SPROC where ALL the possible parameters are used. Forexample:CREATE PROC stpSearchContactsUsingContactRef@ContactRef varchar(50),@Status varchar(50),@IgnoreDate int,@Date1 smalldatetime,@Date2 smalldatetimeASSELECTtblContactSources.*FROMtblContactsWHERE(tblContacts.fldContactID LIKE @ContactRef) AND(tblStatuses.fldStatus LIKE @Status) AND((tblContacts.fldDateCreated >= @Date1) AND(tblContacts.fldDateCreated <= @Date2) OR @IgnoreDate = 0)GOIf either of the parameters @ContactRef and @Status is empty, the codethat retrieves the filter values passes in the value '%', otherwise itwill pass in the user's choice. If the user wants not to search on thedate range, then @Date1 and @Date2 are passed in with '' and@IgnoreDate is set to 0.METHOD 2.Create a SPROC which consists of SQL body as a string, with the"WHERE" clause passed in as a string parameter, and both EXEC'd -CREATE PROCEDURE stpTSRGetFilteredTSR@WhereClause as varchar(3999)ASDECLARE @Body as varchar(3999)DECLARE @OrderBy as varchar(3999)SET @Body = 'SET CONCAT_NULL_YIELDS_NULL OFFSELECT TOP 100 PERCENTtblTSRs.fldTSRID, tblTSRs.fldFaultDescription,tblTSRs.fldDateEntered, tblTSRPriorities.fldTSRPriority,tblTSRs.fldTSRPriorityID, tblVehicles.fldRegistrationNumber,tblVehicles.fldVIN, tblTSRStatuses.fldTSRStatus,tblTSRs.fldTSRStatusID, tblTSRs.fldDealerID, tblTSRs.fldEmergency,tblCustomers.fldFirstName + '' '' + tblCustomers.fldSurname ASfldCustomerNameFROMtblContactReasonsINNER JOIN tblContacts ON tblContactReasons.fldContactID =tblContacts.fldContactIDINNER JOIN tblCustomers ON tblContactReasons.fldRegardingID =tblCustomers.fldCustomerIDINNER JOIN tblVehicles ON tblContactReasons.fldVehicleID =tblVehicles.fldVehicleIDRIGHT OUTER JOIN tblTSRs WITH (NOLOCK)LEFT OUTER JOIN tblTSRStatuses ON tblTSRs.fldTSRStatusID =tblTSRStatuses.fldTSRStatusIDLEFT OUTER JOIN tblTSRPriorities ON tblTSRs.fldTSRPriorityID =tblTSRPriorities.fldTSRPriorityID ON tblContacts.fldContactID =tblTSRs.fldContactIDWHERE (GETDATE() <= DATEADD(week, 2, tblTSRs.fldDateEntered))'SET @OrderBy = 'ORDER BY tblTSRs.fldDateEntered'SET @Body = @Body + @WhereClause + @OrderByEXEC (@Body)GOIn this example, the @WhereClause parameter might typically be:' AND (fldDealerID = 9) AND (fldTSRStatusID = 1) 'My question is, is there anything to recommend one method over theother?TIAEdward--The reading group's reading group:http://www.bookgroup.org.uk
View Replies !
Replicating Only One Table, What's The Best Approach ?
I have a single table on database A that needs to be replicated to two databases B and C in as-near-to-real-time as possible. The table is cleared every morning, and receives about 40,000 records per hour. Records are only added to the table, never updated or deleted. I'm wondering what is the best approach to replicate this table : 1 - Transactional Replication 2 - A trigger on the INSERT on table in A that then inserts the record into B and C 3 - Timed jobs running on B and C that pull new records from A thanks all in advance.
View Replies !
Update Trigger Best Approach
I need to create an update trigger which will have from 3-6 fields which can be updated in a master table (it could be different with each record, but it will always be these fields). Based upon that I will need to update corresponding tables. Isn't there a limit based upon the location in the table how you can do this. Is the best way to handle this with a bunch of if's?
View Replies !
Legacy DTS - Is This The Right Approach?? (please See Post)
Hi, I am converting several DTS pkgs to SSIS. Several of the pkgs contain complicated "Active X script" transformations on text files. That is, it would take me a long time to have to re-write this! In the meanwhile, do you think it's just best to use the EXECUTE DTS 2000 task until I have a better grip on SSIS?? Also, what is the equivalent of "Active X script" validation in SSIS? For example, I have an Active X script that checks the values of a particular column in a text file. If the column contains a datefield, then load into the database, if not, then discard... what task in SSIS would replace this logic? (not now, but for later reference) Thanks so much
View Replies !
Data Normalization - Best Approach?
Hi! I wonder what would be the best (at to be honest - how to do it at all) to perform data normalization with SSIS. The scenario is as follows: I got plain table with several columns in it.Some of columns can be copied straight into destination tableSome columns (String) should be lookup in another table to get IDOn success just replace string with IDOn fail - create new record in lookup table and return newly created ID Thanks for any ideas and maybe short samples Anrijs Vitolins
View Replies !
Report Model, Which Approach?
Hi All, I'm a newbie to Report Model and not able to find some conceptual information, Can you guys answer following: I€™ve Employee [Name] ->Employee_Days [Day] (1-*) ->Employee_Day_Activities [Activity_Type, HoursSpent] (1-*) schema and a generated model out of it. Now I€™ve few report requirements which will be developed based on Report Model. These Reports needs aggregate of All similar activities and the hrs spent and it'll be shown with every employee. Like Employee Xyz has spent 14 hrs total on Sick Activity, which will be shown as Employee Sick Hrs Maternity Hrs WorkHrs Xyz 14 0 20 Now i need to know the approach to develop it like i'v to ways in my mind - create 3 custom fields(Sickhrs,Maternityhrs,Workhrs) in the Employee Entity(model), so that managers can create reports like above by simple drag and drop - leave model as it is.. just create more descriptive fields in all the three table and let managers do the activity grouping etc to develop report like above.. Which approach should i follow? Moreover can you point me to some conceptual info like how report model works..etc.. regards faraz
View Replies !
Best Approach To Encrypt Data?
Hi, I want to encrypt certain data like password, ssn, credit card info etc before saving in database. Also, this encrypted data can be queried using standard SQL statements like: select * from users where userid=454 and password = 'encrypted data' The mechanism to encrypt data could be in a .net application. The code that does encryption/decryption should also be protected so that it doesnt work if it falls in wrong hands. Can anyone suggest what would be the best way to accomplish above? thanks, dapi
View Replies !
How To Best Approach The Problem - Need Ideas
SELECT DISTINCT WO_NO, wo_type, wo_status (incomp,compl,open), wo_status_code, wo_create_date, wo_post_date I need to do a stored proc in sql server 2005 where I have to count the total work orders based on their wo_type + wo_status, i.e. open, compltd, incomplete. For example I may have (wo_types) AB, AC, AD, orders that are in open status. I need to count the total AB_Type, AC_Type, AD_Type then the total of all 3 in another column (as Total Open). I must do this for all wo_status. I also have to allow the user to be able to enter the date they want. I.e. if they would want to know on 3/17/08 how many orders were opened that day only. I know I have to use a parameter for this but how do i do that? Should I do separate select statments for each status, multiple tables ? Also for 'open' wo_status the wo_status_code can change from O to S but it is still considered 'open'. For this wo_status how can I get the most recent status_code for the open order, i.e., I want to count a particular 'open'work order's - most recent status_code in the work order count for a given wo_type -->> the order should only be counted once on any given date entered. So if the status changed from O then S the same at 1pm then 2pm, respectively, then only the 2pm status should be counted. Thanks in advance for your assistance.
View Replies !
|
TRACKER
