table hosed: insert

Showing posts with label insert. Show all posts

Friday, March 23, 2012

Is there a better way to do this very simple query(s)?

I' m almost certain that my approach is wrong but it works! Could someone
tell me the correct way to achieve the following?
-- Start Code --
INSERT INTO
Categories (Description)
SELECT DISTINCT CategoryDescription
FROM Import
UPDATE Categories SET StatusID = 1
UPDATE Categories SET DateAdded = GETDATE()
-- End Code --
Also could someone suggest a good book for SQL programming? As you can tell
I am lost!
Thanks,
TomThis can be done during the INSERT (assuming the table is empty when you
begin):
INSERT INTO
Categories (Description, StatusID, DateAdded)
SELECT DISTINCT CategoryDescription, 1, GETDATE()
FROM Import
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:F747781D-B975-4664-AAF8-90C5A0EFB536@.microsoft.com...
I' m almost certain that my approach is wrong but it works! Could someone
tell me the correct way to achieve the following?
-- Start Code --
INSERT INTO
Categories (Description)
SELECT DISTINCT CategoryDescription
FROM Import
UPDATE Categories SET StatusID = 1
UPDATE Categories SET DateAdded = GETDATE()
-- End Code --
Also could someone suggest a good book for SQL programming? As you can tell
I am lost!
Thanks,
Tom|||Since you did not bother to expalin "Import", I am making some
assumptions.
INSERT INTO Categories (cat_description, foobar_status, start_date,
end_date)
SELECT DISTINCT cat_description, 1, start_date, end_date
FROM Import ;
There cannot such a thing as a "status_id" -- the data element is
either an identifier and belongs to one and only one entity. If it is a
status, it is a value that shows the status of some non-key attribute.
Status of what'
People who never learned RDBMS sometimes make fools of themselves by
using IDENTITY as the key for everything, just like they were still in
file systems and had to have a record number.
You do know that a temporal data element is modeled in durations, not
in points, don't you?|||Tom,
I'm sorry I left out the fact that the table is indeed empty.
That is exactly what I was looking for! Thank You! You brought up a good
point however, next w

when I get an updated category list from my vendor,
how would I go about adding only the new items to the Categories table and
setting the StatusID=1, DateAdded=GetDate()?
Thanks again, I really appreciate the help!!!
Tom
"Tom Moreau" wrote:

> This can be done during the INSERT (assuming the table is empty when you
> begin):
> INSERT INTO
> Categories (Description, StatusID, DateAdded)
> SELECT DISTINCT CategoryDescription, 1, GETDATE()
> FROM Import
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Tom" <Tom@.discussions.microsoft.com> wrote in message
> news:F747781D-B975-4664-AAF8-90C5A0EFB536@.microsoft.com...
> I' m almost certain that my approach is wrong but it works! Could someone
> tell me the correct way to achieve the following?
> -- Start Code --
> INSERT INTO
> Categories (Description)
> SELECT DISTINCT CategoryDescription
> FROM Import
> UPDATE Categories SET StatusID = 1
> UPDATE Categories SET DateAdded = GETDATE()
> -- End Code --
> Also could someone suggest a good book for SQL programming? As you can
> tell
> I am lost!
> Thanks,
> Tom
>|||
"--CELKO--" wrote:

> Since you did not bother to expalin "Import", I am making some
> assumptions.
> INSERT INTO Categories (cat_description, foobar_status, start_date,
> end_date)
> SELECT DISTINCT cat_description, 1, start_date, end_date
> FROM Import ;
> There cannot such a thing as a "status_id" -- the data element is
> either an identifier and belongs to one and only one entity. If it is a
> status, it is a value that shows the status of some non-key attribute.
> Status of what'
> People who never learned RDBMS sometimes make fools of themselves by
> using IDENTITY as the key for everything, just like they were still in
> file systems and had to have a record number.
> You do know that a temporal data element is modeled in durations, not
> in points, don't you?
>|||CELKO,
Go to bed! If I wanted to be honored by your intelligence I would have
contacted you directly. My question was simple and answered in a prompt,
precise and professional manner.
Tom Moreau, understood my question, thanks Tom.
Your question, and "solution" for that matter, is far from what I was
asking. My question dealt with 3 (three) columns, your solution 4 (four)!
"Import" is a table, although I agree it is a bad name, I didn't name it!
And finally I disagree with you on the "StatusID", the value actually comes
from a lookup table that makes perfect sense to me, my company and most of
the rest of the world! I simplified the sample query to get my question
answered! StatusID id NOT an IDENTITY but a foreign key.
Thanks Again Tom!|||>> If I wanted to be honored by your intelligence I would have contacted you
directly. <<
You might want to learn how newsgroups work.
No, you just posted it without any clean up. And without any DDL,
either.
Fine, but it makes no sense to anyone who uses ISO-11179 standards. It
might not be your company, but it is the rest of the world. But those
who go fishing for quick kludges are probably not going to learn such
things.
And the reason that I gave you four columns is that they model three
data elements. Again, time is modeled in durations of helf-open
intervals. Look up the work done for the past few decades by Rick
Snodgrass at the University of Arizona. Looking at my answer, I think
I would change it to at least this:
INSERT INTO Categories (cat_description, foobar_status, start_date,
end_date)
SELECT DISTINCT cat_description, 1, CURRENT_TIMESTAMP, NULL
FROM ImportStagingTable;
But I would squeeze out blanks, watch the case of the description
string, etc.|||CELKO,
Go away already will you?
"--CELKO--" wrote:

> You might want to learn how newsgroups work.
>
I know how newsgroups work, that's why I posted the question here. Most
people offer useable suggestions and solutions to the QUESTION ASKED not
thier own take as to what it should be.

> No, you just posted it without any clean up. And without any DDL,
> either.
>
Sorry, there was no cleanup to do! It was a simple question in need of a
simple answer! I'm sorry it was beneath you. I agree there was no DDL but
my question was so SIMPLE I didn't feel it was necessary. I simply asked a
SIMPLE question and you felt the need to show off your intelligence, actuall
y
in this case LACK of intelligence.

> Fine, but it makes no sense to anyone who uses ISO-11179 standards. It
> might not be your company, but it is the rest of the world. But those
> who go fishing for quick kludges are probably not going to learn such
> things.
>
In perticular what subsection of ISO-11179 are you talking about? You don't
know this database and therefore you have no business telling what it does
and does not comply to!

> And the reason that I gave you four columns is that they model three
> data elements. Again, time is modeled in durations of helf-open
> intervals. Look up the work done for the past few decades by Rick
> Snodgrass at the University of Arizona. Looking at my answer, I think
> I would change it to at least this:
> INSERT INTO Categories (cat_description, foobar_status, start_date,
> end_date)
> SELECT DISTINCT cat_description, 1, CURRENT_TIMESTAMP, NULL
> FROM ImportStagingTable;
> But I would squeeze out blanks, watch the case of the description
> string, etc.
I agree with your modeling statement but you are missing the point!
Why are you forcing me an end date when I don't need one? Not that it is
either important OR any of your business, I am tracking fish being released
and I need to know the "description", "status", and "date" of the release.
Very simple!!!

>
"--CELKO--" wrote:

> You might want to learn how newsgroups work.
>
> No, you just posted it without any clean up. And without any DDL,
> either.
>
> Fine, but it makes no sense to anyone who uses ISO-11179 standards. It
> might not be your company, but it is the rest of the world. But those
> who go fishing for quick kludges are probably not going to learn such
> things.
> And the reason that I gave you four columns is that they model three
> data elements. Again, time is modeled in durations of helf-open
> intervals. Look up the work done for the past few decades by Rick
> Snodgrass at the University of Arizona. Looking at my answer, I think
> I would change it to at least this:
> INSERT INTO Categories (cat_description, foobar_status, start_date,
> end_date)
> SELECT DISTINCT cat_description, 1, CURRENT_TIMESTAMP, NULL
> FROM ImportStagingTable;
> But I would squeeze out blanks, watch the case of the description
> string, etc.
>

Is there a better way of doing an INSERT or UPDATE

I have several different situations where I want to
Update the record if it exists
or
add a new record if it does not exist.
An example might be
----
-- Has this table already got a record for this entry...
select @.rowcnt=count(*) from mytable where myField = '123'
if ( @.rowcnt = 0 )
begin ... NO so create the record with an initial cnt of 1
insert into mytable (myField, cnt) values ( 'text123', 1 )
end
else
begin -- ... YES so increment the cnt
Update mytable set cnt=cnt+1 where myField = 'text123'
end
---
The select count(*) can take a long time on a large table.
Table Structure something like
--
id PK
myField varchar(32)
cnt int
--
Thanks
BillTry:
If Exists (select * from mytable where myField = '123')
update ...
else
insert ...
The Exists will perform much better than the Count because it will stop as
soon as it hits a match.
"Bill" <wje@.blueyonder.co.uk> wrote in message
news:33ff429ctg88ethir0ej3g4plhfbr19sqo@.
4ax.com...
>I have several different situations where I want to
> Update the record if it exists
> or
> add a new record if it does not exist.
> An example might be
> ----
> -- Has this table already got a record for this entry...
> select @.rowcnt=count(*) from mytable where myField = '123'
> if ( @.rowcnt = 0 )
> begin ... NO so create the record with an initial cnt of 1
> insert into mytable (myField, cnt) values ( 'text123', 1 )
> end
> else
> begin -- ... YES so increment the cnt
> Update mytable set cnt=cnt+1 where myField = 'text123'
> end
> ---
> The select count(*) can take a long time on a large table.
> Table Structure something like
> --
> id PK
> myField varchar(32)
> cnt int
> --
> Thanks
> Bill|||The count(*) of your select will perform a scan on every record (either
indexed or otherwise) to find the number of instances. The higher the
rowcount, the longer it will take. Since it looks like (from your
example) that you are always looking to update exactly 1 row each time
use EXISTS instead. This will stop the reads once the record has been
located. Additionally, take a look at the indexes . . .:
if ( NOT EXISTS( select * from myTable where myField = '123') )
begin ... NO so create the record with an initial cnt of 1
insert into mytable (myField, cnt) values ( 'text123', 1 )
end
else
begin -- ... YES so increment the cnt
Update mytable set cnt=cnt+1 where myField = 'text123'
end
hope that helps . . .|||> The select count(*) can take a long time on a large table.
do you have a unique index on myField?|||Thanks very much.
Those replies were extremely useful.
Bill
On Thu, 20 Apr 2006 16:59:48 GMT, Bill <wje@.blueyonder.co.uk> wrote:

>I have several different situations where I want to
>Update the record if it exists
>or
>add a new record if it does not exist.
>An example might be
>----
>-- Has this table already got a record for this entry...
>select @.rowcnt=count(*) from mytable where myField = '123'
>if ( @.rowcnt = 0 )
> begin ... NO so create the record with an initial cnt of 1
> insert into mytable (myField, cnt) values ( 'text123', 1 )
> end
>else
> begin -- ... YES so increment the cnt
> Update mytable set cnt=cnt+1 where myField = 'text123'
> end
>---
>The select count(*) can take a long time on a large table.
>Table Structure something like
>--
>id PK
>myField varchar(32)
>cnt int
>--
>Thanks
>Bill|||As others have already pointed out, you can use exists instead of count(*),
but there's another problem with using the logic below in a concurrent
environment.
Here's what you need:
BEGIN TRAN
IF EXISTS (
SELECT *
FROM mytable WITH(UPDLOCK, HOLDLOCK)
WHERE myField = '123'
)
UPDATE...
ELSE
INSERT...
IF @.@.ERROR != 0 OR @.@.ROWCOUNT = 0
ROLLBACK
ELSE
COMMIT
What's most important here is the explicit transaction and WITH(UPDLOCK,
HOLDLOCK). Without these, you'll get inconsistent results in a concurrent
environment. Without the transaction, it's possible for another transaction
to delete the row between the time that the shared lock from the SELECT is
released and the time that the database engine tries to obtain an exclusive
lock for the UPDATE. Without both the transaction and HOLDLOCK, it's
possible for another transaction to insert a row where myField = '123'
between the time that EXISTS is evaluated and the time that the INSERT
starts executing. Without UPDLOCK, it's possible for two concurrent
instances to obtain and hold shared locks on the row where myField = '123'
such that neither can obtain an exclusive lock in order to do the UPDATE,
causing a deadlock. Now, that can happen only if the transaction isolation
level is stricter than READ COMMITTED, that is, REPEATABLE READ or
SERIALIZABLE. If the isolation level is READ COMMITTED (the default), then
other anomalies can occur, such as updates being lost, or primary key
constraint violations.
Thus, any time you issue a SELECT before an UPDATE or INSERT (even within an
EXISTS clause), you should wrap the whole thing in a transaction and
decorate the SELECT with the WITH(UPDLOCK, HOLDLOCK) clause. I prefer to
explicitly specify HOLDLOCK, even if the isolation level is SERIALIZABLE,
because that way if the isolation level is later changed (to improve
performance or scalability, for example), the above problems won't reappear.
"Bill" <wje@.blueyonder.co.uk> wrote in message
news:33ff429ctg88ethir0ej3g4plhfbr19sqo@.
4ax.com...
>I have several different situations where I want to
> Update the record if it exists
> or
> add a new record if it does not exist.
> An example might be
> ----
> -- Has this table already got a record for this entry...
> select @.rowcnt=count(*) from mytable where myField = '123'
> if ( @.rowcnt = 0 )
> begin ... NO so create the record with an initial cnt of 1
> insert into mytable (myField, cnt) values ( 'text123', 1 )
> end
> else
> begin -- ... YES so increment the cnt
> Update mytable set cnt=cnt+1 where myField = 'text123'
> end
> ---
> The select count(*) can take a long time on a large table.
> Table Structure something like
> --
> id PK
> myField varchar(32)
> cnt int
> --
> Thanks
> Bill|||One comment: Using NOT EXISTS will also force a table or index scan. The
query processor has to scan all records to determine if one is missing.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"epperly" <epperlys@.gmail.com> wrote in message
news:1145553039.015639.244770@.i39g2000cwa.googlegroups.com...
> The count(*) of your select will perform a scan on every record (either
> indexed or otherwise) to find the number of instances. The higher the
> rowcount, the longer it will take. Since it looks like (from your
> example) that you are always looking to update exactly 1 row each time
> use EXISTS instead. This will stop the reads once the record has been
> located. Additionally, take a look at the indexes . . .:
> if ( NOT EXISTS( select * from myTable where myField = '123') )
> begin ... NO so create the record with an initial cnt of 1
> insert into mytable (myField, cnt) values ( 'text123', 1 )
> end
> else
> begin -- ... YES so increment the cnt
> Update mytable set cnt=cnt+1 where myField = 'text123'
> end
> hope that helps . . .
>|||On Thu, 20 Apr 2006 22:01:01 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:

>One comment: Using NOT EXISTS will also force a table or index scan. The
>query processor has to scan all records to determine if one is missing.
First of all, if there is an index to support the subquery it will not
need to perform a scan.
Second, EXISTS performs exactly the same as NOT EXISTS. It has to, as
answering either question answers both questions.
Roy Harvey
Beacon Falls, CT

Friday, February 24, 2012

Is Order By affect The Query Speed

hello,
I have a query that insert insert into new table , and then i select from this table,
if i add ORDER BY in the INSERT INTO script , does it affect the speed of the SELECT
i have big table that take about 70 secyes, it will either be ignored, or it will slow the INSERT down|||I'm confused. ORDER BY is not allowed in INSERT statements, is it? Unless part of a sub-query or something along those lines. And why would you do an order-by on an insert, since it makes no logical sense to try to instill order on a table that is not ordered.

The ORDER BY has to be on the select OUT from the table.|||I use ORDER BY in INSERT INTO cause I use INSERT INTO to a SELECT Statment, i have SELECT nested into INSERT

My Question is if i add the data in order in the table do i have better SELECT query performance|||So your ORDER by is not in the INSERT itself, but rather in the SELECT that is gathering the input. That is what I meant.

You're wasting your time and typing fingers. You cannot instill order when inserting to a SQL table, because there is no valid concept of that type of order in a SQL table.

SO, the short answer is NO. It will not affect later query speed. It is also a direct affront to all things good and beautiful. Most probably it would cause a Smiting Blow from the SQL Gods were it to be implemented in a production database.

If order is important, do it on your SELECT when you are pulling data out of the table.

The only time I would put an ORDER BY in an insert statement is if it was necessary in the SUB-SELECT to assure that the right data is pulled from the source table to be slapped into the INSERT table. OR if you need to limit the input from the select...as in: INSERT dbo.MyDestinationTable
SELECT TOP 100
Myfield1,
Myfield2,
MyDate
FROM MySourceTable
ORDER BY MyDate DESC and in this case, the ORDER BY is relative to the SELECT, not the INSERT.|||Thanks man|||You're welcome, dude.|||cowboy,

all of that is straight from the orthodox canon and that's fine and good, yet before I knew any better i wrote a server side pagination thingie that does an INSERT with a SELECT and an ORDER BY into a temp table and it does the row numbering with an identity column. It worked and still works and everytime I read this thing about it not being reliable, I have never seen it not work in practice.|||SO, the short answer is NO. It will not affect later query speed. please, sir, i must disagree

i think i gave the correct answer already

this --

INSERT INTO ... SELECT ... FROM ... ORDER BY ...

is going to be slower than this --

INSERT INTO ... SELECT ... FROM ...

simply because the ORDER BY will take extra time

(and, as we all know, the ORDER BY isn't guaranteed to actually insert them in the correct order anyway)|||OK Sean, I see your point, I knew I should have loaded my Orthodox Sidearm with birdshot so it scattered better. I still think that your example is another situation where the ORDER BY is related to the SELECT rather than the INSERT. Maybe it's semantics, but an order by in your situation is actually used to populate one of the INSERT table's columns, which is the ID column. Conceptually (in my mind, anyway) this applies to the gathering of the data to be inserted. NOT the ordering of the insert table.

In your example, the insertion of the data has nothing (reliably) to do with the actual physical order of the data in the table, nor in and of itself anything to do with the speed of a subsequent select from the table, correct? Of course the speed to be gained, if any, on the select has to do with the construction of the SELECT statement, and any applicable indicies that may be in place. NOT, per se, by anything you did on the insert itself.|||But the original question was whether subsequent SELECTS from the table will be faster, which they are not. There was no question as to which method of inserting was going to be faster. Presumably that is beyond debate.

If you have some free time, and a busy multi-CPU server, do a sp_helptext sp_who2, and scroll down to the bottom. I always loved the comment there:

-- (Seems always auto sorted.) order by spid_sort

On a busy server, parallelism takes over, and you get the results in every which way. I wonder how much processing time is shaved off by commenting out the order by statement. I mean, you might even have 200 or 500 rows to order!|||please, sir, i must disagree

i think i gave the correct answer already

this --

INSERT INTO ... SELECT ... FROM ... ORDER BY ...

is going to be slower than this --

INSERT INTO ... SELECT ... FROM ...

simply because the ORDER BY will take extra time

(and, as we all know, the ORDER BY isn't guaranteed to actually insert them in the correct order anyway)Well then, I guess you've forced my hand. Unfortunately, I've got to disagree with your disagreement then. Your answer was correct in your interpretation of the original question. That is, if he/she (or is that HeShe? I get that confused sometimes, which really has cause problems in the bar at times at closing) was asking if the ORDER BY would case the INSERT to be slower. If that is the case, I am behind you (at a respectable distance, of course) 100%.

However, if heshe asked if using ORDER BY on the INSERT would have any effect on later and independent SELECTs, then I stand by my answer. Nope.|||Daft I know but I have always* wondered whether ordering an insert by the clustered index definition is better\ no different to not ordering an insert. I have a feeling I have read somewhere one way or the other but I can't remember what the upshot was. The thing is - unless SS does implicitly order the insert anyway then it will be slower than an unordered insert as there will be page splits. When using BCP you can tell BCP if the order of data in the file & clustered index order are the same and the bulk insert is faster.

It is this sort of thing that gets me to sleep at night.

*not literally|||But the original question was whether subsequent SELECTS from the table will be faster

Oops.
There was no question as to which method of inserting was going to be faster. Presumably that is beyond debate.
Double oops.|||I have a feeling I have read somewhere one way or the other but I can't remember what the upshot was. perhaps this may help:
INSERT queries that use SELECT with ORDER BY to populate rows guarantees how identity values are computed but not the order in which the rows are inserted

-- Ordering guarantees in SQL Server... (http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx)|||perhaps this may help:
INSERT queries that use SELECT with ORDER BY to populate rows guarantees how identity values are computed but not the order in which the rows are inserted

-- Ordering guarantees in SQL Server... (http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx) Yeah, what he said.|||Thanks Rudy. That does actually fit with my observations - truncate a table\ clustered index, pump in several million rows in a single statement, check the fragmentation and the table turns out to be totally fragmented. I think I had always hoped that SS would order the input to fill the pages up to the fillfactor. I guess it must insert them in whatever order is most efficient for the select irrespective of what might be the most effecient insert order (assuming ordering the insert < splitting pages all over the place).

I appreciate all the logical "no order in relations" stuff - I am thinking solely about the physical aspect.

Did anyone let mgsn know that the physical order of the data (i.e. the clustered index) may affect many of the queries he\ she might perform on the table?|||Yeah, what he said.Thanks to you too Sean :)|||.
.
.
On a busy server, parallelism takes over, and you get the results in every which way. ...
So then; limiting the INSERT SP to a single processor might help reduce page splitting? Not "absolute guarantee", but reduce it.

My (unwarranted?) *impression* with reading about order-by in sub-queries not necessarily returning ordered sets to the outer queries, a symptom I've seen happen on rare occasion, was that it had to do with query plans.

Multi-processing affecting this also makes perfect sense.

However; since Ordering a Clustered Insert is relevant would it follow that setting the "Insert ... from select .. ordered by ..." SP to single processor would help matters?

Again; not looking for perfection, but in the real world, achieving an ordered insert 99.9% of the time.

I'd like to comment that, if the nature of the data is that ajoining records are often the objects of the same select, then having stuff together (in the same page) would be a performance gain.

For example: Let's say you have a non-clustered Child table that's ordered by ParentID. Let's say every "Family" has about 20 children on average, and the pages just happen to fit about 30 or 40 children rows each. Now let's say the child table has 30 million rows (to keep the Query Planner from invoking a full table scan). So; selecting a given 50 families would naturally use the ParentID key to go get the children. You would wind up reading maybe 25 pages on average if the related children (for each family) are all together, and therefore always fitting in either 1 or 2pages. By contrast; if they're all hap-hazzard you would get 50 x 20 page reads in the child table. Of course; at least some of the WHERE would have to be resolved outside of the child data records.

As a 2nd comment. For similar reasons; wouldn't it make sense to put stuff together for head-movement purposes? Not relevant for nearly all tables, but the situation may come when some table is so huge and so key that you put it on it's own HDD, and due to it's nature (let's just say it's financial transactions that are Usually queried within a range of perhaps 1 month but you don't archive until data is 8 months old), then the disks could predominately stay in the same area. Same would apply if lots of ordered transfers are done, so if you built an index that resolves the Where Clause but the actual transfer usually has large amounts of pages to read (so, of 100 million rows, let's say it's selecting a group of 5 million), if the data was all over the place, the heads would thrash. I believe that EVEN IF THE TABLE weren't on it's own drive, this COULD be a thrashing-saving quality.

I'm just saying that although unordered heaps and clusters are logically equivalent to ordered ones, that there are special cases where there's a cost to being unordered and that the cost can be severe for totally random cases. While I'm no expert in Relational databases - the basic concept of "reduce physical reads" surely remains valid.|||Hi vich

I've read this a couple of times. I think I know where you are going but there are a couple of lines that don't work.
Let's say you have a non-clustered Child table that's ordered by ParentID. This is an oxymoron. Did you mean clustered child table?

I'm just saying that although unordered heaps and clusters are logically equivalent to ordered onesAre you saying that a clustered index can be unordered? The definition of a clustered index has ordering right at the centre of it so again I don't quite get it.

Just to be clear - there are two types of table in the sql server world - Steers and Que... oops not them... Heaps & Clustered Indexes. Heaps are unordered and have no B-Tree structure. CIs are ordered (at the leaf level) by the index column(s) and have a B-Tree.

Overall though you are spot on - selected data can be more efficiently retrieved if it is physically contiguous and occupies the minimum number of pages.|||Hi vich

I've read this a couple of times. I think I know where you are going but there are a couple of lines that don't work.
This is an oxymoron. Did you mean clustered child table?

Are you saying that a clustered index can be unordered? The definition of a clustered index has ordering right at the centre of it so again I don't quite get it.

Just to be clear - there are two types of table in the sql server world - Steers and Que... oops not them... Heaps & Clustered Indexes. Heaps are unordered and have no B-Tree structure. CIs are ordered (at the leaf level) by the index column(s) and have a B-Tree.

Overall though you are spot on - selected data can be more efficiently retrieved if it is physically contiguous and occupies the minimum number of pages.
On point 1, by "ordered" I meant "contiguously in sequence" and no, I did mean heap, not clustered.

x1x2x3x4x5
not x3x1x5x4x2

So; "ordered heap" is an oxymoron in strict logical terms, but the records in the heap do physically reside somewhere. My point was that IF you know what groups of rows your will often read together, then physically grouping them will reduce physical reads in direct proportion to rows-per-page. Even if it's only "sometimes", it's still an improvement over being totally random since that will gaurentee worst case page hits.

To rephrase; one aspect of maximizing page hits is through placing rows into the same page if they can predictably be read together.

The phrase "Ordered Heap" must be to a DBA as "Perpetual Motion Machine" is to an engineer so lol; hence my clarification.

On the 2nd point, I was redundant. It should have read:
I'm just saying that although unordered heaps and clusters are logically equivalent.|||Ah - I see. However, in the heap the data being physically contigious would be useless - the engine would still need to scan every page in the table. It doesn't matter how many rows there are - heaps -> scans. Or have I missed your point again :D|||Ah - I see. However, in the heap the data being physically contigious would be useless - the engine would still need to scan every page in the table. It doesn't matter how many rows there are - heaps -> scans. Or have I missed your point again :D
What about indexes? Direct Access? Heap -> Full Scan is only occassionally true, correct? Often even, but not always.

With all do respect sir; in my case of 30 million rows when it's going after about 1000 of them, wouldn't you agree we should shoot the query plan that reads them all?

EDIT - added section below:
I was thinking about what bearing .mdf file's fragmentation has. My 2:30AM conclusion is; not too much. It's all about page hits. If the pages are scattered around, then seek ahead buffering, head movement, etc would come into play, but those ramifications (I think) would be far less than finding as much as possible per page (block really). It would probably come down to row size vs. block size (what is a "Page" anyway, at least as compared with a "block").

Anyway; this all boggels my uninformed mind and I submit to the engineer's recommendations, but I also want to understand the pieces so I can make good tuning decisions some day.

This thread's complete dismissal about physical placement of indexed heap data just struck me as "file this under, question this one'".|||Do you have a NC index on ParentID in this case? And we are still talking about a heap yes (the oxymoron-ordered heap ;))?|||Do you have a NC index on ParentID in this case? And we are still talking about a heap yes (the oxymoron-ordered heap ;))?
Yes, an indexed heap. But if the indexes tell you "go get these 1000 rows", I'm just saying that if prior inserts managed to maximize paging efficiency, then obviously 50 reads are better than 1000.

Sorry; don't know what "NC" is, but I guess I assumed that from the parent/child relationship. However; it wouldn't matter for the point being made. Even if the child's indexes required a full scan; if the DB Engine determined "go get these 1000 rows", it still holds true that 50 page reads trumps 1000. The scenerio did say that all children would be read as part of the "family".

Again; I'm just saying there are special cases, and not all that uncommon ones.|||NC = nonclustered. You have a nonclustered index on parentID?|||Even if the child's indexes required a full scan; if the DB Engine determined "go get these 1000 rows", it still holds true that 50 page reads trumps 1000. I think that is my point. Without any useable index on ParentID then the engine will scan the pages. Remember that scanning the pages involves getting them into memory and checking for the particular ParentID value. It is the number of pages scanned not the number of pages that the data is on that counts. As such, having all the relevent data on a small number of pages is only beneficial if the engine can know this and limit its retrieval to these pages only.|||So then; limiting the INSERT SP to a single processor might help reduce page splitting? Not "absolute guarantee", but reduce it.

Absolutely not. Limiting the number of CPUs that process an insert has no relevance to how data is stored on disk. And creating an "ordered heap" is a bit of a fantasy in 99% of cases, since you expect some deletes to happen on occasion. What do you think SQL Server is going to do with those gaps? Use 'em, that's what.

Besides which, you have eloquently explained in your first scenario EXACTLY why you would want a clustered index on the ParentID column. So the children are all clustered (pun intended) on the same set of pages.|||Besides which, you have eloquently explained in your first scenario EXACTLY why you would want a clustered index on the ParentID column. So the children are all clustered (pun intended) on the same set of pages.Just what I have been trying to say. The crucial difference between this clustered index and the magically ordered heap is that SS also can guarentee that these values are only on these pages and not on any others AND can get there quickly via the B-Tree so it does not need to hunt through the entire table looking for them.|||Absolutely not. Limiting the number of CPUs that process an insert has no relevance to how data is stored on disk. And creating an "ordered heap" is a bit of a fantasy in 99% of cases, since you expect some deletes to happen on occasion. What do you think SQL Server is going to do with those gaps? Use 'em, that's what.

