Friday, July 12, 2013

[T-SQL] performance of sp

[T-SQL] performance of sp

performance of sp

Posted: 12 Jul 2013 12:55 AM PDT

himy sp is taking 5 min in prod.any way i can improve performanceBEGIN TRY DECLARE @alerts TABLE (BatchId int, ConsumerId BIGINT, MemberId INT, RId INT, Category INT, MailToMem BIT, MailToProv BIT, SortOrder varchar(25), StatusVARCHAR(8), ReasonCD varchar(8), Active bit ) DECLARE @Mid TABLE (ConsumerId BIGINT) INSERT INTO @Mid ( ConsumerId )SELECT DISTINCT ConsumerId FROM MCID_XREF xref WITH (NOLOCK) WHERE MemberId = @MemberID --List of all alerts based on MemberID OR MasterConsumerID INSERT INTO @alerts ( BatchId , ConsumerId , MemberId , RId , Category , MailToMem , MailToProv , SortOrder , Status, ReasonCD, Active SELECT BatchId , ConsumerId , MemberId , RId , Category , MailToMem , MailToProv , SortOrder , Status, ReasonCD, Active FROM dbo.Alerts a WHERE MemberId = @MemberID OR ConsumerId IN (SELECT ConsumerId FROM @Mid) select a.MemberId,a.SortOrder ,a.Status ,a.ReasonCD ,a.Active, r.Rid,r.RType,r.Sensitive,r.Weight,r.ROI,r.Program,r.Mgmt_Ranking,r.Significant, (select top 1 Name from library WITH (NoLock) where rID = r.ruleid) as ruleName, min(b.analysisasofdate) initiallyIdentified, max(b.analysisasofdate) mostRecentlyIdentified, a.category, v_cnt.ResponseCategoryCount, max(b.alertBatchId) batchid, r.harvardmednumber from library r WITH (NoLock) inner join @alerts a on a.ruleid = r.ruleid and a.alertbatchid = r.alertbatchid inner join batch b WITH (NoLock) on b.alertbatchId = a.alertbatchId and (a.mailToProv = 1 or a.mailToMem = 1) left outer join dbo.fn_CategoryCount(@memberid) v_cnt on v_cnt.ruleid = a.Rid and a.MemberId = v_cnt.memberid where a.MemberId = @MemberID END TRYBEGIN CATCH--ERROR END CATCHGOany help?

Locking on update...inner join

Posted: 12 Jul 2013 12:36 AM PDT

Just wanted to ask about the following scenario.Update [table1]set table1.value = subquery1.valuefrom(subquery) inner join [table1]Just wondered about the locking effects specifically on table1, could it cause issues where the update has an exclusive lock but in turn that prevents the join? I would imagine that would not be the case, asking those a lot more knowledgable that I am out of curiousity!

MIN and MAX values with a subquery.

Posted: 11 Jul 2013 07:59 AM PDT

Hi Everyone I have the following query in one of my databases. It returns 20 rows. SELECT C_ID, H_Type, Name, A_Name, D_Date as 'First Date', D_Status, D_TYPEFROM DH_infoWHERE (D_Date IN (SELECT MIN(D_Date) AS First_date FROM DH_info GROUP BY C_ID, D_TYPE))I now have to add MAX(D_DATE) to the query . The query should returnC_ID, H_Type, Name, A_Name, MIN(D_Date) as 'First Date', [b]MAX(D_Date) as 'Last Date'[/b], D_Status, D_TYPECan any one help me as I have been working on this for hours without luck.

archive process

Posted: 11 Jul 2013 06:41 AM PDT

hii need to Create process to archive the records ,anybody has any script or any ways to do itthanks

If Exists

Posted: 11 Jul 2013 08:16 PM PDT

Hi all, I just want to know that in my below code is it possible to combine first two exists condition in a single exists and last two in a single exists so that I can put AND condition between them. As my script can give me wrong output as if any condition is true it will give me result as True only.If @firstvoucher value is true and @lastvoucher value is wrong then it will give me output as True only which is wrong...[code="sql"]IF EXISTS ( SELECT gv.VoucherNo FROM GV_Voucher As gv INNER JOIN GV_ReceivedOffice ro ON ro.VoucherNo = gv.VoucherNo INNER JOIN GV_VoucherStatus As gvs ON gv.VoucherStatusId = gvs.VoucherStatusId WHERE gvs.VoucherStatus = 'Active at HO' AND gv.VoucherNo = @FirstVoucher)OR EXISTS( SELECT gv.VoucherBookletNo FROM GV_StoreAllocation As gv JOIN GV_Voucher v on v.VoucherNo = gv.VoucherBookletNo JOIN GV_STNDetails stn ON stn.STNNO = gv.STNNo INNER JOIN GV_VoucherStatus As gvs ON v.VoucherStatusId = gvs.VoucherStatusId WHERE stn.AWBNo IS NULL AND gvs.VoucherStatus = 'Dispatched' and v.VoucherNo = @FirstVoucher)AND EXISTS ( SELECT gv.VoucherNo FROM GV_Voucher As gv INNER JOIN GV_VoucherStatus As gvs ON gv.VoucherStatusId = gvs.VoucherStatusId INNER JOIN GV_ReceivedOffice ro ON ro.VoucherNo = gv.VoucherNo WHERE gvs.VoucherStatus = 'Active at HO' AND gv.VoucherNo = @Lastvoucher)OR EXISTS( SELECT gv.VoucherBookletNo FROM GV_StoreAllocation As gv JOIN GV_Voucher v on v.VoucherNo = gv.VoucherBookletNo JOIN GV_STNDetails stn ON stn.STNNO = gv.STNNo INNER JOIN GV_VoucherStatus As gvs ON v.VoucherStatusId = gvs.VoucherStatusId WHERE stn.AWBNo IS NULL AND gvs.VoucherStatus = 'Dispatched' AND v.VoucherNo = @Lastvoucher)BEGIN PRINT 'Correct voucher nos'ENDELSE BEGIN RAISERROR('User Define: VOucher Already Sold',16,1) END[/code]

sp_send_dbmail Query

Posted: 11 Jul 2013 09:01 AM PDT

Regarding the @query parameter, BOL states: "Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query."I'm calling sp_send_dbmail from a code that updates records in a table. I want to send some information about the record that was updated, including data from child tables joined to the updated record.Since I can't pass a local variable to @query, I'm doing something like:[code="sql"]@query = 'DECLARE @RecordID AS Varchar(12) SET @RecordID = (select top 1 Record_ID FROM Table where ColumnChangedFrom1 = 0 ORDER BY Modify_Date DESC); SELECT t1.column1, t2.column1, t2.column2 FROM Table AS t1 INNER JOIN Table2 t2 on t1.RecordID = t2.T1RecordID WHERE t1.Record_ID = @RecordID'[/code]While this works so far in testing, I'm not certian that in production it will return the correct records 100 percent of the time. Is there a way to be certain the dbMail query will find the right records?

Splitting a Full Name

Posted: 11 Jul 2013 07:54 AM PDT

I'm trying to split a full name column up into First / Middle / Last....but some of the names are like "Joe B. W. LastName" or "Jenny MiddleName MaidenName MarriedName" and so on...In the past I've used parsename() and charindex() to solve this with "cleaner" data. How would I go about handling these?Thanks

APPLY Syntax

Posted: 11 Jul 2013 08:40 AM PDT

I got the following example of some code to use an OUTER APPLY to get some data I need. I was able to get it to work in my environment, but there's one piece of the syntax I don't understand.[code]select Table1Key, MAX(LastModifiedDate)from Table1outer apply ( select Table1.LastModifiedDate union all select LastModifiedDate from RTable01 where RTable01.Table1Key = Table1.Table1Key union all select LastModifiedDate from RTable02 where RTable02.Table1Key = Table1.Table1Key union all select LastModifiedDate from RTable30 where RTable30.Table1Key = Table1.Table1Key ) ---------------------------------------------------------AllTables(LastModifiedDate)----------------------------------------------------------group by Table1.Table1Key[/code]What is the AllTables() statement at then end of the OUTER APPLY() doing? I can't find any syntax references to it, and the whole thing doesn't work without it, so I feel like I should find out what it's doing!

passing different values into a variable

Posted: 11 Jul 2013 02:55 AM PDT

HiI have a an SP that returns the size of the backup files into a table. To execute the Sp i have to pass in the paramaters @db and @type.@db is the database name and @type is the type of backupfile So for example i would type[code="sql"]exec dbo.Sp_GetBackup 'adventureworks','d'[/code]This would return the adventureworks full backup history.My question is how can i automate this so i dont have to manually enter the database name and the type of backupfile i want it to return.

Finding the most recently modified row from a group of tables

Posted: 11 Jul 2013 04:07 AM PDT

I'm thinking there's some clever way of doing this, probably involving a CTE and recursion, but I'm not seeing how to get started....Given a [Table1] with a primary key called [Table1Key].There are 30 tables with [Table1Key] as a foreign key pointing back to [Table1], call these [RTable01] through [RTable30]. Any specific Table1 row may have related rows in 0 to 30 of these tables. Each of the related tables has a [LastModifiedDate], as does the parent Table1 row.Given two Table1 rows, I want to flag one as 'Most Current' based on the latest of ANY of the LastModifiedDates in related rows and the parent row.I can certainly do this in a brute force ROAR fashion, but I'm trying to stop doing things like that. Any pointers?

Make the formula configurable (can use in different SSIS packages)

Posted: 03 Oct 2012 08:35 PM PDT

I have to use same formula to fill the calculated data in different tables. I want to make this formula configurable so that no need to write formula everytime simply pass the values to the formula and it will return the calculated value.SSIS packages are used to populate the data in the tables and I can't use SQL User defined function because that is decreasing my SSIS package performance.Please suggest some good approach.Help is really appreciable.

stored procedure keeps spinning

Posted: 11 Jul 2013 01:28 AM PDT

hiwhen i run sp in 1 server it keeps spinning and on other server i am getting resultwhat could be the reasoni tried with recompile but no luckthanks

Group by and Where filters

Posted: 11 Jul 2013 03:25 AM PDT

I have a query that seems to operate differently than I though SQL handled Group By with Where...I thought WHERE is used to filter the rows then the group by is done.. but what I am seeing is that the count, and SUM sections have totals for all the rows.. prior to filter.Is this correct? I thought that was how the HAVING worked, but the WHERE was before the totals were generated.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog