Another Temporary Table Q

Hi
I've been playing around with temporary tables ... everything works fine,
but I've read a few articles saying how they can affect overall performance
and I'm wondering if there may be a faster alternative performance.
Basically, the user fires off a query at the DB. With the results, I need to
delete some rows which are retrieved as well (they cannot be filtered out as
part of the SELECT statement because of the effects of LEFT JOINS) as amend
certain data values prior to returning the data to the user. I thought
temporary tables would be the way, so I could just
1) Create Temp Table
2) Populate with INSERT
3) DELETE rows fulfilling certain criteria
4) UPDATE certain data values
5) Return contents of Temporary Table to user as ADO dataset.
I'm wondering now if I could actually get all the Data back from the results
of the initial SELECT statement to the local machine fast, and then run the
DELETE/UPDATES locally so as to remove load from the central server. I've
done timings already comparing Temporary Tables to cycling through the raw
dataset locally and deleting rows and/or updating Data Values. Using
temporary tables is much faster.
So is there a way of getting all the data back as fast as possible from the
central server and then speedily deleting/updating rows of the local dataset
other than the way I've been testing. I'd wondered about creating some
in-local table (using DAO, for example) and then firing SQL at that ... but
I'm just not sure if that would be any quicker.
So in summary-
1) What's the quickest way of getting ALL the data from the server to the
local client?
2) What should I use on the local client?
Any thoughts would be greatly appreciated.
Thanks, again
SimonCan you post some more details, including DDL and some sample
data. There's a good chance this can all be achieved in a single
SELECT.|||<markc600@.hotmail.com> wrote in message
news:1137676726.723163.59050@.g47g2000cwa.googlegroups.com...
> Can you post some more details, including DDL and some sample
> data. There's a good chance this can all be achieved in a single
> SELECT.
Thanks for replying.
It will take me a while to get this together, I'm afraid.|||On Thu, 19 Jan 2006 12:40:29 -0000, Simon Woods wrote:

>Hi
>I've been playing around with temporary tables ... everything works fine,
>but I've read a few articles saying how they can affect overall performance
>and I'm wondering if there may be a faster alternative performance.
>Basically, the user fires off a query at the DB. With the results, I need t
o
>delete some rows which are retrieved as well (they cannot be filtered out a
s
>part of the SELECT statement because of the effects of LEFT JOINS) as amend
>certain data values prior to returning the data to the user. I thought
>temporary tables would be the way, so I could just
>1) Create Temp Table
>2) Populate with INSERT
>3) DELETE rows fulfilling certain criteria
>4) UPDATE certain data values
>5) Return contents of Temporary Table to user as ADO dataset.
>I'm wondering now if I could actually get all the Data back from the result
s
>of the initial SELECT statement to the local machine fast, and then run the
>DELETE/UPDATES locally so as to remove load from the central server.
Hi Simon.
Either I am completely misunderstanding what you're writing, or you are
completely misunderstanding how SQL Server works.
From this message, it appearrs as if you want to get all rows to the
client, then iterate over the results and send update or delete
statements for each row "to remove load from the server".
However, since the data is stored on the server, you'll have to do the
deletes and updates on the server as well. This means that in your
version, you'll have lots of network traffic and you force SQL Server to
do individual updates and deletes in the order that your application
processes them.
It's much better to keep all the logic in a single stored procedure on
the server. Not only will this save you lots of network traffic, it will
also enable the query optimizer to pick the best order of execution for
the updates and deletes (provided you write them as set-based UPDATE and
DELETE statement, of course).
Hugo Kornelis, SQL Server MVP|||Hugo ... thanks for replying ... see below
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info> wrote in message
news:qp50t15rumqtlr0din5fdbqc4tlh1g01kf@.
4ax.com...
> On Thu, 19 Jan 2006 12:40:29 -0000, Simon Woods wrote:
>
> Hi Simon.
> Either I am completely misunderstanding what you're writing, or you are
> completely misunderstanding how SQL Server works.
... the latter is most likely ...

