Fat Fingers

Hey, I just hit a combination of keys that commented an entire block of code in QA
or is it the flashback thing?
I can't find it in the menus, or is it a total hallucinaction?From the Menu:
Edit | Advanced | Comment Out

From the key board (with thin, delicate, piano fingers):
CTRL + SHIFT + C

To reverse, use:
CTRL + SHIFT + R

Regards,

hmscott

Originally posted by Brett Kaiser
Hey, I just hit a combination of keys that commented an entire block of code in QA

or is it the flashback thing?

I can't find it in the menus, or is it a total hallucinaction?

[Edit comment]: I just noticed that after emphasizing the use of thin, delicate fingers, I promptly fat-fingered CTRL to STRL. Sigh.|||Hey, THANKS!

(Damn, learned something new...again...)

EDIT: Hey look at all those options...

I knew about indeting/de-indeting...

no, really i did...|||Brett, you just saved me a lot of typing and tabbing.

blindman|||Originally posted by blindman
Brett, you just saved me a lot of typing and tabbing.

blindman

Hey, Thank Her Royal Majesty's Ship Cott...[;-)]

HMS Cott! Thanks|||y'er welcome|||Hi all,

I have the 'SQL Query Analyzer Keyboard Shortcuts' from BOL printed and nailed on the wall of my office. It's real handy guide for editing in Query Analyzer.|||My bad!

Credit where credit is due: thanks hmscott.

blindman|||I'm glad my ignorance can be put to good use though...

and OMG...printing short cuts as we speak...

(New most of though through menu items...)

Just thought the commenting/uncommenting was a neat trick...|||one little trick that simply blew me away the first time i discovered it, is that when you have a bunch of crap in the query pane, you can highlight just part of it and hit F5 to execute just that part

useful, for example, to execute one line at a time in a procedure, or to execute the subquery in a complex query, or...

rudy
http://r937.com/|||My favourite one is :

CTRL + X + I + E + M + F

this shortcut fixes all the problems in your code so you'll never have to spend hours looking for that invalid column name!|||Hate to say it, but I was chortling for an hour, when I found you could move that bar in the middle with ctrl+B.|||CTRL + X + I + E + M + F :confused:|||Originally posted by blindman
CTRL + X + I + E + M + F :confused:

i second that|||Originally posted by blindman
CTRL + X + I + E + M + F :confused:

I think it's a joke. ;)

Try touch-typing it.|||try
'CTRL+ALT+DEL+Q+W+E+RT+Y+U+I+O+P+A+S+D+F+G+H+J+K+L +M+N+B+V+C++X+Z'

this is the mother of all shortcuts

Fastest Update/Insert Method

There is probably 1001 tips, but here are the two I know.
I Remove all foreign keys before performing the insert /
update, if you need to know why then replay, nb I have not
included Primary key as you will need it to check if
record exists ;)
The second thing is to perform the update on the same
server, taking out the network. What I mean is this if 4gb
on server B is to copied onto 5gb on Server A, then it
will take less time if you write it to a file on server B,
compress it, send it Server A, uncompress it, load it into
a temporary table then perform the SQL.
Ok I now have a stupid question, did you try the
INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
(SELECT PrimaryKey from TEMPTABLE) ?
Hope this helps
Peter

>--Original Message--
>I have a large database of several hundred gig and I need
to perform monthly
>updates of about 4 gigs of that data. However I have to
test to check if the
>update records exists in the current database so that I
may either perform
>an update or an insert. I've tried all kind of different
ways for the
>update, but the fastest appears to be to delete all
records in the current
>database that exist in the update and then do everything
as an insert. This
>still takes 30 hours to complete. I need to know if there
are any tricks or
>tips for updating large databases more quickly.
>TIA
>
>.
>The Update resides in the same database as a separate Update table, I did
not remove the indexes from the Primary table before attempting the update,
but will try that, leaving the PK as the ony key on the table. I've tried
the INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
(SELECT PrimaryKey from TEMPTABLE) ?, but it was slower than deleting all
the records that existing in the Primary table that don't existing in the
TempTable, then doing just an insert of all data from the TEMPTABLE. So
basically:
DELETE PrimaryTable WHERE PrimaryKey IN(SELECT PrimaryKey FROM TempTable)
INSERT INTO PrimaryTable SELECT * FROM TempTable
has been the fastest.
I've tried
DELETE PrimaryTable FROM TempTable WHERE
TempTable.PrimaryKey=PrimaryTable.PrimaryKey
but this is really slow. Also NOT EXISTS, etc.
I think the issue is that it is recomputing the indexes as the query runs.
It will probably be worth dropping the indexes and reapplying them after the
update. I do this on BULK INSERT routines.
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0ac001c46e75$c58c1ee0$a301280a@.phx.gbl...[vbcol=seagreen]
> There is probably 1001 tips, but here are the two I know.
> I Remove all foreign keys before performing the insert /
> update, if you need to know why then replay, nb I have not
> included Primary key as you will need it to check if
> record exists ;)
> The second thing is to perform the update on the same
> server, taking out the network. What I mean is this if 4gb
> on server B is to copied onto 5gb on Server A, then it
> will take less time if you write it to a file on server B,
> compress it, send it Server A, uncompress it, load it into
> a temporary table then perform the SQL.
> Ok I now have a stupid question, did you try the
> INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
> (SELECT PrimaryKey from TEMPTABLE) ?
> Hope this helps
> Peter
>
> to perform monthly
> test to check if the
> may either perform
> ways for the
> records in the current
> as an insert. This
> are any tricks or|||In cases like this I usually separate out the Inserts from the Updates up
front by either placing them in separate staging tables or by a flag in the
existing single staging table. I usually determine this with an EXISTS type
statement. But when it comes down to any updating or Inserting you need to
do them in smaller batches. Trying to update 4GB at a time will take
forever as you are painfully aware. If you do them in smaller batches of
say 10 or 20K at a time you will usually find a much faster overall time.
Doing the updates in order of the clustered indexes usually helps. By that I
mean if you are updating a lot of rows and they are lumped together by the
CI expression the database can do partial scans instead of millions of
seeks.
Andrew J. Kelly SQL MVP
"DWinter" <dwinter@.attbi.com> wrote in message
news:eT2ytjnbEHA.404@.TK2MSFTNGP10.phx.gbl...
> The Update resides in the same database as a separate Update table, I did
> not remove the indexes from the Primary table before attempting the
update,
> but will try that, leaving the PK as the ony key on the table. I've tried
> the INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
> (SELECT PrimaryKey from TEMPTABLE) ?, but it was slower than deleting
all
> the records that existing in the Primary table that don't existing in the
> TempTable, then doing just an insert of all data from the TEMPTABLE. So
> basically:
> DELETE PrimaryTable WHERE PrimaryKey IN(SELECT PrimaryKey FROM TempTable)
> INSERT INTO PrimaryTable SELECT * FROM TempTable
> has been the fastest.
> I've tried
> DELETE PrimaryTable FROM TempTable WHERE
> TempTable.PrimaryKey=PrimaryTable.PrimaryKey
> but this is really slow. Also NOT EXISTS, etc.
> I think the issue is that it is recomputing the indexes as the query runs.
> It will probably be worth dropping the indexes and reapplying them after
the
> update. I do this on BULK INSERT routines.
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:0ac001c46e75$c58c1ee0$a301280a@.phx.gbl...
>

Fastest Update/Insert Method

There is probably 1001 tips, but here are the two I know.
I Remove all foreign keys before performing the insert /
update, if you need to know why then replay, nb I have not
included Primary key as you will need it to check if
record exists ;)
The second thing is to perform the update on the same
server, taking out the network. What I mean is this if 4gb
on server B is to copied onto 5gb on Server A, then it
will take less time if you write it to a file on server B,
compress it, send it Server A, uncompress it, load it into
a temporary table then perform the SQL.
Ok I now have a stupid question, did you try the
INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
(SELECT PrimaryKey from TEMPTABLE) ?
Hope this helps
Peter