Besides which, you have eloquently explained in your first scenario EXACTLY why you would want a clustered index on the ParentID column. So the children are all clustered (pun intended) on the same set of pages.
Drrrr (que anvel falling on my head). That actually did dawn on me last night when thinking of it after posting.

If you know Parent ID will determine a lot of read clusters during future queries, and being a "parent ID" (presumably auto sequenced), it will tend to be a good cluster key (adding to End).

However; if all families tend to grow (ie: Parent table rarely grows) then would page splitting occur a lot? That's where padded indexing and fill factors would come into play, I imagine.

Example: A scientific statistical program that maintains thousands of counters and is available to the scientific community on the internet (therefore; gets LOTS of reads). For sake of arguement, let's say that different scientists are only interested in their pet statistic but when they get it, all data for that statistic type (child rows for that parent) need to be read. So; the child table (the gathered statistic data) is constantly inserted into and the inserts are for all parent IDs (the statistic type).

This would benifit greatly from physical clustering by Statistic Type (parentID) but inserts would require enough padding in each statistic type (index padding?) to prevent page splits during Insert until the next index reorg can refresh the padding amount.

Thank you. I know it's painful teaching stubborn beginners.|||Broadly yes. Don't be too scared of page splits though. If you set a fill factor whereby you never get page splits then it is set too high. One of the problems with page splits is the increase in the number of pages needing to be read as they are not "full". Fill factor has exactly the same effect so putting in such a low fill factor pages never get full is counterproductive as you are increasing the number of pages that need to be read to satisfy queries.

Monday, February 20, 2012

Is multiple action trigger possible

Hello

Is it possible to create a trigger with multiple actions?

I would like to create a trigger with INSERT, DELETE, UPDATE funtions but I have not been above to find a clear syntax example

Below I have created a statement of my trigger. Could somebody please confirm if the syntac in this multiple action trigger is ok.multple actions on a single trigger

Is this syntax correct?

CREATE TRIGGER [dbo].[trig_AddDomCatA]
ON DomainNames
For INSERT, DELETE, UPDATE

INSERT INTO Domain_CatA (DomainName)
SELECT DomainName FROM INSERTED

AS
DELETE FROM Domain_CatA (DomainName)
SELECT DomainName FROM DELETED

AS
UPDATE INTO Domain_CatA (DomainName)
SELECT DomainName FROM UPDATED

Thanks

Lynn

Why do you need triggers?

Is it possible to add the required actions to the insert, update and delete stored procedures?

|||

you have to split the insert/update from the delete trigger.

CREATE TRIGGER [dbo].[trig_AddDomCatA]
ON DomainNames
For INSERT, UPDATE

INSERT INTO Domain_CatA (DomainName)
SELECT DomainName FROM INSERTED

CREATE TRIGGER [dbo].[trig_AddDomCatA]
ON DomainNames
For DELETE

DELETE FROM Domain_CatA (DomainName)
SELECT DomainName FROM DELETED

|||

Hello TATWORTH

Thanks for the reply.

Yes it is possible to use, insert, update and delete stored procedures, which is the current method I am using. However, as I have the same list of data on 26 categories tabes from A-Z, I thought it would be a more speedier and efficient method instead of using stored procedures.

Is there a good reason not to use triggers instead of stored procedures?

Thanks

Lynn

|||

Hello khtan

Thanks for the reply and detailed explanation.

Lynn

|||

>>Is there a good reason not to use triggers instead of stored procedures?

Stored procedures will give you better and more consistent performance. It has been known for triggers that function well during development, bring a production system to its knees. Stored procedures are better for another reason - all the components of an action is brought into one place thus they can be designed as a whole, reviewed as a whole and maintained as a whole.

I fully realise that many DBAs get excellant milage out of triggers, however in 10+ years of designing some 20 SQL server database, I have used triggers once and that was because I though a fellow DBA might enter some data by hand.

|||

Hello TATWORTH

Thanks for the information. I thought that a trigger would save me the task of entering the same stored procedure 26 times. Now repetitive stored procedures do not seem like too much of a chore.

Thanks

|||Just open up all the s.p. in SQL Management Studio and copy and paste ... copy and paste .. copy and paste!

Friday, March 23, 2012

Is there a better way to do this very simple query(s)?

Is there a better way of doing an INSERT or UPDATE

Friday, February 24, 2012

Is Order By affect The Query Speed

Monday, February 20, 2012

Is multiple action trigger possible

table hosed

Blog Archive

About Me