Monday, March 25, 2013

[T-SQL] Performance ISSUE by USING CURSOR HELP !!!!

[T-SQL] Performance ISSUE by USING CURSOR HELP !!!!

Performance ISSUE by USING CURSOR HELP !!!!

Posted: 24 Mar 2013 08:54 PM PDT

Hello Mate,I have the following TSQL but I meet a performance issue on Phase 3. Phase 1 and Phase 2 take about 4 min. The table TEMP2# have 1317832 records. I would to replace the cursor to improve the performance but I don't know how to do this. I'm not a expert :crying:Could you help me ?I have another question I use @@identity actually but if I use SCOPE_IDENTITY() do you I can improve the insert.Thanks,EricCREATE TABLE #TEMP1 (patId integer NULL,ancienPatId integer null) PRINT 'Find patient with no phone number checked SMS - Phase 1'INSERT INTO #temp1SELECT DISTINCT easilyPatient.pat_id,easilyPatient.pat_ancien_id FROM [DI628SW].[NOYAU_PATIENT].[noyau].[Patient] AS easilyPatient WITH (NOLOCK) LEFT JOIN [DI628SW].[NOYAU_PATIENT].[noyau].[TELEPHONE_PATIENT] AS easilyTelPatient WITH (NOLOCK) ON easilyPatient.pat_id = easilyTelPatient.pat_id LEFT JOIN [DI628SW].[NOYAU_PATIENT].[noyau].[TELEPHONE] AS easilyTel WITH (NOLOCK) ON easilyTelPatient.tel_id = easilyTel.tel_idWHERE (easilyTel.tel_envoi_sms = 0 OR easilyTel.[tel_envoi_sms] is null) AND easilyPatient.pat_ancien_id is not null SELECT COUNT(*) FROM #TEMP1--Select * from #Temp1CREATE TABLE #TEMP2( cnetPatId integer, cnetNumTel varchar(40), easilyTypeNumTelId integer, cnetnumeroPrincipal bit not null, cnetSMS integer not null, cnetCommentaire varchar(40), easilyPatId integer null)PRINT 'Matching PATIENT - PHASE 2'INSERT INTO #temp2SELECT CNet.pat_id,LEFT(CNet.[NUM_TEL_REEL], 18) AS CNETNumTel , CASE CNet.[TYPE_NUM_TEL_ID] WHEN 1 THEN 1 WHEN 2 THEN 4 WHEN 3 THEN 3 WHEN 4 THEN 0 WHEN 5 THEN 5 END AS easilyTelCat, CNet.[NUMERO_PRINCIPAL],CNet.[SMS],Cnet.[COMMENTAIRE],tmp1.patIdFROM [DI81SW].[CNET_EXP].[dbo].[NOYAU_NUM_TELEPHONE] AS CNet WITH (NOLOCK)INNER JOIN #temp1 AS tmp1 ON tmp1.ancienPatId = CNet.pat_id WHERE (CNet.[SMS] = 1) and (LEFT(CNet.[NUM_TEL_REEL], 18) not in (select tel_numero collate SQL_Latin1_General_CP1_CI_AS FROM [DI628SW].[NOYAU_PATIENT].[noyau].[TELEPHONE_PATIENT] AS TP WITH (NOLOCK)INNER JOIN [DI628SW].[NOYAU_PATIENT].[noyau].[TELEPHONE] AS T WITH (NOLOCK) ON TP.TEL_ID = T.TEL_ID WHERE t.tel_envoi_sms = 1 AND tmp1.patid = TP.pat_id)) SELECT COUNT(*) FROM #TEMP2--Select * from #temp2--DROP TABLE #TEMP1--DROP TABLE #TEMP2PRINT 'Insert missing phones - Phase 3'DECLARE @cnetPatId integerDECLARE @cnetNumTel varchar(40)DECLARE @easilyTypeNumTelId integerDECLARE @cnetnumeroPrincipal bit DECLARE @cnetSMS bit DECLARE @easilyPatId integer DECLARE @cnetCommentaire VARCHAR(40)DECLARE @bdEasilyTelephone VARCHAR(100)DECLARE @bdEasilyTelephonePATIENT VARCHAR(100)DECLARE @siteCode VARCHAR(10)DECLARE myCursor CURSOR FORSELECT etPatId,cnetNumTel,easilyTypeNumTelId,cnetnumeroPrincipal,cnetSMS,cnetCommentaire,easilyPatId FROM #temp2 OPEN myCursorFETCH NEXT FROM myCursor INTO @cnetPatId,@cnetNumTel,@easilyTypeNumTelId,@cnetnumeroPrincipal,@cnetSMS,@cnetCommentaire,@easilyPatId WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO [DI628SW].[NOYAU_PATIENT].[noyau].[TELEPHONE] (tel_categorie,tel_numero,tel_envoi_sms,tel_principal,tel_commentaire,tel_administratif,tel_actif) SELECT @easilyTypeNumTelId,@cnetNumTel,@cnetSMS,@cnetnumeroPrincipal,@cnetCommentaire, 0,1 DECLARE @idTelephone integer SELECT @idTelephone = @@IDENTITY INSERT INTO [DI628SW].[NOYAU_PATIENT].[noyau].[TELEPHONE_PATIENT] (tel_id, pat_id) SELECT @idTelephone, @easilyPatId FETCH NEXT FROM myCursor INTO @cnetPatId,@cnetNumTel,@easilyTypeNumTelId,@cnetnumeroPrincipal,@cnetSMS,@cnetCommentaire,@easilyPatIdEND CLOSE myCursorDEALLOCATE myCursorDROP TABLE #TEMP1DROP TABLE #TEMP2

