In relation to my last post, I have a question for the SQL-gurus.
I need to update 70k records, and mark all those updated in a special
column for further processing by another system.
So, if the record was
Key1, foo, foo, ""
it needs to become
Key1, fap, fap, "U"
iff and only iff the datavalues are actually different (as above, foo
becomes fap),
otherwise it must become
Key1, foo,foo, ""
Is it quicker to :
1) get the row of the destination table, inspect all values
programatically, and determine IF an update query is needed
OR
2) just do a update on all rows, but adding
and (field1 <> value1 or field2<>value2) to the update query
that is
update myTable
set
field1 = "foo"
markField="u"
where key="mykey" and (field1 <> foo)
The first one will not generate new update queries if the record has
not changed, on account of doing a select, whereas the second version
always runs an update, but some of them will not affect any lines.
Will I need a full index on the second version?
Thanks in advance,
Asger Henriksen 2 4574
[posted and mailed, vänligen svara i nys]
Asger Jensen (ak*@tmnet.dk) writes: Is it quicker to : 1) get the row of the destination table, inspect all values programatically, and determine IF an update query is needed
OR
2) just do a update on all rows, but adding and (field1 <> value1 or field2<>value2) to the update query
that is update myTable set field1 = "foo" markField="u" where key="mykey" and (field1 <> foo)
I'm not sure that I follow, but it sounds to me that the in first
approach you would retrieve rows one by one.
In any case, the second approach leaves all the jub to the computer,
and there is a reason why we have computers, isn't there? :-)
The only catch is that with too many rows in the table there can be
a strain on the transaction log. But with only 70000 rows, this is
not worth worrying about.
Obviously there query will run faster if there is a clustered index
on the column "key".
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
> I'm not sure that I follow, but it sounds to me that the in first approach you would retrieve rows one by one.
Yes, thats what it was. Nasty. In any case, the second approach leaves all the jub to the computer, and there is a reason why we have computers, isn't there? :-)
:-), yes, only in my scenario it took soooo long.
The only catch is that with too many rows in the table there can be a strain on the transaction log. But with only 70000 rows, this is not worth worrying about.
ok,
Obviously there query will run faster if there is a clustered index on the column "key".
There was, actually, but during the 70000 records it became slower and
slower. I solved it by adding a dynamically generated index on ALL
fields, not just "key", this sped things up considerably, so I went
from 1 hour to 10 minutes.
I guess it is due to the server needing to do a lookup/record on all
value fields to see if they have changed, and this can be done more
efficiently with a n index.
Regards
Asger This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Simon |
last post by:
Hi,
If I have a string, (variable len), and I am looking for the first position
of one char in array starting from position 'x'
For example,
// the 'haystack'
$string = "PHP is great,...
|
by: Rune Strand |
last post by:
Hi,
If I have a lot of integers and want do something with each digit as
integer, what is the fastest way to get there?
Eg. Make 12345 into an iterable object, like or "12345"
(Btw: What is...
|
by: laurenq uantrell |
last post by:
I am trying to determine which of three stored procedure designs are
fastest in the Query Analyzer:
One query is a straight SELECT query with all desired rows and a dozen
(tblName.RowName =...
|
by: hoopsho |
last post by:
Hi Everyone,
I am trying to write a program that does a few things very fast
and with efficient use of memory...
a) I need to parse a space-delimited file that is really large,
upwards fo a...
|
by: el_sid |
last post by:
Our developers have experienced a problem with updating Web References in
Visual Studio.NET 2003.
Normally, when a web service class (.asmx) is created, updating the Web
Reference will...
|
by: kebalex |
last post by:
Hi,
I have an app (written in .NET 2.0) which updates a picturebox
according to some user input (a slider control). when the user makes a
change i loop through all of the pixels, do a...
|
by: Klaas Vantournhout |
last post by:
Hi,
I have a question, which is just out of interest.
What is the fastest way to do an odd/even check with c++ and if needed
assembler.
Assume n is an unsigned integer like type (unsigned...
|
by: Phill W. |
last post by:
VB.Net 2005 SP1
Windows Forms Application
What's the fastest way to append text to a TextBox?
I have an application that monitors data written to text files.
It needs to scan some fairly...
|
by: SETT Programming Contest |
last post by:
The SETT Programming Contest: The fastest set<Timplementation
Write the fastest set<Timplementation using only standard C++/C.
Ideally it should have the same interface like std::set.
At least...
|
by: dantz |
last post by:
After reading all of the materials in msdn about interprocess communication
now I am confused. I hope someone can give me some enlightment.
I am developing a multithreaded client-server...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |