A cursor performance related question

Hi All,
I have 800 K records need to be processed one by one in a large table daily
using cursor way to update the default columns. The rest of queries are only
read information from this table. The records in this table could be very
large later on.
My question is what's the best way to use cursor to duel with this table.
here is my options:
1.Use a BIG cursor to lock all the un-processed records and use a singal
connection from Query Analyzer
2. Try to break /subgroup them with flags and run the same procedure above
with muti-Query Analyzer Connections. Each connection only duel part of the
record set.
I only have one SQL server . The testing result is Option 1 has the best
performance.
Is SQL server not good at running query parallelly with muti-connections? or
I need to improve the SQL server hardware staff by adding more memory ?
Any expert can point me to the right way?
Many Thanks,
Stevenews.microsoft.com (stevenxiu@.yahoo.com) writes:
> I have 800 K records need to be processed one by one in a large table
> daily using cursor way to update the default columns. The rest of
> queries are only read information from this table. The records in this
> table could be very large later on.
> My question is what's the best way to use cursor to duel with this
> table. here is my options:
> 1.Use a BIG cursor to lock all the un-processed records and use a singal
> connection from Query Analyzer
> 2. Try to break /subgroup them with flags and run the same procedure
> above with muti-Query Analyzer Connections. Each connection only duel
> part of the record set.
> I only have one SQL server . The testing result is Option 1 has the best
> performance.
> Is SQL server not good at running query parallelly with
> muti-connections? or I need to improve the SQL server hardware staff by
> adding more memory ?
With only this abstract narrative it is impossible to say very much. One
possibility is that you have poor indexing, cause the multiple connections
to block each other.
To get any accurate response you would need to post:
o CREATE TABLE and CREATE INDEX statements for the table.
o The code for the two options you are using.
o Some background on what the code is actually doing.
However, there is fair chance that the answer is option 0: don't use a
cursor at all, but apply set-based logic. This usually improves performance
with magnitudes.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"news.microsoft.com" <stevenxiu@.yahoo.com> wrote in message
news:u9WHOCx8FHA.3044@.TK2MSFTNGP10.phx.gbl...
> I have 800 K records need to be processed one by one in a large table
> daily
> using cursor way to update the default columns.
Chances are that you don't need to do it "one by one" and that it will be
more efficient without a cursor. We won't know for sure unless you post a
better description of your problem. See:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
But based on a few decades with SQL, I have written only five cursors
and I know that I could have avoided three of them if I had the CASE
expression back in the old days.
The fact that you do not know the differences between records and rows
tells me your approach is probably not relational and that yoiur
mindset is still stuck in sequential file processing.|||You should avoid using cursors to perform updates. Set-based updates
perform better for a number of reasons:
(1) Set-based updates write to the transaction log more efficiently. Each
update incurs a certain amount of overhead in the transaction log to mark
the start and end of each write to each affected object in the database.
This includes not only writes to the heap or clustered index, but also
writes to each nonclustered index. In addition, if the same index page is
updated more than once during the thousands of individual updates, then that
page will be recorded in the transaction log once for each related update,
causing the log to grow faster than is necessary. Frequent disk allocations
can hugely affect performance. (This last shouldn't be a problem if you
pre-allocate log space.)
(2) With set-based updates, indexes can be updated en-mass--meaning fewer
costly page splits and fewer writes to each affected database object thus
reducing the frequency of disk head ss.
(3) Locking is more efficient. Set-based updates obtain locks on all of the
affected rows before beginning the write, and then release them as soon as
the changes have been committed. Thousands of individual updates requires
the server to go through the process of obtaining each individual exclusive
lock on each individual row. With set-based updates, locks are more likely
to be escalated when necessary, thereby reducing overhead.
(4) With set-based updates, the system is tasked with writing and writing
only. Thousands of individual updates usually means that several additional
reads are interspersed within the writes. This can cause a lot more disk
activity and in particular, a significant increase in costly disk ss.
If you must use a cursor (I'm not of the opinion that they are always bad;
however, they should only be used as a last resort.), then you should cache
the updates in a temp table or table variable and then flush them using
set-based updates. Inserting individual rows into a table with no indexes
(a heap) or appending to a table with only a clustered index performs pretty
well, and if you have enough memory, temp tables and table variables remain
for the most part in memory. (Writes to tempdb are eventually flushed out
to disk, provided the affected rows and objects still exist by the time the
system gets around to initiating the write.) By appending, I mean that
inserts occur in the same order as the clustered index key--that is, with an
ascending index, each row inserted has a key value that is greater than the
key value in any existing row in the table.
"news.microsoft.com" <stevenxiu@.yahoo.com> wrote in message
news:u9WHOCx8FHA.3044@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I have 800 K records need to be processed one by one in a large table
> daily
> using cursor way to update the default columns. The rest of queries are
> only
> read information from this table. The records in this table could be very
> large later on.
> My question is what's the best way to use cursor to duel with this table.
> here is my options:
> 1.Use a BIG cursor to lock all the un-processed records and use a singal
> connection from Query Analyzer
> 2. Try to break /subgroup them with flags and run the same procedure above
> with muti-Query Analyzer Connections. Each connection only duel part of
> the
> record set.
> I only have one SQL server . The testing result is Option 1 has the best
> performance.
> Is SQL server not good at running query parallelly with muti-connections?
> or
> I need to improve the SQL server hardware staff by adding more memory ?
> Any expert can point me to the right way?
> Many Thanks,
> Steve
>sql

A basic design question

When I set the relationship between two tables with a one-to-many relationship and I want all records deleted from the many side when a row is deleted from the one side, how should I set the Insert and Update Specs (Delete and Update) CASCADE or NO ACTION?

Then if there is a lookup table on the many side how should it be set?

I generally set up the PK-FK constraints and write my own DELETE statements rather than rely on CASCADE Delete. That way I always know the flow of how the data will/should be deleted.|||I was thinking that too. But I was also thinking about how SQL Server will overwrite my thinking if the cascade is not set properly.|||

Hi Jack,

I would suggest to set Update to CASCADE, because the change to the lookup table will be cascaded to the child table.

It does not make difference whether Insert is set to CASCADE or NO ACTION.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

9 million rows in msrepl_commands? I don't even have that many records!

Does more than one msrepl_commands record exist for each record updated?
How can I have so many rows in this table? Does anyone know how to
translate the command fields so I can see what the commands are... with so
many records sp_browsereplcmds isn't even responding.
Thanks
the msrepl_command will also contain the sync commands which are necessary
to deploy your snapshot on the subscriber. If the particular command is very
large it may wrap over one row.
However what probably has caused so many rows to fill up this table is the
fact that all transactions that occur on the publisher are converted into
singletons, so a single insert/update/delete statement that affects 100 rows
on the publisher will be converted into 100 insert/update/delete statements
on the publisher.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Ray Price" <ray.price@.gartner.com> wrote in message
news:OqL%23Fl$UEHA.3944@.tk2msftngp13.phx.gbl...
> Does more than one msrepl_commands record exist for each record updated?
> How can I have so many rows in this table? Does anyone know how to
> translate the command fields so I can see what the commands are... with so
> many records sp_browsereplcmds isn't even responding.
> Thanks
>

800a0cb3 Error in DTS Package

I've been working on a DTS Package that runs every 10 minutes to check for new records to a table and sends out notification emails accordingly. After the email is sent, the new record is updated with a flag so it will not be pulled and emailed again.

This package worked fine for a week (and is currently working - exact same code - in another SQL Server).

As of last night, this process started throwing the following error:

Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Error Code: 0
Error Source= ADODB.Recordset
Error Description: Current Recordset does not support updating. This may be a limitation of the provider,
or of the selected locktype.

Error on Line 110
(ADODB.Recordset (800a0cb3): Current Recordset does not support updating. This may be a limitation
of the provider, or of the selected locktype.)
Step Error code: 800403FE
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100

Thinking I may have screwed something up, I moved the functioning code from the other SQL Server back to the non-functioning SQL Server, and the problem persists. I stopped and restarted the SQL Server (but have not yet rebooted the server it's on), also with no results.

The package is a single ActiveX object written in VB to open up a recordset of unprocessed records (KeySet and Lock Optomistic), send out notification emails, flag the files, then close. The error occurs at the updating of the flag.

What is even more strange, is that this is happening on our development SQL server. Our production SQL server seems unaffected. I originally developed and tested this code on the dev box, and when I was satisfied, I moved it to production for testing. Then last night the dev server stopped functioning, but the production server shows no sign of problems.

I was curious if anyone else has had an experience with a DTS package throwing a similar error.

Any ideas?I took the ActiveX code and copied it to VB and ran it.. when it's pointing to the server in question, the error occurs... when I change the server name to point to the production server, the same code functions properly... amazing....|||I temporarily managed to resolve the problem by rebuilding the packages from scratch. I do not know if this fixed it, or if it was just coincidence that I did this at the same time something else happened, but it began working again.

Unfortunately, as of this afternoon, the production and staging environments redeveloped the problem and my "solution" did not fix it.

Thinking my transaction log may be filled I checked the settings. The DB and the Transaction log are both set to unlimited growth and to grow automatically.

At this point I'm baffled. I have a DTS package on 3 servers... all three running successfully for days on end... spontaneously two of them stop working giving the error in the original post... very very troubling...|||Would you believe I came across something that may have fixed it right after I posted? It wouldn't be the first time...

I found a reference to the same error code, but different error message, in regard to an Oracle error. The solution was to make the recordset a client-side recordset. I had originally thought of this, but the DTS package is in SQL Server, so my thinking was, when this runs, there is no client, so it has to run as a server-side recordset. When I changed ADO to use a client-side recordset (which is still the SQL Server), it started working again. I changed it back, it failed, put it back in again, it worked.

Very strange... why would you need to declare a recordset to be client-side, even though it's running in the SQL Server itself? Since DTS runs under a separate agent, does that count as a "client"?

8 hours to add a bit column

I ran the following:

ALTER TABLE Recipients ADD Obscene BIT NOT NULL DEFAULT 0

On a table with 80 million records. It's been running for 8 hours and counting now. This is ridiculous. No one else is using this server.

Server configuration:
SQL Server 2000 Enterprise Edition with SP3a
2 GB RAM
3.0 GHz P4 with hyperthreading
SCSI RAID

Any ideas why this is taking so long? Can I find out what it's doing? Is there anything I can do to make it go faster?You won't like any of my answers.

Before we descend into that morass though, crank up the NT Performance Monitor and have a look-see at the server. Is it CPU bound, disk bound, memory bound, or are all of those counters at reasonable levels? How many SQL threads (spids) are active? What do the SQL page counters look like?

My guess would be that the box is hideously RAM bound, that it is having page splits up the ying-yang, and that it might be disk bound as well.

Is your log file on a different disk device than your data files? Is that device mirrored instead of RAID? Do you have the ability to add RAM with the box running (some servers can do that!)?

-PatP|||If your answers give me any ideas and any closer to figuring these kinds of problems out, I like them. The business that I'm working for is very data driven and we are moving quickly in a database direction and we need this knowledge.

I've been watching CPU and disc levels in perfmon all day. The system has been steadily disc bound.
sp_who 'active' returns 17 rows.
How do I look at the page counters?

A RAM bound system exhibits itself as disc bound, correct? With lots of paging to/from disc.

The data files and temp db are on a SCSI RAID 5. The main log file is on a separate 250 GB IDE disc (it has grown to 150 GB in the past which wouldn't fit on the RAID)

You won't like any of my answers.

Before we descend into that morass though, crank up the NT Performance Monitor and have a look-see at the server. Is it CPU bound, disk bound, memory bound, or are all of those counters at reasonable levels? How many SQL threads (spids) are active? What do the SQL page counters look like?

My guess would be that the box is hideously RAM bound, that it is having page splits up the ying-yang, and that it might be disk bound as well.

Is your log file on a different disk device than your data files? Is that device mirrored instead of RAID? Do you have the ability to add RAM with the box running (some servers can do that!)?

-PatP|||if you trace an alter table statement it will show you the issue i think you are hitting.

alter table creates a new table in temp space with the new column, inserts all the data from the old into the new temp, then swaps the names around.

with 80million rows, i'd be willing to bet your are being I/O thottled either creating the temp (which using a low logged select..into..) or the insert, (which is using a fully logged insert into..select).

either way, 8 hours sux but doesnt really suprise me.|||I'v never had to wait 8 hours to add a column to a big table. Besides, I thought that the EM did the swap-trick and the alter table did not.|||i'd agree...8 hours is way out there.
you are correct...EM does the old swap'r'roo trick. that was an assumption on my part - uber apologies - that you were using the Enterprise Mangler.

i would suspect the same thing is happening underneath an alter, though.
(i'm moving into the 'out of my ass' realm so i'm going to qualify that statement as an 'idea'- not something i claim to know.)

the page counters are under the Memory object with some useful other SQL specifc page counters SQLServer:BufferManager.

i wonder if your disk queues are backing up for reads or writes?

perhaps that could help understand if a large read/write is actually occuring underneath your alter?|||hm, here's another though. Perhaps the Analyzer is waiting for the table to be freed from a lock. What does sysprocesses say?|||Just curious as to why you made it NOT NULL?

USE Northwind
GO

CREATE TABLE myTable99 (Col1 int)
GO
INSERT INTO myTable99(Col1) SELECT 1
GO

SELECT * FROM myTable99

ALTER TABLE myTable99 ADD Obscene BIT NOT NULL DEFAULT 0
GO

SELECT * FROM myTable99
GO

ALTER TABLE myTable99 ADD Obscene2 BIT DEFAULT 0
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO

If you needed it null maybe you caould have performed batch updates after the fact, the changed ALTERed the column to make it NOT NULL...

Is it still running?|||OK, here comes Robert with his BCP again...But it's true, no matter how you look at it! Non-logged data load would beat "in-line" DDL+DML (because this is exactly what happens when you add a new NON-NULLable column with default - the only way to add a new non-nullable column) If you added the same column but made it nullable, - you'd be onto something else 7 hours and 59 minutes ago. At this point though you can't even interrupt this operation because ALTER TABLE is a fully logged operation. It means that every 0 that came from your DEFAULT is logged in your transaction log (thus its size is very explainable). If you decide to kill the process you'll be looking at 8+ hours of rollback. If you stop the service you'll be looking at "Recovering database x..." for probably as much.|||Just curious as to why you made it NOT NULL?

That's an application issue, right? We are trying to mark certain records as "obscene", so every record should be obscene or not. There should be no NULL. I didn't realize that this would be a large performance issue. If I knew in advance I could have dealt with NULL values in one way or another.

It finished overnight but as of 12:30 AM last night, it was running for 13 hours and still going.

I've previously added datetime columns to the same table with EM (this was added via QA with a ALTER TABLE statement) and it took less than an hour. I was really surprised that this took so long. Ideally, I know what to look for to remedy such an issue and how to prevent such things from happening.

thanks guys!|||Did you look at the code that em scripted for you?

Just make sure you don't have a table called tmp_yourtable..

WAIT...damn I just tested it...it's smart enough to add a _1 to the end...damn that's good|||WAIT...damn I just tested it...it's smart enough to add a _1 to the end...damn that's goodTricky little devils, ain't they ?

-PatP|||curious - when you added the datetime values last time did you set a default of getdate()? or something else? or allow them to be null?|||curious - when you added the datetime values last time did you set a default of getdate()? or something else? or allow them to be null?

They defaulted to NULL. And it was added through EM as opposed to an ALTER TABLE statement.

The exact time on adding the bit column was 17 hours and 23 minutes.

60mil record bounce..

I've recently converted from access to sql and am running into a problem matching records against a massive 60million record table..

The 60mil table consists of nothing more than telephone numbers in a varchar format and I need to compare other tables with this one and tag them as matched..

I am getting a ODBC timeout error and I have set my query time-out to 0..

Why would it call an ODBC driver and why would it timeout? HELP!

**the table is in the same table as my other stuff

any suggestions would be great!Why did you set the time out to 0? Even the best, most efficient queries need some time to work...
:)

Show us your code......|||I assume 0 is the infinite timeout.

60millions rows should'nt really cause a major problem. Do you have appropriate indexes on the table?|||I was assuming 0 was infinte too, but not sure why the error would occur..

It actually happens while querying a view doing a simple Select query joing the two..|||Oh no not views as well. I abs' hate views. When I have to performance fix a SQL problem there is nearly always some terrible implementation of a view somewhere. Like I say have you got decent indexes on the tables involved?|||Actually, no..

It's just a telephone number in a varchar field|||<gulp> what other fields are there in the table, what is the primary key for the table? Is there a clustered index anywhere on the table?|||my question is "you had 60 million records in an access database?" HOW DID THAT NOT CRASH!?!?

601 - Could not continue scan with NOLOCK due to data movement.

Hi,
Lately, I have been getting this problem with a client. The query returns
around 6 thousand records, there is one user only and it's a select statemen
t
with a temporary table in the join.
The server has a sp4 installed. It's a 2003 server.
CREATE TABLE #Temp (Temp_Id INT,
Temp_Id_Count INT NULL,
Temp_Id_Dt DATETIME NULL)
INSERT INTO #Temp (Temp_Id, Temp_Id_Count, Temp_Id_Dt)
SELECT T.Temp_Id, COUNT(TR.Temp_Id_Count), MAX(TR.Temp_Id_Dt)
FROM Test T WITH (NOLOCK)
LEFT JOIN Test_History TR WITH (NOLOCK) ON TR.Temp_Id = T.Temp_Id
WHERE T.Status_Cd = 1234
GROUP BY T.Temp_Id
SELECT DISTINCT T.Temp_Id, S.Client_Id, S.Subject_Id,
FROM SavedData S WITH (NOLOCK)
JOIN #Temp T ON T.Temp_Id = S.Temp_Id
The error after a few seconds is 601 - Could not continue scan with NOLOCK
due to data movement.
Any ideas?
Thanks in advanceWonder wrote:
> Hi,
> Lately, I have been getting this problem with a client. The query returns
> around 6 thousand records, there is one user only and it's a select statem
ent
> with a temporary table in the join.
> The server has a sp4 installed. It's a 2003 server.
>
> CREATE TABLE #Temp (Temp_Id INT,
> Temp_Id_Count INT NULL,
> Temp_Id_Dt DATETIME NULL)
> INSERT INTO #Temp (Temp_Id, Temp_Id_Count, Temp_Id_Dt)
> SELECT T.Temp_Id, COUNT(TR.Temp_Id_Count), MAX(TR.Temp_Id_Dt)
> FROM Test T WITH (NOLOCK)
> LEFT JOIN Test_History TR WITH (NOLOCK) ON TR.Temp_Id = T.Temp_Id
> WHERE T.Status_Cd = 1234
> GROUP BY T.Temp_Id
> SELECT DISTINCT T.Temp_Id, S.Client_Id, S.Subject_Id,
> FROM SavedData S WITH (NOLOCK)
> JOIN #Temp T ON T.Temp_Id = S.Temp_Id
> The error after a few seconds is 601 - Could not continue scan with NOLOCK
> due to data movement.
> Any ideas?
> Thanks in advance
That means data that your query is using has changed while your query
was running. That's one risk of using NOLOCK, you run the risk of
reading data that is in the process of being modified.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

601 - Could not continue scan with NOLOCK due to data movement.

Hi,
Lately, I have been getting this problem with a client. The query returns
around 6 thousand records, there is one user only and it's a select statement
with a temporary table in the join.
The server has a sp4 installed. It's a 2003 server.
CREATE TABLE #Temp (Temp_Id INT,
Temp_Id_Count INT NULL,
Temp_Id_Dt DATETIME NULL)
INSERT INTO #Temp (Temp_Id, Temp_Id_Count, Temp_Id_Dt)
SELECT T.Temp_Id, COUNT(TR.Temp_Id_Count), MAX(TR.Temp_Id_Dt)
FROM Test T WITH (NOLOCK)
LEFT JOIN Test_History TR WITH (NOLOCK) ON TR.Temp_Id = T.Temp_Id
WHERE T.Status_Cd = 1234
GROUP BY T.Temp_Id
SELECT DISTINCT T.Temp_Id, S.Client_Id, S.Subject_Id,
FROM SavedData S WITH (NOLOCK)
JOIN #Temp T ON T.Temp_Id = S.Temp_Id
The error after a few seconds is 601 - Could not continue scan with NOLOCK
due to data movement.
Any ideas?
Thanks in advanceWonder wrote:
> Hi,
> Lately, I have been getting this problem with a client. The query returns
> around 6 thousand records, there is one user only and it's a select statement
> with a temporary table in the join.
> The server has a sp4 installed. It's a 2003 server.
>
> CREATE TABLE #Temp (Temp_Id INT,
> Temp_Id_Count INT NULL,
> Temp_Id_Dt DATETIME NULL)
> INSERT INTO #Temp (Temp_Id, Temp_Id_Count, Temp_Id_Dt)
> SELECT T.Temp_Id, COUNT(TR.Temp_Id_Count), MAX(TR.Temp_Id_Dt)
> FROM Test T WITH (NOLOCK)
> LEFT JOIN Test_History TR WITH (NOLOCK) ON TR.Temp_Id = T.Temp_Id
> WHERE T.Status_Cd = 1234
> GROUP BY T.Temp_Id
> SELECT DISTINCT T.Temp_Id, S.Client_Id, S.Subject_Id,
> FROM SavedData S WITH (NOLOCK)
> JOIN #Temp T ON T.Temp_Id = S.Temp_Id
> The error after a few seconds is 601 - Could not continue scan with NOLOCK
> due to data movement.
> Any ideas?
> Thanks in advance
That means data that your query is using has changed while your query
was running. That's one risk of using NOLOCK, you run the risk of
reading data that is in the process of being modified.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

