SQL Server Performance Forum – Threads Archive

factoring queries…

after writing about a hundred stored procedures for our application, i’m seeing that we have a lot of repetition and similarities between our stored procedures, when looking at the sprocs for a given table. i might have one sproc which selects columns for a given ID, one sproc which selects the same columns for all records, and one sproc which selects the same columns with more complicated filtering and sorting logic. when adding columns to the table, i have to go and update (copy/paste) all sprocs to synchronize them with the table columns. are there efficient ways of factoring out the basic table query, for all the columns i’m interested in, and then layering the filter (WHERE clause) on top of that. would it be efficient to do something like this…? (just for example, not accurate syntax) SELECT * FROM (EXEC innerTableQuery) WHERE table.ID = @ID SELECT * FROM (EXEC innerTableQuery) SELECT * FROM (EXEC innerTableQuery) WHERE (table.ID > @FirstID) AND (table.ID < @LastID) and we could have an ‘innerTableQuery’ sproc which returns all the specified columns. this would be an alternative to having to list all the columns in all the sprocs. also, it gives me the chance to build other more specific sprocs which use that inner query sproc to provide some other sort of filtering. there might even be alternatives with table variables and/or derived tables… i’m not sure. thanks for any insight,
Kirk

quote:
this would be an alternative to having to list all the columns in all the sprocs

Listing all columns in the sprocs is always the best alternative. You avoid returning columns you do not need (extra network trafic). code won’t breaks upon changes in tables. So I’d recommand to specify the columns you need.

quote:
i might have one sproc which selects columns for a given ID, one sproc which selects the same columns for all records, and one sproc which selects the same columns with more complicated filtering and sorting logic.

This is something that you can many times resove with default values.
For example:
CREATE PROCEDURE get_orders
(
@orderid int = null
)
IF @isnull(@orderID,0) = 0
BEGIN
SELECT column_1, column_2..
FROM ORDERS
END
ELSE
BEGIN
SELECT column_1, column_2..
FROM ORDERS
WHERE ORDERID = @orderid
END CREATE PROCEDURE get_users_details
(
@user_id varchar(20)= null
)
SELECT column_1, column_2..
FROM USERS
WHERE ISNULL(@user_id, ”) = ” OR (USER_ID = @user_id)
— or COALESCE(@user_id, USER_ID) =
Though to my experience the first example would user better indexes when there are.
Try to group all those similar sprocs so you’ll end up with a smaller number of sprocs that would be easier to maintain.

quote:
SELECT * FROM (EXEC innerTableQuery) WHERE table.ID = @ID
SELECT * FROM (EXEC innerTableQuery)
SELECT * FROM (EXEC innerTableQuery) WHERE (table.ID > @FirstID) AND (table.ID < @LastID)
and we could have an ‘innerTableQuery’ sproc which returns all the specified columns.

Assuming ‘innerTableQuery’ is a sproc, you can not do
SELECT * FROM (EXEC innerTableQuery)
You would have to first insert the results into a temporary table, then filter the lines with
the rest of your conditions. Sometimes it’s the only (or the better) way but most of te times there are better solutions.
2 possible solutions I can think about: views or functions.
Create a view selecting the columns you need, and work on this view. When you need to change something, you would only have to change the view. It would also perform better since you would be able to use indexes of tables, and you will be avoiding all the overhead of temp tables.
If the inner query needs to accept parameters, you can use inline functions or table value functions. If you need more details on that let us know. Bambola.

I think in this case writing generic queries would be a good idea. The example that Bambola has given can be useful. Adding to what Bambola has written,
CREATE PROCEDURE get_orders
(
@firstid int = null,
@secondid int = null
)
IF @isnull(@firstID,0) = 0 and @isnull(@secondID,0) = 0
BEGIN
SELECT column_1, column_2..
FROM ORDERS
END
ELSE IF @isnull(@firstID,0) <> 0 and @isnull(@secondID,0) = 0
BEGIN
SELECT column_1, column_2..
FROM ORDERS
WHERE ORDERID = @firstID
END
ELSE
BEGIN
SELECT column_1, column_2..
FROM ORDERS
WHERE ORDERID between @firstID and @secondID
END HTH. Gaurav

quote:Originally posted by bambola
2 possible solutions I can think about: views or functions.
Create a view selecting the columns you need, and work on this view. When you need to change something, you would only have to change the view. It would also perform better since you would be able to use indexes of tables, and you will be avoiding all the overhead of temp tables.
If the inner query needs to accept parameters, you can use inline functions or table value functions. If you need more details on that let us know.

ah, i hadn’t thought about views… that’s probably what i’m looking for. i haven’t used them before, even though i probably should be. thanks for giving me a direction to investigate.
Kirk

Acually, there is no need for 3 IF blocks for this sproc. <pre> IF @isnull(@firstID,0) = 0 BEGIN SELECT column_1, column_2.. FROM ORDERS END ELSE BEGIN SELECT column_1, column_2.. FROM ORDERS WHERE ORDERID BETWEEN @firstID AND COALESCE(@secondID, @firstID) END </pre> or if a table scan would take place anyone with 1 <pre> SELECT column_1, column_2.. FROM ORDERS WHERE (ISNULL(@firstid,0) = 0) OR (I_ORDERID BETWEEN @firstid AND COALESCE(@firstid, @secondid)) </pre> <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> I think that too many IF blocks get the sprocs more difficult to read an maintain. And I would use this method only when there are 1-2 optional vaiables (to compair with column value). kirkmarple - If you have 2 sprocs that return the same columns but one has no filters and the other has many, I would stick with 2 different sprocs. If number of columns are the only difference between the sprocs and fitering is done the same, depending on the differenc I would either write a view and 2 different sprocs reading from it or return an extra column/s. There could also be a case in which you have some basic filters you repeat on a certain table, with a need to either select different columns or add filters. In this case you can write a view that returns all the columns and different procedures that select the columns needed and add filters as needed. I hope that was not too confusing. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> If you have more specific questions feel free to ask. Bambola.

i’ve been rewriting my sprocs with views, and it looks like exactly the tool i needed. there’s one common model of a query that i’m wondering if there’s an easier approach. so, i have a view called ValidAssets, and in some cases, i want to join that with a "sub-class" table, to get something like ValidAudioAssets. i want to get something of the effect… (obviously not legal T-SQL). SELECT
ValidAssets.*,
audio_assets.bitrate AS Bitrate
FROM
ValidAssets INNER JOIN
audio_assets ON (…)
WHERE
ValidAssets.AssetID = @AssetID i don’t want to have to redefine all the columns in ValidAssets within the "subclassed" query, since there are 20 or so of them and that defeats the purpose of having the view to factor all that stuff out. any thoughts? thanks,
Kirk

Views cannot accept parameters, but functions can. It seems that what would need here is an INLINE FUNCTION.
CREATE FUNCTION FN_ValidAudioAssets(@AssetID int)
RETURNS TABLE
AS
RETURN SELECTValidAssets.*
, audio_assets.bitrate AS Bitrate
FROMValidAssets
INNER JOIN audio_assets ON (…)
WHERE ValidAssets.AssetID = @AssetID Bambola.

]]>