|
|
Posted by Erland Sommarskog on 09/22/05 00:22
calan (none@nospam.com) writes:
> Relative SQL newbie here......this is probably easy, but....
>
> Lets say I have a table (MainTable) that stores a list of input table
> names, a primary key (PKey), and a field called "Configured" for each
> one. Each of these input tables also contain a field called
> "Configured", which is set to true or false in another process based on
> an OrderNumber. (So an order's inputs are stored in several input
> tables, and the MainTable is a summary table that shows which input
> tables have been configured for any given OrderNumber).
>
> What I need to do is open each input table, and look for a record
> containing a specific OrderNumber and where Configured=true. If a record
> is found, I need to update the Configured field for that table in the
> MainTable, and then move on to the next sub-table.
What is the purpose of all these input tables?
This is a somewhat unorthodox design. A table with table names to process,
is not the normal SQL idiom, at least not for an order system. (For a
database maintenance job it could be.)
You could certainly improve performace by putting the code into a stored
procedure, because you would be saved all the network roundtrips. But the
procedure should not really have a loop over the input table, rather it
should have an UPDATE statement per table.
Table are very much individual objects in SQL Server. Each object has its
set of statistics and indexes, which can result in two similar queries
on two tables can get very different query plans.
Now, I don't know your tables at all, but it is not uncommon when people
have several tables likes, that these tables should in fact be one
single table with one key added.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|