Multiple condition based on where clause in sql server 2008

Posted: 25 Mar 2013 12:46 AM PDT

I had a table Product with these fields[code="plain"]ProductName,Description,Manufacturer,Product Code,Technology,Address,Country,City,Length [/code]I want to search all these fields with these criteria[code="plain"]SearchType='Contains' SearchType='Ends' SearchType='Equals' SearchType='Greater' SearchType='Less' SearchType='Not Equal' SearchType='Like'[/code]Another parameter i will pass whether to use AND/OR operationAt a time we can search 6 fields with any of the search criteria and AND\OR operation the condition in where clause will be like this[code="sql"]WHERE (ProductName = @ProductName) AND (Country Like @Country +%) OR (Manufacturer Likre(Ends) '%' + @Manufacturer ) AND (Length < (Greater) @Length)[/code]So the user can input 6 conditions based on the above criteria.Can u help to get the logic for where clause

how to retrieve the data when a comma separated ids are sent as input parameters

Posted: 24 Mar 2013 11:05 PM PDT

Dear all,I have two tables namely:1. User table2. Location tableGiven below are their design details:[u]User table columns:[/u]UserID INT, ([b]Primary Key[/b])UserForename VARCHAR(200),UserSurname VARCHAR(200),LocationID INt ([b]foriegn key[/b])[u]Location table columns:[/u]LocationID INT, ([b]Primary Key[/b])LocationName VARCHAR(200)[u]Issue:[/u]I need a store procedure to retrieve user details based on the LocationIDs passed as the input parameter where the input parameter is the array of LocationIDs separated by comma. [for eg: '1,2,3']Please kindly help me with a stored procedure as explained above.Thanks in advance,Ram

Mapping Old identity values to new ones

Posted: 24 Mar 2013 07:46 PM PDT

Hi All,I have a requirement in which I insert values into same table, but I need to map old identity column with those newly inserted: Below is the snippet:1st Insert: INSERT INTO [dbo].[tblOrders] ...........--some insert statementSET @newOrderId = @@IDENTITY;CREATE TABLE #temp_result (OldOrderItemID BIGINT, NewOrderItemID BIGINT)INSERT INTO [dbo].[tblOrderItems] ([ItemTypeID] ,[OrderID] ,[Service]) SELECT [ItemTypeID] ,@newOrderId ,[Service]FROM [dbo].[tblOrderItems] AWHERE A.OrderID = @OrderID -- Multiple records for single @orderIDIn table tblOrderItems there is Identity: OrderItemID Seed 1 Increment 1I need a table #temp_result with entry for mapping old OrderItemIDs with newly inserted OrderItemIDs without disturbing the existing tblOrderItems. Any advice?

difference betwwn inner join and intersect in sql

Posted: 24 Mar 2013 07:56 PM PDT

I would like to know when to use inner join/outer join and intersect/except as they probably are doing the same job if not mistaken.what is difference while working with joins and using these keyword intersect/except.

Only allow users to see their own records

Posted: 24 Mar 2013 01:36 PM PDT

I'm working on the database from hell, so bear with me... (they think 'normalization' is a dirty word, or a foreign one...)one of the rules for the users is that they can only see their own records. It's a little more complicated than that, but not a lot. The other fun part is the front end is Access 2013. Since Access can hold up to (I think) 2GB of data, I may have to upsize their backend to SQL Server 2012 Express, and then implement security. And then create stored procedures and views that the users can run, but deny all access to the underlying tables. Then each non-admin user can only see the "records" that are assigned to him. (I would link to or call the stored procedures from the front end, which would likely be Access).If it matters, it's a Customer--buys--Product--from--Vendor database with a few minor tweaks.It's a little more complicated than that - but not a lot. Each user is a salesperson assigned to Territory, so each one would be able see only the records that pertain to that territory. So I would just use SUSER_NAME() to filter the data, and it would filter by the username?(Umm... did ya test it?)... does anybody know a good tutorial on basic SQL Server Security somewhere? I get the feeling that this is where the database may be going.And one small digression - they're doing sales, so having maps of various territories and sites within them looks useful. What's the minimum version of SQL Server that I need for that? (Access Reports are just awful!).Thanks!Pieter

Remove characters after last slash in string

Posted: 24 Mar 2013 05:24 PM PDT

Hi AllI have the following string[code="sql"]declare @string varchar (100)set @string = 'x:\folder1\folder2\folder3\test.txt'select @string[/code]How would I remove all the characters after the last '\' in this string?I need the string to reflect ''x:\folder1\folder2\folder3\'Any Ideas?Thanks

nvarchar

Posted: 24 Mar 2013 07:09 AM PDT

From my understanding nvarchar allows you to store more than one language compared to varchar. Does nvarchar only take up more space if more than one language is stored or does it reserve the extra space regardless?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog