Reducing Recordset Columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Reducing Recordset Columns

Author

Topic

Kristen
Test

22859 Posts

Posted - 2010-03-31 : 04:52:48

We have SProcs for various business processes, these return one/many Recordsets which are then "merged" with a CMS template to display to the user.

The Sprocs are generic in the sense that they return all the columns that are likely to be needed. Of course not every client uses all the columns. But we have wanted to avoid any customisation (because of difficulties of deployment and support), and did not want use dynamic SQL because users would need SELECT permission on the table (now solvable using impersonation I think? so that they will still just need EXECUTE on the Sprocs)

So ... I am thinking of passing a parameter to the Sproc with the Output Column List required for the Recordset, and then applying that to the resultset using dynamic SQL.

Old way:


SELECT Col1, Col2, ...
FROM MyTableA AS A
     JOIN MyTableB AS B
         ON B.SomeID = A.SomeID
WHERE     A.SomeCol1 = @ParamA
      AND B.SomeCol2 = @ParamB

New way (1)


SET @SQLString = N'SELECT ' + @ParamColumnList'
+ 'FROM MyTableA AS A
     JOIN MyTableB AS B
         ON B.SomeID = A.SomeID
WHERE A.SomeCol1 = @ParamA
      AND B.SomeCol2 = @ParamB'

SET @ParmDefinition = N'@ParamA ..., @ParamB ...'
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @ParamA = @ParamA, @ParamB = @ParamB