> From this message, it appearrs as if you want to get all rows to the
> client, then iterate over the results and send update or delete
> statements for each row "to remove load from the server".
> However, since the data is stored on the server, you'll have to do the
> deletes and updates on the server as well. This means that in your
> version, you'll have lots of network traffic and you force SQL Server to
> do individual updates and deletes in the order that your application
> processes them.
On the client I'm actually wanting a read-only set of data so I don't want
to do any updating of the source data only issuing SQL to update and delete
the temporary table.
Having chatted to some of my colleagues, I've been pointed to ADO
disconnected datasets to try and move load away from temporary tables on the
server to recordset processing on the client. However, at the moment my
preference is to go with the temporary table solution - but I'll have to do
a bit more digging.
Thanks again|||On Fri, 20 Jan 2006 13:06:32 -0000, Simon Woods wrote:
(snip)
>Having chatted to some of my colleagues, I've been pointed to ADO
>disconnected datasets to try and move load away from temporary tables on th
e
>server to recordset processing on the client. However, at the moment my
>preference is to go with the temporary table solution - but I'll have to do
>a bit more digging.
Hi Simon,
I'm not familiar with ADO, so I can't comment on the disconnected
datasets. But considering that SQL Server is designed to do quick and
efficient manipulations on large amounts of data, I'm very much inclined
to agree with your preference of doing it on the server.
Hugo Kornelis, SQL Server MVP

another simple snapshot replication question

i have a publisher agent that generates 61 articles for publication
without a problem.
however i'm having trouble understanding exactly how the distribution
agent executes. when executed manually once, it ran successfully, and
the subscriber got the data.
is it possible to run the distribution agent again to re-push/pull data
for the subscribers without running the publisher agent? when i try to
re-run the distributor agent, i get the success message "No replicated
transactions are available." have the snapshot files become
unavailable, or is it just something where the distribution agent knows
that they are older than the subscriber last obtained?
if it's possible to have a distribution agent push/pull data from a
single collection of publisher snapshot articles multiple times, i
would greatly appreciate knowing how that's done.
thanks in advance for any help,
jason
Jason,
to have the distribution agent pick up any new snapshot files, you need to
reinitialize this particular subscriber (sp_reinitsubscription). However if
this is a regular process, perhaps you should just use snapshot replication
instead? Or are you trying to avoid table locking?
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||paul,
sorry, i should have specified that this is a snapshot replication. but
because of the very reason you mentioned, the table locking, i was
hoping that i could tweak the distribution agent steps without having
to republish the data (avoiding further table locks).
does this information suggest any particular solutions as possible /
superior?
thanks for the response,
jason
|||Sounds like transactional would be more beneficial. Unless there are loads
of updates to the same rows, you'll notice an improvement in performance
versus regualr snapshots. Also you'll avoid locking issues by using the
concurrent snapshot generation option when initializing/reinitializing.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||i thought that might be the case. but doesn't transactional replication
come with a performance hit to the updates in the publisher database?
this replication is just to get "production-like" data from the
production database into the development and staging databases. we
don't want to perpetually diminish the performance of the production
database for a process we probably only need to run every couple of
weeks/months. but at the same time, the database is accessed 24-7, so
if we can do the replication without record locking, that's highly
preferable.
another option i'm looking into is a DTS task, which also seems like it
could be done without locking the tables. but i'm still sorting out the
details on DTS with regard to object copies vs. data transformations,
and how to handle identity columns, etc.
sorry for that long contextual tangent. so would you still recommend
transactional replication for a task that really only needs to
replicate data once or twice a month?
thanks,
jason
|||With no downtime of the production site allowed and representative data
required at fortnightly intervals, I'd restore full database backups - much
easier solution with no hit on the production database.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||that's an awesome idea. we use some sql-aware third party backup
software, so if i can figure out how to restore a single database file
from there in a way that sql can use, that's definitely the solution
i'll choose.
thanks for the suggestion!

Another locking question

From this article by
Kalen...http://www.sqlmag.com/Articles/Index.cfm?ArticleID=26922...
she mentioned
"For INSERTS which are blocking, you might want to make sure that your
clustered index is NOT on a column that determines the order that the new
rows are arriving. For example, don't put your clustered index on OrderDate
or OrderNumber, which will always be increasing"
My quesion is why not ?I believe Kalen is just saying that if your inserts are causing blocking you
would not be helping the situation by having an index that uses these same
inserts as the logic to always through the data to the bottom of the last
leaf level of the table (clustered). I think that for inserts that are not
causing blocking it is quite acceptable to use a clustered index on a
sequential key (like an identity column).
Later in that same article Kalen talks about key locks, I've fixed quite a
few blocking issues by adding a clustered index to a table which then would
acquire these key locks. So you would have to test to ensure your inserts
are causing blocking.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OivRkZZhDHA.484@.TK2MSFTNGP12.phx.gbl...
> From this article by
> Kalen...http://www.sqlmag.com/Articles/Index.cfm?ArticleID=26922...
> she mentioned
> "For INSERTS which are blocking, you might want to make sure that your
> clustered index is NOT on a column that determines the order that the new
> rows are arriving. For example, don't put your clustered index on
OrderDate
> or OrderNumber, which will always be increasing"
> My quesion is why not ?
>