Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Sorting based on user input.. Expand / Collapse
Author
Message
Posted Wednesday, July 10, 2013 3:13 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 15, 2014 12:20 PM
Points: 219, Visits: 338
Its a logic error..,sorry for the confusion..

My data in table is: A1 - 1, A2 - 2, A3 - 3, A4 - 4, A5- 5, A6- 6

1. User updates : A3 to 1, A4 to 2, A5 to 3, A6 to 4
Expected : A3-1,A4-2,A5-3,A6-4,A1-5,A2-6

2. Existing : A3-1,A4-2,A5-3,A6-4,A1-5,A2-6
User updates : A3 to 3, A4 to 4, A5 to 5, A6 to 6
Expected : A1 - 1, A2 - 2, A3 - 3, A4 - 4, A5- 5, A6- 6
Post #1472017
Posted Wednesday, July 10, 2013 4:01 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 12:53 PM
Points: 804, Visits: 721
Change your stored procedure to work with input in a table-valued parameter which holds the new sort order for all values. Or at least all values for which the sort order change. The stored procedure then becomes a straight update. The actual logic should be in the UI.

I don't know exactly how your UI works, but I assume that you present the data in a grid or list box, and the user can then move rows up and down in the control. When the user presses save, you compute the new numbers and then populate the TVP with the data. If you saved the old sort numbers, you can gain some efficiency by comparing with the old numbers and only populate the TVP with the change. But if the table only has some 50 rows, that is not really worth the complexity.

If you have never worked with table-valued parameters before, see this article on my web site: http://www.sommarskog.se/arrays-in-sql-2008.html.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1472035
Posted Wednesday, July 10, 2013 4:30 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 15, 2014 12:20 PM
Points: 219, Visits: 338
Thanks for the reply.
In UI I am binding data to a grid.. all the logic will be processed using stored proc and from back end.
In UI user will modify Displayorder of his interest and it will be passed to SP and need to process it.
If multiple updates are there then multiple times proc will be called. I am fine with Multiple calls to database.

Thanks..
Post #1472046
Posted Wednesday, July 10, 2013 5:33 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 12:53 PM
Points: 804, Visits: 721
deepkt (7/10/2013)
Thanks for the reply.
In UI I am binding data to a grid.. all the logic will be processed using stored proc and from back end.
In UI user will modify Displayorder of his interest and it will be passed to SP and need to process it.
If multiple updates are there then multiple times proc will be called. I am fine with Multiple calls to database.


And that's where your problems start. I suggested using a TVP for two reasons:

  • It's more efficient. You get a single round-trip to the database, instead of doing many calls. This is not the least important if you later would move the database to the cloud or somewhere else where there is a long latency between client and database.

  • It's simpler. The current logic you have is very difficult to get right, as you already have experienced. The first call goes right, but the second call gets messed up, because you are likely to make it from the situation before the first call. To compensate for this the client has to compensate for the logic in the stored procedure, or the stored procedure has to compensate for the logic in the client. Neither of these are good software-engineering practices, as different modules should be decoupled from each other as much as possible.



With the solution I suggest, the stored procedure is dirt simple: it's a single update from the TVP. Well, maybe a single MERGE statement as the user may add or delete rows as well. You need to some more work in the client - but not a lot. I don't know anything about grid-binding, but I assume that you can bind a DataTable to the grid, and you can pass a DataTable to a TVP.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1472058
Posted Wednesday, July 10, 2013 5:55 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 3,999, Visits: 3,434
Erland is right - you only need one round-trip to the database instead of one for each row.

In .NET, when you bind data to a grid, it's usually done in the code-behind and is very straightforward. This is where you use a SQL statement and include the ORDER BY clause. The user is then free to edit the fields. When the user clicks save, do the client-side validation then do your validation in the code-behind. Create and populate your table-value parameter and then call your procedure.

I don't know which development environment you're using, but I'd imagine that there would be something similar.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1472065
Posted Wednesday, July 10, 2013 6:01 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 15, 2014 12:20 PM
Points: 219, Visits: 338

Does there any possibility of fine tuning of my procedure to accommodate my requirement, I am stuck with the code.
Please help..

Post #1472069
Posted Wednesday, July 10, 2013 6:12 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 12:53 PM
Points: 804, Visits: 721
deepkt (7/10/2013)

Does there any possibility of fine tuning of my procedure to accommodate my requirement, I am stuck with the code.
Please help..


We are helping. You are refusing to be helped. I don't know why you think that you are stuck with the code. But to fix your current solution, we would need to know the client code as well - and, no thanks, I don't want to see it.

Listen, we don't say these things out of thin air. I have tackled similar problems in the past the way you are trying to do, and I never got it work. There was always one more case. The only reasonable way is to send in all data in one go.

If you really want to update rows one by one, starting with negating all sorter values, and the update each row one by one to the new order. But that's a very ineffecient solution which will kill you sooner or later. So be a good boy and change the code to use a TVP, and learn something you can use in the future.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1472073
Posted Wednesday, July 10, 2013 7:32 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 15, 2014 12:20 PM
Points: 219, Visits: 338
Thanks for the reply..

I understood your point, If possible could you share logic using TVP .

Thanks. Sorry for keep on posting same query. But some how I have to fix it.
Post #1472128
Posted Wednesday, July 10, 2013 8:09 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 12:53 PM
Points: 804, Visits: 721
deepkt (7/10/2013)
I understood your point, If possible could you share logic using TVP .


I have already given it. When the user is done with the grid, populate the sorter column from the order in the grid, pass the TVP, please see http://www.sommarskog.se/arrays-in-sql-2008.html for examples, although assuming that you already have a DataTable, it is very simple. Just pass the DataTable as the value, and the type for the parameter is SqlDbType.Structured. (All assuming that you are using .Net.)

The stored procedure is just a straight update from the DataTable - or a MERGE statement, if you also want to permit the user to add and delete rows.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1472167
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse