March 26, 2014 at 3:37 pm
For my own curiosity I wanted to know how to handle situations where developers ask for specific sprocs vs get all.
I have a table call Foo that has Id, FK (Field1), FK(Field2), field3, field4 and they are all varchars besides the ID,FK1,FK2 field which are ints. I have only one index on the table and it is on the ID. It is the PK on table. It seems like for the most part the developers really want the ID field by FK.
In this case I would have a few sprocs -
Get All of Foo
Get All of Foo by Id
Get ID of Foo by FK(Field1)
Get Id of Foo by FK(Field2)
Next I think about procedure cache. Should it look like this instead?
Get All of Foo
Get All of Foo by Id
Get All of Foo by FK(Field1)
Get All of Foo by FK(Field2)
In the last two cases they would currently throw everything away besides the id. Not sure how to push standards on this. Hopefully I can find some guidance.
March 27, 2014 at 7:58 am
JKSQL (3/26/2014)
For my own curiosity I wanted to know how to handle situations where developers ask for specific sprocs vs get all.I have a table call Foo that has Id, FK (Field1), FK(Field2), field3, field4 and they are all varchars besides the ID,FK1,FK2 field which are ints. I have only one index on the table and it is on the ID. It is the PK on table. It seems like for the most part the developers really want the ID field by FK.
In this case I would have a few sprocs -
Get All of Foo
Get All of Foo by Id
Get ID of Foo by FK(Field1)
Get Id of Foo by FK(Field2)
Next I think about procedure cache. Should it look like this instead?
Get All of Foo
Get All of Foo by Id
Get All of Foo by FK(Field1)
Get All of Foo by FK(Field2)
In the last two cases they would currently throw everything away besides the id. Not sure how to push standards on this. Hopefully I can find some guidance.
Not really what your question is here but you should not return all columns in a table when only 1 column is needed.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 27, 2014 at 9:33 am
So I/O outweighs procedure cache?
March 27, 2014 at 9:37 am
JKSQL (3/27/2014)
So I/O outweighs procedure cache?
Not sure what you mean by that?
There is no reason to create a stored proc to return all the rows of the table when all you need is 1 column. You have to read all that info from disc and then push it all across the network just to let it spill all over the floor.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 27, 2014 at 9:40 am
JKSQL (3/27/2014)
So I/O outweighs procedure cache?
Generally Yes, but also, the scheme you described would have no significant Procedure/Query Cache advantages that I know of. Different queries are still different, except for parameter values. And if you try to parametrize those differences into one query, you'll end up with a Catch-All query ([/url]which is bad).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 27, 2014 at 9:49 am
I don't see any need for a pre-built proc for:
Get All of Foo
Just SELECT the columns you need from the table instead.
For the rest, if you're willing to use dynamic SQL, I'd put them in the same proc.
If not, I'd probably do separate procs for each "major" key lookup. In this case all of them are probably major. But for other tables with potentially lots of lookup keys, I wouldn't want a separate proc for each one/combination.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply