procedure using dynamic SQL? - 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 2000 Forums

SQL Server Development (2000)

procedure using dynamic SQL?

Author

Topic

CorpDirect
Yak Posting Veteran

92 Posts

Posted - 2006-03-28 : 21:22:22

Yes, I know -- dynamic SQL is BAD, shouldn't be used for application code, is generally appropriate only for administrative tasks, etc.

Reference: The Curse and Blessings of Dynamic SQL by Erland Sommarskog

Please forgive the wordy post... I'm trying to be thorough!

So, I'm trying to find a way around using dynamic SQL without making maintenance a nightmare. Here's the situation; can you make any suggestions?

Our ERP application stores much of its data in "split" tables set up as "current" and "history". Each period close (roughly monthly, but irregular as this is a manual operation) completed items (e.g. orders, invoices) are moved to history tables, incomplete items are copied to history but maintained in current, and any new items are recorded in current tables throughout the period. The numbers of records are not beyond SQL Server's capacity by any means; in current tables there may be a few tens of thousands of records, while in their paired history tables there may be a few hundreds of thousands or a few million. That's just how the application is built, a design artifact left from its days of using FoxPro data files.

Within the application one has to choose "current" or "history" for any operation where this could be relevant -- printing an invoice, searching for payments, reviewing orders, etc. This is obviously poor design, but we have no control over this...

We are building web access for customers to see their orders, invoices, etc. on demand. Information must be available for at least one year through the website, and customers can't be bothered with selecting "current" or "history" based on our arbitrary period closings. Also, we are building other applications that access the same source data quite frequently, and these also need to be able to ignore the current/history split. Result sets for these applications may need to be queried from one or many of these current/history table pairs in a single operation.

Since some duplication of records occurs between current and history tables (remember those incomplete items copied over to history, but left in current?), we have to exclude these duplicates from any query results. I've tried several things, including this:

SELECT col1, col2, col3 FROM tblA_current
UNION ALL
SELECT col1, col2, col3 FROM tblA_history
WHERE id_col NOT IN (SELECT id_col FROM tbl_current)

I've also tried just UNION on select statements, but some duplication can still occur as records can be updated in current but left unchanged in history -- so UNION sees them as distinct, of course.

When I pull data from just the current tables, everything is fast, fast, fast... and even when I pull from just the history tables, even though the tables are many times larger, everything is still pretty fast because that's what SQL Server does well!

Now I'm trying to think of a way to determine on-the-fly whether data should be pulled from current or history tables and to only query against those tables. Let's say that an inquiry needs to return data from 5 different current/history table pairs. If I could identify whether the data is stored in current tables before querying, then I could eliminate both the manual choice and the joining or unioning of tables. Since I don't know another way, I'm considering dynamic SQL.

Something like this:

DECLARE @currhist CHAR(1)
-- determine current or history data source
IF EXISTS (SELECT * FROM tblA_current WHERE <criteria>)
  SET @currhist = 'C'
ELSE
  SET @currhist = 'H'
-- return results for tblA using stored procedure
EXECUTE sproc_tblA
  @source = @currhist
-- return results for tblB using stored procedure
EXECUTE sproc_tblB
  @source = @currhist

-- stored procedure example with dynamic SQL
CREATE PROCEDURE sproc_tblA (
  @source CHAR(1) = 'C' -- default to current
  )
AS
DECLARE @src_tbl NVARCHAR(4000)
DECLARE @SQLtext NVARCHAR(4000)
-- set data source to current or history
IF @source = 'C'
  SET @src_tbl = 'tblA_current'
ELSE IF @source = 'H'
  SET @src_tbl = 'tblA_history'
-- generate dynamic SQL
SET @SQLtext = 'SELECT col1, col2, col3 FROM ' + quotename(@src_tbl)
-- execute query
EXECUTE sp_executesql @SQLtext

That's all just off my head and untested, so may not run, but that's the general idea. Of course some filter criteria will also be included, error checking, et cetera. The actual procedures may involve multiple tables, and will certainly be much more complex. Several procedures may be executed to return a collection of result sets, all of which will have to pull from either current or history, but that can be determined by a single check at the beginning before executing all the procedures.

The alternative seems to be maintaining virtually duplicate code for querying current tables or history tables. This could become a maintenance headache as complex code is required for some procedures, and would have to be duplicated.

How much would it help in terms of caching execution plans (and any other performance benefits) to use sp_executesql with dynamic SQL in this way? Can anyone offer suggestions for better methods of handling this situation? I'm all ears!

Thanks for reading,

Daniel

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-28 : 21:31:02

You may not really required Dynamic SQL.

You can also consider using a temp table ?


create table #result (col1, col2 col3 . . .)
insert into #result (col1, col2, col3 . . .)
select col1, col2, col3 . . .
from   current
where  . .  .

insert into #result (col1, col2, col3 . . .)
select col1, col2, col3 . . . 
from   history h
where  not exists (select * from #result x where . . . )
and    . . .

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

CorpDirect
Yak Posting Veteran

92 Posts

Posted - 2006-03-28 : 21:42:17

Thanks for the reply khtan. I'm not sure about this part, though...?

quote:

Originally posted by khtan

...
from   history h
where  not exists (select * from #result x where . . . )
...

What would go in the "where not exists (...)" bit?

An example table, order_item, might be uniquely identified by (order_no, line_no). How could this be used in your "where not exists" clause to eliminate duplicate selections from the history table?

Thanks,

Daniel

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-28 : 21:47:11

select ....
from  history h
where not exists (select * from #result x 
                  where  x.order_no = h.order_no and x.line_no = h.line_no)

CorpDirect
Yak Posting Veteran

92 Posts

Posted - 2006-03-28 : 21:54:45

One concern about this: if a procedure queries say 6 pairs of curr/hist tables with complex joins, and the table pairs have hundreds of thousands of rows (or some have millions) per each pair, AND we could have many users pulling similar reports at the same time, could this be a big hit on resources?

Well, I may have just answered my own question as the user must provide some filter criteria, and only matching records would get pulled into temp tables, right? Assuming of course I write the procedure that way.

Interesting, thanks. I'll try this and see how it works.

Anyone have other suggestions?

Daniel

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-28 : 22:02:56

Your table may have millions of records but users most probably will only required 20% of it most of the time.
And as long as the tables are properly indexed and the query optimized the performance should be reasonable.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources