Reducing Round Trips

Question

Reducing Round Trips

Steve Jones - SSC Editor

SSC Guru

Points: 736173
More actions
June 27, 2019 at 12:00 am

#3656231

Comments posted to this topic are about the item Reducing Round Trips

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Stewart "Arturius" Campbell SSC Guru Points: 72624 More actions · Answer 1

Nice, easy question, thanks Steve

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

Jon 0x7f800000 SSC-Addicted Points: 497 More actions · Answer 2

Is it not one round trip if you put both statements in the same batch? In Java I think the following is only 1 round trip:

Statement stmt = cn.createStatement();
ResultSet rs = stmt.executeQuery("DECLARE @P1 int; \n" + 
"EXEC sp_prepare @P1 output, \n" + 
"N'@P1 int', \n" + 
"N'SELECT a FROM test_table WHERE b=@P1'; \n" + 
"EXEC sp_execute @P1, 3;");

Steve Jones - SSC Editor SSC Guru Points: 736173 More actions · Answer 3

Reworded slightly. It would be one trip with that code.

I was trying to raise awareness of the simplicity of sp_prepexec().

Jon 0x7f800000 SSC-Addicted Points: 497 More actions · Answer 4

Cool, thanks for confirming. You definitely achieved your goal of raising awareness of sp_prepexec() - I'll remember that one for the future 🙂

palotaiarpad SSCertifiable Points: 5617 More actions · Answer 5

Please clear it up for me. Why isn't an option to create a procedure. If i'm using that SQL snippet from an application with different parameters, i'd surely create a procedure. For me it seems to be easier to maintain too.

This reply was modified 5 years, 4 months ago by palotaiarpad.

Jon 0x7f800000 SSC-Addicted Points: 497 More actions · Answer 6

Hi Palotaiarpad.

I think this is because the question states, "I need to code some SQL in my application", which means that it's an ad-hoc query from the application. Creating a stored procedure from the application code means that it's not a pre-existing stored procedure already in the DB. One wouldn't want to create a stored procedure for every ad-hoc query being done from the application (which reminds me, those procedures would probably have to be dropped afterwards too). The question is also looking to reduce round trips, and stored procedures have to be created in a batch by themselves, which would force two round trips if using that approach. On connections with high latency, those round trips can be a death sentence.

If it was not an ad-hoc query and instead was a fairly static query that was going to be used a lot, then creating it as a stored procedure in the DB makes sense.

palotaiarpad SSCertifiable Points: 5617 More actions · Answer 7

Ok, then it's due to my english knowledge. I missed the 'in' and understood it as 'for'. 🙂