56 million records search

Hey folks...

So I have a table that looks like this:

CREATE TABLE [tblStation] (
[CAMPAIGN] [varchar] (8),[LISTNUM] [varchar] (10),
[PHONE] [varchar] (10),
[EVENTTIME] [datetime] ,
[STATION] [int],
[OPERATOR] [varchar] (16),
[EVENTCODE] [varchar],
[CALLSPAN] [decimal](18, 0),
[FDISP] [int],
[RECORDNUM] [varchar],
[STC] [varchar],
[PROMOC] [varchar],
[EXP_CAMP] [varchar],
[PROMO3] [varchar],
[MAXATT] [char],[LISTNAME] [varchar],
[SITENAME] [char],
[Row_id] [int] IDENTITY

It's taking nine seconds to run the following command:

SELECT count([fdisp])

FROM [TrunkFiles_new].[dbo].[tblStation] WITH (NOLOCK)

WHERE fdisp IS NULL

Anyone familiar with a table of this size having performance like
this? The [fdisp] column has a non clustered index on it.

Thanks in advance...> SELECT count([fdisp])
> FROM [TrunkFiles_new].[dbo].[tblStation] WITH (NOLOCK)
> WHERE fdisp IS NULL

First of all, this query doesn't make any sense. The expression
COUNT([FDISP]) will only count FDISP values that are not null and you
have also specified WHERE FDISP IS NULL. Consequently, the result will
always be zero. For the purpose of discussion, I'll assume you meant to
specify COUNT(*):

SELECT COUNT(*)
FROM [TrunkFiles_new].[dbo].[tblStation] WITH (NOLOCK)
WHERE FDISP IS NULL

Do you also have a clustered index on the table? About how many rows
have a NULL FDISP value? 9 seconds may be reasonable for an index
seek/scan depending on the amount of data that needs to be read to
determine the count.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Jesus Christ's Evil Twin" <radpin@.hotmail.com> wrote in message
news:2a0f8137.0311041354.5da87b63@.posting.google.c om...
> Hey folks...
> So I have a table that looks like this:
> CREATE TABLE [tblStation] (
> [CAMPAIGN] [varchar] (8),
>[LISTNUM] [varchar] (10),
> [PHONE] [varchar] (10),
> [EVENTTIME] [datetime] ,
> [STATION] [int],
> [OPERATOR] [varchar] (16),
> [EVENTCODE] [varchar],
> [CALLSPAN] [decimal](18, 0),
> [FDISP] [int],
> [RECORDNUM] [varchar],
> [STC] [varchar],
> [PROMOC] [varchar],
> [EXP_CAMP] [varchar],
> [PROMO3] [varchar],
> [MAXATT] [char],
>[LISTNAME] [varchar],
> [SITENAME] [char],
> [Row_id] [int] IDENTITY
> It's taking nine seconds to run the following command:
> SELECT count([fdisp])
> FROM [TrunkFiles_new].[dbo].[tblStation] WITH (NOLOCK)
> WHERE fdisp IS NULL
>
> Anyone familiar with a table of this size having performance like
> this? The [fdisp] column has a non clustered index on it.
> Thanks in advance...

50 codes with country names 2 fields

This is my problem: I have a report with 100 records. Every record was assigned a different {country.code}. I was given a hard copy with the list of 45 countries and their respective code (1 represents USA, 2 Mexico, 3 Spain etc). My report doesn't have the name of the country, only the code. Is there something where I say something like: if {country.code} = "3" then "Spain" else...............Do I have to do this for all 45 codes with its country name? That means 45 if-then-else statements.
Is there a quick way of Having my 100 records in my report spell the country name?
Thanks for your help.
JavPut the code / name mapping into a new table and join to it.
If you can't do that then use a select case statement in a formula, e.g.
Select {table.field}
Case 1 : "USA"
Case 2 : "Mexico"
...
Default : "Unknown!"

See the help on "select expression" in the search tab, not the index tab.

400 million record update

Hi,
I have a sql2k table with 400 Million records. All records need to be
updated (one field.).
1. What id tested was that DTS transfer(updating the field during the
transferring) to a diff table in the same db. It took 67 hours.
2. The second try I did was Select into the the same db. same thing -- 70
hours.
Is there any faster method taht I could use the accomplish this?
ThanksWith an index on a very specific field such as identity or spread-out date,
you can do this in a batch mode, something like this:
declare @.i int
set @.i = 1 (or min from your table)
get max id from table
while @.i < maxid
begin
begin tran
delete from table where id between @.i and @.i + somenumber (50K-00K')
check for error
commit tran
set @.i = @.i + somenumber
end
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Mecn" <mecn@.yahoo.com> wrote in message
news:%23bek6QtXIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a sql2k table with 400 Million records. All records need to be
> updated (one field.).
> 1. What id tested was that DTS transfer(updating the field during the
> transferring) to a diff table in the same db. It took 67 hours.
> 2. The second try I did was Select into the the same db. same thing -- 70
> hours.
> Is there any faster method taht I could use the accomplish this?
> Thanks
>
>|||Hi,
What you are testing is not really an update but the copy of a huge table.
Could you give more details about exactly what are you going to update, field
data type, operation.
Could you restore a copy of this database to some other place and test
running a simple UPDATE statement? Note that since this will be one
transaction you will need enough disk space for the transaction log to grow.
Hope this helps,
Ben Nevarez
"Mecn" wrote:
> Hi,
> I have a sql2k table with 400 Million records. All records need to be
> updated (one field.).
> 1. What id tested was that DTS transfer(updating the field during the
> transferring) to a diff table in the same db. It took 67 hours.
> 2. The second try I did was Select into the the same db. same thing -- 70
> hours.
> Is there any faster method taht I could use the accomplish this?
> Thanks
>
>|||Just so I can be clear , when you say UPDATE , I think you mean tarnsfer of
data (copy).
Some things you could try is:
use TABLOCK
If possible, use Simple Recovery Mode
Possibly disable indices and rebuild at the end .
These are just some ideas, but is dependant on the exact table structures,
code etc
--
Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"Mecn" <mecn@.yahoo.com> wrote in message
news:%23bek6QtXIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a sql2k table with 400 Million records. All records need to be
> updated (one field.).
> 1. What id tested was that DTS transfer(updating the field during the
> transferring) to a diff table in the same db. It took 67 hours.
> 2. The second try I did was Select into the the same db. same thing -- 70
> hours.
> Is there any faster method taht I could use the accomplish this?
> Thanks
>
>|||67hours for 400millions?
its really really slow... (around 1600rows/sec)
are you sure that you use the bulk insert option and make sure you setup the
batch size value (to 10 000 or something like this)
your disk subsystem as an impact too, but you should be able to load the
table in 1 to 2hours. (depends on the size of 1 row)
also make sure you drop the indexes before the loading process, then
recreate the indexes. (recreating the indexes will add some processing time
after the loading, so estimate it between 15min to 1h regarding the number
of indexes)
the idea is to cut the big table into small batchs.
in our developments we reach 200 000rows loaded by second.
"Mecn" <mecn@.yahoo.com> wrote in message
news:#bek6QtXIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a sql2k table with 400 Million records. All records need to be
> updated (one field.).
> 1. What id tested was that DTS transfer(updating the field during the
> transferring) to a diff table in the same db. It took 67 hours.
> 2. The second try I did was Select into the the same db. same thing -- 70
> hours.
> Is there any faster method taht I could use the accomplish this?
> Thanks
>
>|||Thanks for all the responses.
1. You are right I was doing transfer instead of updating the field. I think
that uase DTS with 400,000 batch size/fast load(same as bulk insert?) to a a
non-index table or Insert into (bulk insert?) will be fast than undate the
field. -- 67 Hours....too long.
Any faster ways?
Agree?
"Jeje" <willgart@.hotmail.com> wrote in message
news:FB0540B8-0A9B-4603-AD86-01742FFC7DD7@.microsoft.com...
> 67hours for 400millions?
> its really really slow... (around 1600rows/sec)
> are you sure that you use the bulk insert option and make sure you setup
> the batch size value (to 10 000 or something like this)
> your disk subsystem as an impact too, but you should be able to load the
> table in 1 to 2hours. (depends on the size of 1 row)
> also make sure you drop the indexes before the loading process, then
> recreate the indexes. (recreating the indexes will add some processing
> time after the loading, so estimate it between 15min to 1h regarding the
> number of indexes)
> the idea is to cut the big table into small batchs.
> in our developments we reach 200 000rows loaded by second.
> "Mecn" <mecn@.yahoo.com> wrote in message
> news:#bek6QtXIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I have a sql2k table with 400 Million records. All records need to be
>> updated (one field.).
>> 1. What id tested was that DTS transfer(updating the field during the
>> transferring) to a diff table in the same db. It took 67 hours.
>> 2. The second try I did was Select into the the same db. same thing --
>> 70 hours.
>> Is there any faster method taht I could use the accomplish this?
>> Thanks
>>|||The table datasize = 80GB, total rows = 375million
"Jeje" <willgart@.hotmail.com> wrote in message
news:FB0540B8-0A9B-4603-AD86-01742FFC7DD7@.microsoft.com...
> 67hours for 400millions?
> its really really slow... (around 1600rows/sec)
> are you sure that you use the bulk insert option and make sure you setup
> the batch size value (to 10 000 or something like this)
> your disk subsystem as an impact too, but you should be able to load the
> table in 1 to 2hours. (depends on the size of 1 row)
> also make sure you drop the indexes before the loading process, then
> recreate the indexes. (recreating the indexes will add some processing
> time after the loading, so estimate it between 15min to 1h regarding the
> number of indexes)
> the idea is to cut the big table into small batchs.
> in our developments we reach 200 000rows loaded by second.
> "Mecn" <mecn@.yahoo.com> wrote in message
> news:#bek6QtXIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I have a sql2k table with 400 Million records. All records need to be
>> updated (one field.).
>> 1. What id tested was that DTS transfer(updating the field during the
>> transferring) to a diff table in the same db. It took 67 hours.
>> 2. The second try I did was Select into the the same db. same thing --
>> 70 hours.
>> Is there any faster method taht I could use the accomplish this?
>> Thanks
>>|||yes, fast load is the bulk insert.
but a batch of 400 000 is too large, try to reduce it (to 10 000) and see
the difference.
each batch is a transaction, if the batch is big the transaction will take
more time to be commited.
you have to check the disk, memory & cpu activity during the load to
identify the bottleneck.
also insure that the database recovery is set to "simple", and make sure the
log files are on a separate controller and disks.
and validate that the source query used is correctly optimized.
and if everything is on the same server, try to spread the source database
or files on a different disks then the targeted database / table
If you read & write on the same disks this can cause performance issues.
"Mecn" <mecn@.yahoo.com> wrote in message
news:Okn4KT2XIHA.4272@.TK2MSFTNGP05.phx.gbl...
> Thanks for all the responses.
> 1. You are right I was doing transfer instead of updating the field. I
> think that uase DTS with 400,000 batch size/fast load(same as bulk
> insert?) to a a non-index table or Insert into (bulk insert?) will be fast
> than undate the field. -- 67 Hours....too long.
> Any faster ways?
> Agree?
>
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:FB0540B8-0A9B-4603-AD86-01742FFC7DD7@.microsoft.com...
>> 67hours for 400millions?
>> its really really slow... (around 1600rows/sec)
>> are you sure that you use the bulk insert option and make sure you setup
>> the batch size value (to 10 000 or something like this)
>> your disk subsystem as an impact too, but you should be able to load the
>> table in 1 to 2hours. (depends on the size of 1 row)
>> also make sure you drop the indexes before the loading process, then
>> recreate the indexes. (recreating the indexes will add some processing
>> time after the loading, so estimate it between 15min to 1h regarding the
>> number of indexes)
>> the idea is to cut the big table into small batchs.
>> in our developments we reach 200 000rows loaded by second.
>> "Mecn" <mecn@.yahoo.com> wrote in message
>> news:#bek6QtXIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I have a sql2k table with 400 Million records. All records need to be
>> updated (one field.).
>> 1. What id tested was that DTS transfer(updating the field during the
>> transferring) to a diff table in the same db. It took 67 hours.
>> 2. The second try I did was Select into the the same db. same thing --
>> 70 hours.
>> Is there any faster method taht I could use the accomplish this?
>> Thanks
>>
>|||Hi
SET ROWCOUNT 10000
update_rows:
UPDATE tbakle SET ...
WHERE <condition>
IF @.@.ROWCOUNT > 0 GOTO update_rows
SET ROWCOUNT 0
"Mecn" <mecn@.yahoo.com> wrote in message
news:%23bek6QtXIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a sql2k table with 400 Million records. All records need to be
> updated (one field.).
> 1. What id tested was that DTS transfer(updating the field during the
> transferring) to a diff table in the same db. It took 67 hours.
> 2. The second try I did was Select into the the same db. same thing -- 70
> hours.
> Is there any faster method taht I could use the accomplish this?
> Thanks
>
>

4/7/2003 vs 04/07/2003

Hi,
I have a parameter query in SQL. The Query selects records in a date range based on the parameters.

my problem is that if I put the dates in the format of 4/7/2003 it won't work and I have to insert the date like 04/07/2003.

I don't know how to fix this. Its important since the parameters are coming froman Access ADP front end through the calendar object which saves the date in the 4/7/2003 format.

Thanksplease post an example or your query!|||Thanks for your time Paul.

Here is my query:
-----------------------
SELECT
dbo.tblComIssue.ComID AS ID,
CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) AS Date, ISNULL(dbo.tblContact.FirstName, '') + ' '
+ ISNULL(dbo.tblContact.LastName, '') AS Caller

FROM dbo.tblComIssue LEFT OUTER JOIN
dbo.tblContact ON
dbo.tblComIssue.ContactID = dbo.tblContact.ContactID

WHERE (CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) BETWEEN @.Date1 AND @.Date2)
ORDER BY dbo.tblComIssue.ComDate DESC

-----------
I also tried this instead of the "BETWEEN" function:

WHERE (CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) >= @.Date1) AND (CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) <= @.Date2)
ORDER BY dbo.tblComIssue.ComDate DESC
-----------

The wired part is that I have another query for "Date" not Date Range and it works fine:

SELECT dbo.tblComIssue.ComID, CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) AS Date, ISNULL(dbo.tblContact.FirstName, '')
+ ' ' + ISNULL(dbo.tblContact.LastName, '') AS Caller
FROM dbo.tblComIssue LEFT OUTER JOIN
dbo.tblContact ON dbo.tblComIssue.ContactID = dbo.tblContact.ContactID
WHERE (CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) = @.Date)
ORDER BY dbo.tblComIssue.ComDate DESC

------

Thanks|||It looks like your problem is comparing strings not dates. I would convert @.Date1 & @.Date2 to datetime datatype and compare dates.|||Thanks,
Yes that was the problem.
I used Enterprise manages to define the data types.

Thanks paul