which will result in only one query plan (but specific to the client's implementation)

or

New way (2)


INSERT INTO #MyTemp
SELECT A.PkID
FROM MyTableA AS A
     JOIN MyTableB AS B
         ON B.SomeID = A.SomeID
WHERE A.SomeCol1 = @ParamA
      AND B.SomeCol2 = @ParamB

SET @SQLString = N'SELECT ' + @ParamColumnList'
+ 'FROM #MyTemp AS T
     JOIN MyTableA AS A
         ON A.PkID = T.PkID
     JOIN MyTableB AS B
         ON B.SomeID = A.SomeID'

this method has no WHERE clause in the second (dynamic SQL) query - that is done by the first query.

(We do have quite a lot of Sprocs where the IDs are first pulled into a Temp Table (although usually an @TableVar) and then that is used JOINed to many tables for the Display stage.)

Order By:

Method 1 could add that as Dynamic SQL - which has some appeal (client side can have more control)

Method 2 can do it how it always has (using CASE statements etc.) and the #MyTEMP table can have an IDENTITY and use that for Order By in the dynamic SQL.

I'm not a big fan of Dynamic SQL, and thus I lean towards Method 2, but maybe Method 1 will mean that I use more-and-more variations over time, e.g. to optimise the SQL Query Plan, and thus I should go that route.

Or maybe there are other methods I should consider?

Thoughts?

Thanks guys

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-31 : 05:02:32

Isn't it possible to show only the required columns when you show data in the front end application? Based on the input list of ouput columns, do check

If recordset(0).Name="MY Column name" then
--include it
end if

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-03-31 : 05:11:12

Yes, but I am SELECTing columns that are then not used by Client (so I have some wasted SELECT time and also some wasted Transmission time to the Client)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-31 : 05:19:15

Is it for a customised report where users can select the columns they want to see?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-03-31 : 08:51:00

Not exactly, but similar to that.

We have a number of clients all using the same package.

Lets say that the SProc returns 20 columns (ProductCode, ProductName, ProductPrice, BasketQty, ...)

There is also a CMS template (that we initially set up with appropriate "design" for the client, but the client can change thereafter).

The CMS Template looks like this:


HEADER TEMPLATE:

<table>
<tr>
<th>Product:</th>
<th>Price:</th>
<th>Qty:</th>
...
</tr>

ROW TEMPLATE:

<tr>
<td>{ProductName} ({ProductCode})</td>
<td>{ProductPrice}</td>
<td><input type="text" id="BasketQty" value="{BasketQty}"</td>
...
</tr>

FOOTER TEMPLATE:

</table>

our "engine" then renders the output the user sees using that template. The "Row Template" is repeated for each row in the recordset.

The Client may not use all the possible columns from the Sproc in their Template.

However, possibly important point, the CMS Template will not change very often, so the actual column list used will be the same each time it is used in SQL / Query Plan (unlike "users can select the columns they want to see" where there is usually a lot of variation in column list)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-31 : 09:11:42

Why not just make sperate stored procs for each template?

Kristen
Test

22859 Posts

Posted - 2010-04-01 : 03:03:48

I think I haven;t explained my scenario very well.

We do have separate Sprocs for each template. But the user may change the template, and thus the actual fields used may be fewer than the Sproc returns.

I don't want to change the Sproc when the template is changed as that adds too much change control, and means that each client will have different sprocs.

So my suggestion is to use dynamic SQL so that only the columns used in the template are actually in the SELECT list.

The App could fairly easily work out what columns are actually in the template, and the logic of the SProc won't change, just the SELECT list

Looking for the best way to do this using only EXECUTE permission on the SProc, not SELECT permission on the table

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-01 : 05:59:07

<<
Looking for the best way to do this using only EXECUTE permission on the SProc, not SELECT permission on the table
>>

When you use SQL Server 2008, isn't it possible using Impersonation as specified in your first post?

Also I dont see the point on joing the #temp tables with real tables

Is it possible to apply this logic?


CREATE PROCEDURE your_proc
(
@paramA ..
.
.
@required_cols varchar(1000)
)
as
Create table #temp (col1..............) --- All the possible columns that are included 
insert into #temp(col list)
SELECT Col1, Col2, ...
FROM MyTableA AS A
     JOIN MyTableB AS B
         ON B.SomeID = A.SomeID
WHERE     A.SomeCol1 = @ParamA
      AND B.SomeCol2 = @ParamB

declare @sql varchar(max)
set @sql='SELECT '+@required_cols+' FROM #temp'
EXEC(@sql)

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-04-01 : 06:01:42

Yes, that would work well. I wonder if there is some "wasted CPU" putting all columns in #TEMP just to be able to then select some of them?

e.g. if come columns are TEXT / VARCHAR(MAX) - they will be retrieved from DB, stored in #TEMP, and then

not used

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-01 : 06:24:22

quote:
Originally posted by Kristen

Yes, that would work well. I wonder if there is some "wasted CPU" putting all columns in #TEMP just to be able to then select some of them?

e.g. if come columns are TEXT / VARCHAR(MAX) - they will be retrieved from DB, stored in #TEMP, and then not used

Yes. There is a possibility

Have you tried impersonation?
Also you are trying dynamic static-sql

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-04-01 : 06:30:41

"Have you tried impersonation?"

I read it somewhere, but have forgotten what the syntax is

do you remember?

"Also you are trying dynamic static-sql"

What's that? (I assume I know, but am not sure of the term "dynamic static-sql")

Kristen
Test

22859 Posts

Posted - 2010-04-01 : 06:33:33

Impersonation - found it here: http://www.sommarskog.se/grantperm.html

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-01 : 06:45:30

<<
What's that? (I assume I know, but am not sure of the term "dynamic static-sql")
>>

I just said to mean that you want dynamic sql functionality but to perform like static sql (No need of SELECT permission for the table)

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-04-01 : 07:17:24

Ah, OK. Yes, correct. But that should mean that there is only one query plan in the cache (whereas typical dynamic SQL will usually have a number of variations, each taking on some cache space.

I'm now leaning towards my first method, to make the actual query fully dynamic SQL. Erland Sommarskog's articles are quite persuasive about tricks that can be used to optimise dynamic SQL which are not available for regular Sprocs.

For example, if there is an ID index and a Date index then if the parameters for the date range are far apart, and a specifc value has been provided for the ID then a Query Plan using the ID index is better - for a narrow date range then the Date index is better.

For SProc the first execution will define the Query Plan of course ... so will only be good for one, or the other.

This is hard to work around in Sproc (probably have to call child-sproc A or B, each with their own query plan.

With dynamic SQL just add "AND 1=1" for the first case, and "AND 2=2" for the second to force two separate query plans in the Cache

So ... if I go down "fully dynamic SQL" route then it will be easy for the to add those sorts of variations over time, as tuning becomes necessary.

Unless anyone has any other ideas?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources