Questions tagged [set-based]

51 questions
0
votes
1 answer

Set based calculation for events table, exceptuating an event

I have a table which contains events for change the state of a maintenance service. This is a sample of data: id date event 234 2009-04-22 10:00:00 Service Request 234 2009-04-22 10:02:00 Service Approbation 234 2009-04-22 10:03:00 Service…
Jhonny D. Cano -Leftware-
  • 17,663
  • 14
  • 81
  • 103
0
votes
0 answers

replacing the cursors with set based

I have sql script with a lot of cursors which take too long to execute.I need to replace them with set based operations. I can't understand how can replace them and having the same results.Here is part of the code: SET @CurAddress = Cursor static…
0
votes
0 answers

Convert Cursors to Set-Based

I need some help. I need to convert this code with cursors to set-based operations. I can't understand how I can make the set-based code do the same as with cursors. DECLARE Cursor static local FOR SELECT Cid,Abrev FROM EntityType OPEN Cur FETCH…
0
votes
1 answer

how to transfer this iterative solution to a set-based solution

/* Stored Procedure April 30, 2021 Mohamad Chaker */ USE CIS111_BookStoreMC GO --drop the procedure IF OBJECT_ID('spAssetInfo') IS NOT NULL DROP PROC spAssetInfo GO --Create the stored procedure CREATE PROC spAssetInfo AS --Create a temporary…
0
votes
2 answers

DB stored procedure operation design - set based vs cursor based

I'm told that when designing stored procedures, set based operations scale better than cursor based ones. Can someone give a succinct explanation of why this is?
hawkeye
  • 34,745
  • 30
  • 150
  • 304
0
votes
0 answers

SQL Server Set-Based Update With Multiple Matches

I found this question, which presents the same situation. Trying to do a set-based update, and the table to be updated matchesmultiple rows in the table being used for update values. A minimal example is below: CREATE TABLE #tempUpdates (ID INT,…
Jeffrey Van Laethem
  • 2,601
  • 1
  • 20
  • 30
0
votes
1 answer

Is it possible to replace cursor in SQL when the query is hierarchical?

I have three tables in MsSQL to store data. First one is the Children contains the following rows ID, Name, Age, FatherID, SchoolID The second one contains the data of their fathers, like this FatherID, Name, Age The third one is the School with…
0
votes
1 answer

Can a SQL Select statement be converted to a Set-Based select?

I have an SP that creates and runs a SQL query. The end result is used for some reporting. This query though takes a long time to run (greater than 30 seconds is a long time and sometimes goes for over a minute). I have read a little about…
Mike
  • 1,853
  • 3
  • 45
  • 75
0
votes
1 answer

Update trigger does not update for all rows in a set based update query

I have the tables Cities and Countries with the following triggers: When I update any City, it should insert a row into the Audit table saying 'The City with ID 'any ID' has been modified'. Cities table has a foreign key relationship with Countries…
MAK
  • 1,915
  • 4
  • 20
  • 44
0
votes
2 answers

SQL display ID of any customer who has never placed an order, using set-based operations only

Couldn't find anything on this, using set-based operations. Not sure if I'm on the right track, but I have 2 guesses on how to do this, but I'm not sure if I'm even close, or if MINUS is the right set-based operation to use: SELECT customerid FROM…
Jaluvshuskies
  • 35
  • 1
  • 7
0
votes
1 answer

Converting Foxpro program to MySQL stored procedure

I am in a process on converting a legacy system to web app using Ruby on Rails and MySQL. There are few places that I'm stuck at while converting the data layer to MySQL procedures. Giving a scenario below; FUNCTION first_function SELE Table1 REPL…
vishless
  • 829
  • 1
  • 5
  • 28
0
votes
2 answers

Removing While Loop in String Search Query

I have a query that takes one record from #itemtemp, and locates all entries from ReportCSharp that match, inserting those matches into the #link_table. At present, the query runs in 7.5 min, which appears slow for iterating over only 1458 records…
Sauron
  • 6,399
  • 14
  • 71
  • 136
0
votes
2 answers

iterative executing stored procedure with a set based approach

I have an issue where I am trying to replace the following code with a different solution. Currently I am using a cursor but it is running to slowly. I am under the understanding that iterative solutions can only be completed with cursors or while…
0
votes
1 answer

MySQL: The fastest way to split a table based on a condition

I have two tables: 1) is a list of all parameter-ids and the info to which set of parameters the parameter-id belongs 2) is data that includes some of the parameter-ids, and some additional data such as timestamp and values. I'm designing a…
Flo Win
  • 154
  • 10
0
votes
1 answer

Low performance set based storedprocedure

I am using following set based code in sql to calculate the customers bill but it takes 3 Min to performs the operation on 40000 records !!! let me know what is the problem ??? with cte…
franchesco totti
  • 582
  • 1
  • 7
  • 28