>--Original Message--
>I have a large database of several hundred gig and I need
to perform monthly
>updates of about 4 gigs of that data. However I have to
test to check if the
>update records exists in the current database so that I
may either perform
>an update or an insert. I've tried all kind of different
ways for the
>update, but the fastest appears to be to delete all
records in the current
>database that exist in the update and then do everything
as an insert. This
>still takes 30 hours to complete. I need to know if there
are any tricks or
>tips for updating large databases more quickly.
>TIA
>
>.
>
The Update resides in the same database as a separate Update table, I did
not remove the indexes from the Primary table before attempting the update,
but will try that, leaving the PK as the ony key on the table. I've tried
the INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
(SELECT PrimaryKey from TEMPTABLE) ?, but it was slower than deleting all
the records that existing in the Primary table that don't existing in the
TempTable, then doing just an insert of all data from the TEMPTABLE. So
basically:
DELETE PrimaryTable WHERE PrimaryKey IN(SELECT PrimaryKey FROM TempTable)
INSERT INTO PrimaryTable SELECT * FROM TempTable
has been the fastest.
I've tried
DELETE PrimaryTable FROM TempTable WHERE
TempTable.PrimaryKey=PrimaryTable.PrimaryKey
but this is really slow. Also NOT EXISTS, etc.
I think the issue is that it is recomputing the indexes as the query runs.
It will probably be worth dropping the indexes and reapplying them after the
update. I do this on BULK INSERT routines.
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0ac001c46e75$c58c1ee0$a301280a@.phx.gbl...[vbcol=seagreen]
> There is probably 1001 tips, but here are the two I know.
> I Remove all foreign keys before performing the insert /
> update, if you need to know why then replay, nb I have not
> included Primary key as you will need it to check if
> record exists ;)
> The second thing is to perform the update on the same
> server, taking out the network. What I mean is this if 4gb
> on server B is to copied onto 5gb on Server A, then it
> will take less time if you write it to a file on server B,
> compress it, send it Server A, uncompress it, load it into
> a temporary table then perform the SQL.
> Ok I now have a stupid question, did you try the
> INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
> (SELECT PrimaryKey from TEMPTABLE) ?
> Hope this helps
> Peter
>
> to perform monthly
> test to check if the
> may either perform
> ways for the
> records in the current
> as an insert. This
> are any tricks or
|||In cases like this I usually separate out the Inserts from the Updates up
front by either placing them in separate staging tables or by a flag in the
existing single staging table. I usually determine this with an EXISTS type
statement. But when it comes down to any updating or Inserting you need to
do them in smaller batches. Trying to update 4GB at a time will take
forever as you are painfully aware. If you do them in smaller batches of
say 10 or 20K at a time you will usually find a much faster overall time.
Doing the updates in order of the clustered indexes usually helps. By that I
mean if you are updating a lot of rows and they are lumped together by the
CI expression the database can do partial scans instead of millions of
seeks.
Andrew J. Kelly SQL MVP
"DWinter" <dwinter@.attbi.com> wrote in message
news:eT2ytjnbEHA.404@.TK2MSFTNGP10.phx.gbl...
> The Update resides in the same database as a separate Update table, I did
> not remove the indexes from the Primary table before attempting the
update,
> but will try that, leaving the PK as the ony key on the table. I've tried
> the INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
> (SELECT PrimaryKey from TEMPTABLE) ?, but it was slower than deleting
all
> the records that existing in the Primary table that don't existing in the
> TempTable, then doing just an insert of all data from the TEMPTABLE. So
> basically:
> DELETE PrimaryTable WHERE PrimaryKey IN(SELECT PrimaryKey FROM TempTable)
> INSERT INTO PrimaryTable SELECT * FROM TempTable
> has been the fastest.
> I've tried
> DELETE PrimaryTable FROM TempTable WHERE
> TempTable.PrimaryKey=PrimaryTable.PrimaryKey
> but this is really slow. Also NOT EXISTS, etc.
> I think the issue is that it is recomputing the indexes as the query runs.
> It will probably be worth dropping the indexes and reapplying them after
the
> update. I do this on BULK INSERT routines.
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:0ac001c46e75$c58c1ee0$a301280a@.phx.gbl...
>